db2 游标及其余
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
定义游标：
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