《Oracle数据库应用开发及实践》课件07-第7章 PL SQL进阶

-1-本章目标掌握游标定义的语法格式、类型、属性及使用掌握参数游标的定义及使用掌握使用复合类型接收游标数据了解存储过程的作用及特点掌握存储过程的创建、维护及调用了解函数的作用及特点掌握函数的创建、调用及维护掌握包体的定义及包组件的调用掌握语句触发器、BEFORE语句和ALTER语句触发器以及行触发器的使用与维护-2-隐式游标：处理单行SELECTINTO和DML语句隐式游标用于描述数据库中执行的SQL命令，Oracle会自动的为SQL语句分配一段私有的游标区域

显式游标：处理SELECT语句返回的多行数据显式游标由开发人员通过程序显示控制，用于从表中取出多行数据，并将多行数据一行一行的进行单独处理游标-3-处理步骤：定义游标打开游标提取游标数据对游标指针指向的记录进行处理继续处理，直到活动集合中没有记录关闭游标显式游标-4-%ISOPEN：该属性用于检测游标是否已经打开%FOUND：该属性用于检测游标结果集是否存在数据%NOTFOUND：该属性用于检测游标结果集是否不存在数据%ROWCOUNT：该属性用于返回已提取的实际行数游标属性-5-使用标量变量接收数据当使用标量变量接收游标数据时，必须为SELECT列表中的每个列或者表达式提供标量变量，并且数据类型和长度必须匹配。使用记录变量接收数据通过使用PL/SQL记录变量，可以简化单行数据的处理。使用集合变量接收数据通过使用PL/SQL集合变量，可以简化多行单列以及多行多列数据的处理。带参数的游标通过使用参数游标，可以根据参数值的不同生成不同的游标结果集。使用游标-6-当进入循环时，游标FOR循环语句自动打开游标，并提取第一行游标数据当程序处理完当前所提取的数据而进入下一次循环时，游标FOR循环语句自动提取下一行数据供程序处理当提取完结果集合中的所有数据行后结束循环，并自动关闭游标在游标FOR循环语句中，不要在程序中对游标进行人工操作不要在程序中定义用于控制FOR循环的记录游标FOR循环-7-使用游标修改数据

在PL/SQL中，如果进行数据的DML操作，需要进行事务的显示提交当查询语句涉及到多张表时，如果不带有OF子句，会在多张表上同时加锁，如果只在特定表上加锁，需要带有OF子句使用游标删除数据

游标修改和删除操作-8-游标变量是一个指向多行查询结果集合中当前数据行的指针游标变量是动态的游标变量可与不同的查询语句相连，它可以指向不同查询语句的内存处理区域

游标变量-9-定义REFCURSOR类型和游标变量打开游标变量提取数据关闭游标变量游标变量定义TYPEref_type_nameISREFCURSOR[RETURNreturn_type];OPENcursor_variable_nameFORselect_statement;FETCHcursor_variable_nameINTO{variable[,variable]…|record_variable};CLOSEcursor_variable_name-10-强类型参照游标

当定义REFCURSOR类型时，如果指定RETURN子句，在打开游标时，SELECT语句的返回结果必须与RETURN子句指定的记录类型匹配

弱类型参照游标当定义REFCURSOR类型是，如果不指定RETURN子句，在打开游标时可以指定任何SELECT语句

游标变量操作-11-使用游标实例使用游标，将emp表的ename和sal字段的内容，按照sal降序输出到控制台：declarecursorc1isselectename,salfromemporderbysaldesc;beginforv1inc1loopdbms_output.put_line(v1.ename||’’||v1.sal);endloop;end;-12-存储过程（StoredProcedure）是一组预存储在数据库中，为了完成某种特定功能的PL/SQL语句集。优点：使用存储过程体现了标准组件式编程通过存储过程能够实现较快的执行速度使用存储过程能够减少网络流量使用存储过程可加强数据安全性存储过程概述-13-创建无参存储过程：

调用无参存储过程：如果在当前方案中创建存储过程时，用户必须有CREATEPROCEDURE权限如果在其他方案中创建过程，用户必须有CREATEANYPROCEDURE权限无参存储过程CREATEORREPLACEPROCEDUREexecproc_name-14-使用标量变量作为输入参数在为存储过程指定参数时，只能指定过程参数的类型，不能指定过程参数的长度使用记录类型作为输入参数通过使用记录类型作为输入参数，可以将调用环境或者应用程序的记录数据传递到存储过程中。使用集合类型作为输入参数通过使用集合类型作为输入参数，可以将调用环境或者应用程序的集合变量数据传递到存储过程中如果使用集合变量作为过程参数，那么需要使用自定义的嵌套表类型或者VARRAY类型。带输入参数的存储过程-15-使用标量变量作为输出参数当调用带有输出参数的过程时，需要使用变量接收输出参数的数据值使用记录类型作为输出参数通过使用记录类型作为输出参数，可以将记录类型结果数据返回到调用环境或应用程序使用集合类型作为输出参数通过使用集合类型作为输出参数，可以将多行数据返回到调用环境或应用程序中如果使用集合变量作为过程参数，那么需要使用自定义的嵌套表类型或者VARRAY类型

带输出参数的存储过程-16-通过在存储过程中使用输入输出参数，可以在调用存储过程时输入数据到过程，在执行结束后返回结果数据到调用环境或应用程序。当定义输入输出参数时，需要指定参数模式为INOUT。带输入输出参数的存储过程-17-删除存储过程当存储过程不再需要时，可以使用DROPPROCEDURE语句删除指定的过程重新编译存储过程使用ALTERPROCEDURE命令可以重新编译INVALID状态的过程确定过程状态了确定处于INVALID状态的对象，可以查询数据字典USER_OBJECTS

查看过程文本通过查询数据字典USER_SOURCE，可以取得当前用户所拥有的子程序名称及其创建文本维护存储过程-18-建立存储过程运行存储过程验证结果查看程序代码存储过程实例createorreplaceprocedurep1(v_idinemp.empno%type)isbeginupdateempsetsal=sal+1whereempno=v_id;commit;endp1;executep1(7369);selectempno,salfromempwhereempno=7369;selecttextfromuser_sourcewhereNAME=’P1’;-19-输入参数（IN）用于接收调用环境的输入数据输出参数（OUT）用于将结果数据传递到调用环境输入输出参数（INOUT）即能接收输入数据，又能传递结果数据到调用环境中函数概述CREATE[ORREPLACE]FUNCTIONfunction_name[(argument[{IN|OUT|INOUT}]type,argument[{IN|OUT|INOUT}]type]RETURNtype{IS|AS}<声明部分>BEGIN<执行部分>EXCEPTION<可选的异常处理程序>END;-20-创建函数：根据创建语句格式创建实际的函数调用函数：当调用函数时，如果是无参函数，直接引用函数名即可如果在当前方案中创建函数时，用户必须有CREATEFUNCTION权限如果在其他方案中创建函数，用户必须有CREATEANYFUNCTION权限调用函数-21-使用标量变量作为返回类型使用记录类型作为返回类型通过使用记录类型作为函数返回类型，可以将记录类型结果数据返回到调用环境或应用程序使用集合类型作为返回类型通过使用集合类型作为函数返回类型，可以将多行数据返回到调用环境或应用程序中函数返回类型-22-删除函数：重新编译函数：确定函数状态：为了确定处于INVALID状态的对象，可以查询数据字典USER_OBJECTS

查看函数文本：通过查询数据字典USER_SOURCE，可以取得当前用户所拥有的子程序名称及其创建文本

维护函数DROPFUNCTIONfunc1ALTERFUNCTIONget_dept_nameCOMPILE-23-创建函数运行函数删除函数函数实例createorreplacefunctionget_sal(v_idinemp.empno%type)returnnumberisv_salaryemp.sal%type:=0;beginselectsalintov_salaryfromempwhereempno=v_id;return(v_salary);endget_sal;selectget_sal(7839)fromdual;dropfunctionget_salary;-24-包是一组相关过程、函数、变量、常量和游标等PL/SQL程序设计元素的组合，它具有面向对象程序设计语言的特点，是对这些PL/SQL程序单元的封装。组成：包定义（PACKAGE）包体（PACKAGEBODY）包CREATE[ORREPLAC]PACKAGEpackage_name{IS|AS}<定义公用常量、变量、类型、游标><定义公用过程和函数>END[package_name];CREATE[ORREPLACE]PACKAGEBODYpackage_name{IS|AS}<定义私有常量、变量、类型、游标、过程和函数><实现公用过程和函数>END[package_name];-25-定义包：依照创建语句定义包定义包体：实现其所对应的包规范定义的公有子程序调用包：格式：包名.公共元素名调用包-26-通过使用集合变量作为输入参数，可以将调用环境或者应用程序的集合变量数据（或者数组数据）传递到包的公用过程中。通过使用集合类型作为返回类型，可以将多行数据返回到调用环境或者应用程序中。使用集合类型-27-重载（overload）根据不同的参数格式调用不同功能的同名子程序子程序重载的唯一参考前提是参数格式，返回类型不同不能决定重载构造过程：包的构造过程没有任何名称，它是在实现了包的其他过程和函数之后，以BEGIN开始、以END结束的部分当在会话内第一次调用包的公用组件时，会自动执行其构造过程，并且在同一会话内构造过程只会被执行一次重载/构造过程-28-删除包：重新编译包：使用ALTERPACKAGE命令可以重新编译处于INVALID状态的包确定包状态：为了确定处于INVALID状态的包，可以查询数据字典USER_OBJECTS查看包文本：通过查询数据字典USER_SOURCE，可以取得当前用户所拥有的包名称及其创建文本维护包DROPPACKAGEdept_pack;-29-定义：触发器存放在数据库内，触发器内的代码，称为“触发体”（triggerbody），由PL/SQL块构成。分类：语句触发器：在执行DML（INSERT、DELETE、UPDATE）操作时，将激活该类触发器。行触发器：在执行DML操作时，每作用一行被触发一次。INSTEADOF触发器：该触发器是仅基于在视图上进行DML操作所创建的触发器。事件触发器：该类触发器基于Oracle系统事件或客户事件建立并触发。

触发器（TRIGGER）-30-BEFORE触发器：在数据操作前被触发触发器条件谓词：INSERTING：如果触发语句是INSERT语句，则为TRUE，否则为FALSE。UPDATING：如果触发语句是UPDATE语句，则为TRUE，否则为FALSE。DELETING：如果触发语句是DELETE语句，则为TRUE，否则为FALSE。语句触发器-31-BEFORE行触发器：在对每行数据操作前被触发AFTER行触发器：为了记录数据变化，可以使用AFTER行触发器UPDATEOF限制：即只有属于OF指定的列对应表的列数据发生修改时，才会激活触发器WHEN限制：可以使用WHEN子句对触发条件加以限制，只有满足condition指定的条件的行才会被触发

行触发器-32-触发器的使能：重编译触发器：删除触发器：显示触发器信息：通过数据字典视图USER_TRIGGERS，可以显示当前用户所包含的触发器信息维护触发器ALTERTIGGERtrigger_name[DISABLE|ENABLE];ALTERTRIGGERtrigger_nameCOMPILE;DROPTRIGGERtrigger_name;-33-CREATETRIGGER语句文本的字符长度不能超过32KB。触发体内的SELECT语句只能为SELECTINTO语句，或者为定义游标所使用的SELECT语句。触发器中不能使用数据库事务控制语句。由触发器所调用的过程或函数也不能使用数据库事务控制语句。触发器中不能使用LONG、LONGRAW类型。触发器内可以参照LOB类型列的列值，但不能通过:NEW修改LOB列中的数据。触发器使用限制-34-建立触发器验证结果触发器实例createorreplaceprocedurep1(v_idinemp.empno%type)iscreateorreplacetriggerd_updateafterdeleteorupdateofdeptnoondeptforeachrowbegin--当dept表的部门号修改的时候emp表的对应部门号也相应的修改if(updatingand:old.deptno!=:new.deptno)t