培訓(xùn)-oracle dba-lesson信息工程學(xué)院

PL/SQL程序設(shè)信 軟件工程PL/SQL程序設(shè)函函數(shù)、過程、包和觸發(fā)PL/SQL程序設(shè)

PL/SQL塊回PL/SQL塊的結(jié)DECLAREDECLARE可選定義在PL/SQL塊中要使用的對(duì)BEGIN必須執(zhí)行語EXCEPTION可選錯(cuò)誤處理語END必須PL/SQL程序設(shè)

PL/SQL塊回塊（以DECLARE或BEGIN開始）每次使用時(shí)都要進(jìn)編譯塊不 在數(shù)據(jù)庫(kù)中，并且它不能從其它的PLS塊中進(jìn)行調(diào)用。PL/SQL程序設(shè)過程、函數(shù)、包和觸發(fā)過程、函數(shù)、包和觸發(fā)器：都是帶名塊 過程、函數(shù)、包和觸發(fā)器：可以從其它PL/SQL調(diào)用。PL/SQL

子程序：過程、函子程序是帶名的PL/SQL塊，能夠接受參數(shù)和被環(huán)境調(diào)用。在中兩類子程序：過程和函IS|EXCEPTION(可選

PL/SQL程序設(shè)子子程序：過程、函子程序說明（header）決定PL/SQL子程序的名參數(shù)列當(dāng)子程序是函數(shù)時(shí)必須要有返回值(使用關(guān)鍵字IS或AS是必須的PL/SQL程序設(shè)

子程序：過程、函子程序體:是一個(gè)擁 、執(zhí)行和異常處理部分PL/SQL塊部分介于IS|AS和BEGIN之間。在塊中必須使用DECLARE關(guān)鍵字顯示的部分，而在子執(zhí)行部分介于BEGIN和END關(guān)鍵字之間，這部分必須存在。異常處理部分介于EXCEPTION和END之間是可的部PL/SQL程序設(shè)語

創(chuàng)建過CREATE[ORREPLACE][(parameter1[mode1]datatype1,parameter2[mode2]datatype2,..PL/SQLORREPLACE關(guān)鍵 如果過程存在，首先刪除它，然后用新版本建PL/SQL塊以BEGIN開始或以局部變 開始，以END或procedure_name結(jié)束PL/SQLPL/SQL創(chuàng)創(chuàng)建過CREATECREATEORREPLACEPROCEDUREAddNewStudentp_FirstNamestudents.first_name%TYPE, INSERTINTOstudents(ID,first_name,last_name,major,current_credits)VALUES(student_sequence.nextval,p_FirstName,p_LastName,p_Major,0);ENDPL/SQL程序設(shè)參數(shù)和模式

創(chuàng)建過v_NewFirstNamestudents.first_name%TYPE:= students.last_name%TYPEv_NewFirstNamestudents.first_name%TYPE:= students.last_name%TYPE:=students.major%TYPE:=PL/SQL程序設(shè)參數(shù)和模

創(chuàng)建過p_FirstName p_FirstName END形式參數(shù)（p_FirstName,p_LastName,p_Major）PL/SQLPL/SQL創(chuàng)創(chuàng)建過參數(shù)和模實(shí)際參數(shù)(Actualparameters):在調(diào)用過程中作為參數(shù)遞給過程的變量或表達(dá)式就是實(shí)際參數(shù)v_NewFirstNamestudents.first_name%TYPE:= students.last_name%TYPE:=students.major%TYPE:=PL/SQLPL/SQL創(chuàng)創(chuàng)建過參數(shù)和模過程可以通過參數(shù)和調(diào)用環(huán)境進(jìn)行數(shù)據(jù)傳遞有三種傳遞模式—IN、OUT和INOUT。如果沒有為形參數(shù)指定模式，則使用缺省模式INPL/SQL程序設(shè)參數(shù)和模

創(chuàng)建過IN默認(rèn)模必須說必須說實(shí)際參數(shù)變量必初始化實(shí)際參數(shù)必需是實(shí)際參數(shù)必需是量能夠擁有缺省不能分配缺省不能分配缺省PL/SQL程序設(shè)參數(shù)和模

創(chuàng)建過對(duì)形式參數(shù)的約束：在過程 中，限制和參數(shù)的長(zhǎng)度以及限制M參數(shù)的精度和/或刻度范圍都是非法的。取得對(duì)形參的一法是TYP和WTYPE。CREATEORREPLACEPROCEDUREParameterLengthp_Parameter1INOUTVARCHAR2(10),p_Parameter2INOUTNUMBER(3,2))ASp_Parameter1:=p_Parameter2:=END/PL/SQLPL/SQL創(chuàng)創(chuàng)建過參數(shù)和模IN參數(shù)(p_idINSETsal=sal*WHEREempno=END執(zhí)行：SQL>EXECUTEraise_salaryPL/SQLPL/SQL創(chuàng)建過參數(shù)創(chuàng)建過OUT參數(shù)CREATECREATEORREPLACEPROCEDURE(p_idINemp.empno%TYPE,p_nameOUTp_salaryOUTmSELECTename,sal,INTOp_name, FROMempWHEREempno=ENDSQL>VARIABLEg_sal mSQL>EXECUTEquery_emp(7902,:g_name,:g_sal, SQL>PRINTg_name PL/SQLPL/SQL創(chuàng)創(chuàng)建過參數(shù)和模INOUT參數(shù)CREATECREATEORREPLACEPROCEDUREformat_phone(p_phone_noINOUTVARCHAR2)p_phone_no:='('||SUBSTR(p_phone_no,1,3)||')'||SUBSTR(p_phone_no,4,3)||'-'||SUBSTR(p_phone_no,7);ENDPL/SQLPL/SQL創(chuàng)創(chuàng)建過參數(shù)和模INOUT參數(shù)執(zhí)行:g_phone_no:= /SQL>PRINTSQL>EXECUTEformat_phone(:g_phone_no)SQL>PRINTg_phone_noPL/SQLPL/SQL創(chuàng)創(chuàng)建過參數(shù)和模參數(shù)的缺省值(p_nameINdept.dname%TYPEDEFAULTp_locINdept.loc%TYPEDEFAULT'NEWINSERTINTOdept(deptno,dname,ENDPL/SQLPL/SQL創(chuàng)創(chuàng)建過參數(shù)和模參數(shù)的缺省值執(zhí)行add_deptadd_dept(p_loc=>'BOSTON',p_name=>add_dept(p_loc=>'CHICAGO')/SQL>SELECT*FROMPL/SQL程序設(shè)

創(chuàng)建過在子程序 子程CREATECREATEORREPLACEPROCEDURE(p_idINDELETEFROMempWHEREempno=ENDPROCEDUREINSERTINTOlog_table(code,ENDPL/SQL程序設(shè)

創(chuàng)建過在子程序 子程執(zhí)行SQL>EXECSQL>EXECPL/SQL程序設(shè)

創(chuàng)建過 PL/SQL塊中調(diào)用過v_idNUMBER:=7902;--PL/SQLPL/SQL創(chuàng)創(chuàng)建過從一個(gè)過程中調(diào)用另一個(gè)過CURSORemp_cursorISSELECTempnoFROMemp;FORemp_recINemp_cursorENDLOOP;ENDPL/SQLPL/SQL刪刪除過語DROPDROPPROCEDUREDROPDROPPROCEDUREPL/SQL程序設(shè)語

創(chuàng)建函CREATE[ORREPLACE]FUNCTIONfunction_name[(parameter1[mode1]datatype1,parameter2[mode2]..RETURNPL/SQLPL/SQL程序設(shè)說

創(chuàng)建函ORREPLACE關(guān)鍵字 –Parameter、mode和datatype的含義與過程相同,但是應(yīng)該量避免使用OUT和INOUT參數(shù)模式u_datatpe是函數(shù)返回的數(shù)值的類型，不能對(duì)數(shù)據(jù)類型的尺寸進(jìn)行限定。 開始，以END或ENDfunction_name結(jié)束。在PL/SQL塊中，可以使用多個(gè)PL/SQLPL/SQL創(chuàng)創(chuàng)建函CREATECREATEORREPLACEFUNCTION(p_idINemp.empno%TYPE)RETURNNUMBERv_salaryemp.sal%TYPE:=0;SELECTsalINTOv_salaryFROMempWHEREempno=RETURNENDPL/SQLPL/SQL創(chuàng)創(chuàng)建函執(zhí)行SQL>EXECUTE:g_salary:=SQL>PRINT通過get_salary.sql文件運(yùn)行創(chuàng)建的函get_salary.sql文件內(nèi)EXECUTE:g_salary:=PRINTPL/SQLPL/SQL創(chuàng)創(chuàng)建函CREATEORREPLACEFUNCTIONClassInfov_CurrentStudentsNUMBER;v_MaxStudentsNUMBER;v_PercentFullNUMBER;SELECTcurrent_students,max_studentsINTOv_CurrentStudents,v_MaxStudentsFROMclassesWHEREdepartment=p_DepartmentANDcourse=p_Course;v_PercentFull:=v_CurrentStudents/v_MaxStudents*IFv_PercentFull=100 RETURNELSIFv_PercentFull>80THENRETURN'SomeRoom';ELSIFv_PercentFull>60THENRETURN'MoreRoom';ELSIFv_PercentFull>0THEN RETURN'LotsofRoom';ELSERETURN'Empty';ENDENDPL/SQLPL/SQL創(chuàng)創(chuàng)建函執(zhí)行SQL>EXECUTE:g_classinfo:=ClassInfo('SQL>PRINTPL/SQLPL/SQL*創(chuàng)建函在SQLCREATECREATEORREPLACEFUNCTIONtax(p_valueINRETURNNUMBERRETURN(p_value*ENDPL/SQLPL/SQL*創(chuàng)建函在SQLSQL>SELECTSQL>SELECTempno,ename,sal,FROMWHEREdeptno=PL/SQLPL/SQL刪刪除函語DROPDROPFUNCTIONDROPDROPFUNCTIONPL/SQL程序設(shè)

創(chuàng)建包是一個(gè)可以將相關(guān)對(duì)象在一起的PL/SQL結(jié)構(gòu)，是Oracl數(shù)據(jù)庫(kù)的一種模式對(duì)象。包有兩個(gè)獨(dú)立的部分—包的規(guī)范和主體，這兩部分獨(dú)立的在數(shù)據(jù)字典中。包所包含的程序?qū)ο笫沁^程、函數(shù)、變量、常數(shù)、游標(biāo)和異常PL/SQL程序設(shè)

創(chuàng)建包比獨(dú)立的過程和函數(shù) 的優(yōu)點(diǎn)包使組織應(yīng)用開發(fā)更有效包 更有效包允許修改包的對(duì)象而不需要重新編譯從屬的對(duì)象包允許ORACLE包可包含全局變量和游標(biāo)包允許重載過程和函數(shù)PL/SQL程序設(shè)

創(chuàng)建建立一個(gè)包，要執(zhí)行兩個(gè)步驟用CREATEPACKAGE命令建立包規(guī)范。在包規(guī)范中說明程序?qū)ο?，這些對(duì)象稱為公共對(duì)象。公共對(duì)象可以在包外，也可以在包中的其它對(duì)象。用CREATEPACKAGEBODY命令建立包體，在包體中說明定義程序?qū)ο蠖x在包規(guī)范中說明的公共對(duì)象定義附加的包對(duì)象（私用對(duì)象），由于私用對(duì)象是在包中說明，它不能在包 PL/SQL程序設(shè)創(chuàng)建包的規(guī)語法

創(chuàng)建CREATECREATE[ORREPLACE]PACKAGEpackage_namepublictypeanditemdeclarationssubprogramspecificationsENDORREPLACEpublictypeanditemdeclarations subprogram 在包規(guī)范 的變量，默認(rèn)的初始化值是NULLPL/SQLPL/SQL創(chuàng)創(chuàng)建創(chuàng)建包的規(guī)mNUMBER:=0;--initializedtoENDmmINM是一個(gè)全局變量，初始化值是0.10MPL/SQL程序設(shè)創(chuàng)建包語

創(chuàng)建CREATECREATE[ORREPLACE]PACKAGEBODYpackage_nameprivatetypeanditemdeclarationssubprogrambodiesENDORREPLACEprivatetypeanditem subprogrambodies：定義公有和私有的PL/SQLPL/SQLPL/SQL創(chuàng)創(chuàng)建創(chuàng)建包體CREATEORREPLACEPACKAGEBODYcomm_packageIS m( mINNUMBER)RETURNBOOLEANmSELECTMAX(comm)INTO mFROMemp; m> mTHENRETURN(FALSE);ELSEENDIF; m( mINNUMBER) m)THEN m;--resetglobalvariableELSERAISE_APPLICATION_ERROR(-20210,'Invalidcommission');ENDIF; ENDPL/SQL程序設(shè)

創(chuàng)建執(zhí)行：在包的擁有者中調(diào) 執(zhí)行：在不同的模式中調(diào) 執(zhí)行： 數(shù)據(jù)庫(kù)中調(diào) PL/SQL程序設(shè)

創(chuàng)建_2_kiloCONSTANTNUMBER:=CONSTANTNUMBER:=yard_2_meterCONSTANTNUMBER:=0.9144;END執(zhí)行_2_kilo||'SQL>EXECUTE_2_kilo||'PL/SQLPL/SQL刪刪除刪除包的規(guī)范，語法刪除包體，語法DROPDROPPACKAGEBODYPL/SQL程序設(shè)

包的重重載oveloading：是創(chuàng)建一個(gè)程序的多個(gè)版本。對(duì)對(duì)重載的限制 如果兩個(gè)子程序的參數(shù)僅在名稱和模式上不同的話，這兩個(gè)過程能重載本能根據(jù)兩個(gè)過程不同的返回類型對(duì)其進(jìn)行重FUNCTION RETURNFUNCTION RETURN重載函數(shù)的參數(shù)的類族必須不同PROCEDUREoverloadChar(p_TheParameterINCHAR);PROCEDUREoverloadChar(p_TheParameterINVARCHAR2);PL/SQLPL/SQL包包的重重載(overloading)：(p_deptnoINp_nameINdepartments.department_name%TYPEDEFAULT(p_nameINdepartments.department_name%TYPEDEFAULTENDPL/SQLPL/SQL包包的重重載(overloading)：CREATEORREPLACEPACKAGEBODYover_pack (p_deptnoINp_locINdepartments.location_id%TYPEDEFAULTINSERTINTOdepartments(department_id,department_name,VALUES(p_deptno,p_name,p_loc);ENDadd_dept; (p_nameINdepartments.department_name%TYPEDEFAULTp_locINdepartments.location_id%TYPEDEFAULTINSERTINTOdepartments(department_id,department_name,location_id)VALUES(departments_seq.NEXTVAL,p_name,p_loc);ENDadd_dept;ENDover_pack;PL/SQL程序設(shè)

創(chuàng)建觸發(fā)觸發(fā)器類似于過程和函數(shù)，都擁 、執(zhí)行和異常處理過程的名PL/SQL塊。與包類似，觸發(fā)器必須在數(shù)據(jù)庫(kù)中我們面已經(jīng)講過，過程是顯式地通過過程調(diào)用執(zhí)行的，同時(shí)過程調(diào)用可以傳遞參數(shù)。與之相反，觸發(fā)器是在事件發(fā)生時(shí)隱式地運(yùn)行的，并且觸發(fā)器不能接受參數(shù)。運(yùn)行觸發(fā)器的方式叫做激發(fā)(firing)觸發(fā)器觸發(fā)事件可以是對(duì)數(shù)據(jù)庫(kù)表的DML（INSET、PDATE、DELET）操作或某種視圖的操作。Oracl8i把觸發(fā)器功能擴(kuò)展到了可以激發(fā)系統(tǒng)事件，如數(shù)據(jù)庫(kù)的啟動(dòng)和關(guān)閉，以及某種DDL操作。觸發(fā)器主要分為：DML觸發(fā)器、替代觸發(fā)器和系統(tǒng)觸發(fā)器。本次課程主要講述DML觸發(fā)器。觸發(fā)器可用來補(bǔ) 的參照完整性，強(qiáng)制實(shí)施復(fù)雜的業(yè)務(wù)規(guī)則PL/SQL程序設(shè)

創(chuàng)建DML觸發(fā)DML觸發(fā)器由DML(INSERT、UPDATE、DELETE)語句激發(fā)創(chuàng)建DML觸發(fā)器語法CREATE[ORREPLACE]TRIGGER{BEFORE|AFTER}triggering_eventON[FOREACHROW[WHENtrigger_name是觸發(fā)器名，triggering_event指定了何時(shí)激發(fā)發(fā)器table_reference是定義觸發(fā)器的表，PL/SQLPL/SQL創(chuàng)建DML觸發(fā)DML觸發(fā)器類型:觸發(fā)器事件決定了觸發(fā)器類型類取說語INSERT、UPDATE定義那種DML語句會(huì)激發(fā)觸發(fā)定BEFORE或定義在語句執(zhí)后激發(fā)觸發(fā)級(jí)行或語的FOREACHROW子句標(biāo)識(shí)。PL/SQLPL/SQL創(chuàng)建DML觸發(fā)觸發(fā)器的激發(fā)順執(zhí)行BEFORE語句級(jí)觸發(fā)器（如果有的話）對(duì)于受語句影響的每一行執(zhí)行BRFORE行級(jí)觸發(fā)器（如果有的話）執(zhí)行DML語執(zhí)行AFTER行級(jí)觸發(fā)器（如果有的話）執(zhí)行AFTER語句級(jí)觸發(fā)器（如果有的話）PL/SQL程序設(shè)

創(chuàng)建DML觸發(fā)創(chuàng)建語句級(jí)觸發(fā)器：CREATEORREPLACETRIGGERBEFOREINSERTONempIFTO_CHAR(SYSDATE,'DYIN星期六星期天OR(TO_CHAR(SYSDATE,'HH24:MI')NOTBETWEEN'08:00'AND'18:00')RAISE_APPLICATION_ERROR(-20500,'YoumayinsertintoEMPtableonlyduringbusinessENDPL/SQLPL/SQL創(chuàng)建DML觸發(fā)創(chuàng)建語句級(jí)觸發(fā)器：測(cè)SQL>INSERTSQL>INSERTINTOemp(empno,ename,hiredate,job,sal,PL/SQL程序設(shè)

創(chuàng)建DML觸發(fā)創(chuàng)建語句級(jí)觸發(fā)器，使用觸發(fā)謂語動(dòng)詞：CREATEORREPLACETRIGGERsecure_empBEFOREINSERTORUPDATEORDELETEONEMPIF(TO_CHAR(SYSDATE'DYIN星期六星期天OR(TO_CHAR(SYSDATE,'HH24')NOTBETWEEN'08'AND RAISE_APPLICATION_ERROR(-20502,'YoumaydeletefromEMPtableonlyduringbusiness RAISE_APPLICATION_ERROR(-20500,'YoumayinsertintoEMPtableonlyduringbusinessELSIFUPDATING('SAL')RAISE_APPLICATION_ERROR(-20503,'YoumayupdateSALonlyduringbusinessRAISE_APPLICATION_ERROR(-20504,'YoumayupdateEMPtableonlyduringnormalhours.');ENDIF;ENDIF;PL/SQLPL/SQL創(chuàng)建DML觸發(fā)創(chuàng)建行級(jí)觸發(fā)器：CREATECREATEORREPLACETRIGGERBEFOREINSERTORUPDATEONFOREACHIFNOT(:NEW.jobIN('AD_PRES','AD_VP'))AND:NEW.sal>RAISE_APPLICATION_ERROR(-20202,'EmployeecannotearnthisENDPL/SQLPL/SQL創(chuàng)建DML觸發(fā)創(chuàng)建行級(jí)觸發(fā)器：測(cè)SQL>SQL>UPDATESETsal=WHEREename=PL/SQL程序設(shè)

創(chuàng)建DML觸發(fā)在行級(jí)觸發(fā)器中使用:old和 觸發(fā)語句所處理的每一行都激發(fā)一次行級(jí)觸發(fā)器。在觸發(fā)器，你可 當(dāng)前正在被處理的行。這是通過“偽記錄”——和:new實(shí)現(xiàn)的。它們的類型是觸發(fā)語無定義—所有字段都是該語句結(jié)束時(shí)將的更新前行的原始數(shù)該語句結(jié)束時(shí)行刪除前的原始數(shù)無定義—所有字段都是PL/SQLPL/SQL創(chuàng)建DML觸發(fā)在行級(jí)觸發(fā)器中使用:old和CREATEORREPLACETRIGGERaudit_emp_valuesAFTERDELETEORINSERTORUPDATEONempFOREACHROWINSERTINTOaudit_emp_table(user_name,timestamp,empno,old_ename,new_ename,old_job,new_job,PL/SQLPL/SQL創(chuàng)建DML觸發(fā)在行級(jí)觸發(fā)器中使用:old和CREATECREATEORREPLACETRIGGERBEFOREINSERTORUPDATEONFOREACHSELECTINTOFROMENDPL/SQLPL/SQL創(chuàng)建DML觸發(fā)在行級(jí)觸發(fā)器中使用:old和執(zhí)SQL>INSERTSQL>INSERTINTOstudents(first_name,SQL>INSERTINTOstudents(ID,first_name,VALUES(-7,'Zelda',PL/SQLPL/SQL創(chuàng)建DML觸發(fā)在行級(jí)觸發(fā)器中使用限制條BEFOREINSERTORUPDATEOFsalONFOREACHIFINSERTINGm:=ENDIF;mISNULL m:=m:=(NEW.job=PL/SQLPL/SQL程序設(shè)??*創(chuàng)建替代觸發(fā)CREATE[ORREPLACE]TRIGGERINSTEADevent1[ORevent2ORON[REFERENCINGOLDASold|NEWASnew][FOREACHROW]PL/SQLPL/SQL

*創(chuàng)建替代觸發(fā)SELECTempno,ename,sal,deptno,job,hiredateFROMSELECTd.deptno,d.dname,d.loc,sum(e