培训-oracle dba-lesson信息工程学院

PL/SQL程序设信 软件工程PL/SQL程序设函函数、过程、包和触发PL/SQL程序设

PL/SQL块回PL/SQL块的结DECLAREDECLARE可选定义在PL/SQL块中要使用的对BEGIN必须执行语EXCEPTION可选错误处理语END必须PL/SQL程序设

PL/SQL块回块（以DECLARE或BEGIN开始）每次使用时都要进编译块不 在数据库中，并且它不能从其它的PLS块中进行调用。PL/SQL程序设过程、函数、包和触发过程、函数、包和触发器：都是带名块 过程、函数、包和触发器：可以从其它PL/SQL调用。PL/SQL

子程序：过程、函子程序是带名的PL/SQL块，能够接受参数和被环境调用。在中两类子程序：过程和函IS|EXCEPTION(可选

PL/SQL程序设子子程序：过程、函子程序说明（header）决定PL/SQL子程序的名参数列当子程序是函数时必须要有返回值(使用关键字IS或AS是必须的PL/SQL程序设

子程序：过程、函子程序体:是一个拥 、执行和异常处理部分PL/SQL块部分介于IS|AS和BEGIN之间。在块中必须使用DECLARE关键字显示的部分，而在子执行部分介于BEGIN和END关键字之间，这部分必须存在。异常处理部分介于EXCEPTION和END之间是可的部PL/SQL程序设语

创建过CREATE[ORREPLACE][(parameter1[mode1]datatype1,parameter2[mode2]datatype2,..PL/SQLORREPLACE关键 如果过程存在，首先删除它，然后用新版本建PL/SQL块以BEGIN开始或以局部变 开始，以END或procedure_name结束PL/SQLPL/SQL创创建过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程序设参数和模式

创建过v_NewFirstNamestudents.first_name%TYPE:= students.last_name%TYPEv_NewFirstNamestudents.first_name%TYPE:= students.last_name%TYPE:=students.major%TYPE:=PL/SQL程序设参数和模

创建过p_FirstName p_FirstName END形式参数（p_FirstName,p_LastName,p_Major）PL/SQLPL/SQL创创建过参数和模实际参数(Actualparameters):在调用过程中作为参数递给过程的变量或表达式就是实际参数v_NewFirstNamestudents.first_name%TYPE:= students.last_name%TYPE:=students.major%TYPE:=PL/SQLPL/SQL创创建过参数和模过程可以通过参数和调用环境进行数据传递有三种传递模式—IN、OUT和INOUT。如果没有为形参数指定模式，则使用缺省模式INPL/SQL程序设参数和模

创建过IN默认模必须说必须说实际参数变量必初始化实际参数必需是实际参数必需是量能够拥有缺省不能分配缺省不能分配缺省PL/SQL程序设参数和模

创建过对形式参数的约束：在过程 中，限制和参数的长度以及限制M参数的精度和/或刻度范围都是非法的。取得对形参的一法是TYP和WTYPE。CREATEORREPLACEPROCEDUREParameterLengthp_Parameter1INOUTVARCHAR2(10),p_Parameter2INOUTNUMBER(3,2))ASp_Parameter1:=p_Parameter2:=END/PL/SQLPL/SQL创创建过参数和模IN参数(p_idINSETsal=sal*WHEREempno=END执行：SQL>EXECUTEraise_salaryPL/SQLPL/SQL创建过参数创建过OUT参数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创创建过参数和模INOUT参数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创创建过参数和模INOUT参数执行:g_phone_no:= /SQL>PRINTSQL>EXECUTEformat_phone(:g_phone_no)SQL>PRINTg_phone_noPL/SQLPL/SQL创创建过参数和模参数的缺省值(p_nameINdept.dname%TYPEDEFAULTp_locINdept.loc%TYPEDEFAULT'NEWINSERTINTOdept(deptno,dname,ENDPL/SQLPL/SQL创创建过参数和模参数的缺省值执行add_deptadd_dept(p_loc=>'BOSTON',p_name=>add_dept(p_loc=>'CHICAGO')/SQL>SELECT*FROMPL/SQL程序设

创建过在子程序 子程CREATECREATEORREPLACEPROCEDURE(p_idINDELETEFROMempWHEREempno=ENDPROCEDUREINSERTINTOlog_table(code,ENDPL/SQL程序设

创建过在子程序 子程执行SQL>EXECSQL>EXECPL/SQL程序设

创建过 PL/SQL块中调用过v_idNUMBER:=7902;--PL/SQLPL/SQL创创建过从一个过程中调用另一个过CURSORemp_cursorISSELECTempnoFROMemp;FORemp_recINemp_cursorENDLOOP;ENDPL/SQLPL/SQL删删除过语DROPDROPPROCEDUREDROPDROPPROCEDUREPL/SQL程序设语

创建函CREATE[ORREPLACE]FUNCTIONfunction_name[(parameter1[mode1]datatype1,parameter2[mode2]..RETURNPL/SQLPL/SQL程序设说

创建函ORREPLACE关键字 –Parameter、mode和datatype的含义与过程相同,但是应该量避免使用OUT和INOUT参数模式u_datatpe是函数返回的数值的类型，不能对数据类型的尺寸进行限定。 开始，以END或ENDfunction_name结束。在PL/SQL块中，可以使用多个PL/SQLPL/SQL创创建函CREATECREATEORREPLACEFUNCTION(p_idINemp.empno%TYPE)RETURNNUMBERv_salaryemp.sal%TYPE:=0;SELECTsalINTOv_salaryFROMempWHEREempno=RETURNENDPL/SQLPL/SQL创创建函执行SQL>EXECUTE:g_salary:=SQL>PRINT通过get_salary.sql文件运行创建的函get_salary.sql文件内EXECUTE:g_salary:=PRINTPL/SQLPL/SQL创创建函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创创建函执行SQL>EXECUTE:g_classinfo:=ClassInfo('SQL>PRINTPL/SQLPL/SQL*创建函在SQLCREATECREATEORREPLACEFUNCTIONtax(p_valueINRETURNNUMBERRETURN(p_value*ENDPL/SQLPL/SQL*创建函在SQLSQL>SELECTSQL>SELECTempno,ename,sal,FROMWHEREdeptno=PL/SQLPL/SQL删删除函语DROPDROPFUNCTIONDROPDROPFUNCTIONPL/SQL程序设

创建包是一个可以将相关对象在一起的PL/SQL结构，是Oracl数据库的一种模式对象。包有两个独立的部分—包的规范和主体，这两部分独立的在数据字典中。包所包含的程序对象是过程、函数、变量、常数、游标和异常PL/SQL程序设

创建包比独立的过程和函数 的优点包使组织应用开发更有效包 更有效包允许修改包的对象而不需要重新编译从属的对象包允许ORACLE包可包含全局变量和游标包允许重载过程和函数PL/SQL程序设

创建建立一个包，要执行两个步骤用CREATEPACKAGE命令建立包规范。在包规范中说明程序对象，这些对象称为公共对象。公共对象可以在包外，也可以在包中的其它对象。用CREATEPACKAGEBODY命令建立包体，在包体中说明定义程序对象定义在包规范中说明的公共对象定义附加的包对象（私用对象），由于私用对象是在包中说明，它不能在包 PL/SQL程序设创建包的规语法

创建CREATECREATE[ORREPLACE]PACKAGEpackage_namepublictypeanditemdeclarationssubprogramspecificationsENDORREPLACEpublictypeanditemdeclarations subprogram 在包规范 的变量，默认的初始化值是NULLPL/SQLPL/SQL创创建创建包的规mNUMBER:=0;--initializedtoENDmmINM是一个全局变量，初始化值是0.10MPL/SQL程序设创建包语

创建CREATECREATE[ORREPLACE]PACKAGEBODYpackage_nameprivatetypeanditemdeclarationssubprogrambodiesENDORREPLACEprivatetypeanditem subprogrambodies：定义公有和私有的PL/SQLPL/SQLPL/SQL创创建创建包体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程序设

创建执行：在包的拥有者中调 执行：在不同的模式中调 执行： 数据库中调 PL/SQL程序设

创建_2_kiloCONSTANTNUMBER:=CONSTANTNUMBER:=yard_2_meterCONSTANTNUMBER:=0.9144;END执行_2_kilo||'SQL>EXECUTE_2_kilo||'PL/SQLPL/SQL删删除删除包的规范，语法删除包体，语法DROPDROPPACKAGEBODYPL/SQL程序设

包的重重载oveloading：是创建一个程序的多个版本。对对重载的限制 如果两个子程序的参数仅在名称和模式上不同的话，这两个过程能重载本能根据两个过程不同的返回类型对其进行重FUNCTION RETURNFUNCTION RETURN重载函数的参数的类族必须不同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程序设

创建触发触发器类似于过程和函数，都拥 、执行和异常处理过程的名PL/SQL块。与包类似，触发器必须在数据库中我们面已经讲过，过程是显式地通过过程调用执行的，同时过程调用可以传递参数。与之相反，触发器是在事件发生时隐式地运行的，并且触发器不能接受参数。运行触发器的方式叫做激发(firing)触发器触发事件可以是对数据库表的DML（INSET、PDATE、DELET）操作或某种视图的操作。Oracl8i把触发器功能扩展到了可以激发系统事件，如数据库的启动和关闭，以及某种DDL操作。触发器主要分为：DML触发器、替代触发器和系统触发器。本次课程主要讲述DML触发器。触发器可用来补 的参照完整性，强制实施复杂的业务规则PL/SQL程序设

创建DML触发DML触发器由DML(INSERT、UPDATE、DELETE)语句激发创建DML触发器语法CREATE[ORREPLACE]TRIGGER{BEFORE|AFTER}triggering_eventON[FOREACHROW[WHENtrigger_name是触发器名，triggering_event指定了何时激发发器table_reference是定义触发器的表，PL/SQLPL/SQL创建DML触发DML触发器类型:触发器事件决定了触发器类型类取说语INSERT、UPDATE定义那种DML语句会激发触发定BEFORE或定义在语句执后激发触发级行或语的FOREACHROW子句标识。PL/SQLPL/SQL创建DML触发触发器的激发顺执行BEFORE语句级触发器（如果有的话）对于受语句影响的每一行执行BRFORE行级触发器（如果有的话）执行DML语执行AFTER行级触发器（如果有的话）执行AFTER语句级触发器（如果有的话）PL/SQL程序设

创建DML触发创建语句级触发器：CREATEORREPLACETRIGGERBEFOREINSERTONempIFTO_CHAR(SYSDATE,'DYIN星期六星期天OR(TO_CHAR(SYSDATE,'HH24:MI')NOTBETWEEN'08:00'AND'18:00')RAISE_APPLICATION_ERROR(-20500,'YoumayinsertintoEMPtableonlyduringbusinessENDPL/SQLPL/SQL创建DML触发创建语句级触发器：测SQL>INSERTSQL>INSERTINTOemp(empno,ename,hiredate,job,sal,PL/SQL程序设

创建DML触发创建语句级触发器，使用触发谓语动词：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创建DML触发创建行级触发器：CREATECREATEORREPLACETRIGGERBEFOREINSERTORUPDATEONFOREACHIFNOT(:NEW.jobIN('AD_PRES','AD_VP'))AND:NEW.sal>RAISE_APPLICATION_ERROR(-20202,'EmployeecannotearnthisENDPL/SQLPL/SQL创建DML触发创建行级触发器：测SQL>SQL>UPDATESETsal=WHEREename=PL/SQL程序设

创建DML触发在行级触发器中使用:old和 触发语句所处理的每一行都激发一次行级触发器。在触发器，你可 当前正在被处理的行。这是通过“伪记录”——和:new实现的。它们的类型是触发语无定义—所有字段都是该语句结束时将的更新前行的原始数该语句结束时行删除前的原始数无定义—所有字段都是PL/SQLPL/SQL创建DML触发在行级触发器中使用:old和CREATEORREPLACETRIGGERaudit_emp_valuesAFTERDELETEORINSERTORUPDATEONempFOREACHROWINSERTINTOaudit_emp_table(user_name,timestamp,empno,old_ename,new_ename,old_job,new_job,PL/SQLPL/SQL创建DML触发在行级触发器中使用:old和CREATECREATEORREPLACETRIGGERBEFOREINSERTORUPDATEONFOREACHSELECTINTOFROMENDPL/SQLPL/SQL创建DML触发在行级触发器中使用:old和执SQL>INSERTSQL>INSERTINTOstudents(first_name,SQL>INSERTINTOstudents(ID,first_name,VALUES(-7,'Zelda',PL/SQLPL/SQL创建DML触发在行级触发器中使用限制条BEFOREINSERTORUPDATEOFsalONFOREACHIFINSERTINGm:=ENDIF;mISNULL m:=m:=(NEW.job=PL/SQLPL/SQL程序设••*创建替代触发CREATE[ORREPLACE]TRIGGERINSTEADevent1[ORevent2ORON[REFERENCINGOLDASold|NEWASnew][FOREACHROW]PL/SQLPL/SQL

*创建替代触发SELECTempno,ename,sal,deptno,job,hiredateFROMSELECTd.deptno,d.dname,d.loc,sum(e