Orale数据库实验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。