关♥生活，关注互联网。
《数据库(oracle 10g)开发技术》课程实验及参考答案
一、 实验目的
《数据库开发技术》课程之实验八
PL/SQL 的触发器与包
1． 掌握正确使用触发器的方法。
2． 掌握包的正确使用方法。
二、 实验内容和要求
1. 编写一个触发器，在 DEPT 表执行 INSERT 语句后被激发，此触发器将新部门的编号(deptno)、名称
(dname)及执行此操作的用户（USER）、当时的日期（SYSDATE）插入 N_DEPT 表{注：此表已建好，
表结构为 N_DEPT（DEPTNO NUMBER(4),DNAME VARCHAR2(10), UNAME VARCHAR2(20),
INDATE DATE）}。
Create table N_DEPT (DEPTNO NUMBER(4),DNAME VARCHAR2(10), UNAME VARCHAR2(20),
INDATE DATE);
CREATE OR REPLACE TRIGGER T_DEPT_INS
AFTER INSERT ON DEPT
FOR EACH ROW
BEGIN
INSERT INTO N_DEPT VALUES (:new.deptno, :new.dname, user, sysdate);
COMMIT;
END;
测试：
INSERT INTO DEPT VALUES(50, ‘DEVELOP’, ‘BEIJING’)
检查：
SELECT * FROM N_DEPT;
DEPTNO DNAME UNAME INDATE
———- ———- ——————– ————–
50 DEVELOP SCOTT 04-6 月 -10
2. 创建触发器 CHECK_SAL，禁示对职务为 CLERK 的雇员的工资修改值超出 1000 至 2000 的范围，即
CLERK 职务员工的修改后工资值只能在 1000~2000 之间。要求测试该触发器。
步骤 1：创建和编译以下触发器：
CREATE OR REPLACE TRIGGER check_sal
BEFORE
UPDATE OF sal ON emp
FOR EACH ROW
WHEN (old.job=’CLERK’)
BEGIN
IF :new.job=’CLERK’ AND (:new.sal<1000 OR :new.sal>2000) THEN
RAISE_APPLICATION_ERROR(-20001, ‘工资修改值超出 1000 至 2000 的范围, 操作取消！’);
END IF;
END;
步骤 2：在 EMP 表中修改记录，对触发器进行测试：
UPDATE emp SET sal=800 WHERE empno=7876;
执行结果：
UPDATE emp SET sal=800 WHERE empno=7876
*
ERROR 位于第 1 行:
ORA-20001: 工资修改值超出 1000 至 2000 的范围, 操作取消！
ORA-06512: 在”***.CHECK_SAL”, line 3
ORA-04088: 触发器 ‘***.CHECK_SAL’ 执行过程中出错
3. *编写一个管理雇员信息的包 emp_mgmt。包中有成员如下：
程序结构
类型
说明
Emp_count
init
tax_emp
Hire_emp
Fire_emp
公有变量 跟踪员工的总人数变化，插入和删除员工时要修改该变量的值
公有过程 初始化包，初始化员工人数和当前个人所得税率，建议有一个输入参数
p_tax，传入当前个人所得税率，暂定为工资的 8%。
公有函数 通过员工编号计算出员工应交个人所得税款
公有过程 通过员工编号插入员工
公有过程 通过员工编号删除员工
emp_tax_record 记录
用于游标 C_emp 的 RETURN（强类型游标）语句中
C_emp
游标
用于游标 FOR 循环中，会被过程 show_emp_tax 所使用
show_emp_tax
Exist_emp
公有过程 按工资升序输出所有雇员的应交所得税清单
私有函数 判断某个编号的员工是否存在，该函数会被 hire_emp 和 fire_emp 等过程调
用
sal_null
……
异常名
公有或私
有变量
工资为空值时的异常名
可加入你认为需要的各种变量，但在程序中要有变量用途的注释
步骤 1：创建包头和包体：
CREATE OR REPLACE PACKAGE emp_mgmt — 包头部分
IS
EMP_COUNT NUMBER(5);
TYPE emp_tax_record IS RECORD
— 员工总人数
(Empno NUMBER(4), Ename emp.ename%type, Salary NUMBER ); — 记录定义
CURSOR c_emp RETURN emp_tax_record; — 游标定义
Sal_null EXCEPTION; — 异常名定义
PROCEDURE INIT(p_tax NUMBER); — 初始化过程
FUNCTION tax_emp(p_Empno emp.empno%type) — 函数：计算员工应交个人所得税款
RETURN number;
PROCEDURE hire_emp (P_EMPNO NUMBER, P_ENAME VARCHAR2, P_JOB VARCHAR2,
P_SAL NUMBER);
PROCEDURE Fire_emp(P_EMPNO NUMBER);
— 插入员工
— 删除雇员
PROCEDURE show_emp_tax; –按工资升序输出所有雇员的应交所得税清单
END emp_mgmt;
执行结果：
程序包已创建。
CREATE OR REPLACE PACKAGE BODY emp_mgmt — 包体部分
IS
CURSOR c_Emp RETURN emp_tax_record IS
SELECT empno, ename, sal FROM emp ORDER BY sal;
V_tax NUMBER; — 存放当前个人所得税率
——————–私有函数：判断员工是否存在—————————-
FUNCTION EXIST_EMP(P_EMPNO IN emp.empno%type)
RETURN BOOLEAN –判断雇员是否存在的私有函数
IS
V_NUM int;
BEGIN
SELECT COUNT(*) INTO V_NUM FROM EMP WHERE EMPNO= P_EMPNO;
IF V_NUM=1 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END EXIST_EMP;
——————————- 初始化过程 —————————-
PROCEDURE INIT(p_tax NUMBER)
IS
BEGIN
SELECT COUNT(*) INTO EMP_COUNT FROM EMP;
V_tax:= p_tax;
DBMS_OUTPUT.PUT_LINE(‘当前个人所得税率：’||V_tax||’%’);
DBMS_OUTPUT.PUT_LINE(‘当前雇员总人数’||EMP_COUNT);
DBMS_OUTPUT.PUT_LINE(‘初始化过程已经完成！’);
END INIT;
——————公有函数：通过员工编号计算出员工应交个人所得税款—————————-
FUNCTION tax_emp (p_Empno emp.empno%type)
RETURN NUMBER — 定义该函数返回的数据类型，只能定义
IS — 数据类型，不能定义长度
v_Sal NUMBER;
v_ReturnValue NUMBER;
BEGIN
IF EXIST_EMP(P_EMPNO) THEN
SELECT sal INTO v_Sal FROM emp WHERE empno=p_Empno;
IF V_SAL IS NULL THEN
RAISE sal_null;
ELSE
v_ReturnValue:=v_Sal* V_tax;
RETURN v_ReturnValue; — 返回语句，确定该函数返回的值
END IF;
END IF;
Exception
when sal_null then
raise_application_error(-20001, p_empno||’号员工工资为空，无所得税！’);
when others then
raise_application_error(-20002,’程序运行过程中出现错误号为 ‘||sqlcode||’错误信息为’||sqlerrm||’的错
误!’);
END tax_emp;
——————公有过程：通过员工编号插入员工—————————-
PROCEDURE hire_emp (P_EMPNO NUMBER, P_ENAME VARCHAR2, P_JOB VARCHAR2,
P_SAL NUMBER)
IS
BEGIN
IF NOT EXIST_EMP(P_EMPNO) THEN
INSERT INTO emp (EMPNO,ENAME,JOB,SAL) VALUES (P_EMPNO, P_ENAME, P_JOB, P_SAL);
COMMIT;
EMP_COUNT:= EMP_COUNT + 1;
DBMS_OUTPUT.PUT_LINE(‘雇员’||P_EMPNO||’已插入!’);
DBMS_OUTPUT.PUT_LINE(‘当前雇员总人数’||EMP_COUNT);
ELSE
DBMS_OUTPUT.PUT_LINE(‘雇员’||P_EMPNO||’已存在,不能插入!’);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘雇员’||P_EMPNO||’插入失败!’);
END hire_emp;
——————公有过程：通过员工编号删除员工—————————-
PROCEDURE Fire_emp(P_EMPNO NUMBER)
IS
BEGIN
IF EXIST_EMP(P_EMPNO) THEN
DELETE FROM EMP WHERE EMPNO=P_EMPNO;
COMMIT;
EMP_COUNT:= EMP_COUNT – 1;
DBMS_OUTPUT.PUT_LINE(‘雇员’||P_EMPNO||’已删除!’);
DBMS_OUTPUT.PUT_LINE(‘当前雇员总人数’||EMP_COUNT);
ELSE
DBMS_OUTPUT.PUT_LINE(‘雇员’||P_EMPNO||’不存在，不能删除!’);
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘雇员’||P_EMPNO||’删除失败!’);
END Fire_emp;
——————公有过程：按工资升序输出所有雇员的应交所得税清单—————————-
PROCEDURE show_emp_tax
IS
BEGIN
FOR emp_tax_record IN c_Emp LOOP –输出雇员的所得税
DBMS_OUTPUT.PUT_LINE(emp_tax_record.empno||’, ‘|| emp_tax_record.ename||”’ tax is ‘
||TO_CHAR(tax_emp (emp_tax_record.Empno))||’.’);
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘当前雇员总人数：’||EMP_COUNT);
END show_emp_tax;
END emp_mgmt;
执行结果：
程序包主体已创建。
步骤 2：初始化包：
SET SERVEROUTPUT ON
EXECUTE emp_mgmt.INIT(8);
执行结果：
当前个人所得税率：8%
当前雇员总人数 16
初始化过程已经完成！16
步骤 3：按工资升序输出所有雇员的应交所得税清单：
EXECUTE emp_mgmt.INIT(8);
EXECUTE emp_mgmt.show_emp_tax;
执行结果：
7369, SMITH’ tax is 7744.
7900, JAMES’ tax is 9196.
7876, ADAMS’ tax is 10648.
7521, WARD’ tax is 12100.
7654, MARTIN’ tax is 12100.
7934, MILLER’ tax is 12584.
7844, TURNER’ tax is 14520.
7499, ALLEN’ tax is 15488.
7951, OLIVEN’ tax is 20328.
7782, CLARK’ tax is 23716.
7948, MIKE’ tax is 26136.
7698, BLAKE’ tax is 27588.
7566, JONES’ tax is 28798.
7788, SCOTT’ tax is 29040.
7902, FORD’ tax is 29040.
7839, KING’ tax is 48400.
当前雇员总人数: 16
PL/SQL 过程已成功完成。
步骤 4：对员工表插入一条新记录：
EXECUTE emp_mgmt.hire_emp (8001,’小王’,’CLERK’,1000);
执行结果：
雇员 8001 已插入!
当前雇员总人数 17
PL/SQL 过程已成功完成。
步骤 5：通过全局变量 EMP_COUNT 查看雇员人数：
BEGIN
DBMS_OUTPUT.PUT_LINE(‘当前雇员总人数：’||emp_mgmt.EMP_COUNT);
END;
显示结果为：
当前雇员总人数：17
PL/SQL 过程已成功完成。
步骤 6：删除员工表新插入的记录
EXECUTE emp_mgmt.fire_emp(8001);
执行结果：
雇员 8001 删除失败! — 因为存在禁止删除的触发器，要执行 drop trigger emp_test;
PL/SQL 过程已成功完成。
删除触发器后，再次执行：
EXECUTE emp_mgmt.fire_emp(8001);
执行结果：
雇员 8001 已删除!
当前雇员总人数 16
PL/SQL 过程已成功完成。
步骤 7：通过员工编号计算出员工应交个人所得税款
DECLARE
V_empno emp.empno%type;
BEGIN
V_empno:=7951;
DBMS_OUTPUT.PUT_LINE(V_empno ||’ 应交个人所得税款为：’|| emp_mgmt. tax_emp(V_empno));
END;
执行结果：
7951 应交个人所得税款为：20328
PL/SQL 过程已成功完成。
三、 实验报告
记录实验内容 1 和 2。记录实验过程中遇到的困难和解决问题的方法。
