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:
- Modularity: Allows encapsulating logic in a single unit.
- Reusability: Procedures can be reused across multiple applications.
- Performance: Reduces network traffic by executing multiple SQL statements in a single call.
- Security: You can grant users permission to execute a stored procedure without giving them direct access to the underlying tables.