Stored Procedure in PL/SQL

Stored Procedure

A stored procedure in PL/SQL (Procedural Language/Structured Query Language) is a set of SQL statements that can be executed as a single unit. Stored procedures allow you to encapsulate business logic and perform operations on the database, such as querying, inserting, updating, and deleting data.

Creating a Stored Procedure

Here’s a basic syntax for creating a stored procedure in PL/SQL:

CREATE OR REPLACE PROCEDURE procedure_name
    [ (parameter1 [IN | OUT | IN OUT datatype], 
       parameter2 [IN | OUT | IN OUT datatype], ...) ]
IS
    -- Declare variables
    variable_name datatype;

BEGIN
    -- Procedure logic
    -- SQL statements and PL/SQL code
END procedure_name;

---

Example: A Simple Stored Procedure

Let’s create a stored procedure that inserts a new employee into an employees table:

CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    hire_date DATE
);

CREATE OR REPLACE PROCEDURE add_employee (
    p_employee_id IN NUMBER,
    p_first_name IN VARCHAR2,
    p_last_name IN VARCHAR2,
    p_hire_date IN DATE
) IS
BEGIN
    INSERT INTO employees (employee_id, first_name, last_name, hire_date)
    VALUES (p_employee_id, p_first_name, p_last_name, p_hire_date);

    COMMIT; -- Commit the transaction
END add_employee;

Explanation:

1. Table Creation: We first create a table named employees to store employee information.

2. Procedure Declaration:

• The procedure is named add_employee.
• It has four parameters: p_employee_id, p_first_name, p_last_name, and p_hire_date, all defined as IN parameters.

3. Procedure Logic:

• The procedure contains an INSERT statement to add a new record to the employees table.
• A COMMIT statement is included to save the changes to the database.

---

Executing the Stored Procedure

You can execute the stored procedure using the following SQL block:

BEGIN
    add_employee(1, 'John', 'Doe', TO_DATE('2023-09-27', 'YYYY-MM-DD'));
END;

---

Benefits of Using Stored Procedures:

1. Modularity: Allows encapsulating logic in a single unit.
2. Reusability: Procedures can be reused across multiple applications.
3. Performance: Reduces network traffic by executing multiple SQL statements in a single call.
4. Security: You can grant users permission to execute a stored procedure without giving them direct access to the underlying tables.