第十三章-游標(biāo)管理

第十三章游標(biāo)管理

知識(shí)點(diǎn)回顧順序結(jié)構(gòu)控制語句選擇結(jié)構(gòu)控制語句if語句Case語句循環(huán)結(jié)構(gòu)控制語句基本loop循環(huán)while循環(huán)for循環(huán)2本章目標(biāo)掌握并使用游標(biāo)管理技巧隱式游標(biāo)(掌握)顯式游標(biāo)(掌握)Ref游標(biāo)(了解)3游標(biāo)簡(jiǎn)介2-1Oracle服務(wù)器執(zhí)行PL/SQL程序內(nèi)存單元保存到游標(biāo)中一次處理一行檢索行提取行4游標(biāo)簡(jiǎn)介2-2逐行處理查詢結(jié)果，以編程的方式訪問數(shù)據(jù)游標(biāo)的類型：隱式游標(biāo)REF游標(biāo)顯式游標(biāo)在PL/SQL程序中執(zhí)行DMLSQL語句時(shí)自動(dòng)創(chuàng)建隱式游標(biāo)。顯式游標(biāo)用于處理返回多行的查詢。REF游標(biāo)用于處理運(yùn)行時(shí)才能確定的動(dòng)態(tài)SQL查詢的結(jié)果游標(biāo)類型5隱式游標(biāo)4-1在PL/SQL中使用DML語句時(shí)自動(dòng)創(chuàng)建隱式游標(biāo)隱式游標(biāo)自動(dòng)聲明、打開和關(guān)閉，其名為SQL通過檢查隱式游標(biāo)的屬性可以獲得最近執(zhí)行的DML語句的信息隱式游標(biāo)的屬性有：%FOUND–SQL語句影響了一行或多行時(shí)為TRUE%NOTFOUND–SQL語句沒有影響任何行時(shí)為TRUE%ROWCOUNT–SQL語句影響的行數(shù)%ISOPEN-游標(biāo)是否打開，始終為FALSE6隱式游標(biāo)4-2SQL>SETSERVEROUTPUTONSQL>BEGIN UPDATEt_studentSETf_name='張三豐' WHEREf_id='001'; IFSQL%FOUNDTHEN DBMS_OUTPUT.PUT_LINE('表已更新'); ENDIF;END; /只有在DML語句影響一行或多行時(shí)，才返回True7隱式游標(biāo)4-3SQL>SETSERVEROUTPUTONSQL>DECLARE v_idt_student.f_id%type:='&id'; v_namet_student.f_name%Type:='&name';BEGIN UPDATEt_studentSETf_name=v_name WHEREf_id=v_id; IFSQL%NOTFOUNDTHEN DBMS_OUTPUT.PUT_LINE('編號(hào)未找到。'); ELSE DBMS_OUTPUT.PUT_LINE(‘表已更新'); ENDIF;END;/如果DML語句不影響任何行，則返回True8隱式游標(biāo)4-4SQL>SETSERVEROUTPUTONSQL>beginupdatet_studentsetf_department='CHE'wheref_class=2;ifSQL%Foundthendbms_output.put_line('修改記錄數(shù)為：'||SQL%RowCount);elsedbms_output.put_line('未找到相應(yīng)記錄');endif;end;/返回DML語句影響的行數(shù)9SELECTINTO語句2-1SQL>SETSERVEROUTPUTONSQL>DECLARE v_idt_course.f_id%type; v_namet_course.f_name%type;BEGIN v_id:='&id'; SELECTf_nameINTOv_name FROMt_courseWHEREf_id=v_id;EXCEPTION WHENNO_DATA_FOUNDTHEN DBMS_OUTPUT.PUT_LINE('課程未找到');END;/如果沒有與SELECTINTO語句中的條件匹配的行，將引發(fā)NO_DATA_FOUND異常10SELECTINTO語句2-2SQL>SETSERVEROUTPUTONSQL>DECLARE v_birtht_student.f_birth%type;BEGIN SELECTf_birthINTOv_birthFROMt_student;DBMS_OUTPUT.PUT_LINE(v_birth);EXCEPTION WHENTOO_MANY_ROWSTHEN DBMS_OUTPUT.PUT_LINE('該查詢提取多行');END;/如果SELECTINTO語句返回多個(gè)值，將引發(fā)TOO_MANY_ROWS異常11顯式游標(biāo)2-1顯式游標(biāo)在PL/SQL塊的聲明部分定義查詢，該查詢可以返回多行顯式游標(biāo)的操作過程：數(shù)據(jù)庫打開游標(biāo)CS王五3PHY李四2CS張三1F_departmentf_namef_id提取行變量關(guān)閉游標(biāo)12顯式游標(biāo)2-2SQL>setserveroutputondeclarenamet_student.f_name%type;Cursorcur_stuIsselectf_namefromt_studentwheref_department='CS';beginOpencur_stu;LoopFetchcur_stuIntoname;ExitWhencur_stu%NotFound;dbms_output.put_line('學(xué)生姓名：'||name);EndLoop;Closecur_stu;end;/聲明游標(biāo)打開游標(biāo)提取行關(guān)閉游標(biāo)13帶參數(shù)的顯式游標(biāo)聲明顯式游標(biāo)時(shí)可以帶參數(shù)以提高靈活性聲明帶參數(shù)的顯式游標(biāo)的語法如下：

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循環(huán)游標(biāo)2-1循環(huán)游標(biāo)用于簡(jiǎn)化游標(biāo)處理代碼當(dāng)用戶需要從游標(biāo)中提取所有記錄時(shí)使用循環(huán)游標(biāo)的語法如下：

FOR<record_index>IN<cursor_name>LOOP <executablestatements>ENDLOOP;15循環(huán)游標(biāo)2-2SQL>SETSERVEROUTPUTONSQL>declarestut_student%RowType;Cursorcur_stuIsselectf_id,f_name,f_birthfromt_studentwheref_department='CS';beginForstuIncur_stuLoopdbms_output.put_line('學(xué)號(hào)：'||stu.f_id||'學(xué)生姓名：'||stu.f_name||'出生日期：'||stu.f_birth);EndLoop;end;/16顯式游標(biāo)屬性2-3顯式游標(biāo)屬性值的語法是cursor_name，后面緊跟屬性（例如，c1％ISOPEN）。顯式游標(biāo)和游標(biāo)變量具有相同的屬性。%ISOPEN屬性%FOUND屬性%NOTFOUND屬性%ROWCOUNT屬性17顯式游標(biāo)屬性2-3對(duì)記錄進(jìn)行計(jì)數(shù)并打印,在獲取第3行后打印一條消息DECLARECURSORc1ISSELECTlast_nameFROMemployeesWHEREROWNUM<5ORDERBYlast_name;nameemployees.last_name%TYPE;BEGINOPENc1;LOOPFETCHc1INTOname;EXITWHENc1%NOTFOUNDORc1%NOTFOUNDISNULL;DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT||'.'||name);IFc1%ROWCOUNT=3THENDBMS_OUTPUT.PUT_LINE('---Fetched3rdrow---');ENDIF;ENDLOOP;CLOSEc1;END;18REF游標(biāo)和游標(biāo)變量3-1REF游標(biāo)和游標(biāo)變量用于處理運(yùn)行時(shí)動(dòng)態(tài)執(zhí)行的SQL查詢創(chuàng)建游標(biāo)變量需要兩個(gè)步驟：聲明REF

游標(biāo)類型聲明REF

游標(biāo)類型的變量用于聲明REF游標(biāo)類型的語法為：

TYPE<ref_cursor_name>ISREFCURSOR [RETURN<return_type>];19REF游標(biāo)和游標(biāo)變量3-2打開游標(biāo)變量的語法如下：

OPENcursor_nameFORselect_statement;TYPEmy_curtypeISREFCURSORRETURNstud_det%ROWTYPE;order_curmy_curtype;聲明強(qiáng)類型的REF游標(biāo)TYPEmy_ctypeISREFCURSOR;stud_curmy_ctype;聲明弱類型的REF游標(biāo)20REF游標(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)類型聲明游標(biāo)變量21游標(biāo)變量的優(yōu)點(diǎn)和限制游標(biāo)變量的功能強(qiáng)大，可以簡(jiǎn)化數(shù)據(jù)處理。游標(biāo)變量的優(yōu)點(diǎn)有：可從不同的SELECT

語句中提取結(jié)果集可以作為過程的參數(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語句打開執(zhí)行動(dòng)態(tài)SQL的游標(biāo)變量的語如下：

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_LINE('薪水大于'||p