Procedure in PLSQL

Procedure in PLSQL

In PL/SQL, a procedure is a subprogram that performs a specific task and can be called to execute its statements. Procedures can take parameters (inputs and outputs) and are typically used to encapsulate and organize code to perform a specific action, but they do not return values directly like functions.

---

Here’s a basic overview of a procedure:
Syntax:

CREATE OR REPLACE PROCEDURE procedure_name 
(
   parameter_name [IN | OUT | IN OUT] datatype
) 
IS
   -- Declaration section
BEGIN
   -- Executable section
   -- Code to perform some action
END procedure_name;

• IN: Used to pass values into the procedure.
• OUT: Used to return values from the procedure.
• IN OUT: Used to pass values into the procedure and return updated values.

---

Benefits of Using Procedures:

Code Reusability: Procedures can be reused in multiple places.

Encapsulation: Procedures help in organizing code by encapsulating the logic into manageable chunks.

Maintainability: Easier to maintain and update, as changing the procedure updates the logic everywhere it's called.

---

Let's create a procedure that inserts data into an employees table. We'll assume the following structure for the table:

Employees Table:

CREATE TABLE employees (
   emp_id   NUMBER(5) PRIMARY KEY,
   emp_name VARCHAR2(50),
   salary   NUMBER(10, 2),
   dept_id  NUMBER(5)
);

---

Procedure to Insert Data:

This procedure will take in the employee's ID, name, salary, and department ID as parameters and insert them into the employees table.

Example of the Procedure:

CREATE OR REPLACE PROCEDURE add_employee (
   p_emp_id   IN NUMBER,
   p_emp_name IN VARCHAR2,
   p_salary   IN NUMBER,
   p_dept_id  IN NUMBER
)
IS
BEGIN
   -- Check if employee ID already exists
   IF NOT EXISTS (SELECT 1 FROM employees WHERE emp_id = p_emp_id) THEN
      -- Insert new employee record
      INSERT INTO employees (emp_id, emp_name, salary, dept_id)
      VALUES (p_emp_id, p_emp_name, p_salary, p_dept_id);

      -- Commit the transaction
      COMMIT;
   ELSE
      -- Raise an error if the employee ID already exists
      RAISE_APPLICATION_ERROR(-20002, 'Employee ID already exists!');
   END IF;
END add_employee;

Explanation:

Procedure Name: add_employee

Parameters:
p_emp_id: Employee's ID.
p_emp_name: Employee's name.
p_salary: Employee's salary.
p_dept_id: Department ID the employee belongs to.

Logic:

• The procedure first checks if the emp_id already exists in the employees table.
• If the ID does not exist, it inserts a new employee record.
• If the ID already exists, it raises an error.

---

Calling the Procedure:

To add an employee to the table, you can call the procedure as follows:

BEGIN
   add_employee(101, 'John Doe', 60000, 10);
END;

This will insert a new employee record with:

Employee ID: 101
Employee Name: John Doe
Salary: 60000
Department ID: 10

---

Handling Duplicate Employee ID:

If you try to insert another employee with the same ID (e.g., 101), the procedure will raise the following error:

ORA-20002: Employee ID already exists!

This ensures that no duplicate employee records are inserted into the table.