触发器和内置程序包

程序包和触发器2程序包程序包是对相关过程、函数、变量、游标和异常等对象的封装程序包由规范和主体两部分组成声明程序包中公共对象。包括类型、变量、常量、异常、游标规范和子程序规范等声明程序包私有对象和实现在包规范中声明的子程序和游标程序包规范主体3创建程序包程序包规范CREATE[ORREPLACE]PACKAGEpackage_nameIS|AS[Publicitemdeclarations][Subprogramspecification]END[package_name];程序包主体CREATE[ORREPLACE]PACKAGEBODYpackage_nameIS|AS[Privateitemdeclarations][Subprogrambodies][BEGINInitialization]END[package_name];4程序包的优点模块化更轻松的应用程序设计信息隐藏新增功能性能更佳5用于定义公用的信息createorreplacepackagemonis--定义公用的动态游标typeSystem_RefCursorisrefcursor;--定义公用的自定义数据类型typeCommon_RecordisRecord(monthvarchar2(10),areavarchar2(10),OpenCountnumber,DeleCountnumber);global_save_moneyconstantnumber:=3;global_get_moneyconstantnumber:=4;global_change_moneyconstantnumber:=5;endmon;6程序包中的游标游标的定义分为游标规范和游标主体两部分在包规范中声明游标规范时必须使用RETURN子句指定游标的返回类型RETURN子句指定的数据类型可以是：用

%ROWTYPE属性引用表定义的记录类型程序员定义的记录类型7程序包中的游标2-2SQL>CREATEORREPLACEPACKAGEcur_packISCURSORord_cur(vcodeVARCHAR2)RETURNorder_master%ROWTYPE;PROCEDUREord_pro(vcodeVARCHAR2);ENDcur_pack;/SQL>CREATEORREPLACEPACKAGEBODYcur_packASCURSORord_cur(vcodeVARCHAR2)RETURNorder_master%ROWTYPEISSELECT*FROMorder_masterWHEREVENCODE=vcode;PROCEDUREord_pro(vcodeVARCHAR2)ISor_recorder_master%ROWTYPE;BEGINOPENord_cur(vcode);LOOPFETCHord_curINTOor_rec;EXITWHENord_cur%NOTFOUND;DBMS_OUTPUT.PUT_LIne(’返回的值为'||or_rec.orderno);ENDLOOP;ENDord_pro;ENDcur_pack;/8有关子程序和程序包的信息USER_OBJECTS视图包含用户创建的子程序和程序包的信息USER_SOURCE视图存储子程序和程序包的源代码SELECTobject_name,object_typeFROMUSER_OBJECTSWHEREobject_typeIN('PROCEDURE','FUNCTION','PACKAGE','PACKAGEBODY');SELECTline,textFROMUSER_SOURCEWHERENAME='TEST';9内置程序包8-1扩展数据库的功能为PL/SQL提供对SQL功能的访问用户SYS拥有所有程序包是公有同义词可以由任何用户访问10内置程序包SQL>SETSERVEROUTPUTONSQL>DECLAREl_numNUMBER;counterNUMBER;BEGINcounter:=1;WHILEcounter<=10LOOPl_num:=DBMS_RANDOM.RANDOM;DBMS_OUTPUT.PUT_LINE(l_num);counter:=counter+1;ENDLOOP;END;/DBMS_RANDOM

包可用来生成随机整数触发器触发器是当特定事件出现时自动执行的存储过程特定事件可以是执行更新的DML语句和DDL语句触发器不能被显式调用触发器中不能使用事务触发器的功能：自动生成数据自定义复杂的安全权限提供审计和日志记录启用复杂的业务逻辑创建触发器的语法CREATE[ORREPLACE]TRIGGERtrigger_nameAFTER|BEFORE|INSTEADOF[INSERT][[OR]UPDATE[OFcolumn_list]][[OR]DELETE]ONtable_or_view_name[REFERENCING{OLD[AS]old/NEW[AS]new}][FOREACHROW][WHEN(condition)]pl/sql_block;触发器的组成部分3-1触发器由三部分组成：触发器语句（事件）定义激活触发器的DML事件和DDL事件触发器限制执行触发器的条件，该条件必须为真才能激活触发器触发器操作（主体）包含一些SQL语句和代码，它们在发出了触发器语句且触发限制的值为真时运行14触发器的组成部分更新表BEFORE触发器的工作原理激活触发器保存更新Oracle数据库触发器的组成部分Oracle数据库更新表保存更新激活触发器AFTER触发器的工作原理创建触发器CREATEORREPLACETRIGGERaiu_itemfileAFTERINSERTONitemfileFOREACHROWBEGINIF(:NEW.qty_hand=0)THENDBMS_OUTPUT.PUT_LINE('警告：已插入记录，但数量为零');ELSEDBMS_OUTPUT.PUT_LINE(‘已插入记录');ENDIF;END;/触发器类型触发器的类型有：触发器类型模式(DDL)触发器DML触发器数据库级触发器语句级(表级)触发器行级触发器INSTEADOF触发器触发器类型DDL触发器数据库级触发器DML触发器语句级触发器行级触发器INSTEADOF触发器在模式中执行DDL语句时执行在发生打开、关闭、登录和退出数据库等系统事件时执行在对表或视图执行DML语句时执行无论受影响的行数是多少，都只执行一次对DML语句修改的每个行执行一次用于用户不能直接使用DML语句修改的视图19系统事件触发器系统事件：数据库启动、关闭，服务器错误createtriggerad_startup

afterstartup

ondatabase

begin

--dosomestuff

end;

/必须具有Sysdba权限的用户才可以创建系统触发器。20模式级触发器用户事件：用户登陆、注销，create/alter/drop/grant/revoke/rename/truncate/logoff模式触发器SQL>CREATETABLEdropped_obj(obj_nameVARCHAR2(30),obj_typeVARCHAR2(20),drop_dateDATE);SQL>CREATEORREPLACETRIGGERlog_drop_objAFTERDROPONSCHEMABEGININSERTINTOdropped_objVALUES(ORA_DICT_OBJ_NAME,ORA_DICT_OBJ_TYPE,SYSDATE);END;/模式触发器表级触发器SQL>CREATEORREPLACETRIGGERtrgdemoAFTERINSERTORUPDATEORDELETEONorder_masterBEGINIFUPDATINGTHENDBMS_OUTPUT.PUT_LINE(‘已更新ORDER_MASTER中的数据');ELSIFDELETINGTHENDBMS_OUTPUT.PUT_LINE(‘已删除ORDER_MASTER中的数据');ELSIFINSERTINGTHENDBMS_OUTPUT.PUT_LINE(‘已在ORDER_MASTER中插入数据');ENDIF;END;/语句级触发器23表级触发器表级触发器可以验证用户是否有对表操作的权限验证在一段时间内无法对表进行操作24行级触发器new或者old只能用在行级triggerforeachrow表示行级触发器行级触发器只有Updating才有字段级的判断行级触发器的判断操作行级触发器的序列号行级触发器行级触发器SQL>CREATETABLETEST_TRG(IDNUMBER,NAMEVARCHAR2(20));SQL>CREATESEQUENCESEQ_TEST;SQL>CREATEORREPLACETRIGGERBI_TEST_TRGBEFOREINSERTORUPDATEOFIDONTEST_TRGFOREACHROWBEGINIFINSERTINGTHENSELECTSEQ_TEST.NEXTVALINTO:NEW.IDFROMDUAL;ELSERAISE_APPLICATION_ERROR(-20020,'不允许更新ID值！');ENDIF;END;/启用和禁用触发器删除触发器启用、禁用和删除触发器SQL>ALTERTRIGGERaiu_itemfileDISABLE;SQL>ALTERTRIGGERaiu_itemfileENABLE;