java課件第11章oracle游標

第九章Oracle事務、觸發(fā)器和函數(shù)本章目標游標概念與分類游標的操作游標游標是SQL的一個內存工作區(qū)，由系統(tǒng)或用戶以變量的形式定義。游標的作用就是用于臨時存儲從數(shù)據(jù)庫中提取的數(shù)據(jù)塊。游標分為三種類型：隱式Cursor顯式CursorRefCursor（動態(tài)Cursor）。隱式游標對于Select…INTO…語句，一次只能從數(shù)據(jù)庫中獲取到一條數(shù)據(jù)，對于這種類型的DMLSql語句，就是隱式Cursor。例如：Select/Update/Insert/Delete操作。作用：可以通過隱式Cusor的屬性來了解操作的狀態(tài)和結果，從而達到流程的控制。隱式Cursor是系統(tǒng)自動打開和關閉Cursor。Cursor的屬性包含：SQL%ROWCOUNT整型代表DML語句成功執(zhí)行的數(shù)據(jù)行數(shù)。SQL%FOUND布爾型值為TRUE代表插入、刪除、更新或單行查詢操作成功。SQL%NOTFOUND布爾型與SQL%FOUND屬性返回值相反。SQL%ISOPEN布爾型DML執(zhí)行過程中為真，結束后為假。隱式游標示例1用隱式的游標判斷是否修改數(shù)據(jù)成功：setserveroutputon;beginupdatepersonsetid='P003'whereid='P010';ifSQL%FOUNDthendbms_output.put_line('updatesuccessfull');commit;elsedbms_output.put_line('updatefaulied');endif;end;隱式游標示例2判斷影響的行數(shù)：setserveroutputon;declarerowCountinteger;beginupdatepersonsetname='Jack';rowCount:=SQL%ROWCOUNT;--輸出本次共影響幾行

dbms_output.put_line('Effectedrowsis:'||rowCount);end;顯式Cursor

對于從數(shù)據(jù)庫中提取多行數(shù)據(jù)，就需要使用顯式Cursor。顯式Cursor的屬性包含：%ROWCOUNT整型獲得FETCH語句返回的數(shù)據(jù)行數(shù)。%FOUND布爾型最近的FETCH語句返回一行數(shù)據(jù)則為真，否則為假。%NOTFOUND布爾型與%FOUND屬性返回值相反。%ISOPEN布爾型游標已經打開時值為真，否則為假。對于顯式游標的運用分為四個步驟：定義游標---Cursor[CursorName]IS;必須要使用is.打開游標---Open[CursorName];操作數(shù)據(jù)---Fetch[Cursorname]intosomeVar;判斷游標是否獲取到行值exitwhenc1%notfound;關閉游標---Close[CursorName],這個Step絕對不可以遺漏。顯式Cursor用法-1遍歷數(shù)據(jù)：setserveroutputon;declarersperson%rowType;cursorc1isselect*fromperson;beginopenc1;loopfetchc1intors;--每一次將值寫到rs記錄集中

exitwhenc1%notfound;--判斷是否獲取到了數(shù)據(jù)

dbms_output.put_line('行號：'||c1%rowcount||'編號'||rs.id||'姓名'||);endloop;ifc1%isopenthen–關閉游標

closec1;endif;end;顯式Cursor用法-2setserveroutputon;declarecursorc1isselectid,namefromperson;--聲明兩個變量

v_idperson.id%type;v_%type;beginopenc1;loop--分別對應寫入兩個值,注意與select語句的對應關系

fetchc1intov_id,v_name;exitwhenc1%notfound;DBMS_OUTPUT.put_line('Nameis:'||v_name);endloop;ifc1%isopenthenclosec1;endif;end;顯式Cursor用法-3setserveroutputon;declarecursorc1isselect*fromperson;begin--使用for..in直接打開的游標,直接使用rs變量即可

forrsinc1loopdbms_output.put_line(rs.id||''||);endloop;ifc1%isopenthenclosec1;endif;end;綜合示例declarecursorclisselectempno,empjobfromemp;--定義光標保存查詢結果

pnoemp.empno%TYPE;pjobemp.empjob%TYPE;beginopencl;--打開游標，即執(zhí)行查詢

loop--循環(huán)開始

fetchclintopno,pjob;--取一條記錄中的員工編號和工種，并賦給pnopjobexitwhencl%notfoun--退出循環(huán)條件

--判斷員工工種，執(zhí)行加薪

ifpjob='PRESIDENT'thenupdateempsetsal=salwhereempno=pno;elseifpjob='MANAGER'thenupdateempsetsal=sal+800whereempno=pno;elseupdateempsetsal=sal+400whereempno=pno;endif;endloop;closecl;--關閉游標

commit;--提交end;/按員工的工種長工資,總裁1000元，經理長800元其，他人員長400元RefCursor（動態(tài)游標）與隱式Cursor,顯式Cursor的區(qū)別：RefCursor是可以通過在運行期間傳遞參數(shù)來獲取數(shù)據(jù)結果集。而另外兩種Cursor，是靜態(tài)的，在編譯期間就決定數(shù)據(jù)結果集。Refcursor的使用:Type[Cursortypename]isrefcursor;Define動態(tài)的Sql語句Opencursor操作數(shù)據(jù)---Fetch[Cursorname]CloseCursor動態(tài)游標示例declaretypecursor_typeisrefcursor;myCursorcursor_type;v_sqlvarchar2(500);v_personperson%rowType;beginv_sql:='select*frompersonwherenamelike''%a%''';openmyCursorforv_sql;loopfetchmyCursorintov_person;exitwhenmycursor%notfound;dbms_output.put_line(v_person.id||v_);endloop;exception--異常處理塊，確保關閉游標

whenothersthenclosemyCursor;ifmyCursor%isopenthenclosemyCursor;endif;end;帶參數(shù)的游標示例語法：聲明參數(shù)，不可以聲明大小cursorc2(jobcvarchar2)isselectename,salfromempwherejob=jobc;執(zhí)行語句:Openc2(‘clerk’);帶參數(shù)的游標示例setserveroutputon;declarecursorc1(p_idvarchar2)isselect*frompersonwhereid=p_id;begin--注意下面的參數(shù)，或是使用openc1('P001');forrsinc1('P002')loopdbms_output.put_line(rs.id||);endloop;end;游標綜合示例用游標刪除用戶指定的時間創(chuàng)建的表：begin opencur; loop fetchcurintov_name; exitwhencur%notfound; v_sql:=‘droptable’||v_name; dbms_output.put_line(v_sql); executeimmediatev_sql;--動態(tài)執(zhí)行sql endloop; executeimmediatev_sql; exception whenot