pl-sql
  1. pl-sql-procedure
PreviousNext

Procedure - (PL/SQL Procedure)

In PL/SQL, a procedure is a reusable program unit that performs a specific task. A procedure may or may not return any value. It is typically used to encapsulate a set of SQL statements that are executed together as a single unit. Procedures can be created and stored in an Oracle database for later use.

Syntax

The syntax for creating a procedure in PL/SQL is as follows:

CREATE [OR REPLACE] PROCEDURE procedure_name [(parameter1 [IN | OUT | IN OUT] datatype [, parameter2 [IN | OUT | IN OUT] datatype, ...])]
IS
    -- Declarations
BEGIN
    -- Statements
END [procedure_name];

Here, procedure_name is the name of the procedure that is being created. The OR REPLACE clause allows an existing procedure with the same name to be replaced. parameter1, parameter2, etc are optional input and output parameters for the procedure. datatype specifies the datatype of the parameter.

Example

Here is an example of a simple PL/SQL procedure that calculates the factorial of a number:

CREATE OR REPLACE PROCEDURE factorial(n IN NUMBER, result OUT NUMBER) IS
BEGIN
    result := 1;

    FOR i IN 1..n LOOP
        result := result * i;
    END LOOP;
END factorial;

Explanation

In this example, we have created a procedure called factorial that takes an input parameter n and an output parameter result. Inside the procedure, we have initialized result to 1 and used a loop to iterate from 1 to n, multiplying result by the loop variable i. At the end of the loop, the final value of result is returned as the output value.

Use

Procedures are commonly used in PL/SQL to encapsulate SQL statements that are executed together as a single unit. They can be used to simplify complex SQL operations and to encapsulate business logic that is reused across multiple applications.

Important Points

  • Procedures are reusable program units that perform a specific task.
  • They may or may not return a value.
  • They are typically used to encapsulate SQL statements that are executed together as a single unit.
  • They can be stored in an Oracle database for later use.

Summary

In summary, a procedure is a reusable program unit in PL/SQL that can encapsulate a set of SQL statements that are executed together as a single unit. They are typically used to simplify complex SQL operations and to encapsulate business logic that is reused across multiple applications. Procedures can be stored in an Oracle database for later use.

GOTOFunction
Published on: