【训练6.1】 使用隐式游标的属性,判断对雇员工资的修改是否成功。
步骤1:输入和运行以下程序:
BEGIN
UPDATE emp SET sal=sal+100 WHERE empno=1234;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE('成功修改雇员工资!');
ROLLBACK;
ELSE
DBMS_OUTPUT.PUT_LINE('修改雇员工资失败!');
END IF;
END;
运行结果为:
修改雇员工资失败!
PL/SQL 过程已成功完成。
步骤2:将雇员编号1234改为7788,重新执行以上程序:
运行结果为:
成功修改雇员工资!
PL/SQL 过程已成功完成。
说明:本例中,通过SQL%FOUND属性判断修改是否成功,并给出相应信息。
=================================================================
【训练7.1】 用游标提取emp表中7788雇员的名称和职务。
DECLARE
v_ename VARCHAR2(10);
v_job VARCHAR2(10);
CURSOR emp_cursor IS
SELECT ename,job FROM emp WHERE empno=7788;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO v_ename,v_job;
DBMS_OUTPUT.PUT_LINE(v_ename||','||v_job);
CLOSE emp_cursor;
END;
执行结果为:
SCOTT,ANALYST
PL/SQL 过程已成功完成。?
说明:该程序通过定义游标emp_cursor,提取并显示雇员7788的名称和职务。
作为对以上例子的改进,在以下训练中采用了记录变量。
================================================================
【训练7.2】 用游标提取emp表中7788雇员的姓名、职务和工资。
DECLARE
CURSOR emp_cursor IS SELECT ename,job,sal FROM emp WHERE empno=7788;
emp_record emp_cursor%ROWTYPE;
BEGIN
OPEN emp_cursor;
FETCH emp_cursor INTO emp_record;
DBMS_OUTPUT.PUT_LINE(emp_record.ename||','|| emp_record.job||','|| emp_record.sal);
CLOSE emp_cursor;
END;
执行结果为:
SCOTT,ANALYST,3000
PL/SQL 过程已成功完成。?
说明:实例中使用记录变量来接收数据,记录变量由游标变量定义,需要出现在游标定义之后。
注意:可通过以下形式获得记录变量的内容:
记录变量名.字段名。
==================================================================
【训练7.3】 显示工资最高的前3名雇员的名称和工资。
DECLARE
V_ename VARCHAR2(10);
V_sal NUMBER(5);
CURSOR emp_cursor IS SELECT ename,sal FROM emp ORDER BY sal DESC;
BEGIN
OPEN emp_cursor;
FOR I IN 1..3 LOOP
FETCH emp_cursor INTO v_ename,v_sal;
DBMS_OUTPUT.PUT_LINE(v_ename||','||v_sal);
END LOOP;
CLOSE emp_cursor;
END;
执行结果为:
KING,5000
SCOTT,3000
FORD,3000
PL/SQL 过程已成功完成。
说明:该程序在游标定义中使用了ORDER BY子句进行排序,并使用循环语句来提取多行数据。
============================================================================
【训练8.1】 使用特殊的FOR循环形式显示全部雇员的编号和名称。
DECLARE
CURSOR emp_cursor IS
SELECT empno, ename FROM emp;
BEGIN
FOR Emp_record IN emp_cursor LOOP
DBMS_OUTPUT.PUT_LINE(Emp_record.empno|| Emp_record.ename);
END LOOP;
END;
执行结果为:
7369SMITH
7499ALLEN
7521WARD
7566JONES
PL/SQL 过程已成功完成。
? 说明:可以看到该循环形式非常简单,隐含了记录变量的定义、游标的打开、提取和关闭过程。Emp_record为隐含定义的记录变量,循环的执行次数与游标取得的数据的行数相一致。
==============================================================================
【训练8.2】 另一种形式的游标循环。
BEGIN
FOR re IN (SELECT ename FROM EMP) LOOP
DBMS_OUTPUT.PUT_LINE(re.ename);
END LOOP;
END;
执行结果为:
SMITH
ALLEN
WARD
JONES
说明:该种形式更为简单,省略了游标的定义,游标的SELECT查询语句在循环中直接出现。
==================================================================================
【训练9.1】 使用游标的属性练习。
DECLARE
V_ename VARCHAR2(10);
CURSOR emp_cursor IS
SELECT ename FROM emp;
BEGIN
OPEN emp_cursor;
IF emp_cursor%ISOPEN THEN
LOOP
FETCH emp_cursor INTO v_ename;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(to_char(emp_cursor%ROWCOUNT)||'-'||v_ename);
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE('用户信息:游标没有打开!');
END IF;
CLOSE emp_cursor;
END;
执行结果为:
1-SMITH
2-ALLEN
3-WARD
PL/SQL 过程已成功完成。
? 说明:本例使用emp_cursor%ISOPEN判断游标是否打开;使用emp_cursor%ROWCOUNT获得到目前为止FETCH语句返回的数据行数并输出;使用循环来获取数据,在循环体中使用FETCH语句;使用emp_cursor%NOTFOUND判断FETCH语句是否成功执行,当FETCH语句失败时说明数据已经取完,退出循环。
【练习1】去掉OPEN emp_cursor;语句,重新执行以上程序。
=========================================================
【训练10.1】 带参数的游标。
DECLARE
V_empno NUMBER(5);
V_ename VARCHAR2(10);
CURSOR emp_cursor(p_deptno NUMBER, p_job VARCHAR2) IS
SELECT empno, ename FROM emp
WHERE deptno = p_deptno AND job = p_job;
BEGIN
OPEN emp_cursor(10, 'CLERK');
LOOP
FETCH emp_cursor INTO v_empno,v_ename;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno||','||v_ename);
END LOOP;
END;
执行结果为:
7934,MILLER
PL/SQL 过程已成功完成。
说明:游标emp_cursor定义了两个参数:p_deptno代表部门编号,p_job代表职务。语句OPEN emp_cursor(10, 'CLERK')传递了两个参数值给游标,即部门为10、职务为CLERK,所以游标查询的内容是部门10的职务为CLERK的雇员。循环部分用于显示查询的内容。
【练习1】修改Open语句的参数:部门号为20、职务为ANALYST,并重新执行。
================================================================
也可以通过变量向游标传递参数,但变量需要先于游标定义,并在游标打开之前赋值。对以上例子重新改动如下:
【训练10.2】 通过变量传递参数给游标。
SET SERVEROUTPUT ON
DECLARE
v_empno NUMBER(5);
v_ename VARCHAR2(10);
v_deptno NUMBER(5);
v_job VARCHAR2(10);
CURSOR emp_cursor IS
SELECT empno, ename FROM emp
WHERE deptno = v_deptno AND job = v_job;
BEGIN
v_deptno:=10;
v_job:='CLERK';
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_empno,v_ename;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_empno||','||v_ename);
END LOOP;
END;
执行结果为:
7934,MILLER
PL/SQL 过程已成功完成。
说明:该程序与前一程序实现相同的功能。
===============================================================================
【训练11.1】 动态SELECT查询。
DECLARE
str varchar2(100);
v_ename varchar2(10);
begin
str:='select ename from scott.emp where empno=7788';
execute immediate str into v_ename;
dbms_output.put_line(v_ename);
END;
执行结果为:
SCOTT
PL/SQL 过程已成功完成。
说明:SELECT...INTO...语句存放在STR字符串中,通过EXECUTE语句执行。
===============================================================================
【训练11.2】 按名字中包含的字母顺序分组显示雇员信息。
输入并运行以下程序:
declare
type cur_type is ref cursor;
cur cur_type;
rec scott.emp%rowtype;
str varchar2(50);
letter char:= 'A';
begin
loop
str:= 'select ename from emp where ename like ''%'||letter||'%''';
open cur for str;
dbms_output.put_line('包含字母'||letter||'的名字:');
loop
fetch cur into rec.ename;
exit when cur%notfound;
dbms_output.put_line(rec.ename);
end loop;
exit when letter='Z';
letter:=chr(ascii(letter)+1);
end loop;
end;
运行结果为:
包含字母A的名字:
ALLEN
WARD
MARTIN
BLAKE
CLARK
ADAMS
JAMES
包含字母B的名字:
BLAKE
包含字母C的名字:
CLARK
SCOTT
说明:使用了二重循环,在外循环体中,动态生成游标的SELECT语句,然后打开。
通过语句letter:=chr(ascii(letter)+1)可获得字母表中的下一个字母。
==========================================================================
【训练12.1】 查询编号为1234的雇员名字。
DECLARE
v_name VARCHAR2(10);
BEGIN
SELECT ename
INTO v_name
FROM emp
WHERE empno = 1234;
DBMS_OUTPUT.PUT_LINE('该雇员名字为:'|| v_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('编号错误,没有找到相应雇员!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('发生其他错误!');
END;
执行结果为:
编号错误,没有找到相应雇员!
PL/SQL 过程已成功完成。
说明:在以上查询中,因为编号为1234的雇员不存在,所以将发生类型为“NO_DATA_FOUND”的异常。
“NO_DATA_FOUND”是系统预定义的错误类型,
EXCEPTION部分下的WHEN语句将捕捉到该异常,
并执行相应代码部分。在本例中,输出用户自定义的错误信息“编号错误,
没有找到相应雇员!”。如果发生其他类型的错误,
将执行OTHERS条件下的代码部分,显示“发生其他错误!”
==========================================================================
【训练12.2】 由程序代码显示系统错误。
DECLARE
v_temp NUMBER(5):=1;
BEGIN
v_temp:=v_temp/0;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('发生系统错误!');
DBMS_OUTPUT.PUT_LINE('错误代码:'|| SQLCODE( ));
DBMS_OUTPUT.PUT_LINE('错误信息:' ||SQLERRM( ));
END;
执行结果为:
发生系统错误!
错误代码:?1476
错误信息:ORA-01476: 除数为 0
PL/SQL 过程已成功完成。
说明:程序运行中发生除零错误,由WHEN OTHERS捕捉到,执行用户自己的输出语句显示错误信息,然后正常结束。
在错误处理部分使用了预定义函数SQLCODE( )和SQLERRM( )来进一步获得错误的代码和种类信息。
==========================================================================
【训练13.1】 定义新的系统错误类型。
DECLARE
V_ENAME VARCHAR2(10);
NULL_INSERT_ERROR EXCEPTION;
PRAGMA EXCEPTION_INIT(NULL_INSERT_ERROR,-1400);
BEGIN
INSERT INTO EMP(EMPNO) VALUES(NULL);
EXCEPTION
WHEN NULL_INSERT_ERROR THEN
DBMS_OUTPUT.PUT_LINE('无法插入NULL值!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('发生其他系统错误!');
END;
执行结果为:
无法插入NULL值!
PL/SQL 过程已成功完成。
说明:NULL_INSERT_ERROR是自定义异常,同系统错误1400相关联。
======================================================================
【训练14.1】 插入新雇员,限定插入雇员的编号在7000~8000之间。
SET SERVEROUTPUT ON
执行结果为:
雇员编号小于7000的下限!
PL/SQL 过程已成功完成
说明:在此例中,自定义了两个异常:new_excp1和new_excp2,
分别代表编号小于7000和编号大于8000的错误。
在程序中通过判断编号大小,产生对应的异常,
并在异常处理部分回退插入操作,然后显示相应的错误信息。
===============================================================
【训练14.2】 使用RAISE_APPLICATION_ERROR函数引发系统异常。
DECLARE
new_no NUMBER(10);
new_excp1 EXCEPTION;
new_excp2 EXCEPTION;
BEGIN
new_no:=6789;
INSERT INTO emp(empno,ename)
VALUES(new_no, '小郑');
IF new_no<7000 THEN
RAISE new_excp1;
END IF;
IF new_no>8000 THEN
RAISE new_excp2;
END IF;
COMMIT;
EXCEPTION
WHEN new_excp1 THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('雇员编号小于7000的下限!');
WHEN new_excp2 THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('雇员编号超过8000的上限!');
END;
--------------------------------------------
执行结果为:
DECLARE
*
ERROR 位于第 1 行:
ORA-20001: 编号小于7000的下限!
ORA-06512: 在line 9
说明:在本训练中,使用RAISE_APPLICATION_ERROR引发自定义异常,并以系统错误的方式进行显示。错误编号为20001和20002。
注意:同上一个训练比较,此种方法不需要事先定义异常,可直接引发。
=========================================================================
【练习1】修改雇员的工资,通过引发异常控制修改范围在600~6000之间。
=========================================================================
=========================================================================
阶段训练
=========================================================================
=========================================================================
可以参考下面的程序片断将出错信息记录到表中,其中,errors为记录错误信息的表,SQLCODE为发生异常的错误编号,SQLERRM为发生异常的错误信息。
DECLARE
v_error_code NUMBER;
v_error_message VARCHAR2(255);
BEGIN
...
EXCEPTION
...
WHEN OTHERS THEN
v_error_code := SQLCODE ;
v_error_message := SQLERRM ;
INSERT INTO errors
VALUES(v_error_code, v_error_message);
END;
【训练1】 将雇员从一个表复制到另一个表。
步骤1:创建一个结构同EMP表一样的新表EMP1:
CREATE TABLE emp1 AS SELECT * FROM SCOTT.EMP WHERE 1=2;
步骤2:通过指定雇员编号,将雇员由EMP表移动到EMP1表:
SET SERVEROUTPUT ON
DECLARE
v_empno NUMBER(5):=7788;
emp_rec emp%ROWTYPE;
BEGIN
SELECT * INTO emp_rec FROM emp WHERE empno=v_empno;
DELETE FROM emp WHERE empno=v_empno;
INSERT INTO emp1 VALUES emp_rec;
IF SQL%FOUND THEN
COMMIT;
DBMS_OUTPUT.PUT_LINE('雇员复制成功!');
ELSE
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('雇员复制失败!');
END IF;
END;
执行结果为:
雇员复制成功!
PL/SQL 过程已成功完成。
步骤2:显示复制结果:
SELECT empno,ename,job FROM emp1;
执行结果为:
EMPNO ENAME JOB
-------------- -------------- ----------------
7788 SCOTT ANALYST
说明:emp_rec变量是根据emp表定义的记录变量,
SELECT...INTO...语句将整个记录传给该变量。
INSERT语句将整个记录变量插入emp1表,如果插入成功(SQL%FOUND为真),则提交事务,否则回滚撤销事务。
试修改雇员编号为7902,重新执行以上程序。
==============================================================
【训练2】 输出雇员工资,雇员工资用不同高度的*表示。
输入并执行以下程序:
SET SERVEROUTPUT ON
BEGIN
FOR re IN (SELECT ename,sal FROM EMP) LOOP
DBMS_OUTPUT.PUT_LINE(rpad(re.ename,12,' ')||rpad('*',re.sal/100,'*'));
END LOOP;
END;
输出结果为:
SMITH ********
ALLEN ****************
WARD *************
JONES ******************************
MARTIN *************
BLAKE *****************************
CLARK *****************************
SCOTT ******************************
KING **************************************************
TURNER ***************
ADAMS ***********
JAMES **********
FORD ******************************
MILLER *************
执行结果为:
PL/SQL 过程已成功完成。
? 说明:第一个rpad函数产生对齐效果,第二个rpad函数根据工资额产生不同数目的*。该程序采用了隐式的简略游标循环形式。
==========================================================================
【训练3】 编写程序,格式化输出部门信息。
输入并执行如下程序:
SET SERVEROUTPUT ON
DECLARE
v_count number:=0;
CURSOR dept_cursor IS SELECT * FROM dept;
BEGIN
DBMS_OUTPUT.PUT_LINE('部门列表');
DBMS_OUTPUT.PUT_LINE('---------------------------------');
FOR Dept_record IN dept_cursor LOOP
DBMS_OUTPUT.PUT_LINE('部门编号:'|| Dept_record.deptno);
DBMS_OUTPUT.PUT_LINE('部门名称:'|| Dept_record.dname);
DBMS_OUTPUT.PUT_LINE('所在城市:'|| Dept_record.loc);
DBMS_OUTPUT.PUT_LINE('---------------------------------');
v_count:= v_count+1;
END LOOP;
DBMS_OUTPUT.PUT_LINE('共有'||to_char(v_count)||'个部门!');
END;
输出结果为:
部门列表
------------------------------------
部门编号:10
部门名称:ACCOUNTING
所在城市:NEW YORK
------------------------------------
部门编号:20
部门名称:RESEARCH
所在城市:DALLAS
...
共有4个部门!
PL/SQL 过程已成功完成。
说明:该程序中将字段内容垂直排列。V_count变量记录循环次数,即部门个数
==========================================================================
【训练4】 已知每个部门有一个经理,编写程序,统计输出部门名称、部门总人数、总工资和部门经理。
输入并执行如下程序:
SET SERVEROUTPUT ON
DECLARE
v_deptno number(8);
v_count number(3);
v_sumsal number(6);
v_dname varchar2(15);
v_manager varchar2(15);
CURSOR list_cursor IS
SELECT deptno,count(*),sum(sal) FROM emp group by deptno;
BEGIN
OPEN list_cursor;
DBMS_OUTPUT.PUT_LINE('----------- 部 门 统 计 表 -----------');
DBMS_OUTPUT.PUT_LINE('部门名称 总人数 总工资 部门经理');
FETCH list_cursor INTO v_deptno,v_count,v_sumsal;
WHILE list_cursor%found LOOP
SELECT dname INTO v_dname FROM dept
WHERE deptno=v_deptno;
SELECT ename INTO v_manager FROM emp
WHERE deptno=v_deptno and job='MANAGER';
DBMS_OUTPUT.PUT_LINE(rpad(v_dname,13)||rpad(to_char(v_count),8)
||rpad(to_char(v_sumsal),9)||v_manager);
FETCH list_cursor INTO v_deptno,v_count,v_sumsal;
END LOOP;
DBMS_OUTPUT.PUT_LINE('--------------------------------------');
CLOSE list_cursor;
END;
输出结果为:
-------------------- 部 门 统 计 表 -----------------
部门名称 总人数 总工资 部门经理
ACCOUNTING 3 8750 CLARK
RESEARCH 5 10875 JONES
SALES 6 9400 BLAKE
-------------------------------------------------------------
PL/SQL 过程已成功完成。?
说明:游标中使用到了起分组功能的SELECT语句,统计出各部门的总人数和总工资。
再根据部门编号和职务找到部门的经理。该程序假定每个部门有一个经理。
==========================================================================
【训练5】 为雇员增加工资,从工资低的雇员开始,为每个人增加原工资的10%,限定所增加的工资总额为800元,显示增加工资的人数和余额。
输入并调试以下程序:
SET SERVEROUTPUT ON
DECLARE
V_NAME CHAR(10);
V_EMPNO NUMBER(5);
V_SAL NUMBER(8);
V_SAL1 NUMBER(8);
V_TOTAL NUMBER(8) := 800; --增加工资的总额
V_NUM NUMBER(5):=0; --增加工资的人数
CURSOR emp_cursor IS
SELECT EMPNO,ENAME,SAL FROM EMP ORDER BY SAL ASC;
BEGIN
OPEN emp_cursor;
DBMS_OUTPUT.PUT_LINE('姓名 原工资 新工资');
DBMS_OUTPUT.PUT_LINE('---------------------------');
LOOP
FETCH emp_cursor INTO V_EMPNO,V_NAME,V_SAL;
EXIT WHEN emp_cursor%NOTFOUND;
V_SAL1:= V_SAL*0.1;
IF V_TOTAL>V_SAL1 THEN
V_TOTAL := V_TOTAL - V_SAL1;
V_NUM:=V_NUM+1;
DBMS_OUTPUT.PUT_LINE(V_NAME||TO_CHAR(V_SAL,'99999')||
TO_CHAR(V_SAL+V_SAL1,'99999'));
UPDATE EMP SET SAL=SAL+V_SAL1
WHERE EMPNO=V_EMPNO;
ELSE
DBMS_OUTPUT.PUT_LINE(V_NAME||TO_CHAR(V_SAL,'99999')||TO_CHAR(V_SAL,'99999'));
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('---------------------------');
DBMS_OUTPUT.PUT_LINE('增加工资人数:'||V_NUM||' 剩余工资:'||V_TOTAL);
CLOSE emp_cursor;
COMMIT;
END;
输出结果为:
姓名 原工资 新工资
---------------------------------------------
SMITH 1289 1418
JAMES 1531 1684
MARTIN 1664 1830
MILLER 1730 1903
ALLEN 1760 1936
ADAMS 1771 1771
TURNER 1815 1815
WARD 1830 1830
BLAKE 2850 2850
CLARK 2850 2850
JONES 2975 2975
FORD 3000 3000
KING 5000 5000
-----------------------------------------------
增加工资人数:5 剩余工资:3
PL/SQL 过程已成功完成。
【练习1】按部门编号从小到大的顺序输出雇员名字、工资以及工资与平均工资的差。
【练习2】为所有雇员增加工资,工资在1000以内的增加30%,工资在1000~2000之间的增加20%,2000以上的增加10%。
==========================================================================
==========================================================================
练 习
==========================================================================
==========================================================================
1. 关于显式游标的错误说法是:
A. 使用显式游标必须先定义
B. 游标是一个内存区域
C. 游标对应一个SELECT 语句
D. FETCH 语句用来从数据库中读出一行数据到游标
2. 有4条与游标有关的语句,它们在程序中出现的正确顺序是:
1) OPEN abc
2) CURSOR abc IS SELECT ename FROM emp
3) FETCH abc INTO vname
4) CLOSE abc
A. 1、2、3、4 B. 2、1、3、4
C. 2、3、1、4 D. 1、3、2、4
3. 用来判断FETCH语句是否成功,并且在FETCH语句失败时返回逻辑真的属性是:
A. %ROWCOUNT B. %NOTFOUND
C. %FOUND D. %ISOPEN
4. 在程序中执行语句SELECT ename FROM emp WHERE job='CLERK' 可能引发的异常类型是:
A. NO_DATA_FOUND
B. TOO_MANY_ROWS
C. INVALID_CURSOR
D. OTHERS
5. 有关游标的论述,正确的是:
A. 隐式游标属性%FOUND代表操作成功
B. 显式游标的名称为SQL
C. 隐式游标也能返回多行查询结果
D. 可以为UPDATE语句定义一个显式游标