实验：游标与异常
实验目的
（1）掌握游标的基本操作。
（2）掌握游标的属性操作
（3）掌握参数化游标的使用。
（4）掌握循环游标的使用。
（5）掌握隐式游标的使用。
（6）掌握预定义异常的处理。
（7）理解自定义异常的处理。
实验内容
1、创建一个PL/SQL块，包括以下内容：
（a）声明一个游标emp_cur2，从EMP表中选择员工姓名、薪水和雇佣日期。
（b）从游标中检索每一行，显示每个雇员信息。
SQL> SET SERVEROUTPUT ON
SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD';
SQL> ed
已写入 file afiedt.buf
1 DECLARE
2 v_name emp.ename%type;
3 v_hiredate emp.hiredate%type;
4 v_sal emp.sal%type;
5 CURSOR emp_cur IS SELECT ename,hiredate,sal FROM emp;
6 BEGIN
7 OPEN emp_cur;
8 FETCH emp_cur INTO v_name,v_hiredate,v_sal;
9 DBMS_OUTPUT.PUT_LINE(v_name||' '||v_hiredate||' '||v_sal);
10 WHILE (emp_cur%FOUND) LOOP
11 FETCH emp_cur INTO v_name,v_hiredate,v_sal;
12 IF(emp_cur%FOUND) THEN
13 DBMS_OUTPUT.PUT_LINE(v_name||' '||v_hiredate||' '||v_sal);
14 END IF;
15 END LOOP;
16 CLOSE emp_cur;
17* END;
SQL> /
SMITH 1980-12-17 800
ALLEN 1981-02-20 1600
WARD 1981-02-22 1250
JONES 1981-04-02 2975
MARTIN 1981-09-28 1250
BLAKE 1981-05-01 2850
CLARK 1981-06-09 2450
SCOTT 1987-04-19 3000
KING 1981-11-17 5000
TURNER 1981-09-08 1500
ADAMS 1987-05-23 1100
JAMES 1981-12-03 950
FORD 1981-12-03 3000
MILLER 1982-01-23 1300
PL/SQL 过程已成功完成。
法二：
DECLARE
CURSOR emp_cur IS SELECT ename,hiredate,sal FROM emp;
BEGIN
FOR emp_test IN emp_cur LOOP
DBMS_OUTPUT.PUT_LINE(emp_test.ename||' '||emp_test.hiredate||' '||emp_test.sal);
END LOOP;
END;
2、将第1题的（b）改为：
（b）从游标中检索每一行，将在1981年工作的雇员工资增长200。（使用WHERE CURRENT OF子句）
extract(year from date)
DECLARE
CURSOR emp_cur IS SELECT ename,hiredate,sal FROM emp FOR UPDATE;
BEGIN
FOR emp_test IN emp_cur LOOP
IF EXTRACT(YEAR FROM emp_test.hiredate)=1981 THEN
UPDATE emp
SET sal=sal+200 WHERE CURRENT OF emp_cur;
DBMS_OUTPUT.PUT_LINE(emp_test.ename||' '||emp_test.hiredate||' '||emp_test.sal);
END IF;
END LOOP;
END;
ALLEN 1981-02-20 1600
WARD 1981-02-22 1250
JONES 1981-04-02 2975
MARTIN 1981-09-28 1250
BLAKE 1981-05-01 2850
CLARK 1981-06-09 2450
KING 1981-11-17 5000
TURNER 1981-09-08 1500
JAMES 1981-12-03 950
FORD 1981-12-03 3000
COMMIT;
SQL> SELECT ENAME,SAL,HIREDATE FROM EMP;
ENAME SAL HIREDATE
---------- ---------- ----------
SMITH 800 1980-12-17
ALLEN 1800 1981-02-20
WARD 1450 1981-02-22
JONES 3175 1981-04-02
MARTIN 1450 1981-09-28
BLAKE 3050 1981-05-01
CLARK 2650 1981-06-09
SCOTT 3000 1987-04-19
KING 5200 1981-11-17
TURNER 1700 1981-09-08
ADAMS 1100 1987-05-23
ENAME SAL HIREDATE
---------- ---------- ----------
JAMES 1150 1981-12-03
FORD 3200 1981-12-03
MILLER 1300 1982-01-23
已选择14行。
3、创建一个PL/SQL程序块。输出工资为某个值的员工姓名。
提示：1、可使用替换变量。
2、异常处理：
如果没有相应员工，则输出‘no such employee’；
如果相应员工多于一个，则输出’more than one employee’。
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 v_sal NUMBER:=&sal;
3 v_ename emp.ename%type;
4 BEGIN
5 SELECT ename INTO v_ename FROM emp WHERE sal=v_sal;
6 DBMS_OUTPUT.PUT_LINE('员工姓名：'||' '||v_ename||' '||'薪水'||' '||v_sal);
7 EXCEPTION
8 WHEN NO_DATA_FOUND THEN
9 DBMS_OUTPUT.PUT_LINE('no such employee');
10 WHEN TOO_MANY_ROWS THEN
11 DBMS_OUTPUT.PUT_LINE('more then one employee');
12 END;
13 /
输入 sal 的值: 2000
原值 2: v_sal NUMBER:=&sal;
新值 2: v_sal NUMBER:=2000;
no such employee
PL/SQL 过程已成功完成。
输入 sal 的值: 1300
原值 2: v_sal NUMBER:=&sal;
新值 2: v_sal NUMBER:=1300;
员工姓名： MILLER 薪水 1300
PL/SQL 过程已成功完成。
4、有如下代码：
Declare
cursor empcur is select ename from emp where sal>2000;
vname emp.ename%type;
Begin
fetch empcur into vname;
dbms_output.put_line(vname);
end;
其中存在异常，在不改动所给代码的前提下，请写好异常处理部分。
SQL> ED
已写入 file afiedt.buf
1 Declare
2 e_emp_remaining exception;
3 pragma exception_init(e_emp_remaining,-1001);
4 cursor empcur is select ename from emp where sal>2000;
5 vname emp.ename%type;
6 Begin
7 fetch empcur into vname;
8 dbms_output.put_line(vname);
9 exception
10 when e_emp_remaining then
11 dbms_output.put_line('游标无效');
12* end;
13 /
游标无效
PL/SQL 过程已成功完成。
