db2开发基础.docx
《db2开发基础.docx》由会员分享，可在线阅读，更多相关《db2开发基础.docx（15页珍藏版）》请在冰点文库上搜索。
db2开发基础
DB2数据库开发基础—存储过程
1．为什么要使用存储过程
1）减少客户机与服务器之间的网络使用率。
客户机应用程序将控制权传送到数据库服务器上的存储过程。
存储过程在数据库服务器上执行中间处理，而不需要在网络中传送不需要的数据。
2）提高安全性。
通过使用静态SQL的存储过程包含数据库特权，数据库管理员（DBA）特权，可以提高安全性。
调用存储过程的客户机应用程序的用户不需要数据库特权。
3）提高可靠性。
在数据库应用程序环境中，许多任务是重复的。
通过重用一个公共过程，存储过程就可以高效地解决这些重复情况。
2．使用SQL创建一个最简单的存储过程
执行创建存储过程语句
CREATEPROCEDURETEST.P_PROCEDURE_TEST
（）
BEGIN
END;
执行该存储过程
callTEST.P_PROCEDURE_TEST（）;
3．存储过程的结构
/*
创建存储过程
*/
CREATEPROCEDUREprocedure_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开头
举例说明：
CREATEPROCEDURETEST.P_PROCEDURE_TEST
（INin_varinteger,
OUTout_varvarchar（20）,
INOUTinout_varvarchar（20））
BEGIN
END;
6．变量声明
存储过程中可以使用关键字DECLARE声明变量，然后在后续程序过程中使用变量来处理逻辑。
定义变量时可以指定一个初始值。
CREATEPROCEDURETEST.P_PROCEDURE_TEST
（INin_varinteger,
OUTout_varvarchar（20）,
INOUTinout_varvarchar（20））
BEGIN
DECLAREvar1INTEGERDEFAULT0;
DECLAREvar2VARCHAR（5）DEFAULT'bc';
END;
7．赋值语句
存储过程使用关键字SET给变量赋值。
CREATEPROCEDURETEST.P_PROCEDURE_TEST
（INin_varinteger,
OUTout_varvarchar（20）,
INOUTinout_varvarchar（20））
BEGIN
DECLAREvar1INTEGERDEFAULT0;
DECLAREvar2VARCHAR（5）DEFAULT'bc';
SETvar1=in_var;
SETvar1=var1+1;
SETvar2=var2||'d';
SETout_var=var2;
SETinout_var=out_var;
END;
等号左边和右边的字段类型须保持一致。
8．常用操作符
1）关系运算符
大于（>），小于（<），等于（=），非等于（<>），大于等于（>=），小于等于（<=）
2）关系运算符
AND,OR,NOT
9．条件控制语句
1）使用IFTHENELSE的结构
IF
/*条件1*/
THEN
/*执行语句1*/
ELSEIF
/*条件2*/
THEN
/*执行语句2*/
...
...
ELSE
/*其他情况执行语句*/
ENDIF;
例如：
CREATEPROCEDURETEST.P_PROCEDURE_TEST
（INin_varinteger,
OUTout_varvarchar（20）,
INOUTinout_varvarchar（20））
BEGIN
DECLAREvar1INTEGERDEFAULT0;
DECLAREvar2VARCHAR（5）DEFAULT'bc';
SETvar1=in_var;
SETvar1=var1+1;
SETvar2=var2||'d';
IFvar1=1
THEN
SETvar1=var1*10;
ELSEIFvar1=2
THEN
SETvar1=var1+10;
else
SETvar2='a'||var2;
ENDIF;
SETout_var=var2;
SETinout_var=out_var;
END;
注意结束时候请不要遗漏“ENDIF”;
2）使用CASE…WHEN的结构
CASEWHEN
/*条件1*/
THEN
/*执行语句1*/
WHEN
/*条件2*/
THEN
/*执行语句2*/
...
...
ELSE
/*其他情况执行语句*/
ENDCASE;
例如：
CREATEPROCEDURETEST.P_PROCEDURE_TEST
（INin_varinteger,
OUTout_varvarchar（20）,
INOUTinout_varvarchar（20））
BEGIN
DECLAREvar1INTEGERDEFAULT0;
DECLAREvar2VARCHAR（5）DEFAULT'bc';
SETvar1=in_var;
SETvar1=var1+1;
SETvar2=var2||'d';
CASE
WHENvar1=1
THEN
SETvar1=var1*10;
WHENvar1=2
THEN
SETvar1=var1+10;
ELSE
SETvar2='a'||var2;
ENDCASE;
SETout_var=var2;
SETinout_var=out_var;
END;
两种结构的区别：
IF结构只能用于DB2过程、触发器、函数
CASE结构可以用于基本SQL语句和DB2过程
10．循环语句
1）WHILE循环
WHILE/*条件*/DO
/*
执行语句
*/
ENDWHILE;
例如：
CREATEPROCEDURETEST.P_PROCEDURE_TEST
（INin_varinteger,
OUTout_varvarchar（20）,
INOUTinout_varvarchar（20））
BEGIN
DECLAREvar1INTEGERDEFAULT0;
DECLAREvar2VARCHAR（5）DEFAULT'bc';
SETvar1=in_var;
SETvar1=var1+1;
SETvar2=var2||'d';
WHILEvar1<100DO
SETvar1=var1+1;
ENDWHILE;
SETout_var=cast（char（var1）asvarchar（20））;
SETinout_var=out_var;
END;
2）LOOP循环
LOOP循环只能在游标中使用，请参见12节游标。
11．异常处理
异常处理是程序开发的关键。
异常处理提供了一种处理异常的机制，将一些可以遇见的异常事先做好异常处理。
方便调试程序以及提高程序质量。
1）自定义异常
DECLARECONDITION
FORSQLSTATE;
注：
可以从7-9或T-Z开头
2）异常捕捉
DECLAREEXIT|CONTINUE|UNDO
HANDLERFOR异常|SQLSTATE
BEGIN
--获取SQLCODE，SELSTATE，SQLERRM信息到变量中
END;
注：
EXIT:
表示发生异常后，退出程序，之前的操作提交。
UNDO表示发生异常后，退出程序，之前的操作回滚。
CONTINUE表示发生异常后，程序继续执行。
FOR 所捕捉的异常可以是：
SQLEXCEPTION，SQLWARNING，NOTFOUND，自定义异常
3）异常抛出
SIGNAL 抛出自定义异常或者是已经定义的异常
SIGNALSQLSTATE[SETMESSAGE_TEXT=]; 抛出异常，制定异常的SQLSTATE和异常信息
RESIGNAL 用在异常句柄捕捉中，指把此异常抛到上一层。
RESIGNAL和SIGNAL之间的区别在于，SIGNAL是在非HANDLER处理逻辑中抛出异常，而RESIGNAL则是在HANDLER处理逻辑中抛出异常。
语法一致。
4）注：
PROCEDURE中能够定义和捕捉异常，FUNCTION中不支持异常处理。
例如：
CREATEPROCEDURETEST.P_PROCEDURE_TEST
（INin_varinteger,
OUTout_varvarchar（20）,
INOUTinout_varvarchar（20）,
OUTOUT_ERR_MSGvarchar（2000））
BEGIN
--系统变量
DECLARESQLSTATECHAR（5）;
DECLARESQLCODEINTEGER;
--自定义变量
DECLAREvar1INTEGERDEFAULT0;
DECLAREvar2VARCHAR（5）DEFAULT'bc';
DECLAREEXITHANDLERFORSQLEXCEPTION
BEGIN
SETOUT_ERR_MSG=RTRIM（CHAR（CURRENTTIMESTAMP））||'SQLERROR：
SQLCODE='||RTRIM（CHAR（SQLCODE））||',SQLSTATE='||SQLSTATE||'';
END;
setvar1=1/in_var;
SETvar1=in_var;
SETvar1=var1+1;
SETvar2=var2||'d';
SETout_var=cast（char（var1）asvarchar（20））;
SETinout_var=out_var;
END;
12．游标
1）静态游标
静态SQL：
静态SQL语句一般用于嵌入式SQL应用中，在程序运行前，SQL语句必须是确定的，例如SQL语句中涉及的列名和表名必须是存在的。
静态SQL语句的编译是在应用程序运行前进行的，编译的结果会存储在数据库内部。
而后程序运行时，数据库将直接执行编译好的SQL语句，降低运行时的开销。
使用静态游标去打开一个静态SQL结果集。
从例子中去了解如何使用游标。
下面这个例子是SYSIBM.SYSTABLES表中的字段排成一行，字段用逗号分隔开的一个例子。
使用游标，去遍历SYSIBM.SYSCOLUMNS表实现。
CREATEPROCEDURETEST.P_PROCEDURE_TEST
（
OUTout_varvarchar（2000）,
OUTOUT_ERR_MSGvarchar（2000））
BEGIN
--定义系统变量
DECLARESQLSTATECHAR（5）;
DECLARESQLCODEINTEGER;
--字定义变量
DECLAREvar2VARCHAR（50）DEFAULT'';
DECLAREIS_NOT_FOUNDINTEGERDEFAULT0;/*请见LOOP*/
DECLAREc1CURSORFOR
selectNAME
fromSYSIBM.SYSCOLUMNS
wheretbname='SYSTABLES'andtbcreator='SYSIBM'
orderbycolno;
DECLAREc2CURSORWITHRETURNFOR
selectNAME
fromSYSIBM.SYSCOLUMNS
wheretbname='SYSTABLES'andtbcreator='SYSIBM'
orderbycolno;
--定义SQL异常
DECLAREEXITHANDLERFORSQLEXCEPTION
BEGIN
SETOUT_ERR_MSG=RTRIM（CHAR（CURRENTTIMESTAMP））||'SQLERROR：
SQLCODE='||RTRIM（CHAR（SQLCODE））||',SQLSTATE='||SQLSTATE||'';
END;
--定义NOTFOUND异常
DECLARECONTINUEHANDLERFORNOTFOUND/*为什么要定义这么一个异常*/
BEGIN
SETIS_NOT_FOUND=1;
END;
setout_var='';
openc1;
c1_loop:
/*LOOP用法介绍*/
loop
fetchc1intovar2;
setout_var=out_var||var2||',';
ifIS_NOT_FOUND=1
thenleavec1_loop;
endif;
endloop;
closec1;
openc2;
--closec2;
END;
定义notfound异常：
如果一个游标打开一个空结果集合，或者在读取完集合中最后一条记录后，无记录可以读取，这样的情况下，会抛出notfound异常，不做处理，程序自动结束。
2）动态游标
动态SQL：
动态SQL语句是在应用程序运行时被编译和执行的，例如，使用DB2的交互式工具CLP访问数据库时，用户输入的SQL语句是不确定的，因此SQL语句只能被动态地编译。
使用动态游标去打开一个动态SQL。
动态游标不仅可以执行SQL查询语句，还可以执行insert,update,delete,create等语句。
以下例子是将静态游标中的例子转换为用动态游标实现。
最后执行建表语句，测试动态游标还可以执行SQL查询语句以外的语句。
CREATEPROCEDURETEST.P_PROCEDURE_TEST
（
OUTout_varvarchar（2000）,
OUTOUT_ERR_MSGvarchar（2000））
BEGIN
DECLARESQLSTATECHAR（5）;
DECLARESQLCODEINTEGER;
DECLAREvar2VARCHAR（50）DEFAULT'';
DECLAREIS_NOT_FOUNDINTEGERDEFAULT0;
DECLAREsql_stmtVARCHAR（2000）;
--声明放游标的值
DECLAREstmt1STATEMENT;
--声明动态游标存储变量
DECLAREc1CURSORFORstmt1;
--定义SQL异常
DECLAREEXITHANDLERFORSQLEXCEPTION
BEGIN
SETOUT_ERR_MSG=RTRIM（CHAR（CURRENTTIMESTAMP））||'SQLERROR：
SQLCODE='||RTRIM（CHAR（SQLCODE））||',SQLSTATE='||SQLSTATE||'';
END;
--定义NOTFOUND异常
DECLARECONTINUEHANDLERFORNOTFOUND
BEGIN
SETIS_NOT_FOUND=1;
END;
setout_var='';
setsql_stmt='selectNAMEfromSYSIBM.SYSCOLUMNSwheretbname=''SYSTABLES''andtbcreator=''SYSIBM''orderbycolno';--
--将语句放入游标值中
PREPAREstmt1FROMsql_stmt;
openc1;
c1_loop:
loop
fetchc1intovar2;
setout_var=out_var||var2||',';
ifIS_NOT_FOUND=1
thenleavec1_loop;
endif;
endloop;
closec1;
--用静态游标执行建表语句
setsql_stmt='createtabletest.cursor_test（seqinteger）';
PREPAREstmt1FROMsql_stmt;
EXECUTEstmt1;
END;
13．定义临时表
DECLAREGLOBALTEMPORARYTABLEtable_name（
/*
参数列表
*/
）NOTLOGGEDINtablespace_nameWITHREPLACE;
其中notlogged指不记录事务日志，withreplace指在程序结束后自动删除该临时表。
注意在使用时需要加上”Session”作为表的模式名，否则编译器会认为是数据库中的实表。
例如：
CREATEPROCEDURETEST.P_PROCEDURE_TEST
（INin_varinteger,
OUTout_varinteger,
INOUTinout_varvarchar（20）,
OUTOUT_ERR_MSGvarchar（2000））
BEGIN
--系统变量
DECLARESQLSTATECHAR（5）;
DECLARESQLCODEINTEGER;
--自定义变量
DECLAREvar1INTEGERDEFAULT0;
DECLAREvar2VARCHAR（5）DEFAULT'bc';
DECLAREEXITHANDLERFORSQLEXCEPTION
BEGIN
SETOUT_ERR_MSG=RTRIM（CHAR（CURRENTTIMESTAMP））||'SQLERROR：
SQLCODE='||RTRIM（CHAR（SQLCODE））||',SQLSTATE='||SQLSTATE||'';
END;
DECLAREGLOBALTEMPORARYTABLESEQ_TABLE（
SEQINTEGER
）NOTLOGGEDWITHREPLACE;
whilein_var<100do
setin_var=in_var+1;
insertintoSESSION.SEQ_TABLE
values（in_var）;
ENDWHILE;
SETout_var=（selectcount（*）fromSESSION.SEQ_TABLE）;
END;
升级会员 