oracle數(shù)據(jù)庫(kù)基礎(chǔ)6-游標(biāo)管理課件

第六章1回顧PL/SQL是一種可移植的高性能事務(wù)處理語(yǔ)言PL/SQL引擎駐留在Oracle服務(wù)器中PL/SQL塊由聲明部分、可執(zhí)行部分和異常處理部分組成PL/SQL支持的數(shù)據(jù)類(lèi)型包括標(biāo)量數(shù)據(jù)類(lèi)型、LOB數(shù)據(jù)類(lèi)型和屬性類(lèi)型控制結(jié)構(gòu)包括條件控制、循環(huán)控制和順序控制PL/SQL支持動(dòng)態(tài)SQL運(yùn)行時(shí)出現(xiàn)的錯(cuò)誤叫做異常異常可以分為預(yù)定義異常和用戶(hù)定義的異常2目標(biāo)掌握游標(biāo)管理技巧3游標(biāo)簡(jiǎn)介2-2逐行處理查詢(xún)結(jié)果，以編程的方式訪(fǎng)問(wèn)數(shù)據(jù)游標(biāo)的類(lèi)型：隱式游標(biāo)REF游標(biāo)顯式游標(biāo)在PL/SQL程序中執(zhí)行DMLSQL語(yǔ)句時(shí)自動(dòng)創(chuàng)建隱式游標(biāo)。顯式游標(biāo)用于處理返回多行的查詢(xún)。REF游標(biāo)用于處理運(yùn)行時(shí)才能確定的動(dòng)態(tài)SQL查詢(xún)的結(jié)果游標(biāo)類(lèi)型5隱式游標(biāo)4-1在PL/SQL中使用DML語(yǔ)句時(shí)自動(dòng)創(chuàng)建隱式游標(biāo)隱式游標(biāo)自動(dòng)聲明、打開(kāi)和關(guān)閉，其名為SQL通過(guò)檢查隱式游標(biāo)的屬性可以獲得最近執(zhí)行的DML語(yǔ)句的信息隱式游標(biāo)的屬性有：%FOUND–SQL語(yǔ)句影響了一行或多行時(shí)為T(mén)RUE%NOTFOUND–SQL語(yǔ)句沒(méi)有影響任何行時(shí)為T(mén)RUE%ROWCOUNT–SQL語(yǔ)句影響的行數(shù)%ISOPEN-游標(biāo)是否打開(kāi)，始終為FALSE6隱式游標(biāo)4-2SQL>SETSERVEROUTPUTONSQL>BEGIN UPDATEtoysSETtoyprice=270 WHEREtoyid='P005'; IFSQL%FOUNDTHEN DBMS_OUTPUT.PUT_LINE(‘表已更新'); ENDIF;END; /只有在DML語(yǔ)句影響一行或多行時(shí)，才返回True7隱式游標(biāo)4-4SQL>SETSERVEROUTPUTONSQL>BEGIN UPDATEvendor_master SETvenname='RobMathew' WHEREvencode='V004'; DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);END;/返回DML語(yǔ)句影響的行數(shù)9SELECTINTO語(yǔ)句2-1SQL>SETSERVEROUTPUTONSQL>DECLARE empidVARCHAR2(10); desigVARCHAR2(10);BEGIN empid:='&Employeeid'; SELECTdesignationINTOdesig FROMemployeeWHEREempno=empid;EXCEPTION WHENNO_DATA_FOUNDTHEN DBMS_OUTPUT.PUT_LINE('職員未找到');END;/如果沒(méi)有與SELECTINTO語(yǔ)句中的條件匹配的行，將引發(fā)NO_DATA_FOUND異常10SELECTINTO語(yǔ)句2-2SQL>SETSERVEROUTPUTONSQL>DECLARE empidVARCHAR2(10);BEGIN SELECTempnoINTOempidFROMemployee;EXCEPTION WHENTOO_MANY_ROWSTHEN DBMS_OUTPUT.PUT_LINE('該查詢(xún)提取多行');END;/如果SELECTINTO語(yǔ)句返回多個(gè)值，將引發(fā)TOO_MANY_ROWS異常11顯式游標(biāo)2-2SQL>SETSERVEROUTPUTONSQL>DECLAREmy_toy_pricetoys.toyprice%TYPE; CURSORtoy_curIS SELECTtoypriceFROMtoys WHEREtoyprice<250;BEGIN OPENtoy_cur; LOOP FETCHtoy_curINTOmy_toy_price; EXITWHENtoy_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE('TOYPRICE=:玩具單價(jià)=：'||my_toy_price); ENDLOOP; CLOSEtoy_cur;END;聲明游標(biāo)打開(kāi)游標(biāo)提取行關(guān)閉游標(biāo)13帶參數(shù)的顯式游標(biāo)聲明顯式游標(biāo)時(shí)可以帶參數(shù)以提高靈活性聲明帶參數(shù)的顯式游標(biāo)的語(yǔ)法如下：

CURSOR<cursor_name>(<param_name><param_type>)ISselect_statement;SQL>SETSERVEROUTPUTONSQL>DECLARE desigVARCHAR2(20); emp_codeVARCHAR2(5); empnmVARCHAR2(20); CURSORemp_cur(desigparamVARCHAR2)IS SELECTempno,enameFROMemployee WHEREdesignation=desig;BEGIN desig:='&desig'; OPENemp_cur(desig); LOOP FETCHemp_curINTOemp_code,empnm; EXITWHENemp_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(emp_code||''||empnm); ENDLOOP; CLOSEemp_cur;END;14使用顯式游標(biāo)更新行2-1允許使用游標(biāo)刪除或更新活動(dòng)集中的行聲明游標(biāo)時(shí)必須使用SELECT…FORUPDATE語(yǔ)句

CURSOR<cursor_name>IS SELECTstatementFORUPDATE; UPDATE<table_name> SET<set_clause> WHERECURRENTOF<cursor_name>更新的語(yǔ)法DELETEFROM<table_name>WHERECURRENTOF<cursor_name>刪除的語(yǔ)法15循環(huán)游標(biāo)2-1循環(huán)游標(biāo)用于簡(jiǎn)化游標(biāo)處理代碼當(dāng)用戶(hù)需要從游標(biāo)中提取所有記錄時(shí)使用循環(huán)游標(biāo)的語(yǔ)法如下：

FOR<record_index>IN<cursor_name>LOOP <executablestatements>ENDLOOP;17循環(huán)游標(biāo)2-2SQL>SETSERVEROUTPUTONSQL>DECLARE CURSORmytoy_curIS SELECTtoyid,toyname,toyprice FROMtoys;BEGIN FORtoy_recINmytoy_cur LOOP DBMS_OUTPUT.PUT_LINE(‘玩具編號(hào)：'||''||toy_rec.toyid||''||‘玩具名稱(chēng)：'||''||toy_rec.toyname||''||‘玩具單價(jià)：'||''||toy_rec.toyprice); ENDLOOP;END;18REF游標(biāo)和游標(biāo)變量3-1REF游標(biāo)和游標(biāo)變量用于處理運(yùn)行時(shí)動(dòng)態(tài)執(zhí)行的SQL查詢(xún)創(chuàng)建游標(biāo)變量需要兩個(gè)步驟：聲明REF游標(biāo)類(lèi)型聲明REF游標(biāo)類(lèi)型的變量用于聲明REF游標(biāo)類(lèi)型的語(yǔ)法為： TYPE<ref_cursor_name>ISREFCURSOR [RETURN<return_type>];19REF游標(biāo)和游標(biāo)變量3-3SQL>DECLARE TYPEtoys_curtypeISREFCURSORRETURNtoys%ROWTYPE; toys_curvartoys_curtype; toys_rectoys%ROWTYPE;BEGIN OPENtoys_curvarFOR SELECT*FROMtoys; FETCHtoys_curvarINTOtoys_rec;... CLOSEtoys_curvar;END;聲明REF游標(biāo)類(lèi)型聲明游標(biāo)變量21游標(biāo)變量的優(yōu)點(diǎn)和限制游標(biāo)變量的功能強(qiáng)大，可以簡(jiǎn)化數(shù)據(jù)處理。游標(biāo)變量的優(yōu)點(diǎn)有：可從不同的SELECT語(yǔ)句中提取結(jié)果集可以作為過(guò)程的參數(shù)進(jìn)行傳遞可以引用游標(biāo)的所有屬性可以進(jìn)行賦值運(yùn)算使用游標(biāo)變量的限制：不能在程序包中聲明游標(biāo)變量FORUPDATE子句不能與游標(biāo)變量一起使用不能使用比較運(yùn)算符22使用游標(biāo)變量執(zhí)行動(dòng)態(tài)SQL可以使用游標(biāo)變量執(zhí)行動(dòng)態(tài)構(gòu)造的SQL語(yǔ)句打開(kāi)執(zhí)行動(dòng)態(tài)SQL的游標(biāo)變量的語(yǔ)如下：

OPENcursor_nameFORdynamic_sqlstring[USINGbind_argument_list];DECLAREr_empemp%ROWTYPE;TYPEc_typeISREFCURSOR;curc_type;p_salaryNUMBER;BEGINp_salary:=2500;OPENcurFOR'select*fromempwheresal>:1orderbysaldesc'USINGp_salary;DBMS_OUTPUT.PUT_