CREATE PACKAGE BODY_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

CREATE PACKAGE BODY定义一个包体。CREATE OR REPLACE PACKAGE BODY可以创建新的包体或替换现有的包体。包是组织和封装数据库应用程序逻辑的重要工具。通过合理设计和使用包，可以提高代码的可维护性、重用性和性能。

简介

使用CREATE PACKAGE BODY语句创建包的主体，该主体是数据库中的相关过程、存储函数和其他程序对象的封装集合。包体定义了这些对象。在前面的CREATE PACKAGE语句中定义的包规范声明了这些对象。

语法

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 ]; }]

参数说明

参数	描述
package_name	已创建的包名称。
declaration	私有变量、类型、游标或`REF CURSOR`声明。
forward_declaration	前向声明，用于在实际定义之前声明一个过程或函数。在一个模块中，您可以创建多个子程序。如果这些子程序相互调用，则每个子程序均需要一个前向声明。必须先声明子程序，然后才能进行调用。您可以通过前向声明来声明子程序，而无需进行具体定义。前向声明及其相应的定义必须位于同一代码块中。
proc_name	公共存储过程名称。
func_name	公共函数名称。
STRICT	使用`STRICT`关键字，指定在使用`NULL`参数调用时不执行的函数。相反，该函数返回`NULL`。
LEAKPROOF	使用`LEAKPROOF`关键字，指定除了返回值之外，不显示有关参数的任何信息。
PARALLEL { UNSAFE \| RESTRICTED \| SAFE }	`PARALLEL`子句允许使用并行顺序扫描（并行模式）。与串行顺序扫描不同，并行顺序扫描在查询过程中使用多个`worker`并行扫描关系。取值范围如下： UNSAFE（默认值）：存储过程或函数不能以并行模式执行。存在此类存储过程或函数时，会强制执行串行执行计划。 RESTRICTED：存储过程或函数可以按并行模式执行，但执行限制为并行组中的前几个。如果任何特定关系的限定条件具有存在并行限制的任何内容，则不会为并行执行选择该关系。 SAFE：存储过程或函数可以按并行模式执行，没有任何限制。
execution_cost	execution_cost指定一个正数，提供函数的估计执行成本，单位为cpu_operator_cost（CPU操作成本）。如果函数返回一个集合，则这是每个返回行的成本。默认值为0.0025。
result_rows	result_rows是查询计划程序预计函数返回的估计行数。默认值为1000。
SET	使用SET子句为函数的持续时间指定参数值： config_param：指定参数名称。 value：指定参数值。 FROM CURRENT：确保在函数结束时还原参数值。
program_body	构成函数或过程主体的`pragma`、声明和`PL/SQL`语句。声明可以包括变量、类型、`REF CURSOR`或子程序声明。
argname	参数的名称。
IN \| IN OUT \| OUT	参数模式。
argtype	程序参数的数据类型。
DEFAULT value	输入参数的默认值。
rettype	返回数据类型。
DETERMINISTIC	使用DETERMINISTIC以指定在提供相同参数值时，函数将始终返回相同结果。DETERMINISTIC函数不可修改数据库。
statement	`PL/SQL`程序语句。包初始化部分中的语句在第一次引用包时每个会话执行一次。

示例

以下是一个完整的包创建和使用示例。

创建测试表。

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

创建包头。

CREATE OR REPLACE PACKAGE Employee_Pkg AS
    -- 常量
    MAX_SALARY CONSTANT NUMBER := 100000;

    -- 过程声明
    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 Get_Employee_Name(
        p_emp_id IN NUMBER
    ) RETURN VARCHAR2;

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

创建包体。

CREATE OR REPLACE PACKAGE BODY Employee_Pkg AS

    -- 私有变量
    g_total_employees NUMBER := 0;

    -- 过程实现
    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 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;

使用包。

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;