db2 游标及其他

定义游标：
DECLARE 游标名 CURSOR FOR
   Select 语句；
打开游标：
    OPEN　游标名；
取值：
    　FETCH　游标名　INTO 变量列表
例：
DECLARE c1 CURSOR FOR
SELECT CAST(salary AS DOUBLE)
FROM staff
WHERE DEPT = deptNumber
ORDER BY salary;
DECLARE EXIT HANDLER FOR NOT FOUND
SET medianSalary = 6666;
SET medianSalary = 0;
SELECT COUNT(*) INTO v_numRecords
FROM staff
WHERE DEPT = deptNumber;
OPEN c1;
WHILE v_counter < (v_numRecords / 2 + 1) DO
FETCH c1 INTO medianSalary;
SET v_counter = v_counter + 1;
END WHILE;
CLOSE c1;
注：游标的申明如果放在中间段，要用”begin。。。end;”.段分割标志分割开；
动态sql
1) declare stmt varchar(1024);
set stmt='create table zhouhaiming( f1 smallint, f2 varchar(9), f3 char(5) )';
prepare s1 from stmt;
execute s1;
set stmt='insert into zhouhaiming values (1,'www','aaa')';
prepare s1 from stmt;
execute s1;
2) DECLARE CURSOR C1 FOR STMT1;
      PREPARE STMT1 FROM
         'ALLOCATE C2 CURSOR FOR RESULT SET ?';
临时表的建立
DECLARE GLOBAL TEMPORARY TABLE TABLE_NAME
AS (FULLSELECT) DEFINITION ONLY
EXCLUDING IDENTITY COLUMN ATTRIBUTES
   ON COMMIT DELETE ROWS
   NOT LOGGED IN 临时表空间名with   replace；
第一行規定臨時表的名稱.
   第二行規定臨時表的列的定義.
   第三行規定不是從源結果表定義中復制的恒等列.
   第四行規定如果沒有打開WITH GOLD光標,將會刪除表的所有行.
   第五行規定不對表的改變進行記錄.
   With replace选项会隐式的自动删除该临时表。
  例如:
   DECLARE GLOBAL TEMPORARY TABLE DEC_BSEMPMS
   AS (SELECT * FROM BSEMPMS) DEFINITION ONLY
   EXCLUDING IDENTITY COLUMN ATTRIBUTES
   ON COMMIT DELETE ROWS
   NOT LOGGED；
DB2中的几个全局变量
n        ROW_COUNT—影响行数
UPDATE CORPDATA.PROJECT
SET PRSTAFF = PRSTAFF + 1.5
WHERE DEPTNO = deptnbr;
GET DIAGNOSTICSrcount = ROW_COUNT;
n        RETURN_STATUS--返回状态
CALL TRYIT;--调用存储过程
GET DIAGNOSTICSRETVAL = RETURN_STATUS;
IF RETVAL <> 0 THEN
...
LEAVE A1;
ELSE
...
END IF;
n        SQLSTATE—SQL返回错误代码
注：使用前必先定义
declare sqlstate char(5);
declare state char(5);
insert into tbname values(…)
set state=sqlstate;
if(state<> '00000') then
return -1;
end if;     
关于ATOMIC和NOT ATOMIC
P1:BEGIN ATOMIC –P1段的事务会自动回滚
P1:BEGIN NOT ATOMIC –P1段的事务不会自动回滚
DB2中的条件句柄
句柄类型:
n        CONTINUE
n        EXIT
n        UNDO
条件类型：
n        SQLSTATE string
n        SQLEXCEPTION
n        SQLWARNING
n        NOT FOUND
例：
1）DECLARE EXIT HANDLER FOR NOT FOUND
SET medianSalary = 6666;
2） DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE EXIT HANDLER FOR not_found
SET rating = -1;
3)    DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE c1 CURSOR FOR
SELECT deptno, deptname, admrdept
FROM department
ORDER BY deptno;
DECLARE CONTINUE HANDLER FOR not_found
SET at_end = 1;
如何抽取/提交存储过程
db2 "get routine into 文件名 from procedure 存储过程名"
抽取存储过程；
提交存储过程
db2 "put routine from 文件名"
安装已编译好的存储过程。
如何在命令符下提交存储过程
在存储过程的最后加上@符号，然后在命令符下打入：db2 -td@ -vf procfile.sql 就可以生成过程。
非存储过程的SQL文件，在命令符下打入：db2 –tvf sqlfile.sql
从存储过程返回结果集(游标)的用法
1、建一sp返回结果集
CREATE PROCEDURE DB2INST1.Proc1 ( 
     LANGUAGE SQL
     result sets 2 --(返回两个结果集)
P1: BEGIN
         declare c1 cursor   with return to caller for
             select   market_code
             from     tb_market_code;
         --指定该结果集用于返回给调用者
         declare c2 cursor   with return to caller for
             select   market_code
             from     tb_market_code;
          open c1;
          open c2;
END P1                                       

2、建一SP调该sp且使用它的结果集

CREATE PROCEDURE DB2INST1.Proc2 (
out out_market_code char(1))
     LANGUAGE SQL
P1: BEGIN
declare loc1,loc2 result_set_locator varying;
--建立一个结果集数组
call proc1;
--调用该SP返回结果集。
associate result set locator(loc1,loc2) with procedure proc1;
--将返回结果集和结果集数组关联
allocate cursor1 cursor for result set loc1;
allocate cursor2 cursor for result set loc2;
--将结果集数组分配给cursor
fetch   cursor1 into out_market_code;
--直接从结果集中赋值
close cursor1;        
END P1
－－－－－－－－－－－－－－－－－－－－－－－
db2存储过程里单引号：
一个单引号，是最正常的情况，比如：'asdfas'，这代表一个字符串，显示的内容是 asdfas ；

两个单引号，一般成对出现在一个单引号之内，表示一个单引号之内的单引号，比如
'sdfs''123''dfsdf'，这种字符串显示的时候就是 sdfs'123'dfsdf

三个或四个单引号的情况是在一个和两个的情况之上发展出来的，比如'asd'''||输入值||''''，它显示的时候内容就是：
asd'输入值'。
（把一对两个单引号分开了，一部分在||之前，一部分在||之后）
（后面四个单引号分开看，中间两个是一对--代表一对单引号之内的一个单引号，外边两个是一对--代表一对单引号）
－－－－－－－－－－－－－－－－－－－－
解释不大正确
一个单引号是你的解释，2个单引号不是你解释的意思，两个单引号代表空串，也就是所谓的空字符，三个和你解释的一样，四个其实和三个一个意思。
二个单引号的意思在有些数据库里面等同于null，例如db2
select * from a where name=''
等同于
select * from a where name is null
当然，有些数据库的确可以用两个单引号当作去异使用，但是绝大多数不是
就算是sqlserver虽然支持两个单引号的做法，但是有时候还是会出错，所以最好还是用三个比较好
比如
'abc＝‘'＋’dda‘拼出来可能是abc’＝dda
但是大多数情况下它会报错，因为认为前面是‘abc='、
'+'、dda'三个部分，一般这里都会报错

问题1：
什么时候才会发生not found异常 ，以及db2中sqlcode的值是如何变化的？
在db2中，一条select 语句也有可能发生not found异常，譬如
declare sqlcode integer default 0;
declare sql_code integer default 0;
declare classCode varchar(40) ;
select app_class_code into classCode from kf_app_class where app_name='无效记录';
set sql_code=sqlcode;
如果此时没有检索到记录，那么sqlcode的值为100，有的话为0;
我们可以定义NOT FOUND 异常处理

declare sqlcode integer default 0;
declare sql_code integer default 0;
declare classCode varchar(40) ;
begin
declare continue handler for not found
begin
--注如果发生not found那么此时的sqlcode必定为100
set sql_code=sqlcode;/*在这里sqlcode的值为100；*/
--如果再次得到sqlcode的值那么它的值变为0
set sql_code=sqlcode;/*这里sqlcode变成了0，因为上一条语句执行成功了，那么sqlcode变成了0*/
end;
select app_class_code into classCode from kf_app_class where app_name='无效记录';
set sql_code=sqlcode;/*同理此时如果没有取到数据，那么会进declare continue handler ，返回后sqlcode的值也为0*/
end;
所以我们可以通过两种方法来捕获和处理not found
方法1：

begin
declare continue handler for not found
begin
--异常处理代码
end;
sql语句
end;
方法2：

begin

sql语句
if sqlcode=100 then
--异常处理代码
end if;
end;
问题2：
定义了游标，怎么fecth一条记录，怎么进行循环？
Q:定义了游标假设发生not found 异常，那么是在open cursorName的时候还是在fecth的时候发生异常？
A：检验游标中的数据是否取完或者有无记录，应该在fecth的时候，而不是发生在open cursorName的时候，
下面一个例子详细的说明了游标使用过程

begin
declare sqlcode integer default 0;
declare app_code varchar(10);
declare cursor1 cursor for select app_code from kf_app_class ;
open cursor1;
cursorLoop:
loop
fecth cursor1 into app_code ;
if sqlcode=100 then leave cursorLoop;
end if;
end loop;
end;

Q:sqlcode 可以直接用吗？
A:在db2中，如果要使用sqlcode那么必须在使用前declare;
譬如
declare sqlcode integer default 0;
if sqlcode =? then
end if;
附注
db2的其他异常处理

对应 oracle的 when other exceptions

declare exit handler for sqlwaring,sqlexcption
begin
－－处理异常
end;

当程序执行exit handler异常处理后，那么会退出程序，不会在接着执行，也就是


declare exit handler for sqlwaring,sqlexcption
begin
－－处理异常
end;
sql语句1；
sql语句2；
执行sql语句1发生异常，会进入 exit handler ，然后退出程序，不会在执行 sql语句2