db2开发基础.doc

DB2数据库开发基础—存储过程

1．为什么要使用存储过程
1) 减少客户机与服务器之间的网络使用率。客户机应用程序将控制权传送到数据库服务器上的存储过程。存储过程在数据库服务器上执行中间处理，而不需要在网络中传送不需要的数据。
2) 提高安全性。通过使用静态 SQL 的存储过程包含数据库特权，数据库管理员（DBA）特权，可以提高安全性。调用存储过程的客户机应用程序的用户不需要数据库特权。
3) 提高可靠性。在数据库应用程序环境中，许多任务是重复的。通过重用一个公共过程，存储过程就可以高效地解决这些重复情况。

2．使用SQL创建一个最简单的存储过程
执行创建存储过程语句
CREATE PROCEDURE TEST.P_PROCEDURE_TEST
()
BEGIN
END;

执行该存储过程

call TEST.P_PROCEDURE_TEST();

3．存储过程的结构

/*
创建存储过程
*/
CREATE PROCEDURE procedure_name
(/*参数列表*/)
/*存储过程体*/
BEGIN
/*
变量声明
1.系统变量
2.自变量
*/
......
......
/*
游标声明
*/
......
......

/*
异常处理
*/
......
......

/*
临时表声明
*/
......
......

/*
执行语句
*/
......
......

END;
注意声明的顺序必须按照此顺序，否则无法编译通过。

4．数据类型
定长型字符串（CHAR）
变长型字符串（VARCHAR）
整数类型（SMALLINT、INTEGER、BIGINT）
带小数点的数字类型（DECIMAL、REAL、DOUBLE）
时间类型（DATE、TIME、TIMESTAMP）
对象类型（BLOB、CLOB、DBCLOB）

5．参数声明
DB2储存过程的参数分为两部分：输入和输出参数。

参数表示方式:
输入参数用IN开头
输出参数用OUT开头
既是输入又是输出参数用INOUT开头
举例说明：
CREATE PROCEDURE TEST.P_PROCEDURE_TEST
(IN in_var integer,
OUT out_var varchar(20),
INOUT inout_var varchar(20))
BEGIN
END;

6．变量声明
存储过程中可以使用关键字DECLARE声明变量，然后在后续程序过程中使用变量来处理逻辑。定义变量时可以指定一个初始值。
CREATE PROCEDURE TEST.P_PROCEDURE_TEST
(IN in_var integer,
OUT out_var varchar(20),
INOUT inout_var varchar(20))
BEGIN
DECLARE var1 INTEGER DEFAULT 0;
DECLARE var2 VARCHAR(5) DEFAULT 'bc';
END;

7．赋值语句
存储过程使用关键字SET给变量赋值。

CREATE PROCEDURE TEST.P_PROCEDURE_TEST
(IN in_var integer,
OUT out_var varchar(20),
INOUT inout_var varchar(20))
BEGIN
DECLARE var1 INTEGER DEFAULT 0;
DECLARE var2 VARCHAR(5) DEFAULT 'bc';
SET var1 = in_var;
SET var1 = var1+1;
SET var2 = var2||'d';
SET out_var = var2;
SET inout_var= out_var;
END;

等号左边和右边的字段类型须保持一致。

8．常用操作符
1) 关系运算符
大于（>），小于（<），等于(=)，非等于(<>)，大于等于(>=)，小于等于(<=)

2) 关系运算符
AND,OR,NOT

9．条件控制语句
1) 使用IF THEN ELSE的结构
IF
/*条件1*/
THEN
/*执行语句1*/
ELSEIF
/*条件2*/
THEN
/*执行语句2*/
...
...
ELSE
/*其他情况执行语句*/
END IF;

例如：
CREATE PROCEDURE TEST.P_PROCEDURE_TEST
(IN in_var integer,
OUT out_var varchar(20),
INOUT inout_var varchar(20))
BEGIN
DECLARE var1 INTEGER DEFAULT 0;
DECLARE var2 VARCHAR(5) DEFAULT 'bc';
SET var1 = in_var;
SET var1 = var1+1;
SET var2 = var2||'d';

IF var1=1
THEN
SET var1=var1*10;
ELSEIF var1=2
THEN
SET var1=var1+10;
else
SET var2='a'||var2;
END IF;

SET out_var = var2;
SET inout_var= out_var;

END;

注意 结束时候请不要遗漏“END IF”;

2) 使用CASE…WHEN的结构
CASE WHEN
/*条件1*/
THEN
/*执行语句1*/
WHEN
/*条件2*/
THEN
/*执行语句2*/
...
...
ELSE
/*其他情况执行语句*/
END CASE;

例如：
CREATE PROCEDURE TEST.P_PROCEDURE_TEST
(IN in_var integer,
OUT out_var varchar(20),
INOUT inout_var varchar(20))
BEGIN
DECLARE var1 INTEGER DEFAULT 0;
DECLARE var2 VARCHAR(5) DEFAULT 'bc';
SET var1 = in_var;
SET var1 = var1+1;
SET var2 = var2||'d';

CASE
WHEN var1=1
THEN
SET var1=var1*10;
WHEN var1=2
THEN
SET var1=var1+10;
ELSE
SET var2='a'||var2;
END CASE;

SET out_var = var2;
SET inout_var= out_var;

END;

两种结构的区别：
IF 结构只能用于DB2过程、触发器、函数
CASE 结构可以用于基本SQL语句和DB2过程

10．循环语句
1) WHILE循环
WHILE /*条件*/ DO
/*
执行语句
*/
END WHILE;

例如：

CREATE PROCEDURE TEST.P_PROCEDURE_TEST
(IN in_var integer,
OUT out_var varchar(20),
INOUT inout_var varchar(20))
BEGIN
DECLARE var1 INTEGER DEFAULT 0;
DECLARE var2 VARCHAR(5) DEFAULT 'bc';
SET var1 = in_var;
SET var1 = var1+1;
SET var2 = var2||'d';

WHILE var1 < 100 DO
SET var1 = var1+1;
END WHILE;

SET out_var = cast(charvar1) as varchar(20));
SET inout_var= out_var;

END;

2) LOOP循环

LOOP循环只能在游标中使用，请参见12节 游标。

11．异常处理
异常处理是程序开发的关键。异常处理提供了一种处理异常的机制，将一些可以遇见的异常事先做好异常处理。方便调试程序以及提高程序质量。

1) 自定义异常     
  DECLARE CONDITION     
      FOR SQLSTATE ;   
  注： >可以从7-9或T-Z开头
2) 异常捕捉     
  DECLARE EXIT | CONTINUE | UNDO    
  HANDLER FOR 异常 | SQLSTATE     
 BEGIN   
       -- 获取SQLCODE，SELSTATE，SQLERRM信息到变量中 
   END;    
  注： EXIT:表示发生异常后，退出程序，之前的操作提交。
UNDO表示发生异常后，退出程序，之前的操作回滚。
CONTINUE表示发生异常后，程序继续执行。
      FOR 所捕捉的异常可以是SQLEXCEPTION，SQLWARNING，NOT FOUND，自定义异常

3) 异常抛出     
  SIGNAL     抛出自定义异常或者是已经定义的异常
  SIGNAL SQLSTATE [SET MESSAGE_TEXT = ];    抛出异常，制定异常的SQLSTATE和异常信息
  RESIGNAL     用在异常句柄捕捉中，指把此异常抛到上一层。
  RESIGNAL 和 SIGNAL 之间的区别在于，SIGNAL是在非HANDLER处理逻辑中抛出异常，而RESIGNAL则是在HANDLER处理逻辑中抛出异常。语法一致。    

4) 注：PROCEDURE中能够定义和捕捉异常，FUNCTION中不支持异常处理。 

例如：

CREATE PROCEDURE TEST.P_PROCEDURE_TEST
(IN in_var integer,
OUT out_var varchar(20),
INOUT inout_var varchar(20),
OUT OUT_ERR_MSG varchar(2000))
BEGIN
--系统变量
DECLARE SQLSTATE CHAR(5);
DECLARE SQLCODE INTEGER;
--自定义变量
DECLARE var1 INTEGER DEFAULT 0;
DECLARE var2 VARCHAR(5) DEFAULT 'bc';

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET OUT_ERR_MSG = RTRIM(CHAR(CURRENT TIMESTAMP))||' SQL ERROR：SQLCODE='||RTRIM(CHAR(SQLCODE))||',SQLSTATE='||SQLSTATE||'';
END;

set var1=1/in_var;

SET var1 = in_var;
SET var1 = var1+1;
SET var2 = var2||'d';

SET out_var = cast(char(var1) as varchar(20));
SET inout_var= out_var;

END;

12．游标
1) 静态游标
静态 SQL：静态 SQL 语句一般用于嵌入式 SQL 应用中，在程序运行前，SQL 语句必须是确定的，例如 SQL 语句中涉及的列名和表名必须是存在的。静态 SQL 语句的编译是在应用程序运行前进行的，编译的结果会存储在数据库内部。而后程序运行时，数据库将直接执行编译好的 SQL 语句，降低运行时的开销。
使用静态游标去打开一个静态 SQL结果集。

从例子中去了解如何使用游标。
下面这个例子是SYSIBM.SYSTABLES表中的字段排成一行，字段用逗号分隔开的一个例子。使用游标，去遍历SYSIBM.SYSCOLUMNS表实现。
CREATE PROCEDURE TEST.P_PROCEDURE_TEST
(
OUT out_var varchar(2000),
OUT OUT_ERR_MSG varchar(2000))
BEGIN
--定义系统变量
DECLARE SQLSTATE CHAR(5);
DECLARE SQLCODE INTEGER;
--字定义变量
DECLARE var2 VARCHAR(50) DEFAULT '';
DECLARE IS_NOT_FOUND INTEGER DEFAULT 0; /*请见LOOP*/

DECLARE c1 CURSOR FOR
select NAME
from SYSIBM.SYSCOLUMNS
where tbname='SYSTABLES' and tbcreator='SYSIBM'
order by colno;

DECLARE c2 CURSOR WITH RETURN FOR
select NAME
from SYSIBM.SYSCOLUMNS
where tbname='SYSTABLES' and tbcreator='SYSIBM'
order by colno;

--定义SQL异常
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET OUT_ERR_MSG = RTRIM(CHAR(CURRENT TIMESTAMP))||' SQL ERROR：SQLCODE='||RTRIM(CHAR(SQLCODE))||',SQLSTATE='||SQLSTATE||'';
END;
--定义NOT FOUND异常
DECLARE CONTINUE HANDLER FOR NOT FOUND/*为什么要定义这么一个异常*/
BEGIN
SET IS_NOT_FOUND=1;
END;

set out_var='';

open c1;
c1_loop: /*LOOP用法介绍*/
loop
fetch c1 into var2;
set out_var=out_var||var2||',';
if IS_NOT_FOUND=1
then leave c1_loop;
end if;
end loop;
close c1;

open c2;

--close c2;

END;

定义not found异常：如果一个游标打开一个空结果集合，或者在读取完集合中最后一条记录后，无记录可以读取，这样的情况下，会抛出not found异常，不做处理，程序自动结束。

2) 动态游标
动态 SQL：动态 SQL 语句是在应用程序运行时被编译和执行的，例如，使用 DB2 的交互式工具 CLP 访问数据库时，用户输入的 SQL 语句是不确定的，因此 SQL 语句只能被动态地编译。
使用动态游标去打开一个动态SQL。
动态游标不仅可以执行SQL查询语句，还可以执行insert,update,delete,create等语句。
以下例子是将静态游标中的例子转换为用动态游标实现。最后执行建表语句，测试动态游标还可以执行SQL查询语句以外的语句。
CREATE PROCEDURE TEST.P_PROCEDURE_TEST
(
OUT out_var varchar(2000),
OUT OUT_ERR_MSG varchar(2000))
BEGIN

DECLARE SQLSTATE CHAR(5);
DECLARE SQLCODE INTEGER;

DECLARE var2 VARCHAR(50) DEFAULT '';
DECLARE IS_NOT_FOUND INTEGER DEFAULT 0;
DECLARE sql_stmt VARCHAR(2000);

--声明放游标的值
DECLARE stmt1 STATEMENT;
--声明动态游标存储变量
DECLARE c1 CURSOR FOR stmt1;

--定义SQL异常
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET OUT_ERR_MSG = RTRIM(CHAR(CURRENT TIMESTAMP))||' SQL ERROR：SQLCODE='||RTRIM(CHAR(SQLCODE))||',SQLSTATE='||SQLSTATE||'';
END;
--定义NOT FOUND异常
DECLARE CONTINUE HANDLER FOR NOT FOUND
BEGIN
SET IS_NOT_FOUND=1;
END;

set out_var='';

set sql_stmt='select NAME from SYSIBM.SYSCOLUMNS where tbname=''SYSTABLES'' and tbcreator=''SYSIBM'' order by colno';--

--将语句放入游标值中
PREPARE stmt1 FROM sql_stmt;

open c1;
c1_loop:
loop
fetch c1 into var2;
set out_var=out_var||var2||',';
if IS_NOT_FOUND=1
then leave c1_loop;
end if;
end loop;
close c1;

--用静态游标执行建表语句
set sql_stmt='create table test.cursor_test(seq integer)';
PREPARE stmt1 FROM sql_stmt;
EXECUTE stmt1;

END;

13．定义临时表
DECLARE GLOBAL TEMPORARY TABLE table_name (
/*
参数列表
*/
)NOT LOGGED IN tablespace_name WITH REPLACE;

其中not logged指不记录事务日志，with replace指在程序结束后自动删除该临时表。

注意 在使用时需要加上”Session”作为表的模式名，否则编译器会认为是数据库中的实表。

例如：
CREATE PROCEDURE TEST.P_PROCEDURE_TEST
(IN in_var integer,
OUT out_var integer,
INOUT inout_var varchar(20),
OUT OUT_ERR_MSG varchar(2000))
BEGIN
--系统变量
DECLARE SQLSTATE CHAR(5);
DECLARE SQLCODE INTEGER;
--自定义变量
DECLARE var1 INTEGER DEFAULT 0;
DECLARE var2 VARCHAR(5) DEFAULT 'bc';

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET OUT_ERRMSG = RTRIM(CHAR(CURRENT TIMESTAMP))||' SQL ERROR：SQLCODE='||RTRIM(CHAR(SQLCODE))||',SQLSTATE='||SQLSTATE||'';
END;

DECLARE GLOBAL TEMPORARY TABLE SEQ_TABLE (
SEQ INTEGER
)NOT LOGGED WITH REPLACE;

while in_var<100 do
set in_var=in_var+1;
insert into SESSION.SEQ_TABLE
values(in_var);
END WHILE;

SET out_var=(select count(*) from SESSION.SEQ_TABLE);

END;