Oracle数据库中的存储过程和触发（七）

grade number;

BEGIN

select score into grade

from xsb

where sid=id;

DBMS_OUTPUT.PUT_LINE('学生'||to_char(id)||'的学分: '||to_char(grade));

END;

提示：“存储过程体”中不能直接使用select语句，否则会编译错误。如下面的例子：

SET SERVEROUTPUT ON;

CREATE OR REPLACE PROCEDURE fun

(id IN varchar2)

AS

BEGIN

select score

from xsb

where sid=id;

END;

（3）存储过程的调用 ①调用格式

[{EXEC | EXECUTE}]

[([=>]| @ [,...N])][;]

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

例子1：比如调用上面的过程，输出一句“今天很美好！”

EXEC prfun;

或者

BEGIN

prfun;

END;

例子2：调用上面的例子2

EXEC xsfun(id=>'151101');

例子3：将上面的例子2也可以改为下面的情况。

--将查询的值赋给grade作为值给“带出来”。

SET SERVEROUTPUT ON;

CREATE OR REPLACE PROCEDURE xscfun

(id IN varchar2,grade OUT number)

AS

BEGIN

select score into grade

from xsb

where sid=id;

END;

调用

DECLARE

grade number;

BEGIN

xscfun(id=>'151101',grade=>grade);

DBMS_OUTPUT.PUT_LINE('学生'||to_char('151101')||'的学分: '||to_char(grade));

END;

（4）存储的修改

修改存储过程和修改视图一样，虽然也有ALTER PROCEDURE语句，但它是用于重新编译或验证现有过 程的。如果要修改过程定义，仍然使用CREATE OR REPLACE PROCEDURE命令，语法格式一样。 其实，修改已有过程本质就是使用CREATE OR REPLEACE PROCEDURE重新创建一个新的过程，只要保 持名字与原来的过程相同即可

（5）删除存储过程 ①命令删除格式

DROP PROCEDURE [.];

②可视化删除方式

3.触发器 （1）触发器

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

①DML触发器

当数据库中发生数据操 纵语言（DML）事件时将调用DML触发器。 一般情况下，DML事件包括用于表或视图 的INSERT语句、UPDATE语句和DELETE语 句，因此DML触发器又可分为三种类型： INSERT、UPDATE和DELETE触发器。

②替代触发器

由于在Oracle中 不能直接对由两个以上的表建立的 视图进行操作，所以给出了替代触 发器。它是Oracle专门为进行视图 操作的一种处理方式。

③系统触发器

系统触发器也由 相应的事件触发，但它的激活一般 基于对数据库系统所进行的操作， 如数据定义语句（DDL）、启动或关 闭数据库、连接与断开、服务器错 误等系统事件

（2）触发器的创建

CREATE [OR REPLACE] TRIGGER [.]

{BEFORE | AFTER | INSTED OF}

{DELETE | INSERT | UPDATE [OF [,...N]}

[OR {DELETE | INSERT | UPDATE [OF [,...N]]}]

ON {| }

[FOR EACH ROW[WHEN ()]]

下面的说明注意项看起来有点多，有些地方开始的时候不理解，可以直接跳过，以后再深入的时候可以反过来理解。

说明：

（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子句则表示触发器为语句级触发器，触发器 在激活后只执行一次，而不管这一操作将影响多少行。使用FOR EACH ROW子句则表示触发器为行级触发器Mssq触发器，行 级触发器在DML语句操作影响到多行数据时，触发器将针对每一行执行一次。

注意：

① 代码大小。触发器代码大 小必须小于32 KB。

② 触发器中有效语句可以包括DML语 句，但不能包括DDL语句。ROLLBACK、 COMMIT、SAVEPOINT也不能使用。

③ LONG、LONG RAW和 LOB的限制如下。

④ 引用包变量的限制。如果UPDATE或 DELETE语句检测到当前的UPDATE冲突， 则Oracle执行ROLLBACK到SAVEPOINT 上并重新启动更新，这样可能需要多 次才能成功。

例子1：向学生表中插入数据时，触发语句“The trigger is working!”.

第一步首先定义：

SET SERVEROUTPUT ON;

CREATE OR REPLACE TRIGGER xsbisert

AFTER INSERT ON XSB

DECLARE

V_value number;

BEGIN

DBMS_OUTPUT.PUT_LINE('The trigger is working!');

END;

第二步：向表中插入数据以触发触发器

insert into xsb

values('151303','赵州','男','1999-3-5','计算机',47);

提示：每一次向学生表中插入数据时，都会激活触发器，从而执行触发器。

例子2：将从学生表XSB中删除的数据的学号和姓名添加到CXSB表中。

首先创建表CXSB；

create table cxsb(

SID VARCHAR(20) NOT NULL PRIMARY KEY,

SNAME VARCHAR(20) NOT NULL

);

创建触发器：

CREATEORREPLACETRIGGERxsbDel

BEFOREDELETEONXSBFOREACHROW

DECLARE

PRAGMAAUTONOMOUS_TRANSACTION;

BEGIN

insertintocxsb

values(:OLD.sid,:OLD.sname);

--提交事务

commit;

END;

delete

fromxsb

wheresid='151302';

例子3：同时触发插入，更新和删除的触发器。

SET SERVEROUTPUT ON;

CREATE OR REPLACE TRIGGER IUD

AFTER INSERT OR DELETE OR UPDATE ON XSB FOR EACH ROW

DECLARE

info char(20);

BEGIN

--insert激活语句

IF INSERTING THEN

info:='insert';

--delete激活语句

ELSIF DELETING THEN

info:='delete';

--update激活语句

ELSIF UPDATING THEN

info:='update';

END IF;

DBMS_OUTPUT.PUT_LINE(info);

END;

insert into xsb values('151304','彭雪君','男','1999-5-6','计算机',56);

update xsb

set profession='通信工程'

where sid='151202';

delete

from xsb

where sid='151301';

（3）创建替代触发器

第一步：创建视图：

CREATE VIEW XSBview

AS

select sid,sname,score

from xsb;

select * from xsbview;

第二步：创建替代触发器

CREATE OR REPLACE TRIGGER viv

INSTEAD OF INSERT ON XSBview FOR EACH ROW

BEGIN

--触发触发器之后向学生表XSB中插入数据

insert into xsb

values(:NEW.sid,:NEW.sname,'男','1999-6-7','计算机',:NEW.score);

END;

第三步：向视图中插入语句

--向视图中插入数据，以此触发触发器，从而向学生表XSB中插入数据

insert into XSBview

values('151305','张华',55);

提示：向视图中插入数据的insert语句并没有执行，实际上执行插入操作的语句是insert of触发器中的SQL语句。

（4）创建系统触发器

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

语法格式：

CREATE OR REPLACE TRIGGER [.]

{BEFORE | AFTER}

{ | }

ON {DATABASE | [用户方案名.]SCHEMA}

说明：

DDL事件：可以是一个或多个DDL事件，事件 间用OR分开。激活DDL事件的语句主要是以 CREATE、ALTER、DROP等关键字开头的语句。数据库事件：可以是一个或 多个数据库事件，事件间用 OR分开，包括STARTUP、 SHUTDOWN、 SERVERERROR等。

DATABASE：表示是数据库级触发器，对应 数据库事件。而SCHEMA表示是用户级触发 器，对应DDL事件。

例子：创建一个用户事件触发器，记录用户SYSTEM所删除的对象。

第一步：首选i创建一个表，用于存储删除的对象。

CREATE TABLE NoteTab(

sname VARCHAR(20) NOT NULL PRIMARY KEY,

stype VARCHAR(20) NOT NULL,

stime date

);

第二步：创建系统触发器

CREATE OR REPLACE TRIGGER NoteTri

BEFORE DROP ON SCOTT.SCHEMA

BEGIN

insert into notetab

values(ora_dict_obj_name,ora_dict_obj_type,SYSDATE);

END;

提示：上面的ora_dict_obj_name,ora_dict_obj_type可以直接用，不需要声明；其中的SCOTT.SCHEMA表示我在SCOTT用户下面创建用户级的触发器。

第三步：

删除创建的表，触发删除触发器。

CREATE TABLE table1(id int);

DROP TABLE table1;

（5）可视化方式创建触发器

（6）禁用和启动触发器

启用和禁用触发器

语法格式：

ALTER TRIGGER [.]

DISABLE | ENABLE;

ALTER TRIGGER Notetab DISABLE;

如果禁用一个表中的所有触发器

ALTER TABLE

{DISABLE | ENABLE}

ALL TRIGGER；

（7）删除触发器

DROP TRIGGER [.]

或者使用下面可视化方式删除触发器

（编辑：威海站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!