您的当前位置:首页正文

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

2023-04-28 来源:客趣旅游网
实验三 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

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

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

Before insert or update or delete On 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);

6

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;

7

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;

8

实验四 PL/SQL子程序 (2学时)

【实验目的】 (1)掌握创建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;

9

二、 实验内容

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(’没有找到职工!’);

10

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、 写函数子程序,以部门号为参数,计算该部门职工的总人数,并调用这个子程序。

11

因篇幅问题不能全部显示,请点此查看更多更全内容