Procedures in PL/SQL

PL/SQL provides procedures as reusable code blocks that perform specific actions or business logic within a database environment.

• They are reusable programs that execute specific tasks.
• They consist of a procedure header (name and parameters).
• They include a procedure body with executable statements.
• They help implement business logic efficiently.

The procedure contains two parts:

Procedure Header

• The procedure header includes the procedure name and optional parameter list.
• It is the first part of the procedure and specifies the name and parameters

Procedure Body

• The procedure body contains the executable statements that implement the specific business logic.
• It can include declarative statements, executable statements, and exception-handling statements

Create Procedures in PL/SQL

To create a procedure in PL/SQL, use the CREATE PROCEDURE command:

Syntax:

CREATE OR REPLACE PROCEDURE procedure_name (
    param1 IN NUMBER,
    param2 IN VARCHAR2,
    result OUT VARCHAR2
)
IS
BEGIN
    -- executable statements
END;
/

Note: Procedures in PL/SQL without parameters are written without parentheses after the procedure name

Example: In this example, we will create a procedure in PL/SQL

CREATE PROCEDURE GetStudentDetails (
    p_StudentID IN NUMBER
)
IS
    v_FirstName Students.FirstName%TYPE;
    v_LastName  Students.LastName%TYPE;
    v_BirthDate Students.BirthDate%TYPE;
    v_City      Students.City%TYPE;
    v_Country   Students.Country%TYPE;
BEGIN
    SELECT FirstName, LastName, BirthDate, City, Country
    INTO v_FirstName, v_LastName, v_BirthDate, v_City, v_Country
    FROM Students
    WHERE StudentID = p_StudentID;

    DBMS_OUTPUT.PUT_LINE(v_FirstName || ' ' || v_LastName);
END;
/

• Accepts an input parameter p_StudentID.
• Retrieves FirstName, LastName, BirthDate, City, and Country using SELECT INTO.
• Stores the result in local variables.
• Displays output using DBMS_OUTPUT.PUT_LINE

Parameters in Procedures

In PL/SQL, parameters are used to pass values into procedures. There are three types of parameters used in procedures:

IN parameters

• Used to pass values into the procedure
• Read-only inside the procedure
• Can be a variable, literal value, or expression in the calling statement.

OUT parameters

• Used to return values from the procedure to the calling program
• Read-write inside the procedure
• Must be a variable in the calling statement to hold the returned value

IN OUT parameters

• Used for both passing values into and returning values from the procedure
• Read-write inside the procedure
• Must be a variable in the calling statement

Modify Procedures in PL/SQL

To modify an existing procedures in PL/SQL use the ALTER PROCEDURE command:

Syntax

CREATE OR REPLACE PROCEDURE procedure_name (
    param1 IN NUMBER,
    param2 IN VARCHAR2
)
IS
BEGIN
    -- Query
END;
/

Example: In this example, we will modify a procedure in PL/SQL:

CREATE OR REPLACE PROCEDURE GetStudentDetails (
    p_StudentID IN NUMBER
)
IS
    v_FirstName Students.FirstName%TYPE;
    v_LastName  Students.LastName%TYPE;
    v_City      Students.City%TYPE;
BEGIN
    SELECT FirstName, LastName, City
    INTO v_FirstName, v_LastName, v_City
    FROM Students
    WHERE StudentID = p_StudentID;

    DBMS_OUTPUT.PUT_LINE(v_FirstName || ' ' || v_LastName || ' - ' || v_City);
END;
/

• Modifies the procedure using CREATE OR REPLACE PROCEDURE.
• Accepts an input parameter p_StudentID.
• Retrieves FirstName, LastName, and City using SELECT INTO.
• Displays output using DBMS_OUTPUT.PUT_LINE.

Drop Procedure in PL/SQL

To drop a procedure in PL/SQL use the DROP PROCEDURE command

Syntax:

DROP PROCEDURE procedure_name 

PL/SQL DROP PROCEDURE Example

In this example, we will delete a procedure in PL/SQL

DROP PROCEDURE GetStudentDetails

Advantages of Procedures

• Improve application performance as procedures are precompiled and executed efficiently.
• Reduce network traffic since SQL statements are stored in the database and reused instead of being sent repeatedly.
• Promote code reusability, similar to functions and methods in languages like C, C++, and Java.

Disadvantages of Procedures

• Stored procedures can increase memory usage, so the database administrator should define an appropriate limit based on application needs.
• Debugging stored procedures can be limited or complex, especially in systems like MySQL.