Stored procedure in PLSQL

A stored procedure in PL/SQL (Procedural Language/SQL) is a named block of code that can be stored in the database and executed later. Stored procedures encapsulate a sequence of SQL statements and can include conditional logic, loops, and error handling. They are often used for repetitive tasks, data manipulation, and to encapsulate business logic.

Key Features of Stored Procedures

1. Modularity: Stored procedures allow you to encapsulate business logic, making it easier to maintain and reuse code.

2. Performance: They can improve performance because the SQL execution plan can be reused, and the procedure is precompiled.

3. Security: By using stored procedures, you can restrict direct access to the underlying tables and enforce business rules.

4. Transaction Control: Stored procedures can manage transactions, allowing for complex operations that require commit and rollback.

5. Parameters: Stored procedures can accept parameters, enabling you to pass values when calling them.

Syntax of a Stored Procedure

Here is a basic syntax structure for creating a stored procedure in PL/SQL:

CREATE OR REPLACE PROCEDURE procedure_name
[ (parameter1 datatype1 [IN | OUT | IN OUT], parameter2 datatype2 [IN | OUT | IN OUT], ...) ]
IS
-- Declaration Section
BEGIN
-- Executable Section
-- SQL statements, control structures, etc.
EXCEPTION
-- Exception handling section
END procedure_name;

Components

1. Procedure Name: The name of the procedure.

2. Parameters: Optional parameters that can be passed to the procedure. Each parameter has a name, data type, and a mode (IN, OUT, IN OUT).

3. IS: Begins the declaration section.

4. Declaration Section: Here, you declare local variables, cursors, and other PL/SQL constructs.

5. Executable Section: The core logic of the procedure, where SQL statements and PL/SQL code are executed.

6. Exception Handling Section: This is where you handle any exceptions or errors that may occur during execution.

Example of a Stored Procedure

Here’s an example of a simple stored procedure that inserts a new employee record into an employees table.

Table Structure

Assume we have a table named employees defined as follows:

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

Stored Procedure to Insert Employee

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

COMMIT; -- Commit the transaction

EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
DBMS_OUTPUT.PUT_LINE('Error: Employee ID already exists.');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END add_employee;

Explanation of the Example

1. Parameters: The procedure add_employee takes five parameters corresponding to the columns of the employees table.

p_employee_id: ID of the employee (input).

p_first_name: First name of the employee (input).

p_last_name: Last name of the employee (input).

p_email: Email of the employee (input).

p_hire_date: Date of hire (input).

1. Executable Section: The INSERT INTO statement adds a new record to the employees table with the provided parameter values.

2. COMMIT: This statement commits the transaction, ensuring that the changes are saved to the database.

3. Exception Handling:

If an attempt is made to insert an employee with an employee_id that already exists, a DUP_VAL_ON_INDEX exception is raised, and a message is printed.

The OTHERS exception handles any other errors that may arise during execution.

Calling the Stored Procedure

To execute the stored procedure, you can use an anonymous PL/SQL block or directly call it from SQL:

BEGIN
add_employee(101, 'John', 'Doe', '[e-mail]', TO_DATE('2023-01-01', 'YYYY-MM-DD'));
END;

Conclusion

Stored procedures in PL/SQL are powerful tools for encapsulating business logic, improving performance, and ensuring security in database applications. They allow developers to write reusable code that can manage complex tasks, making it easier to maintain and scale database systems.