Note: A schema level subprogram is a standalone subprogram.
PL/SQL Procedure
A procedure is a group of PL/SQL statements that you can call by its name. These sub-programs can not return a value directly and are mainly used to perform a particular task. Stored procedures offer useful in the areas of memory allocation, development, performance, security and integrity.
Creating a Procedure
A procedure is created with the CREATE OR REPLACE PROCEDURE statement i.e., if you have previously created a procedure by name ‘A’, you can replace it with a new procedure.
Syntax
|
1 2 3 4 5 6 |
Create or Replace Procedure Procedure_Name [(Parameter_Name [IN | OUT | IN OUT ] Type […])] [IS | AS] Begin Procedure_Body End Procedure_Name; |
- Procedure_Name is used to specify the name of the procedure.
- CREATE keyword is used to develop a new procedure and [OR REPLACE] option allows us to modify an existing procedure.
- The optional parameter list contains Name and Types of Parameters.
- IN represents that argument value will be passed from outside the Procedure. It is a read-only parameter. Parameters are passed by reference. Inside the procedure or a sub-program, an IN Parameter acts as a constant. It cannot be assigned a value. It is the default mode of parameter passing.
- An OUT parameter returns a value to the calling program. OUT represents that this parameter will be used to return a value outside of the procedure. The actual parameter must be variable and it is passed by value. Inside the subprogram, an OUT parameter acts like a variable.
- An IN OUT parameter passes an initial value to the sub-program and returns an updated value to the caller. We can read and write values using this parameter.
- The Executable part is included in the Procedure Body.
Example
|
1 2 3 4 5 6 |
create or replace procedure HelloWorld as begin dbms_output.put_line('Hello World'); end HelloWorld; / |
Output
Execute a Procedure
A standalone procedure can be called by using the EXECUTE keyword or by calling or mentioning the procedure by its name from a PL/SQL block.
Syntax
|
1 |
Execute [Procedure-Name]; |
Alter a Procedure
The ALTER statement is used to explicitly recompile or re-design a standalone stored procedure. This is used in case you need to alter/change the values or elements previously created in the procedure or if you need to add new statements in a procedure. This also prevents the associated run-time compilation errors and performance overhead.
Syntax
|
1 |
Alter Procedure [Procedure-Name]; |
Delete or Drop a Procedure
The DROP keyword is used in PL/SQL to delete a stand-alone stored procedure from a previously created database.
Note: Do not use this statement to delete a procedure that is part of any package that is not of any use to you. Instead, drop the entire package using the DROP PACKAGE statement
Syntax
|
1 |
Drop Procedure [Procedure-Name]; |
This was all about PL/SQL procedures, if you noticed anything incorrect of missing in above tutorial then please mention it by commenting below. You can also ask your doubts.
