《Oracle数据库》实验指导书级

【实验目的】

(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

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),

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;

BEGIN

 DELETE dept_sal;  --触发时首先删除映射表信息

 FOR v_emp IN cur_emp LOOP

   --DBMS_OUTPUT.PUT_LINE(v_emp.deptno || v_emp.total_emp || v_emp.total_sal);

   --插入数据

   INSERT INTO dept_sal

    VALUES(v_emp.deptno,v_emp.total_emp,v_emp.total_sal);

 END LOOP;

END;

/

--对emp表进行DML操作

INSERT INTO emp(empno,deptno,sal) VALUES('123','10',10000);

SELECT * FROM dept_sal;

DELETE EMP WHERE empno=123;

SELECT * FROM dept_sal;

4、创建触发器，它记录表的删除数据

--创建表

CREATE TABLE employee

(

 id    VARCHAR2(4)  NOT NULL,

 name VARCHAR2(15) NOT NULL,

 age  NUMBER(2)     NOT NULL,

 sex  CHAR          NOT NULL

);

DESC employee;

--插入数据

INSERT INTO employee VALUES('e101','zhao',23,'M');

INSERT INTO employee VALUES('e102','jian',21,'F');

--创建记录表

CREATE TABLE old_employee AS

 SELECT * FROM employee;

DESC old_employee;

--创建触发器

CREATE OR REPLACE TRIGGER tig_old_emp

AFTER DELETE ON employee  --

FOR EACH ROW  --语句级触发，即每一行触发一次

BEGIN

 INSERT INTO old_employee

   VALUES(:old.id,:old.name,:old.age,:old.sex);  --:old代表旧值

END;

/

--下面进行测试

DELETE employee;

SELECT * FROM old_employee;

5、创建触发器，比较emp表中更新的工资。

CREATE OR REPLACE TRIGGER sal_emp

BEFORE UPDATE ON emp

FOR EACH ROW

BEGIN

IF :OLD.sal > :NEW.sal THEN

   DBMS_OUTPUT.PUT_LINE('工资减少');

ELSIF :OLD.sal < :NEW.sal THEN

   DBMS_OUTPUT.PUT_LINE('工资增加');

 ELSE

   DBMS_OUTPUT.PUT_LINE('工资未作任何变动');

 END IF;

 DBMS_OUTPUT.PUT_LINE('更新前工资 ：' || :OLD.sal);

 DBMS_OUTPUT.PUT_LINE('更新后工资 ：' || :NEW.sal);

END;

/

--执行UPDATE查看效果

UPDATE emp SET sal = 3000 WHERE empno = '7788';

6、需要对在表上进行DML操作的用户进行安全检查，看是否具有合适的。

Create table foo(a number);

Create trigger biud_foo

Begin

  If user not in (‘DONNY’) then

Raise_application_error(-20001, ‘You don’t have access to modify this table.’);

End if;

End;

/

即使SYS，SYSTEM用户也不能修改foo表。

写存储过程，显示所指定雇员名所在的部门名和位置。

CREATE OR REPLACE PROCEDURE DeptMesg(pename emp.ename%TYPE,

pdname OUT dept.dname%TYPE,ploc OUT dept.loc%TYPE) AS

BEGIN

SELECT dname,loc INTO pdname,ploc 

FROM emp,dept

WHERE emp.deptno=dept.deptno AND emp.ename=pename;

END;

/

VARIABLE vdname VARCHAR2(14);

VARIABLE vloc VARCHAR2(13);

EXECUTE DeptMesg('SMITH',:vdname，:vloc);

PRINT vdname vloc;

1、定义一个为修改职工表（emp）中某职工工资的存储过程子程序，职工名作为形参,若该职工名在职工表中查找不到，就在屏幕上提示“查无此人”然后结束子程序的执行；否则若工种为MANAGER的，则工资加＄1000；工种为SALESMAN，工资加＄500；工种为ANALYST，工资加＄200，否则工资加＄100。

create or replace procedure xggz(name varchar2)  is

k_job emp.job%type; 

addsal emp.sal%type;

begin

select job into k_job from emp where ename=name;

if  k_job=’MANAGER’   then  

addsal:=1000;

elsif  k_job=’SALESMAN’ then 

  addsal:=500;

elsif  k_job=’ANALYST’  then  

addsal:=200;

else

addsal:=100;

end if;

update emp set  sal=sal+addsal  where  ename=name;

exception

when no_data_found then

dbms_output.put_line(‘查无此人”);

end;

2、通过dept表查询出所有部门号,对每个部门雇员的工资进行调整，将工资高于（包含$2000）$2000的雇员每人增加$500,将工资低于$2000的雇员每人增加到$2000。但应注意雇员工资调整后不应大于$10000,否则显示出错信息,并退出程序。并统计显示各部门人数及工资调整后的总和。

declare 

cursor dept_cursor is

select distinct deptno from dept;

cursor emp_cursor(v_deptno emp.deptno%type)is

select ename,sal from emp 

where deptno=v_deptno;

allsal number(5):=0;

allemp number(3):=0;

err exception;

begin

for deptrec in dept_cursor loop

 for emprec in emp_cursor(deptrec.deptno) loop

  if emprec.sal>=2000 then emprec.sal:=emprec.sal+500;

  else emprec.sal:=2000;

  end if;

 if emprec.sal>10000 then raise err;

 end if;

updata emp set sal=emprec.sal where ename=emprec.ename;

allsal:=allsal+emprec.sal;

allemp:=emp_cursor%rowcount;

end loop;

dbms_output.put_line(deptrec.deptno,allemp,allsal);

allsal:=0;

end loop;

commit work;

exception

when err then

dbms_output.put_line('发现有职工的工资大于10000');

rollback work;

when others then

dbms_output.put_line('发现其他类型错误');

rollback work;

end;

实验四     PL/SQL子程序 （2学时）

【实验目的】

 掌握创建PL/SQL子程序的方法。

(2) 掌握利用PL/SQL子程序提高代码重用性的方法。

【实验内容】

一、PL/SQL子程序概述

在PL/SQL中，子程序是命名的PL/SQL块，它可以带参数，能被PL/SQL块等调用。子程序有两种：过程和函数。一般可使用一个过程执行某种特定的操作，使用一个函数完成某种计算获得一个值。

利用过程和函数子程序，用户可以对自己的PL/SQL块命名以创建模块代码，提高对代码的重用。

子程序与未命名的或无名的PL/SQL块一样，也由三部分组成：说明部分、可执行部分和可选的异常处理部分。各部分的内容分别与PL/SQL块相同。在子程序和PL/SQL块的说明部分，除说明类型、变量、常量、游标和异常外，还可以说明子程序，即嵌套子程序。该嵌套子程序的使用范围仅限于说明它的当前模块。

1、定义过程子程序

定义过程的语法格式为：

PROCEDURE 过程名[ (参数表)] IS

说明部分

BEGIN

执行部分

EXCEPTION

  异常处理程序

END;

每个参数的说明格式为：

参数名[IN或OUT或IN OUT]数据类型[:=或DEFAULT值]

参数有以下三种类型：

（1）IN参数类型：表示输入给过程的参数。

（2）OUT参数类型：表示参数在过程中将被赋值，可以传给过程体的外部。

（3）IN OUT参数类型：表示该类参数既可以向过程体传值，也可以在过程体中赋值，以便向过程体外传值。

2、定义函数子程序

  函数子程序类似于过程子程序，它具有一个函数说明和一个函数体。过程的函数之间的主要差异在于：函数返回一个计算值。

  定义函数的语法格式为：

FUNCTION 函数名[ (参数表)] 

RETURN 返回值类型 IS

说明部分

BEGIN

执行部分（至少有一个RETURN语句返回一个值）

EXCEPTION

  异常处理程序

END;

二、实验内容

1、设计一个过程子程序，根据输入职工所在的部门号和职工名来修改该职工的工资；若该职工的部门号为10，则工资加$100；若部门号为20，则工资加￥300；否则工资加￥200.（设部门号与职工名作为过程的输入形式参数）

程序的代码如下：

PROCEDURE rais_sal(dept_no integer,v_name varchar2) IS

  addsal real;

  v_sal number;

  salary_mis EXCEPTION;

BEGIN

select sal into v_sal from emp

where ename=v_name and deptno=dept_no;

IF v_sal IS NULL THEN RAISE salary_mis;

elsif dept_no=10 then addsal:=100;

elsif dept_no=20 then addsal:=300;

else addsal:=200;

END IF;

update emp

 set sal=sal+addsal

where ename=v_name and deptno=dept_no;

COMMIT WORK;

EXCEPTION

WHEN  NO_DATA_FOUND THEN

 dbms_output.putline(’没有找到职工’||v_name);

WHEN salary_mis THEN

 dbms_output.putline(v_name||’工资是空的，有错误！’);

WHEN Others THEN

 dbms_output.putline(’发现其他错误！’);

END rais_sal;  /*在END后，可以给出本过程名，表示过程到此结束*/

过程调用为一个PL/SQL语句，其语句调用形式为：

rais_sal(10,’SCOTT’);

2、定义一个函数子程序，根据输入的职工号计算该职工的年收入总额。

程序如下：

FUNCTION compsumal(v_empno number)

RETURN real IS /*函数返回值是实型值*/

sum_sal real;

BEGIN

  select 12*(sal+nvl(comm,0)) INTO sum_sal from emp

/* nvl(参数1,参数2) 为空值处理函数参见课本P88* /

where emono= v_empno;

RETURN(sum_sal);

EXCEPTION

WHEN  NO_DATA_FOUND THEN

 dbms_output.putline(’没有找到职工!’);

return(-1);

WHEN Others THEN

 dbms_output.put_line(’发现其他错误！’);

  return(-2);

END compsumal; /*在END 后的函数名compsumal 是可选的，可以省略*/

可用如下PL/SQL赋值语句调用上述函数：

y_sum:= compsumal(7788);

在主调函数中可以对y_sum的值进行判断。如果是-1，说明未找到该职工，不能计算；如果是-2，说明有其他错误，否则就是要计算该职工的年收入总额。

3、写过程子程序，以雇员名为参数，显示所指定雇员名所在的部门名和位置，并调用这个子程序。

4、写函数子程序，以部门号为参数，计算该部门职工的总人数，并调用这个子程序。