PL/SQL Procedure

A subprogram is a module or a small unit/part of a program that performs a particular task. In modular programming, a program can optionally consist of multiple subprograms.

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

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

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

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

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

Drop Procedure [Procedure-Name];