⚙️ Module 10: Procedures, Functions & Packages

CREATE [OR REPLACE] PROCEDURE procedure_name
  (parameter1 IN/OUT datatype,
   parameter2 IN/OUT datatype)
AS
  DECLARE
    -- Local variables
BEGIN
  -- Procedure logic
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END procedure_name;
/

CREATE OR REPLACE PROCEDURE hire_employee
  (p_name IN VARCHAR2,
   p_salary IN NUMBER,
   p_emp_id OUT NUMBER)
AS
BEGIN
  INSERT INTO employees(first_name, salary)
  VALUES (p_name, p_salary)
  RETURNING employee_id INTO p_emp_id;
  
  COMMIT;
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    DBMS_OUTPUT.PUT_LINE('Employee already exists');
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END hire_employee;
/

-- Execute procedure
DECLARE
  v_emp_id NUMBER;
BEGIN
  hire_employee('John Doe', 75000, v_emp_id);
  DBMS_OUTPUT.PUT_LINE('New Employee ID: ' || v_emp_id);
END;
/

CREATE [OR REPLACE] FUNCTION function_name
  (parameter1 IN datatype)
  RETURN return_datatype
AS
  v_result return_datatype;
BEGIN
  -- Function logic
  RETURN v_result;
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END function_name;
/

CREATE OR REPLACE FUNCTION calc_bonus
  (p_salary IN NUMBER)
  RETURN NUMBER
AS
BEGIN
  IF p_salary >= 100000 THEN
    RETURN p_salary * 0.15;
  ELSIF p_salary >= 75000 THEN
    RETURN p_salary * 0.10;
  ELSE
    RETURN p_salary * 0.05;
  END IF;
END calc_bonus;
/

-- Use function in SELECT
SELECT employee_id, first_name, salary, calc_bonus(salary) AS bonus
FROM employees;

-- Package Specification
CREATE OR REPLACE PACKAGE emp_mgmt_pkg AS
  -- Public procedures and functions
  PROCEDURE hire_employee(p_name VARCHAR2, p_salary NUMBER);
  FUNCTION get_emp_salary(p_emp_id NUMBER) RETURN NUMBER;
  PROCEDURE terminate_employee(p_emp_id NUMBER);
END emp_mgmt_pkg;
/

-- Package Body
CREATE OR REPLACE PACKAGE BODY emp_mgmt_pkg AS
  -- Private variables and procedures
  v_package_version VARCHAR2(10) := '1.0';
  
  PROCEDURE hire_employee(p_name VARCHAR2, p_salary NUMBER) AS
  BEGIN
    INSERT INTO employees(first_name, salary)
    VALUES (p_name, p_salary);
    COMMIT;
  END hire_employee;
  
  FUNCTION get_emp_salary(p_emp_id NUMBER) RETURN NUMBER AS
    v_salary NUMBER;
  BEGIN
    SELECT salary INTO v_salary
    FROM employees
    WHERE employee_id = p_emp_id;
    RETURN v_salary;
  END get_emp_salary;
  
END emp_mgmt_pkg;
/