【Oracle】存储过程和触发器

一、存储过程

1、存储过程的创建

以命令方式创建存储过程

创建存储过程使用CREATE PROCEDURE语句，语法格式为：
CREATE [OR REPLACE] PROCEDURE <过程名>                   /*定义过程名*/
   [ (<参数名> <参数类型> <数据类型> [ DEFAULT <默认值>] [, …n])]    /*定义参数类型及属性*/
{ IS | AS }
   [<变量声明>]                                             /*变量声明部分*/
   BEGIN
      <过程体>                                             /*PL/SQL过程体*/
   END [<过程名>][;]

在视图(VIEW)中只能用AS不能用IS;
在游标(CURSOR)中只能用IS不能用AS。

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

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

rownum=1：查询第一行的记录。

【例3】

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

2、存储过程的调用

调用存储过程一般使用EXEC语句，语法格式为：
[ { EXEC | EXECUTE } ]  <过程名>
   [ ( [<参数名> =>] <实参> | @<实参变量> [,…n]) ] [;]
在PL/SQL块中也可以直接使用过程名来调用存储过程。

【例1】调用上面创建的存储过程proc。

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

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

3、存储过程的修改

修改已有过程本质就是使用CREATE OR REPLACE PROCEDURE重新创建一个新的过程，只要保持名字与原来的过程相同即可。

4、存储过程的删除

删除过程的语法格式为：
DROP PROCEDURE [<用户方案名>.] <过程名>;

二、触发器

触发器是一种特殊的数据库对象，它可以在特定的事件发生时自动执行一些操作，可以用于实现复杂的数据约束、数据验证、数据审计等功能。

（1）DML触发器。当数据库中发生数据操纵语言（DML）事件时将调用DML触发器。一般情况下，DML事件包括对表或视图的INSERT语句、UPDATE语句和DELETE语句，因此DML触发器又可分为三种类型：INSERT、UPDATE和DELETE触发器。
（2）替代触发器。由于在Oracle中不能直接对由两个以上的表建立的视图进行操作，所以给出了替代触发器。它是Oracle专门为进行视图操作的一种处理方式。
（3）系统触发器。系统触发器也是由相应的事件触发，但它的激活一般基于对数据库系统所进行的操作，如数据定义语句（DDL）、启动或关闭数据库、连接与断开、服务器错误等系统事件。

1、创建触发器

（1）创建DML触发器

创建触发器都使用CREATE RIGGER语句。

语法格式为：
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语句块>

说明：
（1）触发器名：触发器与过程名和包的名字不一样，它有单独的名字空间，因此触发器名可以和表名或过程名同名，但在同一个方案中的触发器名不能相同。
（2）BEFORE：触发器在指定操作执行前触发，如BEFORE INSERT表示在向表中插入数据前激活触发器。
（3）AFTER：触发器在指定操作都成功执行后触发，如AFTER INSERT表示向表中插入数据时激活触发器。不能在视图上定义AFTER触发器。
（4）INSTEAD OF：指定创建替代触发器，触发器指定的事件不执行，而执行触发器本身的操作。
（5）DELETE，INSERT，UPDATE：指定一个或多个触发事件，多个触发事件之间用OR连接。
（6）OF：指定在某列上应用UPDATE触发器，如果为多个列，则需要使用逗号分隔。
（7）FOR EACH ROW：在触发器定义中，如果未使用FOR EACH ROW子句则表示触发器为语句级触发器，触发器在激活后只执行一次，而不管这一操作将影响多少行。

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

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

OLD表示要被更改的原来的数据行

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

（3）创建替代触发器

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

首先创建视图：
CREATE VIEW stu_view
AS
 SELECT XSB.学号,专业,课程号,成绩
 FROM XSB, CJB
 WHERE XSB.学号=CJB.学号

创建INSTEAD OF触发器：

向视图插入一行数据：

INSERT INTO stu_view VALUES('151116', '计算机', '101', 85 );
查看数据是否插入：
SELECT * FROM stu_view WHERE 学号= '151116';

查看与视图关联的XSB表的情况：
SELECT * FROM XSB WHERE 学号= '151116';

2、启用和禁用触发器

Oracle提供了ALTER TRIGGER语句用于启用和禁用触发器，语法格式为：
ALTER TRIGGER [<用户方案名>.]<触发器名>
       DISABLE | ENABLE;

其中，DISABLE表示禁用触发器，ENABLE表示启用触发器。例如，要禁用触发器del_xs，使用如下语句：
ALTER TRIGGER del_xs DISABLE;
如果要启用或禁用一个表中的所有触发器，还可以使用如下的语法：
ALTER TABLE <表名>
   {    DISABLE | ENABLE    }
   ALL TRIGGERS;

3、触发器的删除

删除触发器使用DROP TRIGGER语句，语法格式为：
DROP TRIGGER [<用户方案名>.] <触发器名>

【例】删除触发器del_xs。