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 A11||A11; 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.2IN参数CREATE PROCEDURE P2(A2 IN INT) AS A1 INT; BEGIN A1:A21; PRINT A1; END; P2(3);1.2.3OUT参数DECLARE A3 INT; PROCEDURE P3(A2 OUT INT) AS A1 INT; BEGIN A1:A3; A2:A11; END P3; BEGIN A31; P3(A3); PRINT A3; END;1.2.4IN OUT参数DECLARE A3 INT; PROCEDURE P4(A2 IN OUT INT) AS A1 INT; BEGIN A1:A21; A2:A11; 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:ab; 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 参数来获得执行结果而存储函数有返回值它把执行结果直接返回给调用者存储过程中可以没有返回语句而存储函数必须通过返回语句结束不能在存储过程的返回语句中带表达式而存储函数必须带表达式存储过程不能出现在一个表达式中而存储函数可以出现在表达式中。