DB2中游标的使用.docx
- 文档编号:4322078
- 上传时间:2022-11-29
- 格式:DOCX
- 页数:35
- 大小:38.30KB
DB2中游标的使用.docx
《DB2中游标的使用.docx》由会员分享,可在线阅读,更多相关《DB2中游标的使用.docx(35页珍藏版)》请在冰豆网上搜索。
DB2中游标的使用
DB2中游标的使用
注意commit和rollback
使用游标时要特别注意如果没有加withhold选项,在Commit和Rollback时,该游标将被关闭。
Commit和Rollback有很多东西要注意。
特别小心
游标的两种定义方式
一种为
declarecontinuehandlerfornotfound
begin
setv_notfound=1;
end;
declarecursor1cursorwithholdforselectmarket_codefromtb_market_codeforupdate;
opencursor1;
setv_notfound=0;
fetchcursor1intov_market_code;
whilev_notfound=0Do
--work
setv_notfound=0;
fetchcursor1intov_market_code;
endwhile;
closecursor1;
这种方式使用起来比较复杂,但也比较灵活。
特别是可以使用withhold选项。
如果循环内有commit或rollback而要保持该cursor不被关闭,只能使用这种方式。
另一种为
pcursor1:
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 DEC(9,2);
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;
ENDIF;
IF(v_raise>p_max)THEN
SETv_raise=p_max;
ENDIF;
SETp_total=p_total+v_salary*v_raise;
FETCHFROMc_empINTOv_salary,v_bonus,v_comm; --(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)
LANGUAGESQL
SPECIFICcleanup_act
ca:
BEGIN
--Declarevariable
DECLAREv_dateDATE;
--Declarereturncode
DECLARESQLSTATECHAR(5);
--Procedurelogic
DECLAREc_empCURSORFOR --
(1)和上面那种read-onlycursor语法
类似,只是多了FORUPDATE
SELECTemendate
FROMemp_act
FORUPDATE;
OPENc_emp;
FETCHFROMc_empINTOv_date; --注意此处,不要落了
SETp_deleted=0;
WHILE(SQLSTATE='00000')DO
IF(v_date DELETEFROMemp_act WHERECURRENTOFc_emp; -- (2) SETp_deleted=p_deleted+1; ENDIF; FETCHFROMc_empINTOv_date; ENDWHILE; CLOSEc_emp; 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: BEGIN --Declarevariables DECLAREv_salaryDEC(9,2); DECLAREv_bonus DEC(9,2); DECLAREv_comm DEC(9,2); DECLAREv_raise DEC(4,2); --Declarereturncode DECLARESQLSTATECHAR(5); --Procedurelogic DECLAREc_empCURSORFOR SELECTsalary,bonus,comm FROMemployee WHEREjob! ='PRES' FORUPDATEOFsalary; -- (1)如果只是更新表中的一部分字段,可以利用 FORUPDATEOF 率,让DB2引擎知道只有这些特定列要UPDATE OPENc_emp; FETCHFROMc_empINTOv_salary,v_bonus,v_comm; WHILE(SQLSTATE='00000')DO 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; ENDIF; IF(v_raise>p_max)THEN SETv_raise=p_max; ENDIF; UPDATEemployee SETsalary=v_salary*(1+v_raise) WHERECURRENTOFc_emp; FETCHFROMc_empINTOv_salary,v_bonus,v_comm; ENDWHILE; CLOSEc_emp; 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() LANGUAGESQL SPECIFICupd_dept ud: BEGIN --Declarevariable DECLAREv_deptnoCHAR(3); --Declarereturncode DECLARESQLSTATECHAR(5); DECLAREc_deptCURSO
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- DB2 中游 标的 使用