oracle plsql從入門到精通配套資源所有課件章

第18章批量綁定及高級觸發(fā)器特性23一月20241————不可不求、也不可強(qiáng)求————批量綁定概述批量綁定將一個(gè)調(diào)用中的整個(gè)集合一次一起綁定,而不是在一個(gè)循環(huán)中每次重復(fù)只處理一個(gè)集合的元素并造成一次環(huán)境的切換Oracle服務(wù)器使用兩個(gè)“引擎”運(yùn)行PL/SQL程序塊和子程序：PL/SQL運(yùn)行引擎：運(yùn)行所有過程化的語句但是將SQL語句傳遞給SQL引擎SQL引擎：編譯或執(zhí)行SQL語句、并且在某些情況下將數(shù)據(jù)返回給PL/SQL運(yùn)行引擎23一月2024————不可不求、也不可強(qiáng)求————2批量綁定的語法FORALL關(guān)鍵字指示PL/SQL引擎在將集合發(fā)送給SQL語句之前批量綁定輸入的集合BULKCOLLECTINTO關(guān)鍵字指示SQL引擎在將集合返回給PL/SQL引擎之前批量綁定輸出的集合23一月2024————不可不求、也不可強(qiáng)求————3FORALLindexINlower_bound..upper_bound

[SAVEEXCEPTIONS]sql_statement;.BULKCOLLECTINTO collection_name[,collection_name..]...批量綁定FORALL的實(shí)例23一月2024————不可不求、也不可強(qiáng)求————4CREATEPROCEDUREbulk_raise_salary(p_percentNUMBER)ISTYPEidlist_typeISTABLEOFNUMBERINDEXBYPLS_INTEGER;v_empnoidlist_type;BEGINv_empno(1):=7788;v_empno(2):=7844;v_empno(3):=7876;v_empno(4):=7900;v_empno(2):=7902;v_empno(3):=7934;

FORALLiINv_empno.FIRST..v_empno.LASTUPDATEemp_plSETsal=(1+p_percent/100)*salWHEREempno=v_empno(i);END;/EXECUTEraise_salary(20)PL/SQLproceduresuccessfullycompleted.屬性%BULK_ROWCOUNT該屬性是一個(gè)復(fù)合結(jié)構(gòu)、是專為FORALL語句設(shè)計(jì)的它與INDEXBY表極為相似，它的第n個(gè)元素存儲(chǔ)的是一個(gè)DML語句第n次執(zhí)行時(shí)所處理的數(shù)據(jù)行數(shù)如果第n次執(zhí)行沒有影響任何數(shù)據(jù)行，那么%BULK_ROWCOUNT(i)就返回零23一月2024————不可不求、也不可強(qiáng)求————5BULKCOLLECTINTO子句從10g開始，當(dāng)在PL/SQL中使用一個(gè)SELECT語句時(shí)，可以加入BULKCOLLECTINTO子句23一月2024————不可不求、也不可強(qiáng)求————6SETserveroutputONCREATEORREPLACEPROCEDUREbulk_get_emps(p_deptnoNUMBER)ISTYPEemp_tab_typeISTABLEOFemp_pl%ROWTYPE;v_empsemp_tab_type;BEGINSELECT*BULKCOLLECTINTOv_empsFROMemp_plWHEREdeptno=p_deptno;FORiIN1..v_emps.COUNTLOOPDBMS_OUTPUT.PUT_LINE(v_emps(i).empno||''||v_emps(i).ename||''||v_emps(i).job||''||v_emps(i).sal);ENDLOOP;END;BULKCOLLECTINTO子句從10g開始，在PL/SQL中使用cursor時(shí)，可在FETCH語句中加入BULKCOLLECTINTO子句批量綁定23一月2024————不可不求、也不可強(qiáng)求————7CREATEORREPLACEPROCEDUREbulk_get_emps2(p_deptnoNUMBER)ISCURSORcur_empISSELECT*FROMemp_plWHEREdeptno=p_deptno;

TYPEemp_tab_typeISTABLEOFcur_emp%ROWTYPE;v_empsemp_tab_type;BEGINOPENcur_emp;FETCHcur_empBULKCOLLECTINTOv_emps;CLOSEcur_emp;FORiIN1..v_emps.COUNTLOOPDBMS_OUTPUT.PUT_LINE(v_emps(i).empno||''||v_emps(i).ename||''||v_emps(i).job);ENDLOOP;END;帶RETURNING關(guān)鍵字的BULKCOLLECTINTO子句23一月2024————不可不求、也不可強(qiáng)求————8SETserveroutputONCREATEORREPLACEPROCEDUREbulk_raise_salary2(p_percentNUMBER)ISTYPEidlist_typeISTABLEOFNUMBERINDEXBYPLS_INTEGER;TYPEsallist_typeISTABLEOFemp_pl.sal%TYPEINDEXBYBINARY_INTEGER;v_empnoidlist_type;v_new_salssallist_type;BEGINv_empno(1):=7788;v_empno(2):=7844;v_empno(3):=7876;v_empno(4):=7900;v_empno(2):=7902;v_empno(3):=7934;FORALLiINv_empno.FIRST..v_empno.LASTUPDATEemp_plSETsal=(1+p_percent/100)*salWHEREempno=v_empno(i)RETURNINGsalBULKCOLLECTINTOv_new_sals;

FORiIN1..v_new_sals.COUNTLOOPDBMS_OUTPUT.PUT_LINE(v_empno(i)||''||v_new_sals(i));ENDLOOP;END;利用Index數(shù)組的批量綁定23一月2024————不可不求、也不可強(qiáng)求————9CREATEORREPLACEPROCEDUREins_emp2ASTYPEemptab_typeISTABLEOFemp%ROWTYPE;v_empemptab_type;TYPEvalues_of_tab_typeISTABLEOFPLS_INTEGERINDEXBYPLS_INTEGER;v_numbervalues_of_tab_type;BEGINSELECT*BULKCOLLECTINTOv_empFROMemp;FORiIN1..v_emp.COUNTLOOPv_number(i):=i;ENDLOOP;FORALLiINVALUESOFv_numberINSERTINTOins_empsVALUESv_emp(i);END;RETURNING子句在DML語句中可包括一個(gè)RETURNING子句、該子句將受影響的數(shù)據(jù)行中指定列的值返回給PL/SQL變量或宿主變量23一月2024————不可不求、也不可強(qiáng)求————10CREATEORREPLACEPROCEDUREupdate_salary(p_empnoNUMBER)ISv_enameemp_pl.ename%TYPE;v_jobemp_pl.job%TYPE;v_new_salemp_pl.sal%TYPE;BEGINUPDATEemp_plSETsal=sal*1.20WHEREempno=p_empnoRETURNINGename,job,salINTOv_ename,v_job,v_new_sal;DBMS_OUTPUT.PUT_LINE(v_ename||''||v_job||''||v_new_sal);ENDupdate_salary;變異表一個(gè)變異表是：一個(gè)目前正在由DML語句更改的表或者是一個(gè)受到一個(gè)聲明的DELETECASCADE引用完整性操作影響可能需要修改的表但若是語句觸發(fā)器，該表就不被認(rèn)為是一個(gè)變異表23一月2024————不可不求、也不可強(qiáng)求————11變異表上觸發(fā)器的限制觸發(fā)器讀寫表中數(shù)據(jù)時(shí)必須遵守如下規(guī)則：發(fā)出觸發(fā)語句的會(huì)話不能查詢或修改變異表該限制防止一個(gè)觸發(fā)器看到一個(gè)不一致的數(shù)據(jù)集該限制適用于所有使用FOREACHROW子句的觸發(fā)器使用INSTEADOF觸發(fā)器正在修改的視圖不被認(rèn)為是變異的以上這些規(guī)則只適用于行級觸發(fā)器、而語句觸發(fā)器并不受影響23一月2024————不可不求、也不可強(qiáng)求————12變異表：例子23一月2024————不可不求、也不可強(qiáng)求————13CREATEORREPLACETRIGGERcheck_salaryBEFOREINSERTORUPDATEOFsal,jobONempFOREACHROWWHEN(NEW.job<>'PRESIDENT')DECLAREv_minsalemp.sal%TYPE;v_maxsalemp.sal%TYPE;BEGINSELECTMIN(sal),MAX(sal)INTO v_minsal,v_maxsalFROM empWHEREjob=:NEW.job;IF:NEW.sal<v_minsalOR:NEW.sal>v_maxsalTHENRAISE_APPLICATION_ERROR(-20038,'工資已超出允許的范圍！');ENDIF; END;/變異表：例子23一月2024————不可不求、也不可強(qiáng)求————14UPDATEempSETsal=1250WHEREENAME='SMITH復(fù)合觸發(fā)器復(fù)合觸發(fā)器是基于一個(gè)表的單一觸發(fā)器、在該觸發(fā)器中運(yùn)行您為四個(gè)觸發(fā)時(shí)機(jī)的每一個(gè)所指定的操作，這四個(gè)觸發(fā)時(shí)機(jī)分別為：在觸發(fā)語句之前在觸發(fā)語句影響的每一行之前在觸發(fā)語句影響的每一行之后在觸發(fā)語句之后23一月2024————不可不求、也不可強(qiáng)求————1523一月2024————不可不求、也不可強(qiáng)求————16復(fù)合觸發(fā)器的用法復(fù)合觸發(fā)器體支持一種通常的PL/SQL狀態(tài)、在該狀態(tài)中對于每一個(gè)觸發(fā)時(shí)機(jī)觸發(fā)器體的代碼都是可以訪問的復(fù)合觸發(fā)器的常態(tài)是：當(dāng)觸發(fā)語句開始時(shí)建立的在觸發(fā)語句完成時(shí)消失的復(fù)合觸發(fā)器有一個(gè)聲明段、并對每個(gè)時(shí)間點(diǎn)都有一個(gè)程序段使用復(fù)合觸發(fā)器的好處在以下情況下，使用復(fù)合觸發(fā)器將獲益匪淺：對于不同的時(shí)間點(diǎn)（時(shí)機(jī)），在程序中要實(shí)現(xiàn)對共享公用數(shù)據(jù)的一些操作將一些數(shù)據(jù)行累積在一起并存放在第二個(gè)表中以便能夠定期地批量插入這些數(shù)據(jù)。避免變異表錯(cuò)誤（ORA-04091）23一月2024————不可不求、也不可強(qiáng)求————17復(fù)合觸發(fā)器的時(shí)間點(diǎn)程序段在一個(gè)表上定義的一個(gè)復(fù)合觸發(fā)器都具有一個(gè)或多個(gè)時(shí)間點(diǎn)的程序段，一共有以下四個(gè)不同的時(shí)間點(diǎn)：以上每個(gè)時(shí)間點(diǎn)段必須按照表中所列的順序出現(xiàn)在一個(gè)復(fù)合觸發(fā)器的代碼中；若缺少某一個(gè)時(shí)間點(diǎn)段，那么在這個(gè)時(shí)間點(diǎn)就沒有任何事情發(fā)生23一月2024————不可不求、也不可強(qiáng)求————18基于表的復(fù)合觸發(fā)器結(jié)構(gòu)23一月2024————不可不求、也不可強(qiáng)求————19基于視圖的復(fù)合觸發(fā)器結(jié)構(gòu)23一月2024————不可不求、也不可強(qiáng)求————20復(fù)合觸發(fā)器的限制一個(gè)復(fù)合觸發(fā)器的體代碼必須復(fù)合了整個(gè)觸發(fā)器程序塊、而且必須是使用PL/SQL編寫的一個(gè)復(fù)合觸發(fā)器必須是一個(gè)DML觸發(fā)器一個(gè)復(fù)合觸發(fā)器必須被定義在一個(gè)表上或者一個(gè)視圖上一個(gè)復(fù)合觸發(fā)器體不能有初始化段；也不能有異常段在一個(gè)程序段中出現(xiàn)的一個(gè)異常必須在這個(gè)段中處理，復(fù)合觸發(fā)器無法將異常的控制傳遞給其它段。:OLD、:NEW、和:PARENT不能出現(xiàn)在聲明段中、也不能出現(xiàn)在BEFORESTATEMENT段中、還不能出現(xiàn)在AFTERSTATEMENT段中復(fù)合觸發(fā)器的觸發(fā)順序是無法保證的，除非您使用了FOLLOWS子句23一月2024————不可不求、也不可強(qiáng)求————21以復(fù)合觸發(fā)器解決變異表錯(cuò)誤23一月2024————不可不求、也不可強(qiáng)求————22CREATEORREPLACETRIGGERcheck_salaryFORINSERTORUPDATEOFsal,jobONempWHEN(NEW.job<>'PRESIDENT')COMPOUNDTRIGGERTYPEsal_tISTABLEOFemp.sal%TYPE;min_salsal_t;max_salsal_t;TYPEdeptno_tISTABLEOFemp.deptno%TYPE;dept_idsdeptno_t;TYPEdept_sal_tISTABLEOFemp.sal%TYPEINDEXBYVARCHAR2(38);dept_min_saldept_sal_t;dept_max_saldept_sal_t;--例子在下一個(gè)幻燈片中繼續(xù)以復(fù)合觸發(fā)器解決變異表錯(cuò)誤23一月2024————不可不求、也不可強(qiáng)求————23...BEFORESTATEMENTISBEGINSELECTMIN(sal),MAX(sal),NVL(deptno,-1)BULKCOLLECTINTOmin_sal,max_sal,dept_idsFROMempGROUPBYdeptno;FORjIN1..dept_ids.COUNT()LOOPdept_min_sal(dept_ids(j)):=min_sal(j);dept_max_sal(dept_ids(j)):=max_sal(j);ENDLOOP;ENDBEFORESTATEMENT;AFTEREACHROWISBEGINIF:NEW.sal<dept_min_sal(:NEW.deptno)OR:NEW.sal>dept_max_sal(:NEW.deptno)THENRAISE_APPLICATION_ERROR(-20038,'新工資已超出允許的范圍！');ENDIF;ENDAFTEREACHROW;ENDcheck_salary;23一月2024————不可不求、也不可強(qiáng)求————24創(chuàng)建基于DDL語句的觸發(fā)器的語法CREATE[ORREPLACE]TRIGGERtrigger_nameBEFORE|AFTER--Timing[ddl_event1[ORddl_event2OR...]]ON{DATABASE|SCHEMA}

trigger_body23一月2024————不可不求、也不可強(qiáng)求————25創(chuàng)建數(shù)據(jù)庫事件觸發(fā)器觸發(fā)的用戶事件：CREATE、

ALTER、或DROPLoggingon或off觸發(fā)的數(shù)據(jù)庫或系統(tǒng)事件：關(guān)閉或啟動(dòng)數(shù)據(jù)庫一個(gè)正在拋出的特定錯(cuò)誤（或任何錯(cuò)誤）23一月2024————不可不求、也不可強(qiáng)求————26創(chuàng)建基于系統(tǒng)事件的觸發(fā)器CREATE[ORREPLACE]TRIGGERtrigger_nameBEFORE|AFTER--timing[database_event1[ORdatabase_event2OR...]]ON{DATABASE|SCHEMA}

trigger_body23一月2024————不可不求、也不可強(qiáng)求————27LOGON和LOGOFF觸發(fā)器CREATEORREPLACETRIGGERlogon_triggerAFTERLOGONONDATABASEBEGININSERTINTOlog_onoff_table(user_id,log_date,action)VALUES(USER,SYSDATE,'用戶登錄');END;/CREATEORREPLACETRIGGERlogoff_triggerBEFORELOGOFFONDATABASEBEGININSERT