存储函数

📄字数 6.0K

👁️阅读量加载中...

存储函数是由用户定义的SQL函数,使用特定的输入参数来执行一组既定的SQL语句，并且存在返回值。

数据库存储函数的使用场景有：

复杂计算封装‌：存储函数可以封装复杂的计算逻辑，如税率计算、字符串格式化等，从而简化应用程序中的代码‌
‌业务逻辑复用‌：通过存储函数，可以减少重复代码，例如权限校验、日志记录等业务逻辑可以封装在存储函数中，提高代码的重用性和维护性‌
‌提高效率‌：存储函数可以直接在SELECT、WHERE等子句中调用，类似于内置函数，这样可以减少编译次数和数据库连接次数，提升整体效率‌

一、创建存储函数

1.1 语法格式

(VarDefList::= , StmtBlock::=)

1.2 参数说明

Cre_Rep：可选项，存储函数创建语句，若忽略该语句效果等价于CREATE。
- OR REPLACE：指定存储函数存在则替换，用于更新存储函数定义。
- NOFORCE：不强制创建或替换，默认。
- FORCE：强制创建或替换。
FuncDef：函数定义语句。
- FuncDecl：函数声明。
  - IF NOT EXISTS：创建存储函数时存在同名存储函数则忽略此错误，该关键字无法判断已有同名存储函数与当前创建存储函数属性是否一致。
  - TypeName：参数的类型名或返回值类型。
  - schema_name：存储函数所属模式名。
  - func_name：存储函数名。
  - func_args：形式参数定义（包含参数名称、模式、类型、默认值等）。
    - func_arg：单个参数定义。
      - arg_name：参数名称。
      - IN: 表示传入参数。
      - OUT：表示输出参数，在函数内部被赋值，修改结果会返回给调用者。
      - IN OUT。它可以将实参传递进函数。在函数的内部，形参可以被读取也可以被写入。在函数结束时，形参的内容同时也被赋给实参。
      - TypeName：对应参数的数据类型。
      - (ASSIGN | DEFAULT) b_expr：可选的默认值表达式。
    - opt_authid：权限。
      - AUTHID DEFAULT：默认权限。
      - AUTHID DEFINER：使用定义者的权限执行。
      - AUTHID CURRENT_USER | AUTHID USER：使用调用者的权限执行。
  - PIPELINED：指定的函数即成为PIPELINED函数，PIPELINED函数可以在过程中将循环体内的信息实时输出。
- COMMENT SCONST：添加注释。
- IS | AS：任选两个关键字中的一个使用。
- LANGUAGE {PLSQL | C} NAME build_in_func_name：引用的内部接口，C会去调用系统内建函数，即数据库用c语言方式编译好的函数，此方式build_in_func_name为内部函数定义的名称；忽略该参数或者指定PLSQL会当成用户自定义函数处理。
- (schema_name '.')? func_name：可选项，FuncDef语法最后的函数名，需和FuncDecl中定义的过程名保持一致，否则会报错。

注意

使用PIPELINED函数以外的存储函数时需给出返回值。

使用PIPELINED关键字指定的函数即成为PIPELINED函数，PIPELINED函数可以在过程中将循环体内的信息实时输出，此类函数遵循以下规则：

类型约束：
- 返回结果必须为自定义TABLE与VARRAY类型。
- 返回结果支持嵌套RECORD类型，但不支持嵌套包内自定义类型。
函数体限制：
- 函数可以不包含RETURN子句，若包含RETURN子句，RETURN子句不能返回任何表达式。
- 函数只支持函数表的用法，不支持出现语句的其他位置。
查询使用限制：
- 使用PIPELINED函数作为函数表的查询语句不支持字段筛选、不支持字段使用其他函数或其他表达式、不支持条件过滤等。
- PIPELINED函数一般需使用PIPE ROW来实时输出结果。
PIPE ROW 用法：
- 语法格式
- 参数说明
  - b_expr：需实时返回的数据。
- 使用说明
  - PIPELINED函数目前只支持配合PIPE ROW子句提供实时数据返回功能。
  - 使用PIPE ROW语句用于快速实时返回结果，并且PIPE ROW语句只能出现在PIPELINED函数中。PIPE ROW返回结果必须为函数返回值中的元素类型。

1.3 示例

1.3.1 普通函数示例

示例1：
创建一个无参函数。

sql

SQL> CREATE TABLE test_proc_tab(id INT);

SQL> BEGIN
      FOR i IN 1..10 LOOP
        INSERT INTO test_proc_tab VALUES(i);
      END LOOP;
     END;

SQL> CREATE OR REPLACE FUNCTION func_test()
     RETURN INT
     AS
     ret INT;
     BEGIN
       SELECT MAX(id) INTO ret FROM test_proc_tab;
       RETURN ret;
     END;
     /

SQL> SELECT func_test() FROM dual;
+-------+
| EXPR1 |
+-------+
| 10    |
+-------+

示例2：
创建一个表值函数，返回类型为集合类型，与记录类型相似可以返回多行记录。

sql


SQL> CREATE OR REPLACE FUNCTION func_tab(num INT) RETURN TABLE OF RECORD(id INT, name VARCHAR(10)) IS
     DECLARE  subtype rec IS RECORD(id INT, name CHAR(10));
     tab TABLE OF rec;
     BEGIN
       FOR i IN 1 .. num LOOP
         tab.EXTEND;
         tab(tab.last) := rec(i, 'name' || i);
       END FOR;
       RETURN tab;
     END;
     /

SQL> SELECT * FROM TABLE(func_tab(3));

+----+-------+
| ID | NAME  |
+----+-------+
| 1  | name1 |
| 2  | name2 |
| 3  | name3 |
+----+-------+

1.3.2 PIPELINED函数示例

示例3：
自定义函数中使用PIPELINED函数。

sql


-- 创建table
SQL> CREATE OR REPLACE TYPE tab AS TABLE OF VARCHAR(100);
    /
    
-- 创建函数，pipe row内返回的varchar为创建的自定义类型table的元素类型
SQL> CREATE OR REPLACE FUNCTION fun(a INT)
     RETURN tab PIPELINED
     IS
     j VARCHAR:='test';
     BEGIN
       FOR i IN 1..10 LOOP
          PIPE ROW('abc'||j);
          j:= j||(a+i);
       END LOOP;
       PIPE ROW('pipe row end');
       RETURN;
     END;
     /

-- 使用函数
SQL> SELECT * FROM TABLE(fun(1));

+-------------------+
|   COLUMN_VALUE    |
+-------------------+
| abctest           |
| abctest2          |
| abctest23         |
| abctest234        |
| abctest2345       |
| abctest23456      |
| abctest234567     |
| abctest2345678    |
| abctest23456789   |
| abctest2345678910 |
| pipe row end      |
+-------------------+

示例4：
包定义PIPELINED函数。

sql


-- 创建包头
SQL> CREATE OR REPLACE PACKAGE pkg AUTHID DEFINER AS
     TYPE num_tab IS TABLE OF NUMBER;
     FUNCTION pkg_fun(x NUMBER) RETURN num_tab PIPELINED;
     END;
     /

-- 创建包体
SQL> CREATE OR REPLACE PACKAGE BODY pkg AS
     FUNCTION pkg_fun(x NUMBER) RETURN num_tab PIPELINED IS
      BEGIN
        PIPE ROW(x);
        PIPE ROW(x + 100);
        RETURN;
      END;
     END;
     /
  
  -- 使用包函数
  SQL> SELECT * FROM TABLE(pkg.pkg_fun(103));

    +--------------+
    | COLUMN_VALUE |
    +--------------+
    | 103          |
    | 203          |
    +--------------+

示例5：
返回游标结果RECORD类型。

sql


-- 创建表
SQL> CREATE TABLE t_tab(id int, c1 VARCHAR,c2 VARCHAR,c3 VARCHAR,c4 VARCHAR);
  
-- 插入数据
SQL> INSERT INTO t_tab VALUES(1,'1','a','26','z')(2,'2','b','25','y')(3,'3','c','24','x')(4,'4','d','23','w');

-- 创建包头
SQL> CREATE OR REPLACE PACKAGE pkg IS
     TYPE var1 IS t_tab % ROWTYPE;
     TYPE var2 IS TABLE OF var1;
     FUNCTION pkg_func() RETURN var2 PIPELINED;
     END;
     /
- 创建包体
SQL> CREATE OR REPLACE PACKAGE BODY pkg IS
     FUNCTION pkg_func() RETURN var2 PIPELINED
     IS
     TYPE curtype IS REF CURSOR;
     c2 curtype;
     out_rec t_tab%ROWTYPE;
     BEGIN
       OPEN c2 FOR SELECT * FROM t_tab;
       LOOP
          FETCH c2 INTO out_rec;
          EXIT WHEN c2%NOTFOUND;
          PIPE ROW(out_rec);
       END LOOP;
       CLOSE c2;
       RETURN;
     END;
     END;
     /
-- 使用包函数
SQL> SELECT * FROM TABLE(pkg.pkg_func());
+----+----+----+----+----+
| ID | C1 | C2 | C3 | C4 |
+----+----+----+----+----+
| 1  | 1  | a  | 26 | z  |
| 2  | 2  | b  | 25 | y  |
| 3  | 3  | c  | 24 | x  |
| 4  | 4  | d  | 23 | w  |
+----+----+----+----+----+

示例6：
UDT中使用PIPELINED函数。

sql


-- 创建类型var
SQL> CREATE OR REPLACE TYPE var AS VARRAY(10) OF VARCHAR(10);

-- 创建类型va2
SQL> CREATE OR REPLACE TYPE var2 AS VARRAY(10) OF var;

-- 创建类型obj
SQL> CREATE OR REPLACE TYPE obj AS OBJECT(
     a INT,
     MEMBER FUNCTION obj_func() RETURN var2 PIPELINED
     );
     /


-- 创建类型obj body
SQL> CREATE OR REPLACE TYPE BODY obj AS
     MEMBER FUNCTION obj_func() RETURN var2 PIPELINED
     IS
     x var2 := var2();
     BEGIN
      x.extend(8);
      x(1):=var(1);
      x(5):=var(5);
      FOR i IN x.FIRST..x.LAST LOOP
        PIPE ROW(x(i));
      END LOOP;
      RETURN;
     END;
     END;
     /


-- 使用函数
SQL> SELECT * FROM TABLE(obj(1).obj_func());

+--------------+
| COLUMN_VALUE |
+--------------+
| [1]          |
| <NULL>       |
| <NULL>       |
| <NULL>       |
| [5]          |
| <NULL>       |
| <NULL>       |
| <NULL>       |
+--------------+

1.3.3 创建存储函数带运行者身份对象

sql

SQL> CREATE TABLE tb_sel(id INT,sal NUMERIC(6,2));

SQL> INSERT INTO tb_sel VALUES(120,50);

-- 验证语法可用性
SQL> CREATE OR REPLACE FUNCTION compute_bonus (emp_id NUMBER,bonus NUMBER) RETURN NUMBER AUTHID DEFINER IS
     emp_sal NUMBER;
     BEGIN
      SELECT sal INTO emp_sal
      FROM tb_sel
      WHERE id = emp_id;
      RETURN emp_sal + bonus;
     END compute_bonus;
     /


SQL> SELECT compute_bonus(120, 50) FROM DUAL;

+-------+
| EXPR1 |
+-------+
| 100   |
+-------+

-- 函数参数的另一种传入方式
SQL> SELECT compute_bonus(bonus => 50, emp_id => 120) FROM DUAL;

+-------+
| EXPR1 |
+-------+
| 100   |
+-------+

二、删除存储函数

2.1 语法格式

2.2 参数说明

IF EXISTS：删除时存储函数不存在，忽略此错误。
CASCADE' | 'RESTRICT：默认使用RESTRICT，在删除存储函数时会检测存储存储函数是否被其他对象依赖，若存在依赖则删除失败，若无依赖则删除成功；CASCADE则表示强制删除存储函数，强制删除被依赖对象后，数据库还会对依赖对象的valid状态进行对应处理。

2.3 示例

sql

-- 创建函数
SQL> CREATE OR REPLACE FUNCTION func_tab(num INT) RETURN TABLE OF RECORD(id INT, name VARCHAR(10)) IS
     DECLARE  subtype rec IS RECORD(id INT, name CHAR(10));
     tab TABLE OF rec;
     BEGIN
       FOR i IN 1 .. num LOOP
         tab.EXTEND;
         tab(tab.last) := rec(i, 'name' || i);
       END FOR;
       RETURN tab;
     END;
     /

-- 使用函数
SQL> SELECT * FROM TABLE(func_tab(3));

+----+-------+
| ID | NAME  |
+----+-------+
| 1  | name1 |
| 2  | name2 |
| 3  | name3 |
+----+-------+


-- 删除函数
SQL> DROP FUNCTION func_tab;  

-- 已删除，再次执行返回错误
SQL> SELECT * FROM TABLE(func_tab(3));
Error: [E10049 L1 C21] 字段变量或函数"FUNC_TAB"(3)不存在

三、重编译存储函数

重编译存储函数是指在依赖对象发生变化后，数据库中的存储函数因失效而不能正常工作，此时需要通过重新编译的方式恢复其可用性。

3.1 语法格式

3.2 示例

步骤1：
创建表并插入数据，再创建存储函数并使用。

sql

SQL> CREATE TABLE test_proc_tab (id NUMBER);

SQL> INSERT INTO test_proc_tab (id) VALUES (1)(2)(3);  

SQL> CREATE OR REPLACE FUNCTION  func_test()
     RETURN INT
     AS
     ret INT;
     BEGIN
      SELECT MAX(id) INTO ret FROM test_proc_tab;
      RETURN ret;
     END;
     /   

SQL> SELECT func_test() FROM dual;

+-------+
| EXPR1 |
+-------+
| 10    |
+-------+

步骤2：
强制删除表，存储函数失去依赖表失效。

sql

SQL> DROP TABLE test_proc_tab CASCADE;  

SQL> SELECT func_test() FROM dual;
Error: [E8014 L1 C8] 存储过程或函数FUNC_TEST被标识为失效

步骤3：
重新创建表并插入数据，重编译存储函数，可正常使用。

sql

SQL> CREATE TABLE test_proc_tab (id NUMBER);

SQL> INSERT INTO test_proc_tab (id) VALUES (1)(2)(3);

SQL> ALTER FUNCTION func_test RECOMPILE; 

SQL> SELECT func_test() FROM dual;

+-------+
| EXPR1 |
+-------+
| 3     |
+-------+

四、反汇编存储函数

将存储函数反汇编为类似汇编语言的指令码输出。

存储函数 ​

一、创建存储函数 ​

1.1 语法格式 ​

1.2 参数说明 ​

1.3 示例 ​

1.3.1 普通函数示例 ​

1.3.2 PIPELINED函数示例 ​

1.3.3 创建存储函数带运行者身份对象 ​

二、删除存储函数 ​

2.1 语法格式 ​

2.2 参数说明 ​

2.3 示例 ​

三、重编译存储函数 ​

3.1 语法格式 ​

3.2 示例 ​

四、反汇编存储函数 ​

4.1 语法格式 ​