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) BEGINDECLARE 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) BEGINDECLARE 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,NOT9条件控制语句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) BEGINDECLARE 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=2THEN SET var1=var1+10;else SET var2=a|var2;END IF;SET out_var = var2;SET inout_var= out_var;END;注意 结束时候请不要遗漏“END IF”;2) 使用CASEWHEN的结构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) BEGINDECLARE var1 INTEGER DEFAULT 0;DECLARE var2 VARCHAR(5) DEFAULT bc;SET var1 = in_var;SET var1 = var1+1;SET var2 = var2|d;CASEWHEN 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) BEGINDECLARE 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(char(var1) 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 | UNDOHANDLER 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 NAMEfrom SYSIBM.SYSCOLUMNSwhere tbname=SYSTABLES and tbcreator=SYSIBMorder by colno;DECLARE c2 CURSOR WITH RETURN FORselect NAMEfrom SYSIBM.SYSCOLUMNSwhere tbname=SYSTABLES and tbcreator=SYSIBMorder 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用法介绍*/loopfetch 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) BEGINDECLARE 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 FOUNDBEGIN 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: loopfetch 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_ERR_MSG = 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_var100 doset in_var=in_var+1;insert into SESSION.SEQ_TABLEvalues(in_var);END WHILE;SET out_var=(select count(*) from SESSION.SEQ_TABLE);END;