实验四 PLSQL高级编程

实验四PL/SQL高级编程

开发语言及实现平台或实验环境：

Oracle 10g

实践目的

(1) 掌握游标、存储过程、存储函数、包、触发器的基本作用。

(2) 掌握存储过程、存储函数、包、触发器的建立、修改、查看、删除操作。实验要求

(1) 记录执行命令和操作过程中遇到的问题及解决方法，注意从原理上解释原因。

(2) 掌握游标、存储过程、存储函数、包、触发器的命令。

实验内容

1．创建存储过程

(1) 将下列的未命名的PL/SQL，转换成存储过程，存储过程名自己设定，注意比较未命名的PL/SQL 与命名的PL/SQL 的差别，如没有where current of 是什么情况。

declare

cursor emp_cursor is select * from emp where deptno=10 for update;

begin

for emp_record in emp_cursor loop

dbms_output.put_line(emp_record.sal);

update emp set sal=sal*1.1 where current of emp_cursor;

end loop;

end;

/

（2）（3）任选一个

(2) 创建存储过程“dept_count_pro_学号后四位”，实现显示scott方案中dept 表中各本门编号与名称，并显示对应部门的员工姓名和工资。显示结果可如下图，也可自己设计：

(3) 创建存储过程“num_pro_学号后四位”，通过传入参数传入3个数，完成3 个数的从小到大排序，通过 3 个传出参数保存排序后的 3 个数，并执行该存储过程，显示排序结果。

(4)通过user_source 数据字典中查看存储过程。

2．创建函数

（1）（2）任选一个

(1) 创建存储函数“emp_fun_学号后四位”，通过传入参数传入员工的编号，根据传入的员工编号，检查该员工是否存在。如果存在，则返回员工的姓名，否则返回“此员工不存在”，并执行该存储函数。

(2) 假定某电信公司电话收费表call_fee_account，包括缴费号码（telno，字符型）、客户交费日期（pay_date，日期型）、话费（charge，数值型）和滞纳金（late_fee，数值型），不是每笔交费记录都有滞纳金。创建一个存储函数，返回指定日期的收费总和。

(3) 从user_source 数据字典中查看存储函数。

3．包

(1) 创建一个包，包体中包括上面创建过的一个过程，一个函数。

（2）创建一个包体。

（3）执行包。

（4）删除刚才建立的包名和包体。

DROP PACKAGE BODY 包名；

DROP PACKAGE 包名；

4．创建触发器

（1）新建一个部门平均工资表，编写触发器实现当雇员表中新增、删除数据或者修改工资时，重新统计各部门平均工资。

create table avg_sal(deptno ,avg_s )

as select deptno,avg(sal) from emp group by deptno;

参考代码：

create or replace trigger t

after insert or update or delete on emp

begin

delete from avg_sal;

for s in (select deptno,avg(sal) a from emp group by deptno) loop

insert into avg_sal values(s.deptno,s.a);

end loop;

end;

（2）创建一个替代触发器，通过更新视图来更新基本表（如向通过向视图插入一条记录，来实现对部门表和员工表插入数据的操作。

create view emp_dept (empno,ename,deptno,dname)

as select empno,ename,dept.deptno,dname

from emp,dept

where dept.deptno=emp.deptno;

参考代码：

create or replace trigger tr_v_e_d

instead of insert on emp_dept

for each row

begin

触发体;

end;

/

（3）（4）选做一个

create or replace trigger del_deptid

after delete on dept

for each row

begin

delete from emp where deptno=:old.deptno;

end del_deptid;

/

(3)利用SQL*Plus或iSQL*Plus创建行级触发器“update_row_tri_学号后四位”，当dept表的某一“deptno”值更改时，emp表中对应的“deptno”值也跟着进行相应的更改。更改“dept”表的某一“deptno”值，查看“emp”表中对应的“deptno”值是否发生变化。

(4)利用SQL*Plus 或iSQL*Plus 创建语句级触发器“delete_tri_学号后四位”，当删除dept表中某个部门编号时，将就emp表中该员工的所有信息一并删除。删除“dept”表中某个员工的信息，查看“emp”表是否还有该部门员工的信息。