第五章：存储过程和触发器

🌈个人主页：小新_-

🎈个人座右铭：“成功者不是从不失败的人，而是从不放弃的人！”🎈

🎁欢迎各位→点赞👍 + 收藏⭐️ + 留言📝

🏆所属专栏：Oracle网络数据库 欢迎订阅，持续更新中~~~

✨让小新带着你快乐的学习吧~✨

一、存储过程

（一）存储过程的创建

1．以命令方式创建存储过程

2．以界面方式创建存储过程

（二）存储过程的调用

（三）存储过程的修改

二、触发器

（一）以命令方式创建触发器

1．创建DML触发器

编辑

2．创建替代触发器

3．创建系统触发器

（二）触发器的删除

1．以命令方式删除触发器

2．以界面方式删除触发器

存储过程是数据库对象之一，存储过程可以理解成数据库的子程序，在客户端和服务器端可以直接调用它。触发器是与表直接关联的特殊存储过程，是在对表记录进行操作时出发点的。

一、存储过程

在Oracle 11g中，可以在数据库中定义子程序，这种程序块称为存储过程（procedure）。它存放在数据字典中，可以在不同用户和应用程序之间共享，并可实现程序的优化和重用。存储过程的优点如下。

（一）存储过程的创建

1．以命令方式创建存储过程

创建存储过程使用CREATE PROCEDURE语句，语法格式为：

CREATE [OR REPLACE] PROCEDURE <过程名>  			/*定义过程名*/[ (<参数名> <参数类型> <数据类型> [ DEFAULT <默认值>] [, …n])]/*定义参数类型及属性*/
{ IS | AS }[<变量声明>]						/*变量声明部分*/BEGIN<过程体>					/*PL/SQL过程体*/END [<过程名>][;]

相关参数说明如下：

（1）过程名：存储过程名称要符合标识符规则，并且在所属方案中必须是唯一的。关键字OR REPLACE表示在创建存储过程时，如果已存在同名的过程，则重新创建。

（2）参数名：存储过程的参数名也要符合标识符规则，创建过程时，可以声明一个或多个参数，执行过程时应提供相对应的参数。存储过程的参数模式和函数参数一样，也有三种模式，分别为IN、OUT和IN OUT。

（3）DEFAULT：指定过程中IN参数的默认值，默认值必须是常量。

（4）过程体：其中包含PL/SQL语句块。

在存储过程的定义体中，不能使用下列对象创建语句：
CREATE VIEW
CREATE DEFAULT
CREATE RULE 
CREATE PROCEDURE
CREATE TRIGGER

【例1】创建一个简单的存储过程，输出hello world。

CREATE PROCEDURE proc
AS
BEGINDBMS_OUTPUT.PUT_LINE('hello world');
END;

【例2】创建存储过程，计算指定学生的总学分。

CREATE OR REPLACE PROCEDURE totalcredit( xh IN varchar2)
ASxf number;
BEGINSELECT 总学分	INTO xfFROM XSBWHERE 学号=xh AND rownum=1;DBMS_OUTPUT.PUT_LINE(xf);
END;

注意：在存储过程体中，不能使用SELECT语句直接查询，否则会出现编译错误。

【3】计算机某专业总学分大于50分的人数，该存储过程使用了一个输入（IN）参数和一个（OUT）参数。

2．以界面方式创建存储过程

如果要通过界面方式定义上面的存储过程count_grade，步骤如下。

（1）启动SQL Developer，选择myorcl连接的“过程”节点，右击鼠标，选择“新建过程”菜单项进入“创建 PL/SQL 过程”对话框，如图所示。

（2）在“名称”文本框中输入存储过程的名称，单击按钮添加一个参数，在“参数”选项页的“Name”栏中输入各参数名称，在“Type”栏中选择参数的类型，在“Mode”栏中选择参数的模式，在“Default Value”栏中输入参数默认值（如果有的话）。

（3）单击“确定”按钮，在出现的“COUNT_GRADE”过程的编辑框中编写过程语句块，如图7.2所示，单击“编译以进行调试”按钮完成过程的创建。

（二）存储过程的调用

调用存储过程一般使用EXEC语句，语法格式为：

[ { EXEC | EXECUTE } ]  <过程名> [ ( [<参数名> =>] <实参> | @<实参变量> [,…n]) ] [;]

说明：EXEC是EXECUTE的缩写，<参数名>为CREATE PROCUDURE中定义的参数名称。在传递参数的实参时，如果指定了变量名，该变量则用于保存OUT参数返回的值；如果省略“<参数名>=>”，则后面的实参顺序要与定义时参数的顺序一致。

【例4】调用【例1】中的存储过程proc。

EXEC proc;//或者
BEGINproc;
END;

【例5】从XSCJ数据库的XSB表中查询某人的总学分，根据总学分写评语。

CREATE OR REPLACE PROCEDURE update_info( xh in char )
ASxf number;
BEGINSELECT 总学分 INTO xfFROM XSBWHERE 学号=xh AND ROWNUM=1;IF xf>50 THENUPDATE XSB SET 备注= '三好学生' WHERE 学号=xh;END IF;IF xf<42 THENUPDATE XSB SET 备注= '学分未修满' WHERE 学号=xh;END IF;
END;

执行存储过程update_info：

EXEC update_info(xh=>'151242');

【例6】统计XSB表中男女同学的人数。

CREATE OR REPLACE PROCEDURE count_number( sex IN char, num OUT number )
AS
BEGINIF sex= '男' THENSELECT COUNT(性别) INTO numFROM XSBWHERE 性别= '男';ELSESELECT COUNT(性别) INTO numFROM XSBWHERE 性别= '女';END IF;
END;

在调用过程count_number时，需要先定义OUT类型参数，如下：

DECLARE girl_num number;
BEGINcount_number('女', girl_num);DBMS_OUTPUT.PUT_LINE(girl_num);
END;

（三）存储过程的修改

修改存储过程和修改视图一样，虽然也有ALTER PROCEDURE语句，但它是用于重新编译或验证现有过程的。如果要修改过程定义，仍然使用CREATE OR REPLACE PROCEDURE命令，语法格式一样。其实，修改已有过程本质就是使用CREATE OR REPLEACE PROCEDURE重新创建一个新的过程，只要保持名字与原来的过程相同即可。使用界面方式也可很方便地修改存储过程定义。在SQL Developer中，在“过程”节点下选择要修改的存储过程，右击鼠标，选择“编辑”菜单项，在打开的存储过程编辑窗口中修改定义后单击“编译以进行调试”按钮即可。

当某个过程不再需要时，应将其删除，以释放它占用的内存资源。删除过程的语法格式为：

DROP PROCEDURE [<用户方案名>.] <过程名>;

【例7】删除XSCJ数据库中的count_number存储过程。

DROP PROCEDURE count_number;

也可以使用界面方式删除存储过程，具体操作如图所示，请读者自行尝试。

二、触发器

触发器是被指定关联到一个表的数据对象，它不需要调用，当对一个表的特别事件出现时，它就会被激活。触发器的代码也是由SQL语句组成的，因此用在存储过程中的语句也可以用在触发器的定义中。触发器是一类特殊的存储过程，与表的关系密切，用于保护表中的数据。当有操作影响到触发器保护的数据时，触发器将自动执行。

（一）以命令方式创建触发器

1．创建DML触发器

创建DML触发器语法格式为：

CREATE [OR REPLACE] TRIGGER [<用户方案名>.] <触发器名>{ BEFORE∣AFTER∣INSTEAD OF }			/*定义触发动作*/{ DELETE | INSERT | UPDATE [ OF <列名>[,…n] ]}		/*定义触发器种类*/[OR { DELETE | INSERT | UPDATE [ OF <列名>[,…n] ]}]ON  {<表名>∣<视图名>}           	  	 	/*在指定表或视图中建立触发器*/[ FOR EACH ROW [ WHEN(<条件表达式>) ] ]<PL/SQL语句块>

【例8】创建一个表table1，其中只有一列a。在表上创建一个触发器，每次插入操作时，将变量str的值设为“TRIGGER IS WORKING”并显示。

创建表table1：

CREATE TABLE table1(a number);

创建INSERT触发器table1_insert：

CREATE OR REPLACE TRIGGER table1_insertAFTER INSERT ON table1
DECLARE str char(100) :='TRIGGER IS WORKING';
BEGINDBMS_OUTPUT.PUT_LINE(str);
END;

向table1中插入一行数据：

INSERT INTO table1 VALUES(10);

【例9】在XSCJ数据库中增加一个日志表XSB_HIS，表结构和XSB表相同，用来存放从XSB表中删除的记录。创建一个触发器，当XSB表被删除一行时，把删除的记录写到XSB_HIS表中。

CREATE OR REPLACE TRIGGER del_xsBEFORE DELETE ON XSB FOR EACH ROW
BEGININSERT INTO XSB_HIS (学号, 姓名, 性别, 出生时间, 专业, 总学分, 备注)VALUES(:OLD.学号,:OLD.姓名, :OLD.性别, :OLD.出生时间, :OLD.专业, :OLD.总学分, :OLD.备注);
END;

OLD修饰访问操作完成前列的值。触发器建立后向XSB表中插入一行数据，之后查看XSB_HIS表中并没有添加了该行数据，这是因为触发器中的DML语句并没有使用提交语句提交，但触发器中不能使用COMMIT语句，所以需要定义自治事务来提交

【例10】利用触发器在数据库XSCJ的XSB表执行插入、更新和删除三种操作后给出相应提示。

CREATE TRIGGER cue_xsAFTER INSERT OR UPDATE OR DELETE ON XSB FOR EACH ROW
DECLAREInfor char(10);
BEGINIF INSERTING THEN				/*INSERT语句激活了触发器*/Infor:= '插入';ELSIF UPDATING THEN			/*UPDATE语句激活了触发器*/Infor:= '更新';ELSIF DELETING THEN			/*DELETE语句激活了触发器*/Infor:= '删除';END IF;DBMS_OUTPUT.PUT_LINE(Infor);
END;

说明：程序中使用条件谓词IF通过谓词INSERTING、UPDATING和DELETING分别判断是否是INSERT、UPDATE和DELETE激活了触发器。另外，在UPDATE触发器中使用UPDATING(列名)的形式来判断特定列是否被更新。

2．创建替代触发器

创建替代触发器使用INSTEAD OF关键字，一般用于对视图的DML触发。由于视图有可能由多个表进行关联而成，因而并非所有的关联都是可更新的。INSTEAD OF触发器触发时只执行触发器内部的SQL语句，而不执行激活该触发器的SQL语句。例如，若在一个多表视图上定义了INSTEAD OF INSERT触发器，视图各列的值可能允许为空也可能不允许。若视图某列的值不允许为空，则INSERT语句必须为该列提供相应的值。

【例7.11】在XSCJ数据库中创建视图stu_view，包含学生学号、专业、课程号、成绩。该视图依赖于表XSB和CJB，是不可更新视图。可以在视图上创建INSTEAD OF触发器，当向视图中插入数据时分别向表XSB和CJB插入数据，从而实现向视图插入数据的功能。

首先创建视图：

CREATE VIEW stu_view
AS 
SELECT XSB.学号, 专业, 课程号, 成绩FROM XSB, CJBWHERE XSB.学号=CJB.学号

创建INSTEAD OF触发器：

CREATE TRIGGER InsteadTrigINSTEAD OF INSERT ON stu_view FOR EACH ROW
DECLARE xm char(8);xb char(2);cssj date;
BEGINxm:='徐鹤';xb:= '男';cssj:= '1997-07-28';INSERT INTO XSB(学号, 姓名, 性别, 出生时间, 专业) VALUES(:NEW.学号,xm, xb, cssj, :NEW.专业);INSERT INTO CJB VALUES(:NEW.学号, :NEW.课程号, :NEW.成绩);
END;

向视图插入一行数据：

INSERT INTO stu_view VALUES('151116', '计算机', '101', 85 );

查看数据是否插入：

SELECT * FROM stu_view WHERE 学号= '151116';

执行结果如图所示。

查看与视图关联的XSB表的情况：

SELECT * FROM XSB WHERE 学号= '151116';

执行结果如图所示。

3．创建系统触发器

系统触发器可以在DDL或数据库系统事件上被触发。DDL指的是数据定义语句，如CREATE、ALTER和DROP等。而数据库系统事件包括数据库服务器的启动（STARTUP）、关闭（SHUTDOWN）、出错（SERVERERROR）等。创建系统触发器的语法格式为：

CREATE OR REPLACE TRIGGER [<用户方案名>.] <触发器名>{ BEFORE︱AFTER }{ <DDL事件>︱<数据库事件> }ON { DATABASE︱[用户方案名.] SCHEMA }<触发器的PL/SQL语句块>

【例12】创建一个用户事件触发器，记录用户SYSTEM所删除的所有对象。首先以用户SYSTEM身份连接数据库，创建一个存储用户信息的表：

CREATE TABLE dropped_objects
(object_name varchar2(30),object_type varchar(20),dropped_date date
);

创建BEFORE DROP触发器，在用户删除对象之前记录到信息表dropped_objects中。

CREATE OR REPLACE TRIGGER dropped_obj_triggerBEFORE DROP ON SYSTEM.SCHEMA
BEGININSERT INTO dropped_objectsVALUES(ora_dict_obj_name, ora_dict_obj_type, SYSDATE);
END;

现在删除SYSTEM模式下的一些对象，并查询表dropped_objects：

DROP TABLE table1;
DROP TABLE table2;
SELECT * FROM dropped_objects;

以界面方式创建触发器

触发器也可以利用SQL Developer的界面方式创建。

（1）选择myorcl连接的“触发器”节点，右击鼠标，选择“新建触发器”菜单项，进入“创建触发器”窗口，如图所示。

（2）在“名称”栏中输入触发器名称，在“触发器”选项卡中的“触发器类型”下拉列表中选择触发依据，有“Table”“View”“SCHEMA”和“Database”等选项。例如，如果是在表中创建触发器，则这里就选择“TABLE”。可以在“表名”栏中选择触发器所在的表，选中“早于”或“晚于”选项对应BEFORE和AFTER关键字，勾选“插入”、“删除”和“更新”复选框对应触发事件，完成后单击“确定”按钮。

（3）在出现的触发器代码编辑框中编写触发器定义中的PL/SQL语句，完成后单击工具栏的“编译以进行调试”按钮完成触发器的创建，如图所示。

（二）触发器的删除

1．以命令方式删除触发器

删除触发器使用DROP TRIGGER语句，语法格式为：

DROP TRIGGER [<用户方案名>.] <触发器名>

【例13】删除触发器del_xs。

DROP TRIGGER del_xs;

2．以界面方式删除触发器

在“触发器”节点中选择要删除的触发器，右击鼠标，选择“删除触发器”菜单项，在弹出的“删除触发器”对话框中单击“应用”按钮即可。操作如图所示。

最后，感谢大家的观看