oracle 第15章 PLSQL程序设计ppt课件

.,1,第15章PL/SQL程序设计,.,2,本章内容,PL/SQL概述PL/SQL基础控制结构游标异常处理存储子程序包触发器,.,3,本章要求,掌握PL/SQL程序设计基础知识掌握存储过程、函数、包、触发器的应用,.,4,15.1PL/SQL概述,PL/SQL特点PL/SQL功能特性PL/SQL执行过程与开发工具,.,5,15.1.1PL/SQL特点,与SQL语言紧密集成。减小网络流量，提高应用程序的运行性能。模块化的程序设计功能，提高了系统可靠性。服务器端程序设计，可移植性好。,.,6,15.1.2PL/SQL功能特性,语句块结构异常处理变量和类型条件语句循环结构游标过程、函数和触发器包集合动态SQL对象特性,.,7,15.1.3PL/SQL执行过程与开发工具,PL/SQL块,SQL语句,客户端应用程序,PL/SQL引擎,数据库服务器,过程化语句执行器,SQL执行器,块中SQL语句,PL/SQL执行过程,.,8,PL/SQL开发工具SQL*PLUSProcedureBuilderOracleForm、OracleReportsPL/SQLDeveloper,.,9,15.2PL/SQL基础,PL/SQL程序结构词法单元数据类型变量与常量编译指示PL/SQL中的SQL语句,.,10,PL/SQL程序结构,PL/SQL块的组成PL/SQL块分类,.,11,PL/SQL块的组成PL/SQL语言以块为单位，块中可以嵌套子块。一个基本的PL/SQL块由3部分组成：声明（DECLARE），可执行部分（BEGIN），异常处理部分EXCEPTION）。,.,12,声明部分声明部分以关键字DECLARE开始，BEGIN结束。主要用于声明变量、常量、数据类型、游标、异常处理名称以及本地（局部）子程序定义等。可执行部分执行部分是PL/SQL块的功能实现部分，以关键字BEGIN开始，EXCEPTION或END结束（如果PL/SQL块中没有异常处理部分，则以END结束）。该部分通过变量赋值、流程控制、数据查询、数据操纵、数据定义、事务控制、游标处理等实现块的功能。异常处理部分异常处理部分以关键字EXCEPTION开始，END结束。该部分用于处理该块执行过程中产生的异常。,.,13,注意：执行部分是必需的，而声明部分和异常部分是可选的；可以在一个块的执行部分或异常处理部分嵌套其他的PL/SQL块；所有的PL/SQL块都是以“END;”结束,.,14,PL/SQL块分类匿名块命名块函数存储过程包触发器,.,15,15.5.2词法单元,字符集标识符分隔符常量值注释,.,16,字符集大小写字母：AZ，az数字：09空白：制表符、空格和回车数字符号：+-*/=标点符号：!#$%:.“.|=,*-,分隔符,.,19,常量值字符型常量数字型常量布尔型常量：TURE、FALSE、NULL日期型常量,.,20,15.2.3数据类型,数字类型字符类型日期/区间类型行标识类型布尔类型原始类型LOB类型记录类型集合类型,.,21,PL/SQL中常用的基本数据类型,.,22,记录类型的定义TYPErecord_typeISRECORD(field1datatype1NOTNULLDEFAULT|:=expr1,field2datatype2NOTNULLDEFAULT|:=expr2,fieldndatatypenNOTNULLDEFAULT|:=exprn);,.,23,15.2.4变量与常量,变量与常量的定义变量的作用域,.,24,变量声明,变量与常量的定义,变量定义的一般格式：CONSTANTNOTNULLDEFAULT|:=;说明每行只能定义一个标识符。如果加上关键字CONSTANT,则表示所定义的标识符为一个常量，必须为它赋初值。如果定义的标识符不能为空，则必须加上关键字NOTNULL,并赋初值。为标识符赋值时，使用赋值符号：=，默认值为空。,.,25,DECLAREv1NUMBER(4);v2NUMBER(4)NOTNULL:=10;v3CONSTANTNUMBER(4)DEFAULT100;BEGINIFv1ISNULLTHENDBMS_OUTPUT.PUT_LINE(V1ISNULL!);ENDIF;DBMS_OUTPUT.PUT_LINE(v2|v3);END;,.,26,声明一个变量，使它的类型与某个变量或数据库基本表中某个列的数据类型一致，可以使用%TYPE。示例v_empno1emp.empno%TYPE;v_empno2v_empno1%TYPE;,.,27,变量的作用域,变量的作用域是指变量的有效作用范围，从变量声明开始，直到块结束。如果PL/SQL块相互嵌套，则在内部块中声明的变量是局部的，只能在内部块中引用，而在外部块中声明的变量是全局的，既可以在外部块中引用，也可以在内部块中引用。如果内部块与外部块中定义了同名变量，则在内部块中引用外部块的全局变量时需要使用外部块名进行标识。,.,28,DECLAREv_enameCHAR(15);v_outerNUMBER(5);BEGINv_outer:=10;DECLAREv_enameCHAR(20);v_innerDATE;BEGINv_inner:=sysdate;v_ename:=INNERV_ENAME;OUTER.v_ename:=OUTERV_ENAME;END;DBMS_OUTPUT.PUT_LINE(v_ename);END;,.,29,15.2.5编译指示,编译指示是对编译程序发出的特殊指令，也称伪指令。关键字：PRAGMAPL/SQL提供以下四种编译指示：EXCEPTION_INIT告诉编译程序将一个特定的错误号与程序中所声明的异常标识符关联起来。RESTRICT_REFERENCES告诉编译程序打包程序的纯度，即对函数中可以使用的SQL语句和包变量进行限制。,.,30,SERIALLY_REUSEABLE告诉PL/SQL运行时引擎，在数据引用之间不要保持包级数据。AUTONOMOUS_TRANSACTION告诉编译程序，该程序块为自治事务，即该事务的提交和回滚是独立进行的。,.,31,15.2.6PL/SQL中SQL语句,可以在PL/SQL中执行的SQL语句包括SELECTDML(UPDATE、DELETE、INSERT)事务控制语句（COMMIT、ROLLBACK、SAVEPOINT）注意DDL语句不可以直接使用,.,32,SELECTINTOSELECTINTO语句只能查询一个记录的信息，如果没有查询到任何数据，则会产生NO_DATA_FOUND异常；如果查询到多个记录，则会产生TOO_MANY_ROW异常。INTO句子后的变量用于接收查询的结果，变量的个数、顺序应该与查询的目标数据相匹配，也可以是记录类型的变量。DML语句,.,33,DECLAREv_empemp%ROWTYPE;v_enameemp.ename%type;v_salemp.sal%type;BEGINSELECT*INTOv_empFROMempWHEREename=SMITH;DBMS_OUTPUT.PUT_LINE(v_emp.empno|v_emp.sal);selectename,salINTOv_ename,v_salFROMempWHEREempno=7900;DBMS_OUTPUT.PUT_LINE(v_ename|v_sal);END;,.,34,DML语句PL/SQL中DML语句对标准SQL语句中的DML语句进行了扩展，允许使用变量。示例DECLAREv_empnoemp.empno%TYPE:=7500;BEGININSERTINTOemp(empno,ename,sal,deptno)VALUES(v_empno,JOAN,2300,20);UPDATEempSETsal=sal+100WHEREempno=v_empno;DELETEFROMempWHEREempno=v_empno;END;,.,35,WHERE标识符的区分系统首先查看WHERE子句中的标识符是否与表中的列名相同，如果相同，则该标识符被解释为列名；如果没有同名列，系统检查该标识符是不是PL/SQL语句块的变量。字符串比较填充比较：通过在短字符串后添加空格，使两个字符串达到相同长度，然后根据每个字符的ASCII码进行比较。非填充比较：根据每个字符的ASCII码进行比较，最先结束的字符串为小。PL/SQL中规定，对定长的字符串（CHAR类型的字符串和字符串常量）采用填充比较；如果比较的字符串中有一个是变长字符串（VARCHAR2类型的字符串），则采用非填充比较。,.,36,RETURNING如果要查询当前DML语句操作的记录的信息，可以在DML语句末尾使用RETURNING语句返回该记录的信息。RETURNING语句的基本语法：RETURNINGselect_list_itemINTOvariable_list|record_variable;,.,37,DECLAREv_salemp.sal%TYPE;BEGINUPDATEempSETsal=sal+100WHEREempno=7844RETURNINGsalINTOv_sal;DBMS_OUTPUT.PUT_LINE(v_sal);END;,.,38,15.3控制结构,选择结构循环结构跳转结构,.,39,15.3.1选择结构,IF语句IFcondition1THENstatements1;ELSIFcondition2THENstatements2;ELSEelse_statements;ENDIF;注意条件是一个布尔型变量或表达式，取值只能是TRUE，FALSE，NULL。,.,40,例如，输入一个员工号，修改该员工的工资，如果该员工为10号部门，工资增加100；若为20号部门，工资增加150；若为30号部门，工资增加200；否则增加300。,.,41,DECLAREv_deptnoemp.deptno%type;v_incrementNUMBER(4);v_empnoemp.empno%type;BEGINv_empno:=,.,42,搜索式CASE语句,基本语法CASEWHENcondition1THENstatements1;WHENcondition2THENstatements2;WHENconditionnTHENstatementsn;ELSEelse_statements;ENDCASE;,.,43,等值比较的CASE语句,基本语法CASEtest_valueWHENvalue1THENstatements1;WHENvalue2THENstatements2;WHENvaluenTHENstatementsn;ELSEelse_statements;ENDCASE;,.,44,DECLAREv_deptnoemp.deptno%type;v_incrementNUMBER(4);v_empnoemp.empno%type;BEGINv_empno:=,.,45,根据输入的员工号，修改该员工工资。如果该员工工资低于1000，则工资增加200；如果工资在1000-2000之间，则增加150；如果工资在2000-3000之间，则增加100；否则增加50。,.,46,DECLAREv_salemp.sal%type;v_incrementNUMBER(4);v_empnoemp.empno%type;BEGINv_empno:=,.,47,15.3.2循环结构,简单循环WHILE循环FOR循环,.,48,简单循环,语法LOOPsequence_of_statement;EXITWHENcondition;ENDLOOP;注意：在循环体中一定要包含EXIT语句，否则程序进入死循环。,.,49,例如，执行CREATETABLEtemp_table(num_colNUMBER,info_colCHAR(10)语句创建temp_table表，然后利用循环向temp_table表中插入50条记录。程序为：DECLAREv_counterBINARY_INTEGER:=1;BEGINLOOPINSERTINTOtemp_tableVALUES(v_Counter,Loopindex);v_counter:=v_counter+1;EXITWHENv_counter50;ENDLOOP;END;,.,50,WHILE循环,基本语法WHILEconditionLOOPsequence_of_statement;ENDLOOP;,.,51,例如，利用WHILE循环向temp_table表中插入50条记录。程序为：DECLAREv_counterBINARY_INTEGER:=1;BEGINWHILEv_counterINSERTINTOtemp_tableVALUES(v_counter,Loopindex);v_counter:=v_Counter+1;IFv_counter6000THENRAISEe_highlimit;ENDIF;EXCEPTIONWHENe_highlimitTHENDBMS_OUTPUT.PUT_LINE(Thesalaryistoolarge!);ROLLBACK;END;,.,101,OTHERS异常处理器,OTHERS异常处理器是一个特殊的异常处理器，可以捕获所有的异常。通常，OTHERS异常处理器总是作为异常处理部分的最后一个异常处理器，负责处理那些没有被其他异常处理器捕获的异常。,.,102,DECLAREv_salemp.sal%TYPE;e_highlimitEXCEPTION;BEGINSELECTsalINTOv_salFROMempWHEREename=JOAN;UPDATEempSETsal=sal+100WHEREempno=7900;IFv_sal6000THENRAISEe_highlimit;ENDIF;EXCEPTIONWHENe_highlimitTHENDBMS_OUTPUT.PUT_LINE(Thesalaryistoolarge!);ROLLBACK;WHENOTHERSTHENDBMS_OUTPUT.PUT_LINE(Thereissomewronginselecting!);END;,.,103,可以通过两个函数来获取错误相关信息。SQLCODE：返回当前错误代码。如果是用户定义错误返回值为1；如果是ORA-1403：NODATAFOUND错误，返回值为100；其他Oracle内部错误返回相应的错误号。SQLERRM：返回当前错误的消息文本。如果是Oracle内部错误，返回系统内部的错误描述；如果是用户定义错误，则返回信息文本为“User-definedException”。,.,104,DECLAREv_salemp.sal%TYPE;e_highlimitEXCEPTION;v_codeNUMBER(6);v_textVARCHAR2(200);BEGINSELECTsalINTOv_salFROMempWHEREename=JOAN;UPDATEempSETsal=sal+100WHEREempno=7900;IFv_sal6000THENRAISEe_highlimit;ENDIF;EXCEPTIONWHENe_highlimitTHENDBMS_OUTPUT.PUT_LINE(Thesalaryistoolarge!);ROLLBACK;WHENOTHERSTHENv_code:=SQLCODE;v_text:=SQLERRM;DBMS_OUTPUT.PUT_LINE(v_code|v_text);END;,.,105,15.5.3异常的传播,可执行部分异常的传播如果当前语句块有该异常的处理器，则执行之，并且成功完成该语句块。然后，控制权传递到外层语句块。如果当前语句块没有该异常的处理器，则通过在外层语句块中产生该异常来传播该异常。然后，执行对外层语句块执行步骤1。如果没有外层语句块，则该异常将传播到调用环境。,.,106,DECLAREv_salemp.sal%TYPE;BEGINBEGINSELECTsalINTOv_salFROMempWHEREename=JOAN;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE(Thereisnotsuchanemployee!);END;DBMS_OUTPUT.PUT_LINE(Nowthisisoutputtedbyouterblock!);END;/Thereisnotsuchanemployee!Nowthisisoutputtedbyouterblock!,.,107,DECLAREv_salemp.sal%TYPE;BEGINBEGINSELECTsalINTOv_salFROMempWHEREdeptno=10;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE(Thereisnotsuchanemployee!);END;DBMS_OUTPUT.PUT_LINE(Nowthisisoutputtedbyouterblock!);EXCEPTIONWHENTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE(Therearemorethanoneemployee!);END;/Therearemorethanoneemployee!,.,108,声明部分异常的传播声明部分的异常立刻传播到外层语句块，即使当前语句块有异常处理器。异常处理部分的异常的传播异常处理器中产生的异常，可以有RAISE语句显式产生，也可以通过运行时错误而隐含产生。异常立即被传播到外层语句块。,.,109,BEGINDECLAREv_numberNUMBER(6):=ABC;BEGINv_number:=10;EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE(Thisisoutputtedbyinnerblock!);END;EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE(Thisisoutputtedbyouterblock!);END;/Thisisoutputtedbyouterblock!,.,110,15.6存储子程序,存储过程函数局部子程序,.,111,存储子程序是指被命名的PL/SQL块，以编译的形式存储在数据库服务器中，可以在应用程序中进行调用，是PL/SQL程序模块化的一种体现。存储子程序是以独立对象的形式存储在数据库服务器中，因此是一种全局结构，与之对应的是局部子程序，即嵌套在PL/SQL块中的局部过程和函数，其存储位置取决于其所在的父块的位置。,.,112,存储过程,存储过程的创建存储过程的调用存储过程的管理,.,113,存储过程的创建,CREATEORREPLACEPROCEDUREprocedure_name(parameter1_namemodedatatypeDEFAULT|:=value,parameter2_namemodedatatypeDEFAULT|:=value,)AS|IS/*Declarativesectionishere*/BEGIN/*Executablesectionishere*/EXCEPTION/*Exceptionsectionishere*/ENDprocedure_name;,PROCEDUREBODY,.,114,参数模式IN当过程被调用时，实参值被传递给过程。在过程内，该参数起常数作用，可读不可写。调用结束，实参值不变。（默认参数类型）OUT当过程被调用时，实参值被忽略。在过程内，该参数起未初始化的变量作用，值为NULL。过程内，该参数可读可写。调用结束，形参赋给实参。INOUT当过程被调用时，实参值被传递给过程。在过程内，该参数起已初始化变量作用，过程内，该参数可读可写。调用结束，形参赋给实参。,.,115,参数限制声明形参时不能定义形参的长度或精度、刻度参数传递IN参数为引用传递，即实参的指针被传递给形参；OUT、INOUT参数为值传递，即实参的值被复制给形参。,.,116,创建一个存储过程，以部门号为参数，查询该部门的平均工资，并输出该部门中比平均工资高的员工号、员工名。CREATEORREPLACEPROCEDUREshow_emp(p_deptnoemp.deptno%TYPE)ASv_salemp.sal%TYPE;BEGINSELECTavg(sal)INTOv_salFROMempWHEREdeptno=p_deptno;DBMS_OUTPUT.PUT_LINE(p_deptno|averagesalaryis:|v_sal);FORv_empIN(SELECT*FROMempWHEREdeptno=p_deptnoANDsalv_sal)LOOPDBMS_OUTPUT.PUT_LINE(v_emp.empno|v_emp.ename);ENDLOOP;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE(Thedepartmentdoesntexists!);ENDshow_emp;,.,117,通常，存储过程不需要返回值，如果需要返回一个值可以通过函数调用实现。但是，如果希望返回多个值，可以使用OUT或INOUT模式参数来实现。,.,118,创建一个存储过程，以部门号为参数，返回该部门的人数和最高工资。CREATEORREPLACEPROCEDUREreturn_deptinfo(p_deptnoemp.deptno%TYPE,p_avgsalOUTemp.sal%TYPE,p_countOUTemp.sal%TYPE)ASBEGINSELECTavg(sal),count(*)INTOp_avgsal,p_countFROMempWHEREdeptno=p_deptno;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE(Thedepartmentdontexists!);ENDreturn_deptinfo;,.,119,存储过程的调用,在SQL*PLUS中调用EXECprocedure_name(parameter_list)EXECUTEshow_emp(10)在PL/SQL块中调用BEGINprocedure_name(parameter_list);END；,.,120,DECLAREv_avgsalemp.sal%TYPE;v_countNUMBER;BEGINshow_emp(20);return_deptinfo(10,v_avgsal,v_count);DBMS_OUTPUT.PUT_LINE(v_avgsal|v_count);END;,.,121,存储过程的管理,修改存储过程CREATEORREPLACEPROCEDURE重新编译存储过程ALTERPROCEDUREprocedure_nameCOMPILE；删除存储过程DROPPROCEDUREprocedure_name名；查看过程源代码selecttextfromuser_sourcewherename=procedure_name;,.,122,15.6.2函数,函数概述函数的创建函数的调用函数的管理,.,123,函数概述,函数用于返回特定数据，可以返回一个或多个值。在一个函数中必须包含一个或多个RETURN语句函数调用是PL/SQL表达式的一部分，而过程调用可以是一个独立的PL/SQL语句,.,124,函数的创建,CREATEORREPLACEFUNCTIONfunction_name(parameter1_namemodedatatypeDEFAULT|:=value,parameter2_namemodedatatypeDEFAULT|:=value,)RETURNreturn_datatypeAS|IS/*Declarativesectionishere*/BEGIN/*Executablesectionishere*/EXCEPTION/*Exceptionsectionishere*/ENDfunction_name;,FUNCTIONBODY,.,125,创建一个以部门号为参数，返回该部门最高工资的函数。CREATEORREPLACEFUNCTIONreturn_maxsal(p_deptnoemp.deptno%TYPE)RETURNemp.sal%TYPEASv_maxsalemp.sal%TYPE;BEGINSELECTmax(sal)INTOv_maxsalFROMempWHEREdeptno=p_deptno;RETURNv_maxsal;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE(Thedeptnoisinvalid!);ENDreturn_maxsal;,.,126,函数的调用,在SQL语句中调用函数在PL/SQL中调用函数,.,127,DECLAREv_salemp.sal%TYPE;BEGINFORv_deptIN(SELECTDISTINCTdeptnoFROMemp)LOOPv_sal:=return_maxsal(v_dept.deptno);DBMS_OUTPUT.PUT_LINE(v_dept.deptno|v_sal);ENDLOOP;END;,.,128,函数的管理,修改函数CREATEORREPLACEFUNCTIONfunction_name重新编译存储过程ALTERFUNCTIONfunction_nameCOMPILE；删除存储过程DROPFUNCTIONfunction_name；查看过程源代码selecttextfromuser_sourcewherename=function_name;,.,129,15.6.3局部子程序,局部子程序嵌套在其他PL/SQL块中的子程序。只能在其定义的块内部被调用，而不能在其父块外被调用。使用局部子程序时需要注意：局部子程序只在当前语句块内有效；局部子程序必须在PL/SQL块声明部分的最后进行定义；局部子程序必须在使用之前声明，如果是子程序间相互引用，则需要采用预先声明；局部子程序可以重载。,.,130,在一个块内部定义一个函数和一个过程。函数以部门号为参数返回该部门的平均工资；过程以部门号为参数，输出该部门中工资低于部门平均工资的员工的员工号、员工名。,.,131,DECLAREv_deptnoemp.deptno%TYPE;v_avgsalemp.sal%TYPE;FUNCTIONreturn_avgsal(p_deptnoemp.deptno%TYPE)RETURNemp.sal%TYPEASv_salemp.sal%TYPE;BEGINSELECTavg(sal)INTOv_salFROMempWHEREdeptno=p_deptno;RETURNv_sal;ENDreturn_avgsal;PROCEDUREshow_emp(p_deptnoemp.deptno%TYPE)ASCURSORc_empISSELECT*FROMempWHEREsalreturn_avgsal(p_deptno);BEGINFORv_empINc_empLOOPDBMS_OUTPUT.PUT_LINE(v_emp.empno|v_emp.ename);ENDLOOP;ENDshow_emp;BEGINv_deptno:=,.,132,存储子程序与局部子程序区别在于：存储子程序己经编译好放在数据库服务器端，可以直接调用，而局部子程序存在于定义它的语句块中，在运行时先进行编译；存储子程序不能重载，而局部子程序可以进行重载；存储子程序可以被任意的PL/SQL块调用，而局部子程序只能在定义它的块中被调用。,.,133,在一个PL/SQL块中重载两个过程，一个以员工号为参数，输出该员工信息；另一个以员工名为参数，输出员工信息。利用这两个过程分别查询员工号为7902，7934，以及员工名为SMITH，FORD的员工信息。,.,134,DECLAREPROCEDUREshow_empinfo(p_empnoemp.empno%TYPE)ASv_empemp%ROWTYPE;BEGINSELECT*INTOv_empFROMempWHEREempno=p_empno;DBMS_OUTPUT.PUT_LINE(v_emp.ename|v_emp.deptno);EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE(Thereisnotsuchanemployee!);ENDshow_empinfo;PROCEDUREshow_empinfo(p_enameemp.ename%TYPE)ASv_empemp%ROWTYPE;BEGINSELECT*INTOv_empFROMempWHEREename=p_ename;DBMS_OUTPUT.PUT_LINE(v_emp.empno|v_emp.deptno);,.,135,EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE(Thereisnotsuchanemployee!);WHENTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE(Therearemorethanonemployee!);ENDshow_empinfo;BEGINshow_empinfo(7902);show_empinfo(7934);show_empinfo(SMITH);show_empinfo(FORD);END;,.,136,存储子程序与局部子程序区别,存储子程序己经编译好放在数据库服务器端，可以直接调用，而局部子程序存在于定义它的语句块中，在运行时先进行编译；存储子程序不能重载，而局部子程序可以进行重载；存储子程序可以被任意的PL/SQL块调用，而局部子程序只能在定义它的块中被调用,.,137,15.7包,包概述包的创建包的调用包的重载包的初始化包的管理,.,138,包概述,包是包含一个或多个子程序单元（过程、函数等）的容器包是全局的包类型数据库内置包用户创建的包包由包规范和包体两部分组成，在数据库中独立存储,.,139,包规范声明了软件包中所有内容，如过程、函数、游标、类型、异常和变量等，其中过程和函数只包括原型信息，不包含任何子程序代码。包体中包含了在包头中的过程和函数的实现代码。包体中还可以包括在规范中没有声明的变量、游标、类型、异常、过程和函数，但是它们是私有元素，只能由同一包体中其他过程和函数使用。,.,140,创建包规范,语法CREATEORREPLACEPACKAGEpackage_nameIS|ASPRAGMASERIALLY_RESUABLEtype_definition|variable_declaration|exception_declaration|cursor_declaration|procedure_declaration|function_declarationENDpackage_name;,.,141,注意：元素声明的顺序可以是任意的，但必须先声明后使用；所有元素是可选的；过程和函数的声明只包括原型，不包括具体实现。,.,142,创建一个软件包，包括2个变量、2个过程和1个异常。CREATEORREPLACEPACKAGEpkg_empASminsalNUMBER;maxsalNUMBER;e_beyondboundEXCEPTION;PROCEDUREupdate_sal(p_empnoNUMBER,p_salNUMBER);PROCEDUREadd_employee(p_empnoNUMBER,p_salNUMBER);ENDpkg_emp;,.,143,语法CREATEORREPLACEPACKAGEBODYpackage_nameIS|ASPRAGMASERIALLY_RESUABLEtype_definition|variable_declaration|exception_declaration|cursor_declaration|procedure_definition|function_definitionENDpackage_name;,.,144,注意：包体中函数和过程的原型必须与包规范中的声明完全一致；只有在包规范已经创建的条件下，才可以创建包体；如果包规范中不包含任何函数或过程，则可以不创建包体。,.,145,CREATEORREPLACEPACKAGEBODYpkg_empASPROCEDUREupdate_sal(p_empnoNUMBER,p_salNUMBER)ASBEGINSELECTmin(sal),max(sal)INTOminsal,maxsalFROMemp;IFp_salBETWEENminsalANDmaxsalTHENUPDATEempSETsal=p_salWHEREempno=p_empno;IFSQL%NOTFOUNDTHENRAISE_APPLICATION_ERROR(-20000,Theemployeedoesntexist);ENDIF;ELSERAISEe_beyondbound;ENDIF;EXCEPTIONWHENe_beyondboundTHENDBMS_OUTPUT.PUT_LINE(Thesalaryisbeyondbound!);ENDupdate_sal;,.,146,PROCEDUREadd_employee(p_empnoNUMBER,p_salNUMBER)ASBEGINSELECTmin(sal),max(sal)INTOminsal,maxsalFROMemp;IFp_salBETWEENminsalANDmaxsalTHENINSERTINTOemp(empno,sal)VALUES(p_empno,p_sal);ELSERAISEe_beyondbound;ENDIF;EXCEPTIONWHENe_beyondboundTHENDBMS_OUTPUT.PUT_LINE(Thesalaryisbeyondbound!);ENDadd_employee;ENDpkg_emp;,.,147,15.7.2包的调用,概念指软件包中特定的元素或结构的可视范围。在软件包头部声明的任何元素是公有的，在包外都是可见的。包外：通过package.element形式调用；包内：直接通过元素名进行调用。在包体中定义而没有在包头中声明的元素是私有的，只能在包体中引用。,.,148,调用软件包pkg_emp中的过程update_sal，修改7844员工工资为3000。调用add_employee添加一个员工号为1357，工资为4000的员工。BEGINpkg_emp.update_sal(7844,3000);pkg_emp.add_employee(1357,4000);END;,.,149,包的重载,重载子程序必须同，即名称相同，参数不同。如果两个子程序参数只是名称和模式不同，则不能重载。PROCEDUREoverloadme(parameter1INNUMBER);PROCEDUREoverloadme(parameter2OUTNUMBER);不能根据两个函数返回类型不同对它们进行重载。FUNCTIONoverloadmeRETURNDATE;FUNCTIONoverloadmeRETURNNUMBER;重载子程序参数必须在类型系列方面有所不同。PROCEDUREoverloadchar(parameterINCHAR);PROCEDUREoverloadchar(parameterINVARCHAR2);,.,150,在一个包中重载两个过程，分别以部门号和部门名称为参数，查询相应部门员工名、员工号信息。,.,151,CREATEORREPLACEPACKAGEpkg_overloadASPROCEDUREshow_emp(p_deptnoNUMBER);PROCEDUREshow_emp(p_dnameVARCHAR2);ENDpkg_overload;CREATEORREPLACEPACKAGEBODYpkg_overloadASPROCEDUREshow_emp(p_deptnoNUMBER)ASBEGINFORv_empIN(SELECT*FROMempWHEREdeptno=p_deptno)LOOPDBMS_OUTPUT.PUT_LINE(v_emp.empno|v_emp.ename);ENDLOOP;ENDshow_emp;,.,152,PROCEDUREshow_emp(p_dnameVARCHAR2)ASv_deptnoNUMBER;BEGINSELECTdeptnoINTOv_deptnoFROMdeptWHEREdname=p_dname;FORv_empIN(SELECT*FROMempWHEREdeptno=v_deptno)LOOPDBMS_OUTPUT.PUT_LINE(v_emp.empno|v_emp.ename);ENDLOOP;ENDshow_emp;ENDpkg_overload;,.,153,15.7.4包的初始化,包在第一次被调用时从磁盘读取到共享池，并在整个会话的持续期间保持。在此过程中，可以自动执行一个初始化过程，对软件包进行实例化。包的初始化过程只在包第一次被调用时执行，因此也称为一次性过程，它是一个匿名的PL/SQL块，在包体结构的最后，以BEGIN开始。,.,154,在pkg_emp包中，可以在包初始化时给minsal和maxsal两个变量赋值，而在子程序中直接引用这两个变量。CREATEORREPLACEPACKAGEpkg_empASminsalNUMBER;maxsalNUMBER;e_beyondboundEXCEPTION;PROCEDUREupdate_sal(p_empnoNUMBER,p_salNUMBER);PROCEDUREadd_employee(p_empnoNUMBER,p_salNUMBER);ENDpkg_emp;,.,155,CREATEORREPLACEPACKAGEBODYpkg_empASPROCEDUREupdate_sal(p_empnoNUMBER,p_salNUMBER)ASBEGINIFp_salBETWEENminsalANDmaxsalTHENUPDATEempSETsal=p_salWHEREempno=p_empno;IFSQL%NOTFOUNDTHENRAISE_APPLICATION_ERROR(-20000,Theemployeedoesntexist);ENDIF;ELSERAISEe_beyondbound;ENDIF;EXCEPTIONWHENe_beyondboundTHENDBMS_OUTPUT.PUT_LINE(Thesalaryisbeyondbound!);ENDupdate_sal;,.,156,PROCEDUREadd_employee(p_empnoNUMBER,p_salNUMBER)ASBEGINIFp_salBETWEENminsalANDmaxsalTHENINSERTINTOemp(empno,sal)VALUES(p_empno,p_sal);ELSERAISEe_beyondbound;ENDIF;EXCEPTIONWHENe_beyondboundTHENDBMS_OUTPUT.PUT_LINE(Thesalaryisbeyondbound!);ENDadd_employee;BEGINSELECTmin(sal),max(sal)INTOminsal,maxsalFROMemp;ENDpkg_emp;,.,157,15.7.5包的管理,包的修改CREATEORREPLACEPACKAGE包名重新编译包ALTERPACKAGEpackage_nameCOMPILE;ALTERPACKAGEpackage_nameCOMPILESPECIFICATION;ALTERPACKAGEpackage_nameCOMPILEBODY;删除包DROPPACKAGEpackage_name;DROPPACKAGEBODYpackage_name;,.,158,查看包源代码selecttextfromuser_sourcewherename=EMP_PACKAGEandtype=PACKAGE;,.,159,15.8触发器,触发器概述DML触发器INSTEAD-OF触发器系统触发器触发器的管理,.,160,触发器概述,触发器的概念触发器是命名块的一种。触发器的执行是自动进行的，当相应事件发生时就会激发触发器的执行。触发器不接受任何参数,.,161,触发器（TRIGGER）作用维护那些通过创建表时的声明约束不可能实现的复杂的完整性约束通过记录已进行的改变及是谁进行了该项改变来检查一个表中的信息。当一个表发生改变时，自动向其他程序发送需要采取行动的信号在一个发布-预定环境中发布关于各种事件的信息。,.,162,触发器类型DML触发器INSERT、DELETE、UPDATEINSTEAD-OF触发器只可以定义为视图的触发器系统触发器数据库启动或关闭之类的系统事件发生时触发在执行诸如创建表之类的DDL操作时触发,.,163,触发器组成触发器由触发器头部和触发器体两个部分组成，主要包括以下参数：作用对象：表、视图、数据库、模式触发事件：DML、DDL、数据库系统事件触发时间：BEFORE、AFTER触发级别：语句级、行级触发条件：WHEN条件触发操作：SQL语句、PL/SQL块,.,164,15.8.2DML触发器,DML触发器的种类以及执行顺序语句级前触发器行级前触发器DML操作（触发事件）行级后触发器语句级后触发器,.,165,创建DML触发器,CREATEORREPLACETRIGGERtrigger_nameBEFORE|AFTERtriggering_eventOF