Stored Procedures and Functions

Stored procedures and functions in PL/SQL are reusable blocks of code that encapsulate business logic. They allow developers to perform specific tasks, enhance code reusability, and improve application performance by executing logic directly on the database server.

1. What are Procedures and Functions?

• Stored Procedure:

  • A named PL/SQL block that performs a specific task.
  • Does not necessarily return a value.
  • Often used to execute a series of SQL statements or operations.

• Function:

  • A named PL/SQL block that always returns a value.
  • Primarily used for computations and returning a single result.

2. Differences Between Procedures and Functions

3. Creating and Executing Procedures

Syntax:

plsql
Copy code
CREATE OR REPLACE PROCEDURE procedure_name (
   parameter_name [IN | OUT | IN OUT] datatype,
   ...
) IS
   -- Declaration section
BEGIN
   -- Execution section
   ...
EXCEPTION
   -- Exception handling section
   ...
END;
/

Example: Creating a Procedure to Insert Employee Data

plsql
Copy code
CREATE OR REPLACE PROCEDURE insert_employee (
   p_emp_id IN NUMBER,
   p_first_name IN VARCHAR2,
   p_last_name IN VARCHAR2,
   p_salary IN NUMBER
) IS
BEGIN
   INSERT INTO employees (employee_id, first_name, last_name, salary)
   VALUES (p_emp_id, p_first_name, p_last_name, p_salary);
   DBMS_OUTPUT.PUT_LINE('Employee inserted successfully.');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM);
END;
/

Executing the Procedure:

plsql
Copy code
BEGIN
   insert_employee(101, 'John', 'Doe', 50000);
END;
/

4. Creating and Executing Functions

Syntax:

plsql
Copy code
CREATE OR REPLACE FUNCTION function_name (
   parameter_name [IN | OUT | IN OUT] datatype,
   ...
) RETURN return_datatype IS
   -- Declaration section
BEGIN
   -- Execution section
   ...
   RETURN value; -- Return statement
EXCEPTION
   -- Exception handling section
   ...
END;
/

Example: Creating a Function to Calculate Bonus

plsql
Copy code
CREATE OR REPLACE FUNCTION calculate_bonus (
   p_salary IN NUMBER
) RETURN NUMBER IS
   v_bonus NUMBER;
BEGIN
   v_bonus := p_salary * 0.1; -- 10% bonus
   RETURN v_bonus;
END;
/

Executing the Function:

plsql
Copy code
DECLARE
   v_bonus NUMBER;
BEGIN
   v_bonus := calculate_bonus(50000);
   DBMS_OUTPUT.PUT_LINE('Calculated Bonus: ' || v_bonus);
END;
/

Using a Function in a SQL Query:

plsql
Copy code
SELECT employee_id, first_name, calculate_bonus(salary) AS bonus
FROM employees;

5. Parameter Modes: IN, OUT, IN OUT

Examples:

1. IN Parameter:

   plsql
   Copy code
   CREATE OR REPLACE PROCEDURE greet_user (p_name IN VARCHAR2) IS
   BEGIN
      DBMS_OUTPUT.PUT_LINE('Hello, ' || p_name || '!');
   END;
   /
   

   Execution:

   plsql
   Copy code
   BEGIN
      greet_user('Alice');
   END;
   /
   

2. OUT Parameter:

   plsql
   Copy code
   CREATE OR REPLACE PROCEDURE get_employee_salary (
      p_emp_id IN NUMBER,
      p_salary OUT NUMBER
   ) IS
   BEGIN
      SELECT salary INTO p_salary FROM employees WHERE employee_id = p_emp_id;
   END;
   /
   

   Execution:

   plsql
   Copy code
   DECLARE
      v_salary NUMBER;
   BEGIN
      get_employee_salary(101, v_salary);
      DBMS_OUTPUT.PUT_LINE('Employee Salary: ' || v_salary);
   END;
   /
   

3. IN OUT Parameter:

   plsql
   Copy code
   CREATE OR REPLACE PROCEDURE update_salary (
      p_emp_id IN NUMBER,
      p_salary IN OUT NUMBER
   ) IS
   BEGIN
      p_salary := p_salary * 1.1; -- Increase salary by 10%
      UPDATE employees SET salary = p_salary WHERE employee_id = p_emp_id;
   END;
   /
   

   Execution:

   plsql
   Copy code
   DECLARE
      v_salary NUMBER := 50000;
   BEGIN
      update_salary(101, v_salary);
      DBMS_OUTPUT.PUT_LINE('Updated Salary: ' || v_salary);
   END;
   /
   

6. Example: Procedure for Inserting Employee Data

Procedure Code:

plsql
Copy code
CREATE OR REPLACE PROCEDURE insert_employee_data (
   p_emp_id IN NUMBER,
   p_first_name IN VARCHAR2,
   p_last_name IN VARCHAR2,
   p_salary IN NUMBER,
   p_department_id IN NUMBER
) IS
BEGIN
   INSERT INTO employees (employee_id, first_name, last_name, salary, department_id)
   VALUES (p_emp_id, p_first_name, p_last_name, p_salary, p_department_id);

   DBMS_OUTPUT.PUT_LINE('Employee inserted successfully: ' || p_first_name || ' ' || p_last_name);
EXCEPTION
   WHEN DUP_VAL_ON_INDEX THEN
      DBMS_OUTPUT.PUT_LINE('Error: Duplicate Employee ID.');
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM);
END;
/

Execution:

plsql
Copy code
BEGIN
   insert_employee_data(102, 'Jane', 'Smith', 60000, 10);
END;
/

Summary

Mastering procedures and functions helps in creating modular, efficient, and reusable code for complex database operations.