游标与异常处理

一．实验目标

掌握游标的基本用法与Oracle异常处理的方法，具备如下能力：

1、针对数据库应用领域的数据需求，设计出基于Oracle数据库的解决方案的能力；

2、承担Oracle数据库系统的实施、运行与维护等基本工作的能力。

二．实验项目

编写存储过程或存储函数完成以下各题，存储过程或函数依次命名为obj3_1、obj3_2、…

1．用显式游标输出emp表的所有信息。

SET SERVEROUTPUT ON;
create or replace procedure obj3_1
 as
 begin
DECLARE
   CURSOR obj3_1 IS SELECT EMPNO, ENAME,JOB,MGR ,HIREDATE, SAL,COMM FROM emp;
   emp_record obj3_1%ROWTYPE;
BEGIN
   OPEN obj3_1 ;
   LOOP
      FETCH obj3_1 INTO emp_record;
      EXIT WHEN obj3_1%NOTFOUND;
     dbms_output.put_line(emp_record.empno||','||emp_record.ename||','||emp_record.job||','||emp_record.mgr||','||emp_record.hiredate||','||emp_record.sal||','||emp_record.comm);
   END LOOP;
   dbms_output.put_line('row count:'||obj3_1%rowcount);
    CLOSE obj3_1;
END;
end;
.
/

2．用隐式游标输出dept表的所有信息。

SET SERVEROUTPUT ON;
create or replace procedure obj3_2
 as
 begin
DECLARE
   CURSOR obj3_2 IS SELECT DEPTNO, DNAME,CID,LOC,EMPS, INCOME FROM dept;
   dept_record obj3_2%ROWTYPE;
BEGIN
   FOR dept_record IN (SELECT DEPTNO, DNAME,CID,LOC,EMPS, INCOME FROM dept) 
   LOOP
     dbms_output.put_line(dept_record.DEPTNO||','||dept_record.DNAME||','||dept_record.CID||','||dept_record.LOC||','||dept_record.EMPS||','||dept_record.INCOME);
   END LOOP;
END;
end;
.
/
exec obj3_2

3．利用游标，修改员工工资。根据job不同，为员工增加相应的sal：

Job     raise
 clerk    +500
 salesman +750
 analyst  +1000
 otherwise +2000

create or replace procedure obj3_3
as
begin
DECLARE
  CURSOR obj3_3 IS SELECT * FROM emp FOR UPDATE;
   v_zl NUMBER;
   v_emp obj3_3%rowtype;
BEGIN
     FOR v_emp IN obj3_3 LOOP
        CASE v_emp.job 
             WHEN 'clerk' THEN v_zl:=500;
             WHEN 'salesman' THEN v_zl:=750;
             WHEN 'analyst' THEN v_zl:=1000;
             ELSE         v_zl:=2000;
       END CASE;
    UPDATE emp SET sal=sal+v_zl WHERE CURRENT OF obj3_3;
  END LOOP;
END; 
end;
.
/

4．用显式游标输出指定学年欠费的学生名单（含姓名、专业、欠费金额）。

create or replace procedure obj3_4 (ye 收费表.学年%type) as
  va 学生.姓名%type;
  vb 学生.专业%type;
  v1 number;
  v2 number;
  cursor obj3_4 is
    select 姓名,专业,应交学费,已交学