Procedures in plsql
Procedures In pl sql
A Procedure is a subprogram unit that consists of a group of PL/SQL statements. Each procedure in Oracle has its own unique name by which it can be referred. This subprogram unit is stored as a database object. Below are the characteristics of this subprogram unit. understand the concepts on sql through sql online training
- Procedures are simple blocks of a program which can be stored in the database.
- All these procedures can be made by referring to their name, and execute with the PL/SQL statements.
- Procedures are mainly used to execute a process in oracle
- Procedures can also have a nested blocks, or they can be defined and nested inside the other blocks or packages.
- Procedures contains declaration part execution part, exception handling part
- The values can be passed into the procedure or fetched from the procedure through parameters.
- These parameters should be included in the calling statement.
- Procedure can have a RETURN statement to return the control to the calling block, but it cannot return any values through the RETURN statement.
- Procedures cannot be called directly from SELECT statements. They can be called from another block or through EXEC keyword. you can learn on more through pl sql online training
Synax For Procedures on pl sql
Example For procudere
CREATE OR REPLACE PROCEDURE greetings
AS
BEGIN
Get Veeereshkumar’s stories in your inbox
Join Medium for free to get updates from this writer.
dbms_output.put_line(‘Hello World!’);
END;
/
PL/SQL procedure header
Procedure mainly begins with a header which specifies its name and an optional parameter list.
Each parameter can be in either IN, OUT, or INOUT mode. The parameter mode specifies whether a parameter can be read from or write to.
IN
An IN parameter is read-only. You can reference an IN parameter inside a procedure, but you cannot change its value. Oracle uses IN as the default mode. It means that if you don’t specify the mode for a parameter explicitly, Oracle will use the IN mode.
OUT
An OUT parameter is writable. Typically, you set a returned value for the OUT parameter and return it to the calling program. Note that a procedure ignores the value that you supply for an OUT parameter.
INOUT
An INOUT parameter is both readable and writable. The procedure can read and modify it.
Note that OR REPLACE option allows you to overwrite the current procedure with the new code.
PL/SQL procedure body
Similar to an anonymous block, the procedure body has three parts. The executable part is mandatory whereas the declarative and exception-handling parts are optional. The executable part must contain at least one executable statement.
Muhammed Hilmi Koca Alex José Silva NERMİN PARLAK Manuri Amaya Perera
