发布时间 : 星期一 文章《Oracle数据库》实验指导书级更新完毕开始阅读a4a24957336c1eb91a375de2
实验三 PL/SQL触发器和存储过程 (2学时)
【实验目的】
(1)了解触发器的类型。
(2)掌握PL/SQL触发器的使用方法。 (3)了解存储过程的使用方法。 (4)掌握存储过程的使用方法。 【实验内容】 一、 触发器概述 1. 触发器的类型
可以创建被如下语句所触发的触发器: (1)DML语句(DELETE,INSERT,UPDATE); (2)DDL语句(CREATE,ALTER, DROP);
(3)数据库操作(SERVERERROR,LOGON,LOGOFF,STARTUP,SHUTDOWN)。 2. 触发器的设计规则: (1)作用范围清晰
(2)不要让触发器去完成Oracle后台已经能够完成的功能 (3)限制触发器代码的行数 (4)不要创建递归的触发器
(5)触发器仅在被触发语句触发时进行集中的,全局的操作,同用户和数据库应用无关。 3. 触发器的创建
1)使用CREATE TRIGGER语句创建触发器
使用CREATE TRIGGER语句创建触发器的语句格式如下: CREATE [OR REPLACE] TRIGGER name {BEFORE|AFTER } {event [ OR ... ]} ON table [FOR[EACH ] {ROW|STATEMENT}]
[WHEN(condition)] plsql block|call procedures_statement 2)注意事项 (1)触发器可以声明为在对记录进行操作之前,在之前(检查约束之前和 INSERT,UPDATE 或 DELETE 执行前)或之后(在检查约束之后和完成 INSERT, UPDATE 或 DELETE 操作)触发.。
(2)一个 FOR EACH ROW 执行指定操作的触发器为操作修改的每一行都调用一次。 (3)SELECT 并不更改任何行,因此不能创建 SELECT 触发器。这种场合下规则和视图更适合。
(4)触发器和某一指定的表格有关,当该表格被删除时,任何与该表有关的触发器同样会被删除。
(5)在一个表上的每一个动作只能有一个触发器与之关联。
(6)在一个单独的表上,最多只能创建三个触发器与之关联,一个INSERT触发器,一个DELETE触发器和一个UPDATE触发器。 4. 触发器的修改和删除 删除触发器的语句格式为: DROP TRIGGER name ON table;
一个触发器由三部分组成:触发事件或语句、触发限制和触发器动作。触发事件或语句是指引起激发触发器的SQL语句,可为对一指定表的INSERT、UNPDATE或DELETE语句。
触发限制是指定一个布尔表达式,当触发器激发时该布尔表达式是必须为真。触发器作为过程,是PL/SQL块,当触发语句发出、触发限制计算为真时该过程被执行。 5. 实例讲解Oracle数据库自带的几个触发器
Oracle数据库自带的几个触发器(最简单触发器格式)示例如下: --
create or replace trigger MDSYS.sdo_drop_user after drop on DATABASE declare
stmt varchar2(200); BEGIN
if dictionary_obj_type = 'USER' THEN
stmt := 'DELETE FROM SDO_GEOM_METADATA_TABLE ' || ' WHERE SDO_OWNER = ''' || dictionary_obj_name || ''' '; EXECUTE IMMEDIATE stmt; end if; end; ---
create or replace trigger SYS.aurora$server$startup after startup on database call dbms_java.server_startup --
create or replace trigger SYS.JIS$ROLE_TRIGGER$ after drop on database when (ora_dict_obj_type='ROLE') begin
sns_context.role_dropped(ora_dict_obj_name);
http_security_cascade.principal_dropped(ora_dict_obj_name); end;
--删除前备份数据的器
Create Or Replace Trigger YSPJ.T_Bill_reMain_Del Before delete On bill_remain FOR EACH ROW Begin
Insert into BILL_REMAIN_TIGER
Values(:old.BILL_REMAINID,:old.BILL_TYPEID,:old.REMAIN_NUM,:old.ADD_TIME,:old.ORG_ID,:old.STATE,:old.BILL_ID,'删除记录',Sysdate,user); End;
存储过程概述
6. 存储过程的创建
创建存储过程的语句如下:
CREATE[OR REPLACE] PROCEDURE<过程名> <参数1>,「方式l]<数据类型1>,
<参数2>,[ 方式2]<数据类型2>,??) IS|AS is_或as完全等价 BEGIN
2
PL/SQL过程体 END<过程名>
过程参数有以下三种类型:
(1)in参数类型:表示输入给过程的参数。
(2)out参数类型:表示参数在过程中将被赋值,可以传给过程体的外部。
(3)in out参数类型:表示该类参数既可以向过程体传值,也可以在过程体中赋值,以便向过程体外传值。 7. 存储过程的调用
存储过程可以直接用EXECUT命令调用或PL/SQL程序块内部调用。 用EXECUT命令调用存储过程的格式如下: SQL>execute proc_name(par1,par2?);
存储过程也可以被另外的PL/SQL块调用,调用的语句是: declare par1,par2; begin
proc_name(par1,par2…); end;
在调用前要声明变量par1,par2 8. 存储过程的释放
当某个存储过程不再需要时,应将其从内存中删除,以释放它占用的内存资源。释放过程的语句格式如下:
SQL>drop procedure proc_name; proc_name为过程名。
1、编写一个数据库触发器,当任何时候某个部门从dept表中删除时,该触发器将从emp表中删除该部门的所有雇员。(要求:emp表、dept表均为复制后的表) CREATE OR REPLACE TRIGGER del_emp_deptno BEFORE DELETE ON dept FOR EACH ROW BEGIN
DELETE FROM emp WHERE deptno=:OLD.deptno; END;
2、创建触发器,当用户对test表执行DML语句时,将相关信息记录到日志表。 --创建测试表
CREATE TABLE test (
t_id NUMBER(4),
t_name VARCHAR2(20), t_age NUMBER(2), t_sex CHAR );
--创建记录测试表
CREATE TABLE test_log (
l_user VARCHAR2(15),
3
l_type VARCHAR2(15), l_date VARCHAR2(30) );
--创建触发器
CREATE OR REPLACE TRIGGER test_trigger
AFTER DELETE OR INSERT OR UPDATE ON test DECLARE
v_type test_log.l_type%TYPE; BEGIN
IF INSERTING THEN --INSERT触发 v_type := 'INSERT';
DBMS_OUTPUT.PUT_LINE('记录已经成功插入,并已记录到日志'); ELSIF UPDATING THEN --UPDATE触发 v_type := 'UPDATE';
DBMS_OUTPUT.PUT_LINE('记录已经成功更新,并已记录到日志'); ELSIF DELETING THEN v_type := 'DELETE';
DBMS_OUTPUT.PUT_LINE('记录已经成功删除,并已记录到日志'); END IF;
INSERT INTO test_log VALUES(user,v_type,
TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss')); END; /
--下面我们来分别执行DML语句
INSERT INTO test VALUES(101,'zhao',22,'M'); UPDATE test SET t_age = 30 WHERE t_id = 101; DELETE test WHERE t_id = 101; --然后查看效果
SELECT * FROM test; SELECT * FROM test_log;
3、创建触发器,它将映射emp表中每个部门的总人数和总工资。 --创建映射表
CREATE TABLE dept_sal AS
SELECT deptno,COUNT(empno) AS total_emp,SUM(sal) AS total_sal FROM emp GROUP BY deptno;
DESC dept_sal; --创建触发器
CREATE OR REPLACE TRIGGER emp_info
AFTER INSERT OR UPDATE OR DELETE ON emp DECLARE
CURSOR cur_emp IS
SELECT deptno,COUNT(empno) AS total_emp,SUM(sal) AS total_sal FROM emp GROUP BY deptno;
4