oracle10g经典实战第6章 存储过程和触发器.ppt

第6章存储过程和触发器 6 1存储过程在Oracle中 可以在数据库中定义子程序 这种程序块称为存储过程 procedure 它存放在数据字典中 可以在不同用户和应用程序之间共享 并可实现程序的优化和重用 使用存储过程的优点是 1 过程在服务器端运行 执行速度快 2 过程执行一次后代码就驻留在高速缓冲存储器 在以后的操作中 只需从高速缓冲存储器中调用已编译代码执行 提高了系统性能 3 确保数据库的安全 可以不授权用户直接访问应用程序中的一些表 而是授权用户执行访问这些表的过程 非表的授权用户除非通过过程 否则就不能访问这些表 4 自动完成需要预先执行的任务 过程可以在系统启动时自动执行 而不必在系统启动后再进行手工操作 大大方便了用户的使用 可以自动完成一些需要预先执行的任务 6 1 1存储过程的创建和执行 用户存储过程只能定义在当前数据库中 可以使用SQL命令语句或OEM创建存储过程 缺省情况下 用户创建的存储过程归登录数据库的用户所拥有 DBA可以把许可授权给其他用户 在用户的定义中不能使用下列对象创建语句 CREATEVIEWCREATEDEFAULTCREATERULECREATEPROCEDURECREATETRIGGER 6 1 1存储过程的创建和执行 1 SQL命令创建存储过程语法格式 CREATE ORREPLACE PROCEDURE schema procedure name 定义过程名 parameterparameter modedate type n 定义参数类型及属性 IS ASBEGINsql statement PL SQL过程体 要执行的操作 ENDprocedure name其中 procedure name 是过程名 必须符合标识符规则 关键字REPLACE表示在创建过程时 如果已存在同名的过程 则重新创建 如果使用CREATE关键字 则需将原有的过程删除后才能创建 schema 是指定过程属于的用户方案 parameter 是过程的参数 参数名必须符合标识符规则 创建过程时 可以声明一个或多个参数 执行过程时应提供相对应的参数 Parameter mode是参数的类型 过程参数和函数参数一样 也有3种类型 分别为IN OUT和INOUT IN 表示参数是输入给过程的 OUT 表示参数在过程中将被赋值 可以传给过程体的外部 INOUT 表示该类型的参数既可以向过程体传值 也可以在过程体中赋值 sql statement 代表过程体包含的PL SQL语句 6 1 1存储过程的创建和执行 2 调用存储过程直接输入存储过程的名字就可以执行一个已定义的存储过程 语法格式 EXEC UTE procedure name parameter n 其中 procedure name为要调用的存储过程的名字 parameter为参数值 例6 1 计算指定系总学分大于40的人数 CREATEORREPLACEPROCEDUREcount grade zyminchar person numoutnumber ASBEGINSELECTCOUNT ZXF INTOperson numFROMXSWHEREZYM zym ENDcount grade 6 1 1存储过程的创建和执行 例6 2 从XSCJ数据库的XS表中查询某人的总学分 根据总学分写评语 CREATEORREPLACEPROCEDUREupdate info xminchar ASXfnumber BEGINSELECTZXFINTOXFFROMXSWHEREXM xm IFXF 60THENUPDATEXSSETBZ 三好学生 WHEREXM xm ENDIF IFXF 35THENUPDATEXSSETBZ 学分未修满 WHEREXM xm ENDIF ENDupdate info update info存储过程执行 EXECupdate info 李明 6 1 1存储过程的创建和执行 例6 3 计算指定学生的总学分 存储过程使用了一个输入参数和一个输出参数 CREATEORREPLACEPROCEDUREtotalcredit nameINvarchar2 totalOUTnumber ASBEGINSELECTSUM XF INTOtotalFROMXS XS KCWHEREXM nameANDXS XH XS KC XH END 6 1 1存储过程的创建和执行 例6 4 统计表XS中男女同学的人数 存储过程使用了一个输入参数和一个输出参数 CREATEORREPLACEPROCEDUREcount num sexINchar numOUTnumber ASBEGINIFsex 男 THENSELECTCOUNT XB INTOnumFROMXSWHEREXB 男 ELSESELECTCOUNT XB INTOnumFROMXSWHEREXB 女 ENDIF ENDcount num 在调用过程count num时 需要先定义OUT类型参数 调用如下 DECLAREman numNUMBER BEGINcount num 男 man num END 6 1 1存储过程的创建和执行 3 利用OEM创建过程 1 在OEM界面中 如图6 1所示 选择 过程 单击鼠标左键 进入 过程搜索 界面 如图6 2所示 图6 1Oracle企业管理器 图6 2过程搜索界面 6 1 1存储过程的创建和执行 3 利用OEM创建过程 2 单击 创建 按钮 进入过程创建界面 如图6 3所示 在 名称 文本框中指定过程名称count grade 在 方案 中选择建立过程的用户方案ADMIN 然后在 源 代码区域 编辑PL SQL过程语句块 3 代码输入编辑完成后 单击 确定 按钮完成过程创建 图6 3创建过程界面 6 1 2存储过程的编辑修改 例6 5 对存储过程update info进行修改 CREATEORREPLACEPROCEDUREupdate info xminchar ASXfnumber BEGINSELECTZXFINTOXFFROMXSWHEREXM xm IFXF 60THENUPDATEXSSETBZ 三好学生 WHEREXM xm ENDIF IFXF 35THENUPDATEXSSETBZ 学分未修满 WHEREXM xm ENDIF ENDupdate info 6 1 2存储过程的编辑修改 例6 6 创建名为select students的存储过程 默认情况下 该过程可查询所有学生的信息 当该过程需要改为能检索计算机专业的学生信息时 用CREATEORREPLACEPROCEDURE重新定义 1 定义过程CREATEORREPLACEPROCEDUREselect students CUROUTselect cur 07 ASBEGINOPENCURFORSELECTxh xm zym xb cssj zxf bzFROMXSORDERBYXH END 注意 使用查询包头时在SELECT语句中不能使用 标识符 2 修改过程CREATEORREPLACEPROCEDUREselect students CUROUTselect cur 07 ASBEGINOPENCURFORSELECTxh xm zym xb cssj zxf bzFROMXSWHEREZYM 计算机 ORDERBYXH END 6 1 3存储过程的删除 当某个过程不再需要时 应将其从内存中删除 以释放它占用的内存资源 语法格式 DROPPROCEDURE schema procedure name 其中 schema是包含过程的用户 procedure name是将要删除的存储过程名称 例6 7 删除XSCJ数据库中的count num存储过程 DROPPROCEDUREcount num 也可在OEM中选择要删除的存储过程 在如图6 2所示的界面 搜索并选择要删除的存储过程 单击 删除 出现确认删除界面 单击 是 即可删除该存储过程 6 2触发器 触发器 trigger 是一些过程 与表关系密切 用于保护表中的数据 当一个基表被修改 INSERT UPDATE或DELETE 时 触发器自动执行 例如通过触发器可实现多个表间数据的一致性和完整性 触发器和应用程序无关 例如 对于XSCJ数据库有XS表 XS KC表和KC表 当插入某一学号的学生某一课程成绩时 该学号应是XS表中已存在的 课程号应是KC表中已存在的 此时 可通过定义INSERT触发器实现上述功能 触发器的类型有三种 1 DML触发器 Oracle可以在DML 数据操纵语句 语句进行触发 可以在DML操作前或操作后进行触发 并且可以在每个行或该语句操作上进行触发 2 替代触发器 由于在Oracle中不能直接对有两个以上的表建立的视图进行操作 所以给出了替代触发器 它是Oracle专门为进行视图操作的一种处理方法 3 系统触发器 在Oracle8i时 提供了第三种类型的触发器叫系统触发器 它可以在Oracle数据库系统的时间中进行触发 如Oracle数据库的关闭或打开等 6 2触发器 一般情况下 对表数据的操作有插入 修改 删除 因而维护数据的触发器也可分为INSERT UPDATE和DELETE 每张基表最多可建立12个触发器 它们是 1 BEFOREINSERT 2 BEFOREINSERTFOREACHROW 3 AFTERINSERT 4 AFTERINSERTFOREACHROW 5 BEFOREUPDATE 6 BEFOREUPDATEFOREACHROW 7 AFTERUPDATE 8 AFTERUPDATEFOREACHROW 9 BEFOREDELETE 10 BEFOREDELETEFOREACHROW 11 AFTERDELETE 12 AFTERDELETEFOREACHROW 6 2 1利用SQL语句创建触发器 1 语法格式CREATEORREPLACETRIGGER schema trigger name 指定触发器名称 BEFORE AFTER INSTEADOF DELETE ORINSERTE ORUPDATE OFcolumn n 定义触发器种类 ON schema table name view name 指定操作对象 FOREACHROW WHEN condition sql statement n 6 2 1利用SQL语句创建触发器 2 创建触发器的限制创建触发器有以下限制 1 代码大小 触发器代码大小必须小于32K 2 触发器中有效语句可以包括DML语句 但不能包括DDL语句 ROLLBACK COMMIT SAVEPOINT也不能使用 但是 对于系统触发器 systemtrigger 可以使用CREATE ALTER DROPTABLE和ALTER COMPILE语句 3 LONG LONGRAW和LOB的限制 不能插入数据到LONG或LONGRAW 来自LONG或LONGRAW的数据可以转换成字符型 如char varchar2 但是不能超过32K 使用LONG或LONGRAW不能声明变量 在LONG或LONGRAW列中不能使用 NEW和 PARENT 在LOB中的 NEW变量不能修改 4 引用包变量的限制 如果UPDATE或DELETE语句检测到当前的UPDATE冲突 则Oracle执行ROLLBACK到SAVEPOINT上并重新启动更新 这样可能需要多次才能成功 3 触发器触发次序Oracle对事件的触发有16种 它们按照一定次序执行 1 执行BEFORE语句的触发器 2 对于受语句影响的每一行 执行BEFORE语句行级触发器 执行DML语句 执行AFTER行级触发器 3 执行AFTER语句级触发器 6 2 1利用SQL语句创建触发器 4 创建DML触发器触发器与过程名和包的名字不一样 它有单独的名字空间 因而触发器名可以和表名或过程名同名 但在同一个schema 方案 中的触发器名不能相同 DML触发器也叫表级触发器 因为对某个表进行DML操作时会触发该触发器运行而得名 例6 8 假设XSCJ数据库中增加一新表XS HIS 表结构和表XS相同 用来存放从XS表中删除的记录 创建一个触发器 当XS表被删除一行 把删除的记录写到日志表XS HIS中 CREATEORREPLACETRIGGERdel xsBEFOREDELETEONXSFOREACHROWBEGININSERTINTOXS HIS XH XM ZYM XB CSSJ ZXF BZ VALUES OLD XH OLD XM OLD ZYM OLD XB OLD CSSJ OLD ZXF OLD BZ ENDdel xs OLD修饰访问操作完成前列的值 NEW修饰访问操作完成后列的值 6 2 1利用SQL语句创建触发器 例6 9 利用触发器在数据库XSCJ的XS表执行插入 更新和删除3种操作后给出相应提示 CREATETRIGGERcue xsAFTERINSERTORUPDATEORDELETEONXSFOREACHROWDECLAREInforchar 10 BEGINIFINSERTINGTHENInfor 插入 ELSIFUPDATINGTHENInfor 更新 ELSEInfor 删除 ENDIF INSERTINTOSQL INFOVALUES infor ENDcue xs 6 2 1利用SQL语句创建触发器 5 创建替代 Instead of 触发器Instead of用于对视图的DML触发 由于视图有可能由多个表进行关联 Join 而成 因而并非所有的关联都是可更新的 但是可以按如下例子来创建触发器 例6 10 在XSCJ数据库中创建视图和触发器 以说明替代触发器 CREATEORREPLACEVIEWcs kc avgASSELECTXH AVG CJ ASAVG CJFROMXS KCGROUPBYXH 创建替代触发器 CREATETRIGGERcs kc avg delINSTEADOFDELETEONcs kc avgFOREACHROWBEGINDELETEFROMXS KCWHEREXH OLD XH ENDcs kc avg del 6 2 1利用SQL语句创建触发器 6 创建系统触发器Oracle8i开始提供的系统触发器可以在DDL或数据库系统上被触发 DDL指的是数据定义语句 如CREATE ALTER和DROP等 而数据库系统事件包括数据库服务器的启动或关闭 用户登录与退出等 语法格式 CREATEORREPLACETRIGGER scache trigger name BEFORE AFTER ddl event list databse event list ON DATABASE schema SCHEMA when clause tigger body其中 ddl event list 表示一个或多个DDL事件 事件间用OR分开 database event list 表示一个或多个数据库事件 事件间用OR分开 DATABASE 表示是数据库级触发器 而scache表示是用户级触发器 Schema表示用户方案 Trigger body 触发器的PL SQL语句 6 2 1利用SQL语句创建触发器 例6 11 创建当一个用户userA登录时自动记录一些信息的触发器 CREATETRIGGERloguserAconnectsAFTERLOGONONSCHEMABEGININSERTINTOLOGINVALUES userA loguserAconnectsfired ENDloguserAconnects 6 2 2利用OEM创建触发器 1 如图6 1所示的界面中选择触发器 鼠标单击左键 打开 触发器搜索 界面 如图6 5所示 2 单击 创建 按钮 进入 触发器创建 界面 如图6 6所示 该界面包括三个选项页面 一般信息 事件和高级选项页面 图6