YashanDB Docs
search-icon Ctrl K
官网首页 立即体验 文档中心 search-icon Language

#自定义高级包

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

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

# 创建UDP

UDP的创建分为以下两部分：

  • 定义包头（PACKAGE HEAD）

    包头用于声明公有（PUBLIC属性）成员，可包括变量、类型、游标以及子程序对象（存储过程、自定义函数）。UDP创建成功后，公有成员可被其他外部程序引用。

  • 定义包体（PACKAGE BODY）

    包体用于定义公有成员中的子程序对象、声明并定义私有（PRIVATE属性）成员。私有成员可包括变量、类型、游标以及子程序对象，私有成员只能在该包体中使用。

包头、包体通过不同语法进行定义，YashanDB不严格要求同一UDP包头、包体的创建顺序。即使先定义包体再创建包头也能创建成功，但该UDP会因包头不存在而抛出编译错误，在完成包头创建前将始终无法执行和调用。

UDP成员的声明与定义约束如下：

  • 可交叉声明多个不同种类的成员，无顺序限制。但成员的顺序决定其可见性，后续成员对前面的成员不可见。

  • 同一成员（名称区分大小写）不允许重复声明，同一UDP的公有成员与私有成员亦不能重名。

  • 同一UDP中所有声明过的子程序都必须在包体中进行定义。

  • 私有成员必须先声明再定义（即在包体中成员声明必须位于其定义之前）。

  • 所有子程序定义必须位于变量定义之后。

  • 自定义聚集函数不能作为成员。

使用CREATE PACKAGE语句可以分别创建包头或包体，语法定义如下：

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 procedure_declare func_declare

variable_declare（实际语法取决于具体的变量类型）

procedure_declare::=

syntax
PROCEDURE procedure_name ( argument_define , ) RETURN return_datatype

func_declare::=

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

package_body_clause::=

syntax
BODY schema . package_name IS AS package_item_clause package_subprogram_clause

package_subprogram_clause::=

syntax
IS AS procedure_body_clause function_body_clause

procedure_body_clause

function_body_clause

# 1. OR REPLACE

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

# 2. EDITIONABLE | NONEDITIONABLE

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

# 3. package_name

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

# 4. package_head_clause

创建一个UDP的包头。

# 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的公有成员，成员可以是变量、类型、游标或子程序对象（存储过程、自定义函数），但不能为自定义聚集函数。

  • 可交叉声明多个不同种类的成员，无顺序限制。但成员的顺序决定其可见性，后续成员对前面的成员不可见。

  • 同一成员（名称区分大小写）不允许重复声明。

  • 每个package_item_clause子句声明1个成员，每个创建包头的语句中最多可以有1536个package_item_clause子句。

# 4.3.1. variable_declare

用于显式声明变量，可以声明的变量类型以及对应的语法详情请查阅变量。

  • 对于游标，仅允许显式定义，不允许显式声明。

  • 可交叉声明多个不同类的变量且无顺序限制。

# 4.3.2. procedure_declare

用于声明子存储过程。

# 4.3.3. func_declare

用于声明子函数，语法详情请查阅自定义函数。

# 5. package_body_clause

创建一个UDP的包体。

每个package_item_clause子句声明1个成员，每个package_subprogram_clause子句定义1个子程序（存储过程或自定义函数）成员，每个创建包体的语句中package_item_clause子句与package_subprogram_clause子句的总数最多为1536个。

# 5.1. package_item_clause

用于声明包体的私有成员，成员可以是变量、类型、游标或子程序对象（存储过程、自定义函数），但不能为自定义聚集函数。

  • 可交叉声明多个不同种类的成员，无顺序限制。但成员的顺序决定其可见性，后续成员对前面的成员不可见。

  • 私有成员的声明必须位于其定义之后。

# 5.1.1. variable_declare

用于显式声明变量，可以声明的变量类型以及对应的语法详情请查阅变量。

  • 对于游标，仅允许显式定义，不允许显式声明。

  • 可交叉声明多个不同类的变量且无顺序限制。

# 5.1.2. procedure_declare

用于声明子存储过程。

# 5.1.3. func_declare

用于声明子函数，语法详情请查阅自定义函数。

自定义聚集函数不能作为私有成员。

# 5.2. package_subprogram_clause

用于定义UDP的所有子程序，包括公有成员和私有成员中的存储过程或自定义函数。

  • 同一UDP中所有声明过的子程序都必须在包体中进行定义。

  • 私有成员必须先声明再定义（即在包体中成员定义必须位于其声明之后）。

  • 所有子程序定义必须位于变量定义之后。

# 5.2.1. procedure_body_clause

用于定义已声明的存储过程，语法同存储过程。

# 5.2.2. function_body_clause

用于定义已声明的自定义函数，语法同自定义函数。

若某个自定义函数在声明和定义时指定的PIPELINED、PARALLEL_ENABLE以及DETERMINISTIC标识不同，生效效果会因该函数的公有/私有属性不同而存在差异：

  • 若该函数为公有成员：

    • PIPELINED：声明和定义时必须保持一致，否则报错。

    • PARALLEL_ENABLE：始终仅为语法兼容，若声明未指定但定义时指定则报错。

    • DETERMINISTIC：按声明时指定的生效，若声明未指定但定义时指定则报错。

  • 若该函数为私有成员，上述标识均按定义时指定的生效。

示例（单机/共享集群/分布式集群部署）

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

-- 创建UDP的包体，由于未声明相关变量会返回错误，实际上该包体已创建成功
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的包头（此步骤后无需再次创建包体）
CREATE OR REPLACE PACKAGE calc_fee
AS
c NUMBER := 100;
PROCEDURE calc_rev(prefix CHAR);
END calc_fee;
/

-- 创建包内管道函数
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;
/

-- 创建含私有成员的高级包
CREATE OR REPLACE PACKAGE pkg_pri_member IS
FUNCTION func_pub RETURN INT;
END;
/

CREATE OR REPLACE PACKAGE body pkg_pri_member IS
FUNCTION func_PRI(emp_no IN VARCHAR2) RETURN INT;
function func_pub return int is
BEGIN
  RETURN func_PRI('hello');
END;
FUNCTION func_PRI(emp_no IN VARCHAR2) RETURN INT IS
BEGIN
 RETURN 100;
END;
END;
/

# 加载UDP的变量

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

[schema.][package_name.]variable_name;

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

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

# 运行UDP的子程序对象

SQL语句中调用子函数

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

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

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

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

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

过程体中调用子存储过程

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

[package_name.]procedure_name[(arguments)];

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

示例（单机/共享集群/分布式集群部署）

-- 调用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


-- 高级包的公有成员可在SQL语句中正常调用
SELECT pkg_pri_member.func_pub FROM dual;

PKG_PRI_MEMBER.FUNC_PUB
----------------------------
                         100

-- 高级包的私有成员无法在SQL语句中调用
SELECT pkg_pri_member.func_pri('world') FROM dual;

[1:8]YAS-04243 invalid identifier "PKG_PRI_MEMBER"."FUNC_PRI"

# 重载调用子过程

高级包内允许定义使用多个具有相同名称，但形参的名称、数量、顺序或数据类型不同的子过程。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
交流