第14章 DM数据库存储过程与触发器

主讲教师：时间：202x.xx.xx第14章存储过程和触发器

01存储过程概述02存储过程创建和调用目录CONTNETS05异常处理06知识点小结04创建触发器03触发器概述DM数据库允许用户使用DMSQL过程语言创建存储过程或存储函数。通常，将存储过程和存储函数统称为存储模块。DM数据库是一个具有主动特征的数据库管理系统，其主动特征包括约束机制和触发器机制。通过触发器机制，用户可以定义、删除和修改触发器。DM数据库自动管理和运行这些触发器，从而体现系统的主动性，方便用户使用。01存储过程概述

02存储过程创建和调用目录CONTNETS05异常处理06知识点小结04创建触发器03触发器概述存储过程概述

一个存储过程是一个可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看作是对编程中面向对象方法的模拟。它允许控制数据的访问方式。01存储过程概述02存储过程创建和调用目录CONTNETS05异常处理06知识点小结04创建触发器03触发器概述02PARTTWO存储过程创建和调用存储过程的创建存储过程的调用存储过程的编译和删除存储过程创建和调用

例：创建p_get_empinfo的存储过程。该过程有1个输入参数，1个输出参数，输入参数为员工编码in_empno，输出参数out_empinfp员工信息（员工姓名和员工薪水，逗号分割），如果程序出现异常则打印异常信息。存储过程创建和调用

代码如下：createorreplaceprocedurep_get_empinfo(in_empnoinint,out_empinfooutvarchar(100))isv_empnamevarchar(50);v_salaryint;e1exception;beginselectemployee_name,salaryintov_empname,v_salaryfromdmhr.employeewhereemployee_id=in_empno;存储过程创建和调用

续：ifv_salaryisnullthenraisee1;else

out_empinfo:=v_empname||','||v_salary;endif;exceptionwhene1thendbms_output.put_line(v_empname||'--'||'薪水为空');whenno_data_foundthenout_empinfo:='查无此人';dbms_output.put_line('查无此人.');whentoo_many_rowsthenout_empinfo:='员工编号重复';dbms_output.put_line('员工编号重复.');whenothersthenout_empinfo:=sqlcode||'--'||sqlerrm;dbms_output.put_line('sqlerrm(sqlcode):'||sqlerrm(sqlcode));end;02PARTTWO存储过程创建和调用存储过程的创建存储过程的调用存储过程的编译和删除存储过程创建和调用1.立即调用

如果存储过程需要设置参数，那么在调用存储模块的时候就已经为所有的IN、INOUT类型的参数进行了赋值，带有OUT属性的参数的值是通过取得存储过程结果集的方法获取的。

存储过程创建和调用2.参数调用

这种调用方法指的是在调用模块的时候，其参数值用问号来代替，发送给服务器之后，服务器返回参数的准备信息，用户依据服务器返回的参数描述信息进行参数绑定，然后执行，其参数的处理方法与普通的参数处理方法相同。

存储过程创建和调用例：调用创建的存储过程p_get_empinfo。

代码：

declarev_empnoint:=8001;v_empinfovarchar(100);

begincallp_get_empinfo(v_empno,v_empinfo);dbms_output.put_line(v_empinfo);

end;02PARTTWO存储过程创建和调用存储过程的创建存储过程的调用存储过程的编译和删除存储过程创建和调用

存储过程的编译和删除与函数的编译和删除过程相同，具体参见“删除存储函数”所讲内容。01存储过程概述02存储过程创建和调用目录CONTNETS05异常处理06知识点小结04创建触发器03触发器概述触发器概述

触发器是一种特殊的存储过程，它在创建后就存储在数据库中。触发器的特殊性在于它是建立在某个具体的表或视图之上的，或者是建立在各种事件前后的，而且是自动激发执行的，如果用户在这个表上执行了某个DML操作（INSERT、DELETE、UPDATE），触发器就被激发执行。01存储过程概述02存储过程创建和调用目录CONTNETS05异常处理06知识点小结04创建触发器03触发器概述创建触发器

触发器分为表触发器、事件触发器和时间触发器。表触发器是对表里数据操作引发的数据库的触发；事件触发器是对数据库对象操作引起的数据库的触发；时间触发器是一种特殊的事件触发器。

用户可使用触发器定义语句(CREATETRIGGER)在一张基表上创建触发器。并使其处于允许状态。04PARTFOUR创建触发器表触发器事件触发器时间触发器创建触发器用户可使用触发器定义语句(CREATETRIGGER)在一张基表上创建触发器。下面是表触发器定义语句的语法：CREATE[ORREPLACE]TRIGGER[<模式名>.]<触发器名>[WITHENCRYPTION]<触发限制描述>[REFERENCING<trig_referencing_list>][FOREACH{ROW|STATEMENT}][WHEN(<条件表达式>)]<触发器体><trig_referencing_list>::=<referencing_1>|<referencing_2><referencing_1>::=OLD[ROW][AS]<引用变量名>[NEW[ROW][AS]<引用变量>]<referencing_2>::=NEW[ROW][AS]<引用变量名>[OLD[ROW][AS]<引用变量名>]<触发限制描述>::=<触发限制描述1>|<触发限制描述2><触发限制描述1>::=<BEFORE|AFTER><触发事件列表>[LOCAL]ON<触发表名><触发限制描述2>::=INSTEADOF<触发事件列表>[LOCAL]ON<触发视图名><触发表名>::=[<模式名>.]<基表名><触发事件>::=INSERT|DELETE|{UPDATE|{UPDATEOF<触发列清单>}}<触发事件列表>::=<触发事件>|{<触发事件列表>OR<触发事件>}创建触发器

例：创建触发器tr_LogChanges记录表OTHER.READER发生的所有变化。除了记录操作信息外，还记录对表进行变更的用户名。该触发器的记录存放在表OTHER.READERAUDIT中。创建触发器代码：SETSCHEMAOTHER;CREATEORREPLACETRIGGERtr_LogChangesAFTERINSERTORDELETEORUPDATEONOTHER.READERFOREACHROWDECLAREv_ChangeTypeCHAR(1);BEGIN/*‘I’表示INSERT操作，’D’表示DELETE操作，’U’表示UPDATE操作*/IFINSERTINGTHENv_ChangeType:='I';创建触发器续：ELSIFUPDATINGTHENv_ChangeType:='U';ELSEv_ChangeType:='D';ENDIF;/*记录对Reader做的所有修改到表ReaderAudit中，包括修改人和修改时间*/INSERTINTOOTHER.READERAUDITVALUES(v_ChangeType,USER,SYSDATE,:old.reader_id,:,:old.age,:old.gender,:old.major,:new.reader_id,:,:new.age,:new.gender,:new.major);END;04PARTFOUR创建触发器表触发器事件触发器时间触发器创建触发器

用户可使用触发器定义语句(CREATETRIGGER)在数据库全局对象上创建触发器。下面是触发器定义语句的语法：CREATE[ORREPLACE]TRIGGER[<模式名>.]<触发器名>[WITHENCRYPTION]BEFORE|AFTER<触发事件子句>ON<触发对象名>[WHEN<条件表达式>]<触发器体><触发事件子句>:=<DDL事件子句>|<系统事件子句><DDL事件子句>:=<DDL事件>{OR<DDL事件>}<DDL事件>:=DDL|<CREATE|ALTER|DROP|GRANT|REVOKE|TRUNCATE|COMMENT><系统事件子句>:=<系统事件>{OR<系统事件>}<系统事件>:=LOGIN|LOGOUT|SERERR|<BACKUPDATABASE>|<RESTOREDATABASE>|AUDIT|NOAUDIT|TIMER|STARTUP|SHUTDOWN<触发对象名>:=[<模式名>.]SCHEMA|DATABASE创建触发器

例：创建事件触发器TR_EAF，使用事件函数记录数据库中的所有DDL操作。

代码：CREATETABLET_EAF( NINT, SQLTEXTVARCHAR, OBJECTNAMEVARCHAR(128), OBJECTTYPEVARCHAR(128), OBJECTOWNERVARCHAR(128));CREATEORREPLACETRIGGERTR_EAFBEFOREDDLONDATABASEDECLARENNUMBER;STR_STMTVARCHAR;SQL_TEXTDM_NAME_LIST_T;

创建触发器

续：BEGINN:=DM_SQL_TXT(SQL_TEXT);--N为占用嵌套表单元个数 FORIIN1..NLOOPSTR_STMT:=STR_STMT||SQL_TEXT(I);--STR_STMT为获取的DDL语句ENDLOOP;INSERTINTOT_EAFVALUES(N,STR_STMT,DM_DICT_OBJ_NAME,DM_DICT_OBJ_TYPE,DM_DICT_OBJ_OWNER);END;/执行建模式建表语句。createschemasystest;createtableT_systest(c1int);然后，可以在T_EAF中查询到相关的建模式、建表语句。SELECT*FROMT_EAF;创建触发器

查询结果：N SQLTEXT OBJECTNAME OBJECTTYPE OBJECTOWNER1 createschemasystest; SYSTEST SCHEMA SYSTEST 1 createtableT_systest(c1int);T_SYSTEST TABLE SYSDBA 04PARTFOUR创建触发器表触发器事件触发器时间触发器创建触发器

时间触发器属于一种特殊的事件触发器，它使得用户可以定义一些有规律性执行的、定点执行的任务，比如在晚上服务器负荷轻的时候通过时间触发器做一些更新统计信息的操作、自动备份操作等等，因此时间触发器是非常有用的。创建触发器

语法格式：创建触发器

例：定义时间触发器timer2屏幕上每隔一分钟输出一行“HELLOWORLD”。代码：

CREATEORREPLACETRIGGERtimer2

AFTERTIMERondatabase

foreach1dayforeach1minute

BEGIN print'HELLOWORLD';

END;01存储过程概述02存储过程创建和调用目录CONTNETS05异常处理06知识点小结04创建触发器03触发器概述

异常一般是在DMSQL程序执行发生错误时由服务器抛出，也可以在DMSQL块中由程序员在一定的条件下显式抛出。无论是哪种形式的异常，DMSQL程序的执行都会被中止，程序控制转至DMSQL程序的异常处理部分。程序员可以在异常处理部分编写一段程序对异常进行处理，以避免DMSQL程序的异常退出。异常处理05PARTFIVE异常处理定义异常异常的抛出内置函数SQLCODE和SQLERRM异常处理部分

为方便用户编程，DM提供了一些预定义的异常，这些异常与常见的DM错误相对应，如表所示。异常处理

例：定义一个存储过程抛出预定义异常和自定义异常。异常处理代码：05PARTFIVE异常处理定义异常异常的抛出内置函数SQLCODE和SQLERRM异常处理部分

DMSQL程序运行时如果发生错误，系统会自动抛出一个异常。

此外，程序员还可以使用RAISE主动抛出一个异常。例如，当程序运行并不违反数据库规则，但是不满足应用的业务逻辑时，可以主动抛出一个异常并进行处理。

使用RAISE抛出异常分为有异常名和无异常名两种情况。异常处理

有异常名

语法如下：

RAISE<异常名>

可以使用RAISE语句抛出一个系统预定义异常或用户自定义异常。异常处理

无异常名

如果没有在声明部分定义异常变量，也可以在执行部分使用DM提供的系统过程直接抛出自定义异常，语法如下：异常处理05PARTFIVE异常处理定义异常异常的抛出内置函数SQLCODE和SQLERRM异常处理部分

DMSQL程序提供了内置函数SQLCODE和SQLERRM，程序员可以在异常处理部分通过这两个函数获取异常对应的错误码和描述信息。SQLCODE返回错误码，为一个负数。而SQLERRM返回异常的描述信息，为字符串类型。异常处理

若异常为DM服务器错误，则SQLERRM返回该错误的描述信息，否则SQLERRM的返回值遵循以下规则：如果错误码在-15000至-19999间，返回'User-DefinedExcep