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

  • 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


Output


PLSQL Procedure

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

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

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

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.

Leave a Reply

Your email address will not be published. Required fields are marked *