Stored Procedures in PL-SQL

What is a Stored Procedure?

A stored procedure is a named PL/SQL block that is stored in the database and can be executed multiple times. It allows users to encapsulate business logic and execute complex operations efficiently.

Stored procedures are useful for:
✅ Code reusability – Write once, use multiple times.
✅ Performance improvement – Reduces SQL query execution time.
✅ Security – Controls access to data by allowing execution without exposing SQL code.
✅ Reduced network traffic – Multiple SQL statements execute in one call.

---

Creating a Stored Procedure

A stored procedure consists of three main parts:

1. Header – Contains the procedure name and parameters.
2. Declaration Section (Optional) – Defines variables and constants.
3. Execution Section (Mandatory) – Contains the SQL statements.

Basic Syntax

CREATE OR REPLACE PROCEDURE procedure_name 
( parameter1 datatype, parameter2 datatype ) AS 
BEGIN
    -- SQL and PL/SQL statements
END procedure_name;

---

1. Creating a Simple Stored Procedure

Example: Procedure without Parameters

CREATE OR REPLACE PROCEDURE get_student_count AS 
    total_students NUMBER;
BEGIN
    SELECT COUNT(*) INTO total_students FROM Students;
    DBMS_OUTPUT.PUT_LINE('Total Students: ' || total_students);
END get_student_count;

📌 Explanation:

• The procedure get_student_count counts students in the Students table.
• The DBMS_OUTPUT.PUT_LINE statement prints the result.

Executing the Procedure

EXEC get_student_count;

---

2. Stored Procedure with Parameters

Stored procedures can accept input parameters, output parameters, or both.

Syntax for Parameters

CREATE OR REPLACE PROCEDURE procedure_name
( param1 IN datatype, param2 OUT datatype, param3 IN OUT datatype ) AS 
BEGIN
    -- SQL and PL/SQL statements
END;

Parameter Mode	Description
IN	Passes a value into the procedure (default mode).
OUT	Returns a value from the procedure.
IN OUT	Passes a value and returns an updated value.

---

Example: Stored Procedure with IN Parameter

CREATE OR REPLACE PROCEDURE get_student_details (p_id IN NUMBER) AS 
    v_name Students.Name%TYPE;
BEGIN
    SELECT Name INTO v_name FROM Students WHERE StudentID = p_id;
    DBMS_OUTPUT.PUT_LINE('Student Name: ' || v_name);
END get_student_details;

📌 Execution:

EXEC get_student_details(101);


📌 Explanation:

• The p_id parameter receives the StudentID as input.
• The procedure retrieves the corresponding Name and displays it.

---

Example: Stored Procedure with IN and OUT Parameters

CREATE OR REPLACE PROCEDURE get_student_name 
(p_id IN NUMBER, p_name OUT VARCHAR2) AS
BEGIN
    SELECT Name INTO p_name FROM Students WHERE StudentID = p_id;
END get_student_name;

📌 Executing the Procedure with an OUT Parameter

DECLARE
student_name VARCHAR2(50);
BEGIN
get_student_name(101, student_name);
DBMS_OUTPUT.PUT_LINE('Student Name: ' || student_name);
END;


📌 Explanation:

• The procedure takes p_id as input.
• It fetches the Student Name into p_name, which is returned as output.

---

3. Stored Procedure with IN OUT Parameter

IN OUT parameters allow modifying and returning the value.

Example: Updating Student Age

CREATE OR REPLACE PROCEDURE update_student_age
(p_id IN NUMBER, p_age IN OUT NUMBER) AS
BEGIN
    UPDATE Students SET Age = p_age WHERE StudentID = p_id;
    p_age := p_age + 1; -- Increment age for confirmation
END update_student_age;

📌 Executing the Procedure

DECLARE
age NUMBER := 20;
BEGIN
update_student_age(101, age);
DBMS_OUTPUT.PUT_LINE('Updated Age: ' || age);
END;


📌 Explanation:

• The procedure updates the student’s age.
• The IN OUT parameter p_age is modified and returned.

---

4. Stored Procedure with Exception Handling

PL/SQL handles errors using EXCEPTION.

Example: Handling NO_DATA_FOUND

CREATE OR REPLACE PROCEDURE get_student_age (p_id IN NUMBER) AS 
    v_age Students.Age%TYPE;
BEGIN
    SELECT Age INTO v_age FROM Students WHERE StudentID = p_id;
    DBMS_OUTPUT.PUT_LINE('Student Age: ' || v_age);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Error: Student not found.');
END get_student_age;

📌 Explanation:

• If the StudentID does not exist, an error is handled instead of crashing.

---

5. Dropping a Stored Procedure

If you need to remove a procedure, use:

DROP PROCEDURE procedure_name;


📌 Example:

DROP PROCEDURE get_student_details;


---

6. Stored Procedures vs Functions

Feature	Stored Procedure	Function
Purpose	Executes business logic	Returns a value
Returns a Value?	No (uses OUT parameters instead)	Yes (must return a value)
Used in SQL Queries?	No	Yes
Exception Handling?	Yes	Yes
Performance	Faster for batch operations	Ideal for calculations

---

7. Advantages of Stored Procedures

✅ Better Performance – Executes faster as it reduces SQL query parsing.
✅ Code Reusability – Write once, call multiple times.
✅ Security – Restricts direct table access.
✅ Reduced Network Traffic – Multiple SQL operations in a single call.
✅ Exception Handling – Manages errors effectively.

---

8. Calling Stored Procedures from Other PL/SQL Blocks

Stored procedures can be called from other PL/SQL blocks including triggers, functions, and anonymous blocks.

Example: Calling a Procedure Inside Another Procedure

CREATE OR REPLACE PROCEDURE display_student_info(p_id IN NUMBER) AS
BEGIN
    get_student_details(p_id);
END;

📌 Execution:

EXEC display_student_info(101);


📌 Explanation:

• The procedure display_student_info calls another procedure get_student_details.

---

Conclusion

✅ Stored procedures simplify database operations, improve performance, and enhance security.
✅ They allow parameter passing, error handling, and modular programming.
✅ They are widely used in enterprise applications for batch processing, automation, and data security.