DM存储过程和存储函数

1，存储过程

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，是SQL语言层面的代码封装与重用，可以接收参数，也可以返回数据。

语法规则：

CREATE [OR REPLACE] PROCEDURE [IF NOT EXISTS] <过程声明> < AS | IS > <模块体>

<过程声明> ::= <存储过程名定义> [WITH ENCRYPTION][(<参数名><参数模式><参数类型> [<默认值表达式>] )][<调用权限子句>]
<模块体> ::= [<声明部分>] BEGIN <执行部分>[<异常处理部分>] END [存储过程名]
<声明部分> ::=[DECLARE]<变量声明>|<异常变量声明>|<游标定义>|<子过程定义>|<子函数定义>;

1.1，样例

1.1.1，创建

--不带参数的存储过程
CREATE PROCEDURE P0 AS
BEGIN
    PRINT 'HELLO';
END;

--带参数的存储过程
CREATE PROCEDURE P1(A1 IN INT) AS
BEGIN
    PRINT 'A1+1='||A1+1;
END;

1.1.2，调用

--不带参数，以下4种方式均可调用
P0;
P0();
CALL P0;
EXECUTE P0;

--带参数，以下2种方式均可调用
P1(1);
CALL P1(1);
EXECUTE P0(1);--语法分析出错，EXECUTE不能带参数

1.1.3，重编译

用于确认一个存储模块是否还有效。

当指定 CASCADE 后，将级联重编译所有直接或间接引用该存储模块的对象，需要考量影响范围，建议谨慎使用。

ALTER PROCEDURE P1 COMPILE [CASCADE];

1.1.4，删除

DROP PROCEDURE [IF EXISTS] P1;

1.2，参数分类

存储模块及模块中定义的子模块都可以带参数，用来给模块传送数据及向外界返回数据。在存储过程或存储函数中定义一个参数时，必须说明名称、参数模式和数据类型。三种可能的参数模式是：IN(缺省模式)、OUT 和 IN OUT，意义分别为：

• IN：输入参数，用来将数据传送给模块；
• OUT：输出参数，用来从模块返回数据到进行调用的模块；
• IN OUT：既作为输入参数，也作为输出参数。

1.2.1，无参数

CREATE PROCEDURE P1 AS
A1 INT;
BEGIN
    A1:=1;
    PRINT A1;
END;
P1;

1.2.2，IN参数

CREATE PROCEDURE P2(A2 IN INT) AS
A1 INT;
BEGIN
    A1:=A2+1;
    PRINT A1;
END;
P2(3);

1.2.3，OUT参数

DECLARE
A3 INT;
    PROCEDURE P3(A2 OUT INT) AS
    A1 INT;
    BEGIN
        A1:=A3;
        A2:=A1+1;
    END P3;
BEGIN
    A3=1;
    P3(A3);
    PRINT A3;
END;

1.2.4，IN OUT参数

DECLARE
A3 INT;
    PROCEDURE P4(A2 IN OUT INT) AS
    A1 INT;
    BEGIN
        A1:=A2+1;
    A2:=A1+1;
    PRINT 'A1:'||A1;
    PRINT 'A2:'||A2;
    END P4;
BEGIN
    A3:=3;
    P4(A3);
    PRINT 'A3:'||A3;
END;

CREATE PROCEDURE P5(a IN OUT INT) 
AS
    b INT:=10;
BEGIN
    a:=a+b;
    PRINT a;
EXCEPTION
WHEN OTHERS THEN 
    NULL;
END;
P5(3);

2，存储函数

和存储过程类似，只有函数声明部分多了返回值和可选参数

CREATE [OR REPLACE] FUNCTION [IF NOT EXISTS] <函数声明> <AS_OR_IS> <模块体>
<函数声明> ::= <存储函数名定义> [WITH ENCRYPTION][FOR CALCULATE][(<参数名><参数模式><参数类型> [<默认值表达式>])]RETURN <返回数据类型> [<调用选项子句>][PIPELINED]
 

2.1，样例

2.1.1，创建

可以不带参数，但必须有返回值

--不带参数
CREATE OR REPLACE FUNCTION F0 RETURN INTEGER
AS
BEGIN
    PRINT 'HELLO';
    RETURN 1;--可以没有RETURN语句，那么返回值就是NULL
END;

CREATE OR REPLACE FUNCTION F1(C IN INT) RETURN INTEGER
AS
BEGIN
    RETURN C*2;
END;

2.1.2，调用

--不带参数，以下4种方式均可调用
F0;--返回值可以赋值给变量
F0();--返回值可以赋值给变量
CALL F0;--无返回值
SELECT F0();--输出返回值

--带参数，以下3种方式均可调用
F1(3);--返回值可以赋值给变量
CALL F1(3);--无返回值
SELECT F1(3);--输出返回值

2.1.3，重编译

用于确认一个存储模块是否还有效。

当指定 CASCADE 后，将级联重编译所有直接或间接引用该存储模块的对象，需要考量影响范围，建议谨慎使用。

ALTER FUNCTION P1 COMPILE [CASCADE];

2.1.4，删除

DROP FUNCTION [IF EXISTS] P1;

3，区别

存储函数与存储过程在结构和功能上十分相似，主要的差异在于：

• 存储过程没有返回值，调用者只能通过访问 OUT 或 IN OUT 参数来获得执行结果；而存储函数有返回值，它把执行结果直接返回给调用者；
• 存储过程中可以没有返回语句，而存储函数必须通过返回语句结束；
• 不能在存储过程的返回语句中带表达式，而存储函数必须带表达式；
• 存储过程不能出现在一个表达式中，而存储函数可以出现在表达式中。