Oracle数据库实验-PLSQL游标、过程、函数、包的使用

Oracle数据库基础
实验5 PL/SQL游标、过程、函数、包的使用
【实验学时】
2学时
【实验目的】
1.了解PL/SQL语言中显式游标和隐式游标的概念和属性。

2.了解显式游标和隐式游标的差异。

3.掌握PL/SQL中cursor for循环的使用方法。

4.学会在PL/SQL程序中使用游标来解决实际问题。

5.学会在PL/SQL程序中进行异常处理的方法。

6.练习在PL/SQL中自定义异常。

7.理解过程和函数的作用。

8.学会编写过程、函数、包，并加以调用。

【实验内容】
1.用显式游标编写程序，程序的功能是：计算每一个部门的平均工
资。

注意异常的处理（例如，分母为0的异常）。

已写入文件afiedt.buf
1 declare
2 cursor c_dept is select * from dept order by deptno;
3 cursor c_emp(p_dept emp.deptno%type)is select
ename,sal from emp where deptno=p_dept
4 order by ename;
5 r_dept dept%rowtype;
6 v_ename emp.ename%type;
7 v_salary emp.sal%type;
8 v_tot_salary emp.sal%type;
9 begin
10 open c_dept;
11 loop
12 fetch c_dept into r_dept;
13 exit when c_dept%notfound;
14
dbms_output.put_line('department:'||r_dept.deptno||'-'||r_de
pt.dname);
15 v_tot_salary:=0;
16 open c_emp(r_dept.deptno);
17 loop
18 fetch c_emp into v_ename,v_salary;
19 exit when c_emp%notfound;
20
dbms_output.put_line('name:'||v_ename||'salary:'||v_salary);
21 v_tot_salary:=v_tot_salary+v_salary;
22 end loop;
23 close c_emp;
24 dbms_output.put_line('total salary for
dept:'||v_tot_salary);
25 end loop;
26 close c_dept;
27* end;
SQL> /
PL/SQL 过程已成功完成。

2.用cursor for编写上题的程序。

1 declare
2 cursor c_dept is select deptno,dname from dept order
by deptno;
3 cursor c_emp(p_dept emp.deptno%type)is
4 select ename,sal
5 from emp
6 where deptno=p_dept
7 order by ename;
8 v_tot_salary emp.sal%type;
9 begin
10 for r_dept in c_dept loop
11
dbms_output.putline('department:'||r_dept.deptno||'-'||r_dept.e name);
12 v_tot_salary:=0;
13 for r_emp in c_emp(r_dept.deptno)loop
14
dbms_output.put_line('name:'||r_emp.ename||'salary:'||r_emp.sa l);
15 v_tot_salary:=v_tot_salary+r_emp.sal;
16 end loop;
17 dbms_output.put_line('total salary for
dept:'||v_tot_salary);
18 end loop;
19* end;
QL> /
dbms_output.putline('department:'||r_dept.deptno||'-'||r_dept.e
name);
3.创建两个表格，myemp和mybonus表，分别与员工emp表和津
贴bonus表的结构及数据相同。

要求用游标完成操作：取出
myemp表中工资大于1000元的记录，将其工资的30%作为奖金插入到mybonus表中。

create or replace procedure insertjintie() is
CURSOR emp IS SELECT 工号，姓名，工资FROM 职工表
where 工资>1000；
begin
for v_emp in emp loop
insert into 津贴表(工号，工资) values(v_emp.工号，v_emp.工资*0.3);
end loop;
commit;
exception when others then
rollback;
dbms_output.put_line(sqlerrm);
end insertjintie;
4.定义一个存储过程,使用游标方式,根据员工编号参数,查询并打印
该员工的下属的姓名,职位,工资等信息。

1.已写入文件afiedt.buf
2.
3. 1 create or replace procedure
4. 2 emp_empno(v_empno emp.empno%type)
5. 3 as
6. 4 v_emp emp%rowtype;
7. 5 empno_out_of_range exception;
8. 6 begin
9.7 if v_empno<=7000 or v_empno>=8000
then
10.8 raise empno_out_of_range;
11.9 end if;
12.10 select *;
13.11 into v_emp
14.12 where empno=v_empno;
15.13 if SQL%found then
16.14
dbms_output.put_line(v_emp.ename,v_emp.job,v_emp.
sal);
17.15 end if;
18.16 exception
19.17 when empno_out_of_range then
20.18
raise_application_error(-20010,'empno is out of range');
21.19 when no_data_found then
22.20
dbms_output.put_line('too many record');
23.21* end emp_empno;
24.SQL> /
5.定义一个函数,根据部门编号参数,查询出该部门的员工总数,并作
为参数输出。

已写入文件afiedt.buf
1 DECLARE
2 type page_cursor is ref cursor;
3 pp_cursor page_cursor;
4 v_name emp.ename%type;
5 v_job EMP.JOB%type;
6 v_sal emp.sal%type;
7 begin
8 Pagepackage.GetRecord(2,5,pp_cursor);
9 loopfetch pp_cursor into v_name,v_job,v_sal;
10 exit when pp_cursor%notfound;
11 dbms_output.put_line('姓名:'||v_name||' 职
位:'||v_job||'薪水:'||v_sal);
12 end loop;
13* end;
SQL> /
6.定义一个包（自己命名），包有过程proc1和函数fun1，它们都完
成统计emp表中每个工种的员工数目。

分别调用包中的过程和函
数，实现相应功能。

【实验要求】
1、独自认真完成上述应用性实验的题目。

2、在规定时间内将“实验内容”填写实验报告并提交至ftp://172.16.94.8。