PolarDB:CREATE PACKAGE BODY

Overview

You can use the CREATE PACKAGE BODY statements to create the body of a stored package, which is an encapsulated collection of related procedures, stored functions, and other program objects stored together in the database. The package body defines these objects. The package specification which is defined by using the CREATE PACKAGE statement declares these objects.

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

Examples

The following examples show how to create and use a package.

1. Create a test table.

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

2. Create a package header.

   CREATE OR REPLACE PACKAGE Employee_Pkg AS
       -- Constant
       MAX_SALARY CONSTANT NUMBER := 100000;
   
       -- Procedure declaration
       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 declaration
       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;

3. Create a package body.

   CREATE OR REPLACE PACKAGE BODY Employee_Pkg AS
   
       -- Private variable
       g_total_employees NUMBER := 0;
   
       -- Procedure implementation
       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 implementation
       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;

4. Use the package.

   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;