PolarDB:CREATE PACKAGE BODY

Syntax

CREATE [ OR REPLACE ] PACKAGE BODY <package_name>
{ IS | AS }
  [ declaration; ] | [ forward_declaration ] [, ...]
  [ { PROCEDURE <proc_name>
      [ (<argname> [ IN | IN OUT | OUT ] <argtype> [ DEFAULT <value> ] [, ...]) ]
      [ STRICT ]
      [ LEAKPROOF ]
      [ PARALLEL { UNSAFE | RESTRICTED | SAFE } ]
      [ COST <execution_cost> ]
      [ ROWS <result_rows> ]
      [ SET <config_param> { TO <value> | = <value> | FROM CURRENT } ]
    { IS | AS }
        <program_body>
      END [ <proc_name> ];
    |
      FUNCTION <func_name>
      [ (<argname> [ IN | IN OUT | OUT ] <argtype> [ DEFAULT <value> ] [, ...]) ]
      RETURN <rettype> [ DETERMINISTIC ]
      [ STRICT ]
      [ LEAKPROOF ]
      [ PARALLEL { UNSAFE | RESTRICTED | SAFE } ]
      [ COST <execution_cost> ]
      [ ROWS <result_rows> ]
      [ SET <config_param> { TO <value> | = <value> | FROM CURRENT } ]
    { IS | AS }
        <program_body>
      END [ <func_name> ];
    }
  ] [, ...]
  [ BEGIN
      <statement>; [, ...] ]
  END [ <name> ]

Where forward_declaration :=

[ { PROCEDURE <proc_name>
  [ (<argname> [ IN | IN OUT | OUT ] <argtype> [ DEFAULT <value> ] [, ...])
  ] ;
|
    FUNCTION <func_name>
  [ (<argname> [ IN | IN OUT | OUT ] <argtype> [ DEFAULT <value> ] [, ...])
  ]
  RETURN <rettype> [ DETERMINISTIC ]; }]

Parameters

Example

The following example creates a complete package for employee management, covering the full workflow: create a table, define the package specification, implement the package body, and use the package.

Step 1: Create the employees table.

CREATE TABLE employees (
    emp_id     NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name  VARCHAR2(50),
    salary     NUMBER,
    department VARCHAR2(50)
);

Step 2: Create the package specification.

The package specification declares the public constant, procedures, and functions. Anything declared here is accessible to callers.

CREATE OR REPLACE PACKAGE Employee_Pkg AS
    -- Public constant
    MAX_SALARY CONSTANT NUMBER := 100000;

    -- Procedure declarations
    PROCEDURE Add_Employee(
        p_emp_id     IN NUMBER,
        p_first_name IN VARCHAR2,
        p_last_name  IN VARCHAR2,
        p_salary     IN NUMBER
    );

    PROCEDURE Update_Salary(
        p_emp_id     IN NUMBER,
        p_new_salary IN NUMBER
    );

    -- Function declarations
    FUNCTION Get_Employee_Name(
        p_emp_id IN NUMBER
    ) RETURN VARCHAR2;

    FUNCTION Get_Employee_Department(
        p_emp_id IN NUMBER
    ) RETURN VARCHAR2;
END Employee_Pkg;

Step 3: Create the package body.

The package body implements all subprograms declared in the specification. It also defines g_total_employees as a private variable — visible only inside the package body.

CREATE OR REPLACE PACKAGE BODY Employee_Pkg AS

    -- Private variable (not accessible from outside the package)
    g_total_employees NUMBER := 0;

    -- Procedure implementations
    PROCEDURE Add_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 (emp_id, first_name, last_name, salary)
        VALUES (p_emp_id, p_first_name, p_last_name, p_salary);
        g_total_employees := g_total_employees + 1;
    END Add_Employee;

    PROCEDURE Update_Salary(
        p_emp_id     IN NUMBER,
        p_new_salary IN NUMBER
    ) IS
    BEGIN
        IF p_new_salary > MAX_SALARY THEN
            RAISE_APPLICATION_ERROR(-20001, 'Salary exceeds maximum allowed.');
        END IF;
        UPDATE employees
        SET salary = p_new_salary
        WHERE emp_id = p_emp_id;
    END Update_Salary;

    -- Function implementations
    FUNCTION Get_Employee_Name(
        p_emp_id IN NUMBER
    ) RETURN VARCHAR2 IS
        v_name VARCHAR2(100);
    BEGIN
        SELECT first_name || ' ' || last_name
        INTO v_name
        FROM employees
        WHERE emp_id = p_emp_id;
        RETURN v_name;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RETURN 'Employee Not Found';
    END Get_Employee_Name;

    FUNCTION Get_Employee_Department(
        p_emp_id IN NUMBER
    ) RETURN VARCHAR2 IS
        v_department VARCHAR2(100);
    BEGIN
        SELECT department
        INTO v_department
        FROM employees
        WHERE emp_id = p_emp_id;
        RETURN v_department;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            RETURN 'Not Assigned';
    END Get_Employee_Department;

END Employee_Pkg;

Step 4: Use the package.

Call the public procedures and functions using package_name.subprogram_name notation.

DECLARE
    v_name       VARCHAR2(100);
    v_department VARCHAR2(100);
BEGIN
    Employee_Pkg.Add_Employee(
        p_emp_id     => 1001,
        p_first_name => 'Jane',
        p_last_name  => 'Smith',
        p_salary     => 60000
    );

    Employee_Pkg.Add_Employee(
        p_emp_id     => 1002,
        p_first_name => 'John',
        p_last_name  => 'Doe',
        p_salary     => 55000
    );

    Employee_Pkg.Update_Salary(
        p_emp_id     => 1001,
        p_new_salary => 65000
    );

    v_name := Employee_Pkg.Get_Employee_Name(1001);
    v_department := Employee_Pkg.Get_Employee_Department(1001);
    DBMS_OUTPUT.PUT_LINE('NAME: ' || v_name);
    DBMS_OUTPUT.PUT_LINE('DEPARTMENT: ' || v_department);
END;

What's next

• CREATE PACKAGE: Define the package specification that declares public subprograms and constants.