DB2中游标的使用
注意commit和rollback
使用游标时要特别注意如果没有加with hold 选项，在Commit和Rollback时，该游标将被关闭。Commit 和Rollback有很多东西要注意。特别小心
游标的两种定义方式
一种为
declare continue handler for not found
begin
set v_notfound = 1;
end;
declare cursor1 cursor with hold for select market_code from tb_market_code for update;
open cursor1;
set v_notfound=0;
fetch cursor1 into v_market_code;
while v_notfound=0 Do
--work
set v_notfound=0;
fetch cursor1 into v_market_code;
end while;
close cursor1;
这种方式使用起来比较复杂，但也比较灵活。特别是可以使用with hold 选项。如果循环内有commit或r ollback 而要保持该cursor不被关闭，只能使用这种方式。
另一种为
pcursor1: for loopcs1 as cousor1 cursor as
select market_code as market_code
from tb_market_code
for update
do
end for;
这种方式的优点是比较简单，不用（也不允许）使用open,fetch,close。
但不能使用with hold 选项。如果在游标循环内要使用commit,rollback则不能使用这种方式。如果没有c ommit或rollback的要求，推荐使用这种方式(看来For这种方式有问题)。
修改游标的当前记录的方法
update tb_market_code set market_code='0' where current of cursor1;
不过要注意将cursor1定义为可修改的游标
declare cursor1 cursor for select market_code from tb_market_code
for update;
for update 不能和GROUP BY、DISTINCT、ORDER BY、FOR READ ONLY及UNION, EXCEP T, or INTERSECT 但UNION ALL除外）一起使用。
一个游标(cursor)可以被看作指向结果集(a set of rows)中一行的指针(pointer)。游标每个时间点只能指向一行，但是可以根据需要指向结果集中其他的行。
例如：SELECT * FROM employees WHERE sex='M'会返回所有性别为男的雇员，在初始的时候，游标被放置在结果集中第一行的前面。使游标指向第一行，要执行FETCH。当游标指向结果集中一行的时候，可以对这行数据进行加工处理，要想得到下一行数据，要继续执行FETCH。FETCH操作可以重复执行，直到完成结果集中的所有行
在存储过程中使用游标，有如下几个步骤：
声明游标、打开游标、根据需要一次一行，讲游标指向的数据取到本地变量(local variables)中、结束时关闭游标
声明游标：
>>-DECLARE--cursor-name--CURSOR----+------------+--------->
'-WITH HOLD--'
>-----+--------------------------------+--------------------->
| .-TO CALLER--. |
'-WITH RETURN--+------------+--'
'-TO CLIENT--'
>----FOR--+-select-statement-+----------------------------><
'-statement-name---'
WITH RETURN子句用于将游标所定义的结果集传递给另一个存储过程或者应用(an application)
如果select语句中包含CURRENT DATE, CURRENT TIME和CURRENT TIMESTAMP，所有的FETCH语句都会返回相同的日期、时间、时间戳值，因为这些特定寄存器是在打开游标(OPEN CURSOR)的时候进行检查的
FETCH语法：
>>-FETCH--+-------+---cursor-name---------->
'-FROM--'
.-,----------------.
V |
>------INTO-----host-variable---+----------><
FETCH语句使游标指向结果集中的下一行，并且将游标现在的位置赋值给特定的过程变量
例如：一个公司，按照如下规则计算加薪金额：
1.公司中除了总裁(president)外，所有人都会至少增加p_min的薪水
2.任何奖金(bonus)高于$600的员工都会另增加4%
3.员工的佣金(commission)越高，增加越少。佣金(commission)少于$2000的另增加3%，佣金(commission)在$2000到$3000的增加另2%
4.佣金(commission)高于$3000的另增加1%
5.无论每个员工增加多少，增加比例不能高于p_max
CREATE PROCEDURE total_raise ( IN p_min DEC(4,2)
, IN p_max DEC(4,2)
, OUT p_total DEC(9,2) )
LANGUAGE SQL
SPECIFIC total_raise
tr: BEGIN
-- Declare variables
DECLARE v_salary DEC(9,2);
DECLARE v_bonus DEC(9,2);
DECLARE v_comm DEC(9,2);
DECLARE v_raise DEC(4,2);
DECLARE v_job VARCHAR(15) DEFAULT 'PRES';
-- Declare returncode
DECLARE SQLSTATE CHAR(5);
-- Procedure logic
DECLARE c_emp CURSOR FOR
SELECT salary, bonus, comm
FROM employee
WHERE job != v_job; -- (1)这里的SELECT定义了结
果集中的行和
列
OPEN c_emp; -- (2)
SET p_total = 0;
FETCH FROM c_emp INTO v_salary, v_bonus, v_comm; -- (3)得到一行数
据，并将其
复制给本地变量WHILE ( SQLSTATE = '00000' ) DO --SQLSTATE 00000: 操
作执行成功，
并且未产生任何类型的警告或异常情
况。通过这个可以检查是否到达最后一行
SET v_raise = p_min;
IF ( v_bonus >= 600 ) THEN
SET v_raise = v_raise + 0.04;
END IF;
IF ( v_comm < 2000 ) THEN
SET v_raise = v_raise + 0.03;
ELSEIF ( v_comm < 3000 ) THEN
SET v_raise = v_raise + 0.02;
ELSE
SET v_raise = v_raise + 0.01;
END IF;
IF ( v_raise > p_max ) THEN
SET v_raise = p_max;
END IF;
SET p_total = p_total + v_salary * v_raise;
FETCH FROM c_emp INTO v_salary, v_bonus, v_comm; -- (4)在WHILE逻辑中得到
更多的行数据END WHILE;
CLOSE c_emp; -- (5)
END tr
如果只是想把结果集中的第一个值复制给本地变量，而声明一个游标是不恰当的，因为打开游标会耗费很多资源。所以如下这段代码：
DECLARE c_tmp CURSOR FOR
SELECT c1
FROM t1;
OPEN c_emp;
FETCH FROM c_emp INTO v_c1;
CLOSE c_emp;
应当用有FETCH FIRST 1 ROW ONLY的子句的SQL语句：SELECT c1 INTO v_c1 FROM t1 FETCH FIRST 1 ROW ONLY;
positioned delete：利用游标删除当前行
一个用于删除的游标(a deletable cursor)应该符合以下的要求：
1.每个outer fullselect中的FROM子句只跟一个表有关
2.outer fullselect不包含VALUES, GROUP BY, 或者HAVING子句，并且不包括列函数
3.outer fullselect的select列表中不包含DISTINCT
4.select语句不包含ORDER BY或FOR READ ONLY子句
5.游标是静态定义的，或者明确了FOR UPDATE子句
>>-DELETE FROM-|----table-name---------|--------------->
+-----view-name---------+
>----WHERE CURRENT OF--cursor-name--------------------><
例如：在emp_act表中，如果记录的时间比输入参数p_date早的话，就将该记录删除，并返回删除记录总数
CREATE PROCEDURE cleanup_act ( IN p_date DATE
, OUT p_deleted INT )
LANGUAGE SQL
SPECIFIC cleanup_act
ca: BEGIN
-- Declare variable
DECLARE v_date DATE;
-- Declare returncode
DECLARE SQLSTATE CHAR(5);
-- Procedure logic
DECLARE c_emp CURSOR FOR -- (1)和上面那种read-only cursor语法
类似，只是多了FOR UPDATE SELECT emendate
FROM emp_act
FOR UPDATE;
OPEN c_emp;
FETCH FROM c_emp INTO v_date; --注意此处，不要落了
SET p_deleted = 0;
WHILE ( SQLSTATE = '00000' ) DO
IF ( v_date < p_date ) THEN
DELETE FROM emp_act
WHERE CURRENT OF c_emp; -- (2)
SET p_deleted = p_deleted + 1;
END IF;
FETCH FROM c_emp INTO v_date;
END WHILE;
CLOSE c_emp;
END ca银行为什么用db2数据库
直接用DELETE语句删除而不用游标被称作searched delete。像上例这种情况，采用searched delete会比使用positioned delete效率更高。但用positioned delete可以处理更复杂的逻辑
Positioned Update
一个用于更新的游标(A cursor is updatable)应该The cursor is deletable
>>-UPDATE----+-table-name-------------------+-------------->
+-view-name--------------------+
>-----SET--| assignment-clause |--------------------------->
>-----WHERE CURRENT OF--cursor-name----------------------->< CREATE PROCEDURE upd_raise ( IN p_min DEC(4,2)
, IN p_max DEC(4,2) )
LANGUAGE SQL
SPECIFIC upd_raise
发表评论