DB2中游标的使用Word下载.docx
《DB2中游标的使用Word下载.docx》由会员分享,可在线阅读,更多相关《DB2中游标的使用Word下载.docx(35页珍藏版)》请在冰豆网上搜索。
![DB2中游标的使用Word下载.docx](https://file1.bdocx.com/fileroot1/2022-11/29/039ed35f-065f-44a3-a2da-029975b798dd/039ed35f-065f-44a3-a2da-029975b798dd1.gif)
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