大型數(shù)據(jù)庫技術：PL SQL基礎

PL/SQL基礎概述PL/SQL（ProcedureLanguageandSQL）是Oracle對SQL的一種擴充，集成了程序化設計語言中的許多特性PL/SQL的特點過程化和模塊化：將邏輯上相關的語句組織在一個PL/SQL程序塊中使用過程化語言的控制結構：使用了控制語句，以及游標的顯式處理錯誤處理：使用EXCEPTION處理語句處理異?？梢浦残裕撼绦蚩梢浦驳饺魏沃С諳racle和PL/SQL的主機環(huán)境中集成：通過PL/SQL可以將前臺和后臺技術結合起來改善性能：PL/SQL可以改善一個應用的性能、減少網絡的流量概述支持所有的事務控制命令支持所有SQL的DML命令支持所有SQL的DDL命令支持所有SQL的DCL命令支持所有SQL的數(shù)據(jù)類型、函數(shù)、各種運算符PL/SQL程序塊可以存儲在服務器中，被其他程序或SQL命令調用對PL/SQL程序塊可以進行權限管理

PL/SQL中的Select語句PL/SQL的SELECTSELECT列名，列名...INTO

變量1，變量2...FROM表WHERE條件...;在使用SELECT…INTO…時，結果只能有一條，如果返回了多條數(shù)據(jù)或沒有數(shù)據(jù)，則將產生錯誤。（對于多條記錄的遍歷，可以使用游標）PL/SQL程序結構PL/SQL程序的單元由邏輯塊（BLOCK）組成塊可以順序出現(xiàn)，也可以相互嵌套每一個塊分成三部分[DECLEAR] --說明部分（可選的）BEGIN --語句執(zhí)行部分（必需的）[EXCEPTION] --出錯處理程序（可選的）END;/PL/SQL類型程序塊描述無名塊嵌入在應用內或交互式發(fā)出的無名塊有名塊（應用的過程、函數(shù)）可以接受參數(shù)，并返回結果的有名塊，存儲在應用程序中。可以被反復調用。存儲過程、函數(shù)可以接受參數(shù)，并返回結果的有名塊，存儲在服務器端?？梢员环磸驼{用。包有名的PL/SQL模塊，是相關的過程、函數(shù)、標識符的集合。存儲在服務器端?？梢员环磸驼{用。數(shù)據(jù)庫觸發(fā)器與數(shù)據(jù)庫表相關的PL/SQL塊，存儲在服務器端。在客戶與服務器端觸發(fā)事件發(fā)生時自動觸發(fā)。應用觸發(fā)器與一個應用事件相關的PL/SQL塊，存儲在服務器端。在應用程序的觸發(fā)事件時自動觸發(fā)。變量定義CONSTANT表示標識符是一個常量，常量必須賦初值NOTNULL表示變量必須賦初值沒有賦初值的變量，初值都是NULLV_num number(2) :=12;C_zero_value constantnumber :=0;V_char varchar2(10) :=‘asasasa’;V_date date :=’05-jul-99’;V_bool

boolean :=false;V_str varchar2(20);變量[CONSTANT]數(shù)據(jù)類型[NOTNULL][:=DEFAULTPL/SQL表達式];變量類型%TYPE和%ROWTYPE類型%TYPE：表示已經定義的變量類型定義%ROWTYPE：表示已經定義的表、視圖中的紀錄的類型或游標的結構類型優(yōu)點不必了解數(shù)據(jù)庫中列的個數(shù)和數(shù)據(jù)類型如果表結構改變，PL/SQL程序可以不變，減少程序的維護工作BOOKPRINT NUMBER;V_BOOK BOOKPRINT%TYPE;V_EMPNO EMP.EMPNO%TYPE;/*EMP表名，EMPNO列名*/V_REC EMP%ROWTYPE;/*EMP可以是表名或游標名*/自定義類型根據(jù)用戶的需要，用現(xiàn)有的PL/SQL標量類型組合成一個用戶自定義的類型createorreplacetypestudent_typeasobject( id number(5),

firstname varchar2(20),

lastname varchar2(20), major varchar2(30),

current_credits number(3));

v_stu

student_type;變量賦值變量名:=常量或PL/SQL表達式可在說明部分賦值，也可在執(zhí)行部分賦值DECLEAR

num_var number(5):=5;/*說明部分賦值*/

v_emp

emp%rowtype;BEGIN

v_emp.empno:=11011;

/*執(zhí)行部分賦值*/ ……

selectsal

intonum_varfromempwhereempno=7788;變量作用范圍變量如果不在子塊中重新定義，則在PL/SQL塊的所有子塊中有效如果變量在子塊內重新定義，子塊內定義的變量優(yōu)先，此變量的作用范圍僅在本子塊內有效。如果主塊中的變量A和子塊變量A中同時定義時，在子塊中要用主塊的變量A時，必須在變量前加塊的標識符（如：塊的標識符.A)。常用內置函數(shù)函數(shù)說明轉換前的類型TO_CHAR轉換成VARCHAR類型數(shù)字型、日期型TO_DATE轉換成DATE字符型TO_NUMBER轉換成number類型字符型SELECTTO_CHAR(current_credits)INTOv_1FROMstudent2WHEREid=1002IF語句DECLARE

v_sal

emp.sal%type;BEGIN SELECTsalINTOv_salFROMempWHEREempno=7788; IFv_sal<500THEN UPDATEempSETsal=sal*1.5WHEREempno=7788;

ELSIF

v_sal<1500THEN UPDATEempSETsal=sal*1.3WHEREempno=7788; ELSE UPDATEempSETsal=sal*1.0WHEREempno=7788; ENDIF; COMMIT;END;/<條件>是一個布爾型變量或表達式，取值只能是TRUE/FALSE/NULLCASE語句CASE WHENgrade=‘A’THENdbms_output.put_line(‘excellent’); WHENgrade=‘B’THENdbms_output.put_line(‘verygood’); WHENgrade=‘C’THENdbms_output.put_line(‘good’); WHENgrade=‘D’THENdbms_output.put_line(‘fair’); WHENgrade=‘F’THENdbms_output.put_line(‘poor); ELSEdbms_output.put_line(‘nosuchgrade’);ENDCASE;LOOP語句DECLARE

v_counternumber:=1;BEGIN LOOP

INSERTINTOtemp_tableVALUES(v_counter,’loopindex’);

v_counter:=v_counter+1; IFv_counter>50THEN exit ENDIF; ENDLOOP;END;/WHILE語句DECLARE

v_counternumber:=1;BEGIN WHILEv_counter<=50LOOP

INSERTINTOtemp_tableVALUES(v_counter,‘loop_index’);

v_counter:=v_counter+1; ENDLOOP;END;/FOR語句DECLARE

v_counternumber:=1;BEGIN FORv_counterIN1..50LOOP

INSERTINTOtemp_tableVALUES(v_counter,’loopindex’); ENDLOOP;END;/ FORv_counterINREVERSE1..50LOOPGOTO語句DECLARE

v_vonternumber:=1;BEGIN LOOP INSERTINTOtemp_tableVALUES(v_counter,‘loopcount’);

v_counter:=v_counter+1; IFv_counter>50THEN

GOTOENDOFLOOP; ENDIF; ENDLOOP;

<<ENDOFLOOP>> INSERTINTOtemp_table(char_col)VALUES(‘done!’);END;GOTO語句對于塊、循環(huán)或IF語句而言，想要從外層跳到內層是非法的從一個IF子句調轉到IF的另一個子句中是非法的出錯處理塊不能實行跳轉BEGINGOTO innerblock;BEGIN<<innerblock>>...END;GOTOinsideifIFx>3THEN...<<insideif>>INSERTINTO...ENDIF;END;DECLARE

v_room

room%rowtype;BEGINSELECT*INTOv_roomFROMroomsWHERErowid=1;<<insert>>INSERTINTOtemp_table(char_col)VALUES(‘foundarow’);EXCEPTIONWHENno_data_foundTHENGOTOinsert;END;異常處理一個PL/SQL塊的出錯處理（EXCEPTION）部分包含程序處理多個錯誤的代碼。當一個錯誤發(fā)生時，程序控制離開PL/SQL塊的執(zhí)行部分轉移到出錯處理部分...BEGIN...EXCEPTION WHEN錯誤1[OR錯誤2]THEN語句序列1; WHEN錯誤3[OR錯誤4]THEN語句序列2; ... WHENOTHERSTHEN語句序列3;END;異常處理錯誤描述處理方法系統(tǒng)預定義錯誤在PL/SQL中經常出現(xiàn)的25個系統(tǒng)定義的錯誤不必定義，允許服務器隱式地觸發(fā)它們，只需要在出錯處理部分處理它們用戶自定義錯誤開發(fā)者認為是非正常的一個條件必須在說明部分定義，在執(zhí)行部分顯式觸發(fā)它們，在在出錯處理部分處理它們異常代碼異常名稱說明ORA-01403NO_DATA_FOUND查詢沒有返回數(shù)據(jù)ORA-01422TOO_MANY_ROWSSELECT…INTO語句返回多行結果ORA-00001DUP_VAL_ON_INDEX試圖更新或插入重復記錄ORA-01001INVALID_CURSOR非法游表操作ORA-01722INVALID_NUMBER字符串向數(shù)字轉換失敗ORA-06502VALUE_ERROR出現(xiàn)數(shù)字、數(shù)據(jù)轉換、字符串或限制型錯誤ORA-01476ZERO_DIVIDE被零除異常處理DECLARE

v_comm

m%type;BEGIN SELECT comm INTO v_comm FROM emp WHERE empno=7788;EXCEPTION WHENno_data_foundTHEN

dbms_output.put_line(‘nodata!’); WHENtoo_many_rowsTHEN

dbms_output.put_line(‘toomanyrow!’); WHENothersTHEN

null;END;自定義異常用戶自定義的錯誤是程序定義的一個錯誤，并不一定是Oracle錯誤，它可能是與業(yè)務相關的一個錯誤，比如違反了業(yè)務規(guī)則處理用戶自定義的錯誤有三個步驟：定義錯誤、觸發(fā)錯誤、處理錯誤。DECLARE

e_toomanystudent exception;

/*定義錯誤*/

v_currentstudent number(3);

v_maxstudent number(3);

v_errorcode number;

v_errortext varchar2(200);BEGIN SELECTcurrent_student,max_studentsINTO

v_currentstudent,v_maxstudentFROMclasse WHEREdepartment=‘HIS’andcourse=101;IFv_currentstudent>v_maxstudentTHEN

RAISE e_toomanystudent

;

/*觸發(fā)錯誤*/ENDIF;EXCEPTIONWHENno_data_foundortoo_many_rowsTHEN

dbms_output.putline(‘發(fā)生系統(tǒng)預定義錯誤’);WHENe_toomanystudentTHEN /*處理錯誤*/ INSERTINTOlog_table(info) VALUES(‘history101has’||v_currentstudent);WHENothersTHEN

v_errorcode=sqlcode;

v_errortext=substr(sqlerrm,1,200) INSERTINTOlog_table(code,message,info) VALUES(v_errorcode,v_errortext,’Oracleerroroccured’);END;/課堂作業(yè)查找classes表中信息系102課程的當前學生數(shù)與最大學生數(shù)，比較這兩個數(shù)，如果當前學生數(shù)超過最大學生數(shù)是不允許的，就觸發(fā)錯誤。當這種錯誤發(fā)生時，向表log_table中插入一條紀錄，該條紀錄記載出錯信息和代碼declare

v_num number；

e_toomany exception；begin selectcount(1)intov_numfromclasseswheredeptname=‘信息系’andclassid=102； ifv_num>MAX_NUMthen raise e_toomany; endif;exception whene_toomanythen INSERTINTOlog_table(code,message,info) VALUES(sqlcode,substr(sqlerrm,1,200),Toomanystudents!’);end;/課堂作業(yè)現(xiàn)有一個公司的部門及雇員的數(shù)據(jù)庫，由下面三個表組成完成顯示薪金最高的3位員工(SQL或者PL/SQL)求和其部門經理住址相同的員工的姓名及經理姓名刪除部門工作年限為0的工作記錄，若刪除后有雇員沒有在任何部門工作，刪除其雇員記錄Employee（empid，empname，age，address，sal）Department（deptno，deptname，mgr）Work（empid，deptno，worktime）游標游標是一個指向內存區(qū)域的指針，一個PL/SQL結構游標有兩種類型：顯式游標和隱式游標顯式游標：是由程序員定義和命名的，并且在塊的執(zhí)行部分中通過特定語句操縱的內存工作區(qū)隱式游標：是由PL/SQL為DML語句和SELECT語句隱式定義的工作區(qū)ActivesetCurrentrowCursor7369 SMITH CLERK7566 JONES MANAGER7788 SCOTT ANALYST7876 ADAMS CLERK7902 FORD ANALYST顯式游標的處理步驟定義游標打開游標將當前行結果提取到變量中判斷數(shù)據(jù)行是否讀取完關閉游標DECLAREOPENFETCHEMPTY?NoCLOSEYes如果沒有讀取完，就讀取當前行顯式游標的處理步驟Openthecursor.CursorPointerFetcharowfromthecursor.CursorPointerContinueuntilempty.CursorPointerClosethecursor.顯式游標的處理游標是一個不含INTO子句的SELECT語句。SELECT語句允許帶WHERE,ORDERBY,GROUPBY等子句DECLARECURSORemp_cursorISSELECTempno,ename FROMemp;CURSORdept_cursorISSELECT*FROMdeptWHEREdeptno=10;BEGIN...CURSOR游標名ISselect語句;顯式游標的處理執(zhí)行游標所對應的SELECT語句，將其查詢結果放入工作區(qū)，并且指針指向工作區(qū)首部將當前行的數(shù)據(jù)存入到變量中變量數(shù)量和行紀錄的數(shù)據(jù)項數(shù)量要相同，相應數(shù)據(jù)類型一致判斷游標中是否還有行紀錄OPEN游標名;FETCH

游標名

INTO

[變量１,變量２,...]|紀錄名];顯式游標的處理FETCHemp_cursorINTOv_empno,v_ename;DECLARE CURSORc_classesISSELECT*FROMclass;

v_classes

c_classes%rowtype;BEGIN ...OPENc_classes;FETCHc_classesINTOv_classes;...END;/顯式游標的處理完成對行數(shù)據(jù)處理后，可以關閉游標。如果需要，還可以再次打開游標。關閉游標后，系統(tǒng)釋放與該游標關聯(lián)的資源，不能再對游標進行FETCH操作。CLOSE 游標名;CLOSE c_classes;顯式游標的屬性屬性類型描述%ISOPENBoolean如果游標已打開，取值為TRUE，否則取值為FALSE%NOTFOUNDBoolean如果最近一次FETCH操作沒有返回結果，則取值為TRUE,否則為FALSE%FOUNDBoolean如果最近一次FETCH操作沒有返回結果，則取值為FALSE,否則為TRUE%ROWCOUNTNumber值是當前為止返回的紀錄數(shù)，初值為0，每取一條紀錄，該屬性值加1顯式游標的屬性...IFemp_cursor%ISOPENTHEN FETCHemp_cursorINTOv_ename,v_sal;ELSE OPENemp_cursor;ENDIF;......LOOPFETCHemp_cursorINTOv_ename,v_sal;EXITWHENemp_cursor%ROWCOUNT>5oremp_cursor%NOTFOUND;...ENDLOOP;...顯式游標的屬性DECLARE

v_deptno

emp.deptno%type:=&p_deptno;

v_ename

emp.ename%type;

v_sal

emp.sal%type;

CURSOR

emp_cursorISSELECTename,salFROMemp WHEREdeptno=v_deptno;BEGIN

OPEN

emp_cursor;LOOP

FETCH

emp_cursorINTOv_ename,v_sal;EXITWHENemp_cursor%NOTFOUND;INSERTINTOtemp(ename,sal)VALUES(v_ename,v_sal);ENDLOOP;

CLOSE

emp_cursor;COMMIT;END;/游標的FOR循環(huán)使用游標的FOR循環(huán)，可以簡化游標的操作步驟游標的FOR循環(huán)隱式（自動）地完成三個步驟：打開游標；（FETCH）取數(shù)據(jù)；關閉游標紀錄名是系統(tǒng)隱式定義的游標名%ROWTYPE類型的紀錄變量，不必事先定義FOR紀錄名IN游標名LOOP

語句1；

語句2； ...ENDLOOP;游標的FOR循環(huán)DECLARE

v_deptno

emp.deptno%type:=&p_deptno;CURSORemp_cursorISSELECTename,salFROMemp WHEREdeptno=v_deptno;BEGINFORemp_recordINemp_cursorLOOPINSERTINTOtemp(ename,sal)VALUES(emp_record.ename,emp_record.sal);ENDLOOP;COMMIT;/*在本段程序中，沒有打開游標、取數(shù)據(jù)、關閉游標語句*/END;/通過游標操縱數(shù)據(jù)庫當用戶從一張表或多張表中查詢多條紀錄時，必須使用一個顯式游標要操縱數(shù)據(jù)庫中數(shù)據(jù)，在定義游標的查詢語句時，必須加上FORUPDATEOF從句,表示要對表加鎖表加鎖后，在UPDATE或DELETE語句中，加WHERECURRENTOF子句，即可以對鎖定的數(shù)據(jù)進行修改CURSOR游標名

ISSELECT列1，列2…FORM表WHERE條件

FORUPDATE[OFcolumn][NOWAIT];通過游標操縱數(shù)據(jù)庫帶WHERECURRENTOF從句的UPDATE語句和DELETE語句DELETEFROM表WHERECURRENTOF

游標名;UPDATE表SET列1=值1，列2=值2...WHERECURRENTOF

游標名；通過游標操縱數(shù)據(jù)庫DECLARE

v_deptno

emp.deptno%type:=&p_deptno;

v_empno

emp.empno%type;

v_job

emp.job%type;

v_sal

emp.sal%type;CURSORemp_cursorISSELECTename,job,salFROMemp WHEREdeptno=v_deptnoFORUPDATEOFsal;BEGINFORemp_recordINemp_cursorLOOPIFemp_record.sal<800THENUPDATEempSETsal=800WHERECURRENTOFemp_cursor;ENDIF;

dbms_output.put_line(emp_record.empno||emp_record.sal);ENDLOOP;COMMIT;END;查詢emp表某部門的雇員情況，如果某雇員的工資小于800，則將其工資改成800DECLARE

emp_numnumber:=0;s_sal

emp.sal%type;

e_sal

emp.sal%type;e_empno

emp.empno%type;CURSORc1ISSELECTempno,salFROMempORDERBYsal

FORUPDATEOFsal;BEGINOPENc1;SELECTsum(sal)INTOs_salFROMemp;WHILEs_sal<500000LOOPFETCHc1INTOe_empno,e_sal;EXITWHENc1%NOTFOUND;UPDATEempSETsal=sal*1.1WHERECURRENTOFc1;

s_sal:=s_sal+e_sal*0.1;

emp_num:=emp_num+1;ENDLOOP;CLOSEc1;INSERTINTOmsgVALUES(emp_num,s_sal);COMMIT;END;為職工增加10％的工資，從最低工資開始長，增加后工資總額限制在50萬以內帶參數(shù)的游標在定義參數(shù)時，只定義參數(shù)類型，不定義長度此參數(shù)只能在游標的查詢語句中使用只能向游標傳遞參數(shù)數(shù)值，不能通過參數(shù)帶出結果可以有選擇地給參數(shù)提供一個默認值CURSOR游標名(參數(shù)1數(shù)據(jù)類型[{:=|DEFAULT}值]

[,參數(shù)2數(shù)據(jù)類型[{:=|DEFAULT]值]...]ISSELECT語句;帶參數(shù)的游標參數(shù)可以是具體的值，也可以是一個已經賦值的變量不同的參數(shù)值，返回不同的結果OPEN游標名(&參數(shù)1,&參數(shù)2...);DECLARECURSORemp_cursor

(p_deptnoNUMBER,p_jobVARCHAR2)ISSELECT empno,ename FROM empWHERE deptno=p_deptno

AND job=p_job;BEGINOPENemp_cursor(10,'CLERK');...DECLARECURSORdept_cursorISSELECTdeptnoFROMdept;

v_ename

emp.ename%type;

v_sal

emp.sal%type;CURSORemp_cursor(v_deptnonumber)ISSELECTename,salFROMempWHEREdeptno=v_deptno;BEGINFORdept_recordINdept_cursorLOOPEXITWHENdept_cursor%NOTFOUND;OPENemp_cursor(dept_record.deptno);LOOPFETCHemp_cursorINTOv_ename,v_sal;EXITWHENemp_cursor%NOTFOUND;

dbms_output.put_line(dept_record.deptno||’’||’’||v_sal);ENDLOOP;CLOSEemp_cursor;ENDLOOP;COMMIT;END;首先查詢DEPT表取出所有的部門號，然后根據(jù)DEPT表中返回的每一個部門號，從emp表查詢該部門的雇員信息隱式游標隱式游標的特點由系統(tǒng)定義，不需要用戶定義用來處理INSERT,UPDATE,DELETE和單行的SELECT…INTO語句游標名為SQL游標的屬性存儲有關最近一次SQL命令的狀態(tài)信息與顯式游標的屬性類似，也有四個屬性含義和類型與顯式游標的屬性一致隱式游標屬性屬性值DELETEUPDATEINSERTSELECTSQL%FOUNDTRUE成功成功成功返回一行SQL%FOUNDFALSE失敗失敗失敗沒結果SQL%NOTFOUNDTRUE失敗失敗失敗沒結果SQL%NOTFOUNDFALSE成功成功成功返回一行SQL%ROWCOUNT行數(shù)刪除的行數(shù)修改的行數(shù)插入的行數(shù)成功1,失敗0SQL%ISOPENFALSEFALSEFALSEFALSEFALSEBEGINUPDATEempSETsal=10000,deptno=30WHEREempno=9998；

IFSQL%NOTFOUNDTHEN

INSERTINTOemp(empno,sal,deptno)VALUES(9998,10000,30);ENDIF;COMMIT;END;隱式游標VS.顯式游標隱式游標顯式游標當執(zhí)行插入、更新、刪除及查詢只有一條紀錄時，由PL/SQL內部管理，自動打開和關閉游標在程序中顯式地定義、打開和關閉。游標有一個名字游標的屬性前綴是SQL游標的屬性前綴是游標名游標屬性%ISOPEN總是FALSE，因為當語句執(zhí)行完后立即關閉隱式游標%OPEN屬性有一個有效值，依賴游標狀態(tài)SELECT…INTO語句只能執(zhí)行一行可以處理任何行課堂練習通過帶參數(shù)游標，完成下述功能：通過查詢學生成績表Score取出成績不及格的學生學號，然后根據(jù)不及格學生學號，從學生檔案表Student中查詢該學生的信息存儲過程ORREPLACE是一個可選的關鍵字，表示替代原有的過程IS或AS后面是一個完整的PL/SQL塊的三部分（說明部分、執(zhí)行部分、異常處理部分）IN表示輸入變量，OUT表示輸出變量，INOUT表示輸入輸出變量,缺省表示INCREATE[ORREPLACE]PROCEDURE過程名

[(參數(shù)名[IN|OUT|INOUT]數(shù)據(jù)類型,...)]{IS|AS} [說明部分]BEGIN

語句序列

[EXCEPTION出錯處理]END[過程名];CREATEORREPLACEPROCEDUREmodetest(

p_inparaIN number,

p_outpareOUTnumber,

p_inoutparaINOUTnumber)IS

v_localnumber;BEGIN

v_local :=p_inpara;/*

IN類型參數(shù)不能出現(xiàn)在:=的左邊*/

p_outpara :=7;/*OUT類型參數(shù)不能出現(xiàn)在:=的右邊*/

v_local :=p_inoutpara;

p_inoutpara :=7;END;CREATEPROCEDUREraise_salary(emp_id

interger,v_increaseinteger)IS

/*缺省IN*/BEGINUPDATEempSETsal=sal+v_increase

WHEREempno=emp_id;COMMIT;END;存儲過程如果INOUT參數(shù)的值在過程中沒有被更改，則它返回到調用環(huán)境時值不變如果INOUT參數(shù)是字符型，不能指定長度。長度由調用環(huán)境決定CREATEORREPLACEPROCEDUREquery_emp(

v_emp_noINemp.empno%TYPE,

v_emp_nameOUTemp.ename%TYPE,

v_emp_salOUTemp.sal%TYPE,

v_emp_commOUTm%TYPE)ISBEGINSELECTename,sal,commINTOv_emp_name,v_emp_sal,v_emp_comm

FROMempWHEREempno=v_emp_no;ENDquery_emp;函數(shù)參數(shù)都是IN類型，存儲函數(shù)必須返回并且只返回一個結果函數(shù)體的可執(zhí)行部分必須有RETURN語句（RETURN表達式）表達式的數(shù)值類型與RETURN子句中定義要一致CREATE[ORREPLACE]FUNCTION函數(shù)名

[(參數(shù)名[IN]數(shù)據(jù)類型,...)]RETURN數(shù)據(jù)類型{IS|AS} [說明部分]BEGIN

語句序列；

RETURN（表達式）；[EXCEPTION出錯處理]END[函數(shù)名];CREATEORREPLACEFUNCTIONget_sal(p_emp_noINempno%TYPE)RETURNNUMBERIS

v_emp_sal

emp.sal%TYPE:=0;BEGIN SELECTsalINTOv_emp_salFROMempWHEREempno=p_emp_no；

RETURN(v_emp_sal）；EXCEPTIONWHENno_data_foundortoo_many_rowsTHEN

dbms_output.put_line(‘SystemError’)；

WHENothersTHEN

dbms_output.put_line(sqlerrm);ENDget_sal;/函數(shù)CREATEORREPLACEFUNCTIONaverage_sal(v_nINnumber(3))RETURNNUMBERIS CURSORc_empISSELECTempno,salFROMemp;

v_total_sal

emp.sal%TYPE;

v_counternumber;

v_emp_no

emp.empno%TYPE;BEGINFORr_empINc_empLOOPEXITWHENc_emp%ROWCOUNT>v_nORc_emp%NOTFOUND;

v_total_sal:=v_total_sal+r_emp.sal;

v_counter:=c_emp%ROWCOUNT;

v_emp_no:=r_emp.empno;

dbms_output.putline(‘loop=’||v_counter||’;empno=’||v_emp_no);ENDLOOP;RETURN(v_total_sal/v_counter);ENDaverage_sal;/過程VS.函數(shù)參數(shù)類型不同：函數(shù)只有IN類型參數(shù)，而存儲過程有IN、OUT、INOUT三個類型參數(shù)返回值的方法不同：函數(shù)返回只有一個值，而存儲過程返回值由OUT參數(shù)帶出來調用方法不同：過程名（實際參數(shù)１，實際參數(shù)２…）；變量名:＝函數(shù)名（實際參數(shù)１，實際參數(shù)２…）；CREATEORREPLACEPROCEDUREfire_emp(

p_emp_noINemp.empno%TYPE)IS

invalid_employeeEXCEPTION; /*定義錯誤*/BEGINDELETEFROMempWHEREempno=p_emp_no;IFSQL%NOTFOUNDTHENRAISEinvalid_employee;/*觸發(fā)錯誤*/ENDIF;EXCEPTIONWHENinvalid_employeeTHENROLLBACK; INSERTINTOexception_table(line_nr,line) VALUES(1,’employeedoesnotexist.’);WHENothersTHEN

dbms_output.putline(sqlerrm);ENDfire_emp;/過程/函數(shù)中的異常處理在SQL*Plus中使用過程/函數(shù)在SQL*Plus中，用VARIABLE定義的變量在引用時，必須前面加冒號（:）。用ACCEPT接受的變量在引用時，前面加&符號SETSERVEROUTPUTONACCEPT

p_emp_noPROMPT‘pleaseentertheemployeenumber:’VARIABLE

v_emp_namevarchar2(14);VARIABLEv_emp_salnumber;VARIABLEv_emp_commnumber;EXECUTEquery_emp(&p_emp_no,:v_emp_name,:v_emp_sal,:v_emp_comm);EXECUTEdbms_output.putline(‘Infomationforemployee:’||to_char(&p_emp_no));EXECUTEdbms_output.putline(‘Thenameis:’||:v_emp_name);EXECUTEdbms_output.putline(‘Thesalaryis:’||to_char(:v_emp_sal));EXECUTEdbms_output.putline(Thecommissionis:’||to_char(:v_emp_comm));Package包是一個可以將相關對象存儲在一起的PL/SQL結構它包含了兩個分離的組成部分：包說明和包主體包的組成可以將相關的若干程序單元組織到一塊，用一個包名來標識這個集合。程序單元描述過程（procedure）帶有參數(shù)的程序函數(shù)（function）帶有參數(shù)的程序,該程序只返回一個值變量（variable）用于存儲變化值的存儲單元游標（cursor）指向一個SQL語句的指針類型（type）定義一個結構類型常量（constant）指向常數(shù)的指針出錯情況（exception）標志一個出錯情況包中可以包含的程序單元CREATE[ORREPLACE]PACKAGE包名｛IS|AS｝公共變量的定義 |

公共類型的定義 |

公共出錯處理的定義 |

公共游標的定義 |

函數(shù)說明 |

過程說明END;/CREATEPACKAGEsal_packageISPROCEDUREraise_sal(v_empno

emp.empno%TYPE,

v_sal_increment

emp.sal%TYPE);PROCEDUREreduce_sal(v_empno

emp.empno%TYPE,

v_sal_reduce

emp.sal%TYPE);

v_raise_sal

emp.sal%TYPE:=0;

v_reduce_sal

emp.sal%TYPE:=0;END;/PackageCREATE[ORREPLACE]PACKAGEBODY包名｛IS|AS｝私有變量的定義 |

私有類型的定義 |

私有出錯處理的定義 |

私有游標的定義 |

函數(shù)定義 |

過程定義END;/PackageCREATEPACKAGEBODYsal_packageISPROCEDUREraise_sal(v_empno

emp.empno%TYPE,

v_sal_increment

emp.sal%TYPE)ISBEGINUPDATEempSETsal=sal+v_sal_incrementWHEREempno=v_empno;COMMITWORK;

v_raise_sal:=v_raise_sal+v_sal_increment;END;PROCEDUREreduce_sal(v_empno

emp.empno%TYPE,

v_sal_reduce

emp.sal%TYPE)ISBEGINUPDATEempSETsal=sal–v_sal_reduce

WHEREempno=v_empno;COMMITWORK;

v_reduce_sal:=v_reduce_sal+v_sal_reduce;END;END;常用Package所有提供的Package由SYS所擁有，對于不是SYS的用戶，必須擁有EXCEUTE權限才能調用創(chuàng)建數(shù)據(jù)字典的catproc.sql腳本也會創(chuàng)建這些Package所有Oracle提供的程序包都是以DBMS_或UTL_開頭系統(tǒng)包功能DBMS_OUTPUT從存儲過程中輸出信息DBMS_MAIL將Oracle系統(tǒng)與Oracle*Mail連接起來DBMS_LOCK進行復雜的鎖機制管理DBMS_ALEART標識數(shù)據(jù)庫中發(fā)生的某個警告事件（報警）DBMS_PIPE在不同的會話間傳遞信息（管道通信）DBMS_JOB管理作業(yè)隊列中的作業(yè)（已被DBMS_SCHEDULER代替）DBMS_LOB操縱大對象（CLOB,BLOB,BFILE等類型的值）DBMS_JAVA用來和JAVA存儲過程一起工作DBMS_SQL動態(tài)SQL語句（通過該包可在PL/SQL中執(zhí)行DDL命令）（EXECUTE

IMMEDIATE）DBMS_UTILITY一個有用過程的“大雜燴”集合UTL_FILE允許PL/SQL在服務器上讀寫文件UTL_HTTP從PL/SQL內提供對HTTP協(xié)議的訪問UTL_SMTP從PL/SQL內提供對SMTP協(xié)議的訪問常用Package10g引入dbms_scheduler來替代先前的DBMS_JOB，比DBMS_JOB提供了更強大的功能和更靈活的機制Program：提供了scheduler將要運行什么，包含programname，programtype(PLSQL塊、存儲過程、外部程序)，programaction(具體的程序實體，比如說一個PLSQL塊、或者是存儲過程的名稱、或者是外部程序的路徑加名稱)Schedules：指定何時何種頻率來運行job，可以被不同的job重復的利用。比如有兩個job都要求在每天的凌晨4點運行，那么兩個job可以同時指定相同的scheduleJobs：具體的作業(yè)的名稱，依賴于那個program、schedule。也可以不指定program、schedule，而直接寫入program、schedule的相應參數(shù)Chains：把一組program像一個單獨的實體連接在一起，作為一個對象。Job可以指向Chains代替單一的program。這樣就能完成類似如下功能：運行A，然后運行B，當A、B都運行成功則運行C，否則運行Ddbms_schedulerCREATEORREPLACEPROCEDUREP_INSSYSDATEAS

IDNUMBER;

BEGIN

SELECTCOUNT(*)INTOIDFROMSCHEDULER_TEST;

INSERTINTOSCHEDULER_TESTVALUES(ID,SYSDATE);

COMMIT;

END;BEGIN

SYS.DBMS_SCHEDULER.CREATE_SCHEDULE(

schedule_name=>'SCOTT.EVERY_MINUTE’,

start_date=>NULL,

repeat_interval=>‘FREQ=MINUTELY‘,

end_date=>NULL,

comments=>NULL );

END;BEGIN

SYS.DBMS_SCHEDULER.CREATE_PROGRAM(

program_name=>‘SCOTT.PRG_INSSYSDATE‘,

program_type=>’STORED_PROCEDURE‘,

program_action=>’p_insSysdate‘,

number_of_arguments=>0,

enabled=>TRUE,

comments=>NULL);

END;BEGIN

SYS.DBMS_SCHEDULER.CREATE_JOB(

job_name=>‘SCOTT.JOB_INSSYSDATE’

schedule_name=>‘SCOTT.EVERY_MINUTE’,

program_name=>‘SCOTT.PRG_INSSYSDATE’,

comments=>NULL);

END;BEGINFORIIN1..100LOOPBEGINEXECUTEIMMEDIATE'DROPTABLET'||I;EXCEPTIONWHENOTHERSTHENNULL;END;EXECUTEIMMEDIATE'CREATETABLET'||I||'(DUMMYCHAR(1))';EXECUTEIMMEDIATE'INSERTINTOT'||I||'VALUES(''X'')';ENDLOOP;END;/DynamicSQLdeclare

t_vararray

dbms_utility.lname_array;

vc_stringlistvarchar2(4000);

n_idx

binary_integer;begin

--commatotable

vc_stringlist:='dkf,dddl,fewe,klkj';

dbms_ma_to_table(vc_stringlist,n_idx,t_vararray);

dbms_output.put_line('TotalNum:'||to_char(n_idx));

foriin1..n_idxloop

dbms_output.put_line(t_vararray(i));

t_vararray(i):='['||t_vararray(i)||']';endloop;

--tabletocomma

dbms_utility.table_to_comma(t_vararray,n_idx,vc_stringlist);

dbms_output.put_line('');

dbms_output.put_line(vc_stringlist);

end;將字符串每個逗號字符用方括號進行封裝TotalNum:4dkfdddlfeweklkj[dkf],[dddl],[fewe],[klkj]DECLARE

l_tempNUMBER;BEGIN

l_temp:=DBMS_UTILITY.get_time;

dbms_output.put_line('before='||l_temp);

dbms_lock.sleep(5);

l_temp:=DBMS_UTILITY.get_time;

dbms_output.put_line('after='||l_temp);END;/DECLARE

l_tempNUMBER;BEGIN

l_temp:=DBMS_UTILITY.get_cpu_time;

dbms_output.put_line('before='||l_temp);

dbms_lock.sleep(5);

l_temp:=DBMS_UTILITY.get_cpu_time;

dbms_output.put_line('after='||l_temp);END;/before=59955835after=59956335before=10after=10觸發(fā)器觸發(fā)器由說明部分、語句執(zhí)行部分和出錯處理部分三部分組成的PL/SQL有名塊（類似于存儲過程和函數(shù)）觸發(fā)器不接受參數(shù)，不能在程序中調用當觸發(fā)事件發(fā)生時隱式地（自動地）執(zhí)行觸發(fā)事件包括：在數(shù)據(jù)庫表上執(zhí)行如下操作INSERTUPDATEDELETE觸發(fā)器維護不可能在表創(chuàng)建時通過說明約束進行的復雜的完整性約束限制通過紀錄所進行的修改及誰做了修改等信息對表進行審計當表被修改的時候，自動給需要執(zhí)行操作的程序發(fā)信號觸發(fā)器CREATEORREPLACETRIGGERcheck_sal

BEFOREINSERTORUPDATEOFsal,jobONempFOREACHROWWHEN(new.job<>‘PRESIDENT’)DECLAER

v_minsal

sal_guide.minsal%TYPE;

v_maxsal

sal_guide.maxsal%TYPE;

e_sal_out_of_rangeEXCEPTION;BEGINSELECTminsal,maxsalINTOv_minsal,v_maxsal

FROMsal_guideWHEREjob=:new.job;IF(:new.sal<v_minsal)OR(:new.sal>v_maxsal)THENRAISEe_sal_out_of_range;ENDIF;EXCEPTIONWHENe_sal_out_rangeTHENINSERTINTOaudit_message(line_nr,line)VALUES(1,’salary’||to_char(:new.sal)||‘isoutofrangeforemployee’||to_char(:new.empno));END;將超過某工種工資范圍的員工信息紀錄到aduit_message表中。sal_guide表紀錄了每一工種的工資范圍觸發(fā)器觸發(fā)器類型：語句級和行級觸發(fā)事件：表的插入、更新、刪除觸發(fā)時間：BEFORE和AFTER組成部分描述可能值觸發(fā)時間與觸發(fā)事件的時間次序BEFORE,AFTER觸發(fā)事件觸發(fā)觸發(fā)器的數(shù)據(jù)類型INSERT,UPDATE,DELETE觸發(fā)器類型觸發(fā)器體被執(zhí)行的次數(shù)STATEMENT,ROW觸發(fā)器體該觸發(fā)器要執(zhí)行的動作完整的PL/SQL塊觸發(fā)器觸發(fā)器分語句級觸發(fā)器和行級觸發(fā)器兩個級別行級觸發(fā)器與語句觸發(fā)器的區(qū)別主要在于觸發(fā)次數(shù)不同如果該DML語句只影響一行，則語句級和行級觸發(fā)器效果一樣如果該DML語句影響多行，則行級觸發(fā)器觸發(fā)的次數(shù)比語句級觸發(fā)器觸發(fā)的次數(shù)多觸發(fā)器下面DML語句對語句級和行級觸發(fā)器效果一樣。因為這些語句只影響一行下列DML語句的行級與語句級觸發(fā)器效果不同。因為這些語句只影響多行SQL>INSERTINTOdept(deptno,dname)VALUES(50,’education’);SQL>UPDATEdeptSETloc=‘MAUI’WHEREdeptno=50;SQL>DELETEFROMdeptWHEREdeptno=50;SQL>INSERTINTOemp(empno,dname)SELECTempno,enameFROMemp_backup;SQL>UPDATEdeptSETsal=sal*1.1WHEREdeptno=10;觸發(fā)器觸發(fā)時間級別描述BEFORE語句級在觸發(fā)SQL語句之前執(zhí)行一次BEFORE行級在觸發(fā)SQL語句影響的每條紀錄被修改、刪除或插入之前執(zhí)行一次AFTER行級在觸發(fā)SQL語句影響的每條紀錄被修改、刪除或插入之后執(zhí)行一次AFTER語句級在觸發(fā)SQL語句之后執(zhí)行一次觸發(fā)器假如一張表同時定義了這四種DML觸發(fā)器，他們的優(yōu)先級別如下執(zhí)行SQL語句（插入、更新、刪除一條或多條記錄）語句之前觸發(fā)行之前觸發(fā)插入、更新、刪除一條紀錄行之后觸發(fā)語句之后觸發(fā)觸發(fā)器VS.存儲過程觸發(fā)器存儲過程當某類數(shù)據(jù)操縱DML語句發(fā)生時隱式地調用從一個應用或過程中顯式地調用在觸發(fā)器體內禁止使用COMMIT,ROLLBACK語句在存儲過程體內可以使用COMMIT,ROLLBACK語句不能接受參數(shù)輸入可以接受參數(shù)輸入觸發(fā)器事件指明觸發(fā)事件的數(shù)據(jù)操縱語句，有三種可能的值：INSERT,UPDATE,UPDATEOF列名1[,列名2…],DELETE該觸發(fā)器在一個數(shù)據(jù)操作語句發(fā)生時只觸發(fā)一次CREATE[ORREPLACE]TRIGGER觸發(fā)器名{BEFORE|AFTER}事件1[OR事件2...]ON表名PL/SQL塊；觸發(fā)器創(chuàng)建一個BEFORE型語句級觸發(fā)器。限制一周內向emp表插入數(shù)據(jù)的時間，如果是周六、周日，或晚上6點到第二天早上8點之間插入，則中斷操作，并提示用戶不允許在此時間向emp表插入CREATEORREPLACETRIGGERsecure_empBEFOREINSERTONempBEGINIF(TO_CHAR(sysdate,’DY’)IN(’SAT’,’SUN’)OR(TO_CHAR(sysdate,’HH24’)NOTBETWEEN’8’AND’18’)THENRAISE_APPLICATION_ERROR(-20500,’youmayonlyinsertempduringnormalhours.’);ENDIF;END; 觸發(fā)器使用觸發(fā)器謂詞（INSERTING、UPDATING、DELETING）觸發(fā)器可以包含多個觸發(fā)事件，在觸發(fā)器體中使用謂詞判斷是哪個觸發(fā)了觸發(fā)器謂詞行為和值INSERTING如果觸發(fā)事件是INSERT,則謂詞的值為TRUE，否則為FALSEUPDATING如果觸發(fā)事件是UPDATE,則謂詞的值為TRUE，否則為FALSEDELETING如果觸發(fā)事件是DELETE,則謂詞的值為TRUE，否則為FALSE觸發(fā)器CREATEORREPLACETRIGGERsecure_empBEFOREDELETEORINSERTORUPDATEONempBEGINIF(TO_CHAR(sysdate,’DY’)IN(’SAT’,’SUN’)OR(TO_CHAR(sysdate,’HH24’)NOTBETWEEN’8’AND’18’)THEN

IFDELETINGTHEN RAISE_APPLICATION_ERROR(-20502,’youmayonlydeleteempduringnormalhours.’);

ELSIFINSERTINGTHENRAISE_APPLICATION_ERROR(-20500,’youmayonlyinsertempduringnormalhours.’);ELSETHENRAISE_APPLICATION_ERROR(-20504,’youmayonlyupdateempduringnormalhours.’);ENDIF;ENDIF;END; 觸發(fā)器通過在CREATETRIGGER語句中指定FOREACHROW子句創(chuàng)建一個行觸發(fā)器CREATE[ORREPLACE]TRIGGER觸發(fā)器名{BEFORE|AFTER}事件1[OR事件2...]ON表名FOREACHROW[WHEN限制條件]PL/SQL塊；觸發(fā)器將每個用戶對數(shù)據(jù)庫emp表進行數(shù)據(jù)操縱（插入、更新、刪除）