YashanDB Docs
search-icon Ctrl K
官网首页 立即体验 文档中心 search-icon 语言选择

#自定义高级包

自定义高级包是数据库里的一种PL对象，简称UDP。

自定义高级包不适用于存算一体分布式集群部署。

# 创建UDP

UDP的创建分为两部分：

  • 定义PACKAGE HEAD：PACKAGE HEAD用于声明公有（PUBLIC属性）变量、公有类型、公有游标和公有子过程体对象（存储过程、自定义函数）。UDP创建成功后，公有变量、公有类型、公有游标和公有子过程体对象将可以作为UDP的成员被其他外部程序所引用。
  • 定义PACKAGE BODY：PACKAGE BODY用于定义过程体以及私有（PRIVATE属性）变量，PACKAGE BODY也可以直接使用公有变量。私有变量则只能在该PACKAGE BODY中使用，无法被外部其他过程体引用。

PACKAGE HEAD和PACKAGE BODY通过不同语法分别定义，且YashanDB并不严格要求HEAD和BODY的创建顺序，如在HEAD还未创建时先定义BODY，系统将仍然创建该UDP，但会因为HEAD声明不存在而抛出编译错误，该UDP也无法被执行和调用，直到HEAD被创建。

系统对PACKAGE HEAD和PACKAGE BODY还存在如下约束要求：

  • HEAD和BODY内部不允许出现同名的变量或者子过程体对象声明；
  • HEAD中的子过程体对象声明，在BODY中必须有对应的过程体定义，在HEAD中声明的参数缺省值在BODY中可以省略；
  • 对于子过程体对象，HEAD中只能对其进行声明，BODY中只能对其进行过程体定义。

使用CREATE PACKAGE语句可以分别创建PACKAGE HEAD或者PACKAGE BODY，语法定义如下：

create package::=

syntax
CREATE OR REPLACE EDITIONABLE NONEDITIONABLE PACKAGE package_head_clause package_body_clause END package_name ;

package_head_clause::=

syntax
schema . package_name invoker_rights_clause IS AS pragma_clause package_item_clause ,

invoker_rights_clause::=

syntax
AUTHID CURRENT_USER DEFINER

pragma_clause::=

syntax
PRAGMA SERIALLY_REUSABLE ;

package_item_clause::=

syntax
variable_declare pl_declare func_declare

variable_declare

pl_declare::=

syntax
PROCEDURE pl_name ( argument_define , ) RETURN return_datatype

func_declare::=

syntax
FUNCTION func_name ( argument_define , ) RETURN return_datatype PIPELINED PARALLEL_ENABLE DETERMINISTIC

如需创建包内管道函数则需指定PIPELINED选项，包内管道函数的使用规则同独立管道函数。

PARALLEL_ENABLE语法使用规则同PARALLEL_ENBALE。

DETERMINISTIC语法使用规则同DETERMINISTIC。

package_body_clause::=

syntax
BODY schema . package_name IS AS package_item_clause package_subprogram_clause

package_subprogram_clause::=

syntax
IS AS variable_declare BEGIN plsql_statements END pl_name

# 1. OR REPLACE

当要创建的UDP已经存在时，将其进行重建。

# 2. EDITIONABLE | NONEDITIONABLE

用于语法兼容，无实际含义。

# 3. package_name

UDP的名称，不可省略，且需符合YashanDB的对象命名规范。

# 4. package_head_clause

创建一个UDP的HEAD。

# 4.1. invoker_rights_clause

详情请查阅invoker_rights_clause相关描述。

# 4.2. pragma_clause

可选项，表示UDP的变量每次被引用时，是否将其重置为变量声明时定义的缺省值，默认不重置。

在YashanDB里，在UDP中声明的变量均为SESSION级别的全局变量，这意味着在同一个会话中，变量赋值相互影响，而不同会话中的变量则相互独立。通过设置SERIALLY_REUSABLE可以在每次引用变量（客户端到服务端的一次调用）时让其恢复初始值。

示例（单机、共享集群部署）

DROP PACKAGE IF EXISTS calc_fee;

CREATE OR REPLACE PACKAGE calc_fee
AS
c NUMBER := 100;
PROCEDURE branch_quantity(date_from date);
END;
/

CREATE OR REPLACE PACKAGE BODY calc_fee AS
PROCEDURE branch_quantity(date_from DATE) IS
BEGIN
 DBMS_OUTPUT.PUT_LINE(c);
 c := c+1;
 DBMS_OUTPUT.PUT_LINE(c);
END;
END calc_fee;
/

-- 第一次执行
exec calc_fee.branch_quantity(sysdate);
100
101
-- 第二次执行
exec calc_fee.branch_quantity(sysdate);
101
102

-- 设置pragma_clause
CREATE OR REPLACE PACKAGE calc_fee
AS
PRAGMA SERIALLY_REUSABLE;
c NUMBER := 100;
PROCEDURE branch_quantity(date_from date);
END;
/

CREATE OR REPLACE PACKAGE BODY calc_fee AS
PRAGMA SERIALLY_REUSABLE;
PROCEDURE branch_quantity(date_from date) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(c);
c := c+1;
DBMS_OUTPUT.PUT_LINE(c);
END;
END calc_fee;
/

-- 第一次执行
exec calc_fee.branch_quantity(sysdate);
100
101
-- 第二次执行
exec calc_fee.branch_quantity(sysdate);
100
101

# 4.3. package_item_clause

UDP的成员声明，包括变量声明和子过程体对象声明，一个UDP（HEAD或BODY）里可声明的成员数量最多为1024个。

  • 变量声明：支持在声明章节中列出的各类变量的声明，但对于游标，允许显式定义游标，不允许显式声明游标。可交叉声明多个不同类变量且顺序无限制。
  • 子过程体对象声明：支持声明存储过程和自定义函数。允许交叉声明多个存储过程和自定义函数，且顺序无限制。

变量声明必须位于子过程体对象声明的前面，且定义的成员先后顺序决定了后一个成员是否可见前面成员。

# 5. package_body_clause

创建一个UDP的BODY。

# 5.1. package_subprogram_clause

UDP里的过程体定义，包括PROCEDURE或FUNCTION的定义，定义语法同存储过程和自定义函数章节里的过程体语法描述。

# 加载UDP的变量

UDP的变量无法在外部SQL语句中直接访问，而是通过在过程体（内部或外部）中加载使用，加载格式为：

[schema.][package_name.]variable_name;

在被外部过程体调用时，package_name不可省略。

在YashanDB中，UDP的变量与UDP进行绑定管理，如果UDP的HEAD或者BODY发生变更，该UDP的所有变量将被重置。此规则与Oracle数据库存在差异。

# 运行UDP的子过程体对象

SQL语句中调用子函数

通过SQL语句调用UDP的子函数，允许出现在SQL语句的列项、条件项或查询选项（如ORDER BY/LIMIT/OFFSET）等各个位置。以出现在查询列为例的调用格式为：

SELECT package_name.subpl_name[(arguments)] FROM table_name;

CALL/EXEC语句中调用子存储过程

通过CALL/EXEC语句可调用UDP的子存储过程，调用格式为：

(CALL|EXEC) package_name.subpl_name[(arguments)];

过程体中调用子存储过程

通过在过程体（内部或外部）中调用子存储过程，调用格式为：

[package_name.]procedure_name[(arguments)];

在被外部过程体调用时，package_name不可省略。

示例（单机、共享集群部署）

-- 创建无HEAD的UDP
DROP PACKAGE IF EXISTS calc_fee;

-- 创建UDP的BODY，由于未声明相关变量会返回错误，实际上该BODY已创建成功
CREATE OR REPLACE PACKAGE BODY calc_fee AS
PROCEDURE calc_rev(prefix CHAR) IS
 str1 VARCHAR(100) := 'select sum(revenue_total) from finance_info where substr(branch,0,2)=:a';
BEGIN
 EXECUTE IMMEDIATE str1 INTO c USING prefix;
 DBMS_OUTPUT.PUT_LINE(c);
END;
END calc_fee;
/
-- result
YAS-05278 cannot compile body without its specification

-- 创建calc_fee的HEAD（此步骤后无需再次创建BODY）
CREATE OR REPLACE PACKAGE calc_fee
AS
c NUMBER := 100;
PROCEDURE calc_rev(prefix CHAR);
END calc_fee;
/

-- 调用UDP
DECLARE
 c CHAR(2);
BEGIN
 c := '01';
 DBMS_OUTPUT.PUT_LINE(c);
 DBMS_OUTPUT.PUT_LINE(calc_fee.c);
 calc_fee.calc_rev(c);
END;
/
-- result
01
100
76666

-- 创建包内管道函数
CREATE OR REPLACE PACKAGE ya_pkg1 AUTHID DEFINER AS
  TYPE NUMSET_T IS TABLE OF NUMBER;
  FUNCTION ya_pkg_pipe_func(p1 number) RETURN NUMSET_T PIPELINED;
END ya_pkg1;
/

CREATE OR REPLACE PACKAGE BODY ya_pkg1 AS
  FUNCTION ya_pkg_pipe_func(p1 NUMBER) RETURN NUMSET_T PIPELINED IS
  BEGIN
    FOR i IN 1..p1 LOOP
      pipe row(i);
    END LOOP;
    RETURN;
  END ya_pkg_pipe_func;
END ya_pkg1;
/

# 重载调用子过程

高级包内允许定义使用多个具有相同名称，但形参的名称、数量、顺序或数据类型不同的子过程。PL可以通过重载子过程功能实现对此类同名子过程的筛选调用。

PL子过程重载的约束如下：

  • PL重载的目标对象必须为用户自定义包内的子过程，不能为全局/嵌套子过程。

  • 以下子过程无法作为PL重载的目标对象：

    • 不同子过程间仅形参的出入参数方式不同，例如IN和OUT。

    • 不同子过程间仅形参有/无参数默认值的差异。

    • 不同子函数间仅返回值类型不同。

如果自定义包中定义了多个同名子过程，调用对应名称的子过程时，YashanDB将根据传入的实参与各子过程的形参列表进行匹配，从而决定实际需执行哪个子过程。如果最终未匹配到具体的某一个子过程或匹配到多个子过程，调用将失败。

匹配维度包括参数的名称、数量、顺序以及数据类型，关于数据类型的匹配规则如下：

  • 仅数据类型不一致且均为数值型时，将按类型接近、精度优先的原则进行匹配，选取首个被匹配中的子过程进行调用。

  • 仅数据类型不一致时，YashanDB会先尝试将实参的数据类型隐式转换为形参的数据类型再进行匹配。

子过程重载调用方式比较灵活，为避免隐式转换过程中出现异常或导致非预期的结果，建议：

  • 明确定义子过程各个参数接受哪种数据类型，调用时使用与形参完全一致的数据类型。

  • 使用更精确的数据类型写法，例如实参以3.0f方式传入FLOAT类型数据。

  • 通过类型转换函数将实参转换成目标子过程中形参的数据类型，例如将实参通过CAST函数或显式转换为与形参一致的数据类型。

示例（单机、共享集群部署）

DROP PACKAGE IF EXISTS pack_calc;
CREATE OR REPLACE PACKAGE pack_calc AS
    FUNCTION calc_max(val1 NUMBER, val2 NUMBER) RETURN NUMBER;
    FUNCTION calc_max(val1 NUMBER, val2 NUMBER, val3 NUMBER) RETURN NUMBER;
END;
/

CREATE OR REPLACE PACKAGE BODY pack_calc AS
    FUNCTION calc_max(val1 NUMBER, val2 NUMBER) RETURN NUMBER IS 
    BEGIN
        IF val1 > val2 THEN
            RETURN val1;
        ELSE
            RETURN val2;
        END IF;
    END;
    FUNCTION calc_max(val1 NUMBER, val2 NUMBER, val3 NUMBER) RETURN NUMBER IS 
    BEGIN
        RETURN calc_max(calc_max(val1, val2), val3);
    END;
END;
/

SELECT pack_calc.calc_max(1, 2) AS a, pack_calc.calc_max(1, 2, 3) AS b FROM dual;

-- result
          A           B
----------- -----------
          2           3

子过程重载调用时，如果因子过程调用的参数个数或类型不正确无法匹配到对应的子过程，将上报YAS-06923错误。如果子过程调用过程中，匹配到多个符合同等条件的子过程时，将上报YAS-06924错误。常见报错场景示例如下：

  • 声明时出现完全一致的子过程

    示例（单机、共享集群部署）

    CREATE OR REPLACE PACKAGE pack1 AS
    FUNCTION func(val NUMBER) RETURN NUMBER;
    FUNCTION func(val NUMBER) RETURN NUMBER;
END;
/
YAS-05290 duplicate item "FUNC" in package

-- 使用不同的参数个数、参数名或数据类型可以通过验证
CREATE OR REPLACE PACKAGE pack2 AS
    FUNCTION func(val NUMBER) RETURN NUMBER;
    FUNCTION func(val DOUBLE) RETURN NUMBER;
END;
/

  • 声明了不同的子过程，但调用过程中出现了二义性

示例（单机、共享集群部署）

CREATE OR REPLACE PACKAGE pack1 AS
    FUNCTION func(val1 NUMBER) RETURN NUMBER;
    FUNCTION func(val1 NUMBER, val2 NUMBER default 3) RETURN NUMBER;
END;
/

CREATE OR REPLACE PACKAGE BODY pack1 AS
    FUNCTION func(val1 NUMBER) RETURN NUMBER IS BEGIN RETURN 1; END;
    FUNCTION func(val1 NUMBER, val2 NUMBER default 3) RETURN NUMBER IS BEGIN RETURN 2; END;
END;
/

SELECT pack1.func(val1 => 3) FROM dual;
-- 系统无法该匹配到唯一的一个函数导致出错
YAS-06924 too many declarations of 'FUNC' match this call

SELECT pack1.func(val1 => 3, val2 => 4) FROM dual;
-- 通过参数名能识别到调用的函数，可以执行成功
PACK1.FUNC(VAL1=>3,VAL2=>4)
---------------------------
                          2
  • 重载使用时，由于数据类型隐式转换过程产生了二义性

示例（单机、共享集群部署）

CREATE OR REPLACE PACKAGE pack1 AS
    FUNCTION func(val1 NUMBER, val2 VARCHAR) RETURN NUMBER;
    FUNCTION func(val1 NUMBER, val2 NUMBER) RETURN NUMBER;
END;
/

CREATE OR REPLACE PACKAGE BODY pack1 AS
    FUNCTION func(val1 NUMBER, val2 VARCHAR) RETURN NUMBER IS BEGIN RETURN 1; END;
    FUNCTION func(val1 NUMBER, val2 NUMBER) RETURN NUMBER IS BEGIN RETURN 2; END;
END;
/

SELECT pack1.func(1, '2') FROM dual;
-- 执行正常，值为1
PACK1.FUNC(1,'2')
-----------------
                1
                
SELECT pack1.func(1, 2) FROM dual;
-- 执行正常，值为2
PACK1.FUNC(1,2)
---------------
              2

SELECT pack1.func('1', '2') FROM dual;
-- 执行失败，系统无法识别要调用哪个函数
YAS-06924 too many declarations of 'FUNC' match this call

SELECT pack1.func('1', 2) FROM dual;    
-- 执行失败，系统无法识别要调用哪个函数
YAS-06924 too many declarations of 'FUNC' match this call

# 删除自定义高级包

使用DROP PACKAGE语句删除自定义高级包。

示例（单机、共享集群部署）

--创建UDF，调用上例中的UDP子过程体对象
CREATE OR REPLACE FUNCTION calc(c CHAR) RETURN NUMBER IS
BEGIN
  calc_fee.calc_rev(c);
  RETURN calc_fee.c;
END;
/
SELECT calc('01') FROM dual;
 CALC('01') 
----------- 
      76666

--删除UDP
DROP PACKAGE calc_fee;
SELECT calc('01') FROM dual;
[1:8]YAS-04253 PL/SQL compiling errors:
[3:3] YAS-04243 invalid identifier "CALC_FEE"."CALC_REV"
[4:10] YAS-04243 invalid identifier "CALC_FEE"."C"

# 修改自定义高级包

使用ALTER PACKAGE语句修改自定义高级包。

pdf-btn 下载文档 copy-btn 复制链接
edit-icon
反馈
coperate-icon
coperate
合作
communication-icon
communicate
交流