子程序和触发器

子程序和触发器,Oracle数据库,学完本次课程后，你能够：掌握存储过程掌握自定义函数了解程序包相关概念、用途掌握触发器相关概念、用法,本课目标,什么是子程序,命名的PL/SQL块，编译并存储在数据库中子程序的分类过程执行某些操作函数执行操作并返回值,创建并执行过程,语法,CREATEORREPLACEPROCEDURE()-过程名、过程参数IS|AS-声明变量BEGIN-执行部分EXCEPTION-异常处理部分ENDprocedurename;,EXECUTEprocedure_name(parameters_list)；-执行过程,增加老师信息,演示示例：创建并执行过程,需求说明：创建一个过程，增加老师信息参数为姓名、身份证、性别、出生日期、薪水身份证号必须为18位,过程的参数模式,IN用于接受调用程序的值默认的参数模式OUT用于向调用程序返回值INOUT用于接受调用程序的值，并向调用程序返回更新的值,删除老师信息,演示示例：过程参数使用,需求说明：创建一个过程，删除老师信息参数为老师编号、删除条数,过程使用游标,演示示例：过程使用游标,需求说明：创建分页查询教师的过程参数为性别、起始行和结束行返回值为查询结果集,过程中执行DDL,演示示例：过程中执行DDL,需求说明：创建一个过程参数为表名（要创建的表），已有表名（已有表）功能是创建一个和已有表结构相同并且数据一样的新表,语法,CREATETABLEnewTableNameASSELECT*FROMexistsTable,存储过程相关权限和删除,DROPPROCEDUREprocedure_name;,演示示例：删除过程,语法,GRANTEXECUTEONpr_nameTOuserName;GRANTEXECUTEONpr_nameTOPUBLIC;,创建函数,语法,CREATEORREPLACEFUNCTION(param1,param2)-函数名、参数RETURNIS|AS-返回值类型localdeclarations-变量声明BEGINExecutableStatements;RETURNresult;-返回值EXCEPTIONExceptionhandlers;END;,身份证保密处理,演示示例：创建函数,需求说明：创建函数参数为身份证返回值为处理过的身份号如参数值回值：4107211982*3516,函数相关操作,DROPFUNCTIONfn_name;,语法,GRANTEXECUTEONfn_nameTOuserName;GRANTEXECUTEONfn_nameTOPUBLIC;,函数和过程使用原则,原则：使用场合用途,程序包概念,什么是程序包程序包是对相关存储过程、函数、变量、游标和异常等对象的封装程序包由规范和主体两部分组成,声明程序包中公共对象。包括类型、变量、常量、异常、游标规范和子程序规范等,声明程序包私有对象和实现在包规范中声明的子程序和游标,程序包,规范,主体,创建程序包,CREATEORREPLACEPACKAGEBODYpackage_nameIS|ASPrivateitemdeclarationsSubprogrambodiesBEGINInitializationENDpackage_name;,语法,CREATEORREPLACEPACKAGEpackage_nameIS|ASPublicitemdeclarations-变量、常量、游标、数据类型声明Subprogramspecification-子程序（过程、函数）声明ENDpackage_name;,程序包开发步骤,演示示例：开发程序包,程序包中的游标,演示示例：程序包中的游标,静态游标游标的定义分为游标规范和游标主体两部分包规范中声明游标规范时必须使用RETURN子句指定游标的返回类型动态游标在包规范中声明自定义游标类型,注意,不能在包主体中声明游标变量,程序包的管理,ALTERPACKAGEpackage_nameCOMPILEPACKAGE|BODY编译,语法,DROPPACKAGEBODYpackage_name;-删除程序包,触发器,如何实现跟踪并记录所有对教师表的表结构进行改变的操作只要一变动表结构就要记录所有操作到一个日志表中不需要显式调用来执行，而是修改表结构的操作来启动运行,问题,分析,触发器特性,满足哪些需求？不需要显式调用来执行，而是由一个事件来启动运行事件包括哪几种情况？INSERT、UPDATE及DELETE操作或对视图进行类似的操作执行DDL操作数据库的启动与关闭,创建触发器,语法,CREATEORREPLACETRIGGERtrigger_nameAFTER|BEFORE|INSTEADOFINSERTORUPDATEOFcolumn_listORDELETEONtable_or_view_nameREFERENCINGOLDASold/NEWASnewFOREACHROWWHEN(condition)pl/sql_block;,监控教师信息变化,需求说明：监控教师表（teacher）的数据变化记录修改前的关键数据（tname、sal、comm）保存到日志表里思路说明：创建日志表（teacher_log），设计日志表使用触发器监控teacher的数据变化关键数据插入到日志表,演示示例：创建触发器,触发器类型2-1,触发器类型,模式(DDL)触发器,DML触发器,数据库级触发器,语句级触发器,行级触发器,INSTEADOF触发器,触发器类型2-2,DDL触发器数据库级触发器DML触发器语句级触发器行级触发器INSTEADOF触发器,在模式中执行DDL语句时执行,在发生打开、关闭、登录和退出数据库等系统事件时执行,在对表或视图执行DML语句时执行,无论受影响的行