DB2中游标的使用Word下载.docx
《DB2中游标的使用Word下载.docx》由会员分享，可在线阅读，更多相关《DB2中游标的使用Word下载.docx（35页珍藏版）》请在冰豆网上搜索。
forloopcs1ascousor1cursoras
selectmarket_codeasmarket_code
fromtb_market_code
forupdate
do
endfor;
这种方式的优点是比较简单，不用（也不允许）使用open,fetch,close。
但不能使用withhold选项。
如果在游标循环内要使用commit,rollback则不能使用这种方式。
如果没有commit或rollback的要求，推荐使用这种方式（看来For这种方式有问题）。
修改游标的当前记录的方法
updatetb_market_codesetmarket_code='
0'
wherecurrentofcursor1;
不过要注意将cursor1定义为可修改的游标
declarecursor1cursorforselectmarket_codefromtb_market_code
forupdate;
forupdate不能和GROUPBY、DISTINCT、ORDERBY、FORREADONLY及UNION,EXCEPT,orINTERSECT但UNIONALL除外）一起使用。
一个游标（cursor）可以被看作指向结果集（asetofrows）中一行的指针（pointer）。
游标每个时间点只能指向一行，但是可以根据需要指向结果集中其他的行。
例如：
SELECT*FROMemployeesWHEREsex='
M'
会返回所有性别为男的雇员，在初始的时候，游标被放置在结果集中第一行的前面。
使游标指向第一行，要执行FETCH。
当游标指向结果集中一行的时候，可以对这行数据进行加工处理，要想得到下一行数据，要继续执行FETCH。
FETCH操作可以重复执行，直到完成结果集中的所有行
在存储过程中使用游标，有如下几个步骤：
声明游标、打开游标、根据需要一次一行，讲游标指向的数据取到本地变量（localvariables）中、结束时关闭游标
声明游标：
>
-DECLARE--cursor-name--CURSOR----+------------+--------->
'
-WITHHOLD--'
>
-----+--------------------------------+--------------------->
|
.-TOCALLER--.
|
-WITHRETURN--+------------+--'
-TOCLIENT--'
----FOR--+-select-statement-+---------------------------->
<
'
-statement-name---'
WITHRETURN子句用于将游标所定义的结果集传递给另一个存储过程或者应用（anapplication）
如果select语句中包含CURRENTDATE,CURRENTTIME和CURRENTTIMESTAMP，所有的FETCH语句都会返回相同的日期、时间、时间戳值，因为这些特定寄存器是在打开游标（OPENCURSOR）的时候进行检查的
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
CREATEPROCEDUREtotal_raise（IN
p_minDEC（4,2）
IN
p_maxDEC（4,2）
OUTp_totalDEC（9,2））
LANGUAGESQL
SPECIFICtotal_raise
tr:
BEGIN
--Declarevariables
DECLAREv_salaryDEC（9,2）;
DECLAREv_bonus
DEC（9,2）;
DECLAREv_comm
DECLAREv_raise
DEC（4,2）;
DECLAREv_job
VARCHAR（15）DEFAULT'
PRES'
;
--Declarereturncode
DECLARESQLSTATECHAR（5）;
--Procedurelogic
DECLAREc_empCURSORFOR
SELECTsalary,bonus,comm
FROM
employee
WHERE
job!
=v_job;
--
（1）这里的SELECT定义了结果集中的行和
列
OPENc_emp;
--
（2）
SETp_total=0;
FETCHFROMc_empINTOv_salary,v_bonus,v_comm;
--（3）得到一行数据，并将其
复制给本地变量
WHILE（SQLSTATE='
00000'
）DO
--SQLSTATE00000:
操作执行成功，
并且未产生任何类型的警告或异常情
况。
通过这个可以检查是否到达最后一行
SETv_raise=p_min;
IF（v_bonus>
=600）THEN
SETv_raise=v_raise+0.04;
ENDIF;
IF（v_comm<
2000）THEN
SETv_raise=v_raise+0.03;
ELSEIF（v_comm<
3000）THEN
SETv_raise=v_raise+0.02;
ELSE
SETv_raise=v_raise+0.01;
IF（v_raise>
p_max）THEN
SETv_raise=p_max;
SETp_total=p_total+v_salary*v_raise;
--（4）在WHILE逻辑中得到
更多的行数据
ENDWHILE;
CLOSEc_emp;
--（5）
ENDtr
如果只是想把结果集中的第一个值复制给本地变量，而声明一个游标是不恰当的，因为打开游标会耗费很多资源。
所以如下这段代码：
DECLAREc_tmpCURSORFOR
SELECTc1
FROMt1;
OPENc_emp;
FETCHFROMc_empINTOv_c1;
CLOSEc_emp;
应当用有FETCHFIRST1ROWONLY的子句的SQL语句：
SELECTc1INTOv_c1FROMt1FETCHFIRST1ROWONLY;
positioneddelete：
利用游标删除当前行
一个用于删除的游标（adeletablecursor）应该符合以下的要求：
1.每个outerfullselect中的FROM子句只跟一个表有关
2.outerfullselect不包含VALUES,GROUPBY,或者HAVING子句，并且不包括列函数
3.outerfullselect的select列表中不包含DISTINCT
4.select语句不包含ORDERBY或FORREADONLY子句
5.游标是静态定义的，或者明确了FORUPDATE子句
-DELETEFROM-|----table-name---------|--------------->
+-----view-name---------+
----WHERECURRENTOF--cursor-name-------------------->
在emp_act表中，如果记录的时间比输入参数p_date早的话，就将该记录删除，并返回删除记录总数
CREATEPROCEDUREcleanup_act（IN
p_date
DATE
OUTp_deletedINT）
SPECIFICcleanup_act
ca:
--Declarevariable
DECLAREv_dateDATE;
DECLAREc_empCURSORFOR
--
（1）和上面那种read-onlycursor语法
类似，只是多了FORUPDATE
SELECTemendate
FROMemp_act
FORUPDATE;
FETCHFROMc_empINTOv_date;
--注意此处，不要落了
SETp_deleted=0;
）DO
IF（v_date<
p_date）THEN
DELETEFROMemp_act
WHERECURRENTOFc_emp;
SETp_deleted=p_deleted+1;
ENDca
直接用DELETE语句删除而不用游标被称作searcheddelete。
像上例这种情况，采用searcheddelete会比使用positioneddelete效率更高。
但用positioneddelete可以处理更复杂的逻辑
PositionedUpdate
一个用于更新的游标（Acursorisupdatable）应该Thecursorisdeletable
-UPDATE----+-table-name-------------------+-------------->
+-view-name--------------------+
-----SET--|assignment-clause|--------------------------->
-----WHERECURRENTOF--cursor-name----------------------->
CREATEPROCEDUREupd_raise（INp_minDEC（4,2）
INp_maxDEC（4,2））
LANGUAGESQL
SPECIFICupd_raise
ur:
FROMemployee
WHEREjob!
='
FORUPDATEOFsalary;
--
（1）如果只是更新表中的一部分字段，可以利用
FORUPDATEOF<
columnlist>
提高效
率，让DB2引擎知道只有这些特定列要UPDATE
UPDATEemployee
SETsalary=v_salary*（1+v_raise）
ENDur
使用游标时候的COMMIT和ROLLBACK：
数据库程序中很重要的一点就是事务处理（transaction或者theunitofwork（UOW））。
事务当中的任何一部分失败，整个事物就会失败。
利用COMMIT和ROLLBACK进行适当的事务控制对于保证数据完整性来说是至关重要的。
当在使用游标的时候使用COMMIT或者ROLLBACK语句时，游标的行动取决于是否在生命的时候加了WITHHOLD子句。
如果一个游标在声明的时候没有指定WITHHOLD，那么它的所有资源（游标，锁，大对象数据类型或者LOBlocators）都将在COMMIT或者ROLLBACK之后被释放。
因此，如果需要在完成一个事务之后使用游标，就必须重新打开游标，并从第一行开始执行。
如果定义了一个游标WITHHOLD
，游标就会在事务之间保存它的位置和锁（lock）。
需要明白的是，只有保证游标位置的锁被held了。
锁（lock）是个数据库对象（adatabaseobject），我们用它来控制多个应用访问同一个资源的方式。
而一个LOBlocator使存储在本地变量中的4字节的值，程序可以用它来查到数据库系统中的LOB对象的值
定义了WITHHOLD的游标在COMMIT之后
1.仍然保证是打开（open）的
2.游标指向下一个满足条件的行之前
3.在COMMIT语句之后只允许FETCH和CLOSE
4.Positioneddelete和positionedupdate只在同一事务中fetch的行上可用
5.所有的LOBlocators会被释放
6.除了保存声明为WITHHOLD的游标位置的锁，其他锁都会释放
7.当执行了数据修改语句或者含有WITHHOLD游标的修改语句被commit的时候
所有定义为WITHHOLD的游标在ROLLBACK之后：
1.所有游标会被关闭
2.所有在该事务中的锁会被释放
3.所有的LOBlocators会被freed
CREATEPROCEDUREupdate_department（）
SPECIFICupd_dept
ud:
DECLAREv_deptnoCHAR（3）;
DECLAREc_deptCURSO
升级会员 