DB2 存储过程
一、什么是存储过程
受 DB2 服务器控制的一段可执行程序
可以通过SQL的CALL语句来完成对存储过程的调用
在存储过程中可以包含业务逻辑
存储过程可以在本地或远程进行调用
存储过程可以接收或传递参数，生成结果集
二、存储过程特征
包含使用sql语句的过程构造
存储在数据库中且在db2 服务器上运行；
可以由正在使用的sql的应用程序根据名称来调用；
允许应用程序分2部分允许，在客户机上运行应用程序，在服务器上运行存储过程
存储过程在应用程序中的优势
减少了客户机与服务器直接的网络使用率
增强了硬件和软件功能
提高了安全性
减少了开发成本并且提高了可靠性
集中处理了公共例程的安全性、管理和维护
通过sql pl 当前的语句集合和语言特性，可以用sql开发综合的、高级的程序
例如函数、存储过程和触发器。这样便可以将业务逻辑封装到易于维护的数据库对象中，从而提高数据库应用程序的性能。
SQL PL 支持本地和全局变量，包括声明和赋值，还支持条件语句和迭代语句、控制语句的转移、错误管理语句以及返回结果集的方法。
三、什么时候使用存储过程
使用存储过程的合适时机:
应用程序的性能无法满足预期时
客户端数量较多且应用程序中SQL代码分散时
应用程序需要进行繁重的数据库操作，同时这些操作并不需要进行太多的客户交互
应用程序代码更改频繁
需要对客户应用代码进行访问控制时
客户应用需要在一次操作中执行多条 SQL 语句
五、数据类型
字符型：char varchar
日期型 date
数字型 number decilmal integer
详细请看屌丝大哥 db2数据类型介绍的那一课
六、Db2 存储过程基本语法
6.1 存储过程结构
CREATE OR REPLACE PROCEDURE <过程名>
( [ IN | OUT | INOUT ] 参数名 数据类型 默认值 )
LANGUAGE SQL
BEGIN
业务逻辑代码
END;
IN（输入参数）
只是将实参传递给存储过程，但在存储过程中不能对其进行修改。换句话说，对于存储过程而言它是只读的。
OUT（输出参数）
在存储过程结束时向调用者返回。一般在过程中都会被赋值。
INOUT（输入输出参数）
上述两种参数类型的结合体。它可以帮助调用者将实参传递给进程，另外它也能够作为输出参数被修改和赋值。
复合语句实例
复合语句是指包含在BEGIN和END间的语句。它一般包括如下语句类型：
声明语句
赋值语句
控制语句
条件处理语句
说明：
1. 复合语句可以嵌套使用。
2. BEGIN语句可以和标签组合使用，这样可以更清晰的标识语句块的范围。
6.2 变量声明与变量赋值
变量声明语法：
DECLARE 变量名 数据类型 初始值；
Delcare DiaoSiName varchar（20）；
变量赋值语法 ：set 变量名=值；
例如：给屌丝姓名变量赋值。
Set DiaoSiName = ‘奶娃’;
变量声明
DECLARE my_var INTEGER DEFAULT 6;
条件声明
DECLARE not_found CONDITION FOR SQLSTATE ‘02000’;
游标声明
DECLARE c1 CURSOR FOR select * from staff;
异常处理器声明
DECLARE EXIT HANDLER FOR SQLEXCEPTION …;
语法
SET lv_name = expression;
SET lv_name = NULL;
示例
(1) SET salary = salary + salary * 0.1;
(2) SET init_salary = NULL;
(3) SET salary = (select salary from employee where empno = lv_emp_num);
注: 如果 SELECT 语句返回记录超过一行，示例 3 将会返回SQLERROR。
模块 - 规格说明(Module Specification)
模块可以发布type, SP, UDF以供外部使用。
CREATE OR REPLACE MODULE myMod;
ALTER MODULE myMod PUBLISH
TYPE myRowTyp AS ANCHOR ROW myTab;
ALTER MODULE myMod PUBLISH
FUNCTION myFunc(val1 ANCHOR myTab.col1)
RETURNS myRowTyp;
ALTER MODULE myMod PUBLISH
PROCEDURE myProc(OUT param1 ANCHOR myTab.col2);
模块 - 实现(Module Implementation)
下面的代码是模块的实现部分：
ALTER MODULE myMod ADD VARIABLE pkgVar ANCHOR myTab.col1;
ALTER MODULE myMod ADD FUNCTION myFunc(val1 ANCHOR myTab.col1) RETURNS myRowTyp
BEGIN
DECLARE var1 myRowTyp;
SELECT * INTO var1 FROM myTab WHERE col1 < val1 AND col1 > pkgVar;
RETURN var1;
END
ALTER MODULE myMod ADD PROCEDURE myProc(OUT param1 ANCHOR myTab.col2)
BEGIN
DECLARE varRow myRowTyp;
SET param1 = varRow.col2 – pkgVar;
END
模块 - 其他语句
删除整个模块
DROP MODULE myMod;
保留规格说明内容，删除实现
ALTER MODULE myMod DROP BODY;
删除模块中的存储过程(SP)
ALTER MODULE myMod DROP PROCEDURE myProc;
将模块的执行权限赋给joe
GRANT EXECUTE ON MODULE myMod TO joe;
七、控制语句
IF语句
格式：
IF 条件1 THEN statement1;
ELSEIF 条件2 THEN statement2;
ELSE statement3;
END IF;
注:条件成立时为TRUE (真),不成立时为FALSE(假) 和 NULL
IF语句例子
IF rating = 1 THEN
UPDATE EMPLOYEE SET salary = salary*1.10
WHERE empno = i_num;（如果满足于...时，薪水调整1.1倍）
ELSEIF rating = 2 THEN
UPDATE EMPLOYEE SET salary = salary*1.05
WHERE empno = i_num;
ELSE
UPDATE EMPLOYEE SET salary = salary*1.03
WHERE empno = i_num;
END IF;
CASE语句（1 of 2）
简单CASE语句
稍加变形的CASE语句
LOOP语句
语法
[LABEL] LOOP
SQL-procedure-statements;
END LOOP [LABEL];
示例
fetch_loop: LOOP
FETCH c1 INTO v_firstname, v_lastname;
SET counter = counter + 1;
IF counter = 51 THEN
LEAVE fetch_loop;
END IF;
END LOOP fetch_loop;
FOR语句
语法
[LABEL] FOR for-loop-name AS [cursor-name CURSOR FOR]
select-statement
DO
SQL-procedure-statements;
END FOR [LABEL];
示例
DECLARE fullname CHAR(40);
FOR v1 AS c1 CURSOR FOR SELECT firstnme, midinit, lastname FROM employee
DO
SET fullname=lastname||‘,’||firstnme||’,’||midinit;
INSERT INTO tname VALUE (fullname);
END FOR;
====================================分割线================================
最新内容请见作者的GitHub页：http://qaseven.github.io/