2022年游标使用方法_语法 .pdf

《2022年游标使用方法_语法 .pdf》由会员分享，可在线阅读，更多相关《2022年游标使用方法_语法 .pdf（48页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Oracle 游标使用方法及语法大全当查询返回结果超过一行时，就需要一个显式游标，此时用户不能使用select into语句。PL/SQL 管理 隐式游标 ，当查询开始时隐式游标打开，查询结束时隐式游标自动关闭。显式游标 在 PL/SQL块的声明部分声明，在执行部分或异常处理部分打开，取出数据, 关闭。使用游标语法：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 48 页 - - - - - - - - - 名师资料总结 - - -精品资料欢迎下载 - - - - - -

2、 - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 48 页 - - - - - - - - - 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 48 页 - - - - - - - - - 例： SET SERVERIUTPUT ON DECLARE R_emp EMP%ROWTYPE; CURSOR c_emp IS select * from emp; BEGIN OPEN c_emp; Loop FETCH

3、c_emp into r_emp; EXIT WHEN C_EMP%NOTFOUND; is ) End loop; Close c_emp; End; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 48 页 - - - - - - - - - %ROWTYPE也可以用游标名来定义，这样的话就必须要首先声明游标：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第

4、5 页，共 48 页 - - - - - - - - - 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 6 页，共 48 页 - - - - - - - - - For record_name IN(corsor_name(parameter,parameter,) |(query_difinition) loop Statements End loop; 下面我们用for循环重写上面的例子：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - -

5、- - - - - - 名师精心整理 - - - - - - - 第 7 页，共 48 页 - - - - - - - - - 在游标 FOR循环中使用查询在游标 FOR循环中可以定义查询，由于没有显式声明所以游标没有名字，记录名通过游标查询来定义。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 48 页 - - - - - - - - - 游标中的子查询语法如下：可以看出与SQL中的子查询有没什么区别。游标中的更新和删除在 PL/SQL中依然可以使用UPDATE 和 D

6、ELETE 语句更新或删除数据行。显示游标只有在需要获得多行数据的情况下使用。PL/SQL 提供了仅仅使用游标就可以执行删除或更新记录的方法。UPDATE 或 DELETE 语句中的WHERE CURRENT OF子串专门处理要执行UPDATE 或 DELETE 操作的表中取出的最近的数据。要使用这个方法，在声明游标时必须使用FOR UPDATE 子串，当对话使用 FOR UPDATE 子串打开一个游标时， 所有返回集中的数据行都将处于行级（ ROW-LEVEL)独占式锁定，其他对象只能查询这些数据行，不能进行UPDATE 、DELETE或 SELECT.FOR UPDATE 操作。语法：名师

7、资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 48 页 - - - - - - - - - 在多表查询中， 使用 OF自居来锁定特定的表，如果忽略了OF子句，那么所有表中选择的数据行都将被锁定。如果这些数据行已经被其他会话锁定，那么正常情况下oracle将等待，直到数据行解锁。在 UPDATE 和 DELETE 中使用 WHERE CURRENT OF子串的语法如下：WHERE CURRENT OF cursor_name|search_condition 实例 : DEL

8、CARE CURSOR c1 IS SELECT empno,salary FROM emp WHERE comm IS NULL FOR UPDATE OF comm; v_comm NUMBER(10,2); BEGIN FOR r1 IN c1 LOOP IF r1.salary500 THEN v_comm:=r1.salary*0.25; ELSEIF r1.salary1000 THEN v_comm:=r1.salary*0.20; ELSEIF r1.salary3000 THEN v_comm:=r1.salary*0.15; ELSE v_comm:=r1.salary*0

9、.12; END IF; UPDATE emp SET comm=v_comm WHERE CURRENT OF c1; END LOOP; END = oracle 隐式游标 , 显示游标 , 游标循环动态 SELECT 语句和动态游标, 异常处理 , 自定义异常游标的概念 : 游标是 SQL的一个内存工作区，由系统或用户以变量的形式定义。游标的作用就是用于临时存储从数据库中提取的数据块。在某些情况下， 需要把数据从存放在磁盘的表中调到计算机内存中进行处理，最后将处理结果显示出来或最终写回数据库。这样数据处理的速度才会提高，否则频繁的磁盘数据交换会降低效率。游标有两种类型：显式游标和隐式游标

10、。在前述程序中用到的SELECT.INTO.查询语句，一次只能从数据库中提取一行数据，对于这种形式的查询和DML操作，系统都会使用一个隐式游标。 但是如果要提取多行数据，就要由程序员定义一个显式游标，并通过与游标有关的语句进行处理。显式游标对应一个返回结果为多行多列的SELECT 语句。游标一旦打开， 数据就从数据库中传送到游标变量中，然后应用程序再从游标变量中分解出需要的数据，并进行处理。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 10 页，共 48 页 - - - - - -

11、 - - - 隐式游标：如前所述， DML操作和单行SELECT 语句会使用隐式游标，它们是：* 插入操作： INSERT 。* 更新操作： UPDATE 。* 删除操作： DELETE 。* 单行查询操作：SELECT . INTO .。当系统使用一个隐式游标时，可以通过隐式游标的属性来了解操作的状态和结果，进而控制程序的流程。 隐式游标可以使用名字SQL来访问， 但要注意， 通过 SQL游标名总是只能访问前一个 DML操作或单行SELECT 操作的游标属性。所以通常在刚刚执行完操作之后，立即使用 SQL游标名来访问属性。游标的属性有四种，如下所示。Sql 代码：隐式游标的属性返回值类型意义

12、SQL%ROWCOUNT 整型代表 DML语句成功执行的数据行数SQL%FOUND 布尔型值为 TRUE代表插入、删除、更新或单行查询操作成功SQL%NOTFOUND 布尔型与 SQL%FOUND属性返回值相反SQL%ISOPEN 布尔型 DML执行过程中为真，结束后为假【训练 1】使用隐式游标的属性，判断对雇员工资的修改是否成功。步骤 1：输入和运行以下程序：Sql 代码： SET SERVEROUTPUT ON BEGIN UPDATE emp SET sal=sal+100 WHERE empno=1234; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE(

13、成功修改雇员工资！); COMMIT; ELSE DBMS_OUTPUT.PUT_LINE(修改雇员工资失败！); END IF; END; 运行结果为：Sql 代码：修改雇员工资失败！PL/SQL 过程已成功完成。步骤 2：将雇员编号1234 改为 7788，重新执行以上程序：运行结果为：Sql 代码：成功修改雇员工资！PL/SQL 过程已成功完成。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 11 页，共 48 页 - - - - - - - - - 说明：本例中，通过SQL%

14、FOUND属性判断修改是否成功，并给出相应信息。显式游标：游标的定义和操作游标的使用分成以下4 个步骤。1声明游标在 DECLEAR 部分按以下格式声明游标：CURSOR 游标名 ( 参数 1 数据类型 ，参数 2 数据类型 .) IS SELECT 语句 ; 参数是可选部分， 所定义的参数可以出现在SELECT 语句的 WHERE 子句中。 如果定义了参数，则必须在打开游标时传递相应的实际参数。SELECT 语句是对表或视图的查询语句，甚至也可以是联合查询。可以带WHERE 条件、 ORDER BY或 GROUP BY等子句，但不能使用INTO子句。在SELECT 语句中可以使用在定义游标之

15、前定义的变量。2打开游标在可执行部分，按以下格式打开游标：OPEN 游标名 ( 实际参数1 ，实际参数2.); 打开游标时， SELECT 语句的查询结果就被传送到了游标工作区。3提取数据在可执行部分， 按以下格式将游标工作区中的数据取到变量中。提取操作必须在打开游标之后进行。FETCH 游标名 INTO 变量名 1 ，变量名2.; 或FETCH 游标名 INTO 记录变量 ; 游标打开后有一个指针指向数据区，FETCH语句一次返回指针所指的一行数据，要返回多行需重复执行，可以使用循环语句来实现。控制循环可以通过判断游标的属性来进行。下面对这两种格式进行说明：第一种格式中的变量名是用来从游标中

16、接收数据的变量，需要事先定义。 变量的个数和类型应与 SELECT 语句中的字段变量的个数和类型一致。第二种格式一次将一行数据取到记录变量中，需要使用%ROWTYPE事先定义记录变量，这种形式使用起来比较方便，不必分别定义和使用多个变量。定义记录变量的方法如下：变量名表名 | 游标名 %ROWTYPE；其中的表必须存在，游标名也必须先定义。4关闭游标CLOSE 游标名 ; 显式游标打开后，必须显式地关闭。游标一旦关闭，游标占用的资源就被释放，游标变成无效，必须重新打开才能使用。以下是使用显式游标的一个简单练习。【训练 1】用游标提取emp表中 7788 雇员的名称和职务。Sql 代码： SET

17、 SERVEROUTPUT ON DECLARE 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 12 页，共 48 页 - - - - - - - - - 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

18、; DBMS_OUTPUT.PUT_LINE(v_ename|,|v_job); CLOSE emp_cursor; END; 执行结果为 : Sql 代码：SCOTT,ANALYST PL/SQL 过程已成功完成。说明：该程序通过定义游标emp_cursor ，提取并显示雇员7788 的名称和职务。作为对以上例子的改进，在以下训练中采用了记录变量。【训练 2】用游标提取emp表中 7788 雇员的姓名、职务和工资。Sql 代码：SET SERVEROUTPUT ON DECLARE CURSOR emp_cursor IS SELECT ename,job,sal FROM emp WHER

19、E 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; 执行结果为 : Sql 代码：SCOTT,ANALYST,3000 PL/SQL 过程已成功完成。说明： 实例中使用记录变量来接收数据，记录变量由游标变量定义，需要出现在游标定义之后。注意：可通过以

20、下形式获得记录变量的内容：记录变量名 . 字段名。【训练 3】显示工资最高的前3 名雇员的名称和工资。Sql 代码：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 13 页，共 48 页 - - - - - - - - - SET SERVEROUTPUT ON DECLARE V_ename VARCHAR2(10); V_sal NUMBER(5); CURSOR emp_cursor IS SELECT ename,sal FROM emp ORDER BY sal DESC;

21、 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; 执行结果为 : Sql 代码：KING,5000 SCOTT,3000 FORD,3000 PL/SQL 过程已成功完成。说明：该程序在游标定义中使用了ORDER BY 子句进行排序，并使用循环语句来提取多行数据。游标循环【训练 1】使用特殊的FOR循环形式显示全部雇员的编号和名称。Sql 代码S

22、ET SERVEROUTPUT ON 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; 执行结果为：Sql 代码7369SMITH 7499ALLEN 7521WARD 7566JONES PL/SQL 过程已成功完成。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - -

23、- - - - - - - 名师精心整理 - - - - - - - 第 14 页，共 48 页 - - - - - - - - - 说明：可以看到该循环形式非常简单，隐含了记录变量的定义、游标的打开、提取和关闭过程。 Emp_record 为隐含定义的记录变量，循环的执行次数与游标取得的数据的行数相一致。【训练 2】另一种形式的游标循环。Sql 代码SET SERVEROUTPUT ON BEGIN FOR re IN (SELECT ename FROM EMP) LOOP DBMS_OUTPUT.PUT_LINE(re.ename) END LOOP; END; 执行结果为：Sql 代码

24、SMITH ALLEN WARD JONES 说明：该种形式更为简单，省略了游标的定义，游标的SELECT 查询语句在循环中直接出现。显式游标属性虽然可以使用前面的形式获得游标数据，但是在游标定义以后使用它的一些属性来进行结构控制是一种更为灵活的方法。显式游标的属性如下所示。Sql 代码：游标的属性返回值类型意义%ROWCOUNT 整型获得 FETCH 语句返回的数据行数%FOUND 布尔型最近的 FETCH 语句返回一行数据则为真，否则为假%NOTFOUND 布尔型与%FOUND 属性返回值相反%ISOPEN 布尔型游标已经打开时值为真，否则为假可按照以下形式取得游标的属性：游标名 % 属性

25、要判断游标emp_cursor 是否处于打开状态，可以使用属性emp_cursor%ISOPEN 。如果游标已经打开，则返回值为“真”，否则为“假”。具体可参照以下的训练。【训练 1】使用游标的属性练习。Sql 代码SET SERVEROUTPUT ON DECLARE V_ename VARCHAR2(10); CURSOR emp_cursor IS SELECT ename FROM emp; BEGIN 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 15 页，共 48 页

26、- - - - - - - - - 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; 执行结果为：Sql 代码1-SMITH 2-ALLEN

27、3-WARD PL/SQL 过程已成功完成。说明：本例使用emp_cursor%ISOPEN判断游标是否打开；使用emp_cursor%ROWCOUNT获得到目前为止FETCH 语句返回的数据行数并输出；使用循环来获取数据，在循环体中使用FETCH 语句；使用 emp_cursor%NOTFOUND 判断 FETCH 语句是否成功执行，当FETCH语句失败时说明数据已经取完，退出循环。【练习 1】去掉 OPEN emp_cursor; 语句，重新执行以上程序。游标参数的传递【训练 1】带参数的游标。Sql 代码SET SERVEROUTPUT ON DECLARE V_empno NUMBER

28、(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);

29、 END LOOP; END; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 16 页，共 48 页 - - - - - - - - - 执行结果为：Sql 代码7934,MILLER PL/SQL 过程已成功完成。说明：游标emp_cursor 定义了两个参数：p_deptno 代表部门编号，p_job 代表职务。语句OPEN emp_cursor(10, CLERK) 传递了两个参数值给游标，即部门为10、职务为CLERK ，所以游标查询的内容是部门10 的职务为 CLERK的

30、雇员。循环部分用于显示查询的内容。【练习 1】修改 Open语句的参数：部门号为20、职务为 ANALYST ，并重新执行。也可以通过变量向游标传递参数，但变量需要先于游标定义，并在游标打开之前赋值。对以上例子重新改动如下：【训练 2】通过变量传递参数给游标。Sql 代码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

31、 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; 执行结果为：Sql 代码：7934,MILLER PL/SQL 过程已成功完成。说明：该程序与前一程序实现相同的功能。动态 SELECT 语句和动态游标的用法：Or

32、acle 支持动态SELECT 语句和动态游标，动态的方法大大扩展了程序设计的能力。对于查询结果为一行的SELECT 语句，可以用动态生成查询语句字符串的方法，在程序执行名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 17 页，共 48 页 - - - - - - - - - 阶段临时地生成并执行，语法是：execute immediate 查询语句字符串 into 变量 1, 变量 2.; 以下是一个动态生成SELECT 语句的例子。【训练 1】动态 SELECT 查询。Sql 代

33、码：SET SERVEROUTPUT ON 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; 执行结果为：Sql 代码：SCOTT PL/SQL 过程已成功完成。说明： SELECT.INTO.语句存放在STR字符串中，通过EXECUTE 语句执行。在变量声明部分定义的游标是静态的，不能在程序

34、运行过程中修改。虽然可以通过参数传递来取得不同的数据，但还是有很大的局限性。通过采用动态游标，可以在程序运行阶段随时生成一个查询语句作为游标。要使用动态游标需要先定义一个游标类型，然后声明一个游标变量，游标对应的查询语句可以在程序的执行过程中动态地说明。定义游标类型的语句如下：TYPE 游标类型名 REF CURSOR; 声明游标变量的语句如下：游标变量名游标类型名 ; 在可执行部分可以如下形式打开一个动态游标：OPEN 游标变量名 FOR 查询语句字符串; 【训练 2】按名字中包含的字母顺序分组显示雇员信息。输入并运行以下程序：Sql 代码：declare type cur_type is

35、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; 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 18 页，共 48 页 - - - - - - - - - dbms_output.put_line

36、(包含字母 |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; 运行结果为：Sql 代码：包含字母A的名字：ALLEN WARD MARTIN BLAKE CLARK ADAMS JAMES 包含字母B的名字：BLAKE 包含字母C的名字：CLARK SCOTT 说明：使用了二重循环，在外循环

37、体中，动态生成游标的SELECT语句，然后打开。通过语句 letter:=chr(ascii(letter)+1)可获得字母表中的下一个字母。异常处理错误处理错误处理部分位于程序的可执行部分之后，是由WHEN 语句引导的多个分支构成的。错误处理的语法如下：EXCEPTION WHEN 错误 1OR 错误 2 THEN 语句序列1；WHEN 错误 3OR 错误 4 THEN 语句序列2；WHEN OTHERS 语句序列n；END; 其中：错误是在标准包中由系统预定义的标准错误，或是由用户在程序的说明部分自定义的错误，名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - -

38、 - - - - - - - - 名师精心整理 - - - - - - - 第 19 页，共 48 页 - - - - - - - - - 参见下一节系统预定义的错误类型。语句序列就是不同分支的错误处理部分。凡是出现在WHEN后面的错误都是可以捕捉到的错误，其他未被捕捉到的错误，将在WHEN OTHERS 部分进行统一处理，OTHENS 必须是 EXCEPTION 部分的最后一个错误处理分支。如要在该分支中进一步判断错误种类，可以通过使用预定义函数SQLCODE( ) 和 SQLERRM( ) 来获得系统错误号和错误信息。如果在程序的子块中发生了错误，但子块没有错误处理部分，则错误会传递到主程

39、序中。下面是由于查询编号错误而引起系统预定义异常的例子。【训练 1】查询编号为1234 的雇员名字。Sql 代码SET SERVEROUTPUT ON 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_

40、OUTPUT.PUT_LINE( 发生其他错误！); END; 执行结果为：Sql 代码编号错误，没有找到相应雇员！ PL/SQL 过程已成功完成。说明：在以上查询中，因为编号为1234 的雇员不存在，所以将发生类型为“NO_DATA_ FOUND”的异常。“ NO_DATA_FOUND”是系统预定义的错误类型，EXCEPTION部分下的WHEN语句将捕捉到该异常，并执行相应代码部分。在本例中， 输出用户自定义的错误信息“编号错误，没有找到相应雇员 ! ”。 如果发生其他类型的错误，将执行 OTHERS 条件下的代码部分，显示“发生其他错误! ”。【训练 2】由程序代码显示系统错误。Sql 代

41、码SET SERVEROUTPUT ON DECLARE v_temp NUMBER(5):=1; BEGIN v_temp:=v_temp/0; EXCEPTION 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 20 页，共 48 页 - - - - - - - - - WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE( 发生系统错误！); DBMS_OUTPUT.PUT_LINE( 错误代码： | SQLCODE( ); DBMS_OUTPUT.PUT

42、_LINE( 错误信息： |SQLERRM( ); END; 执行结果为：Sql 代码发生系统错误！错误代码： ?1476 错误信息： ORA-01476: 除数为 0 PL/SQL 过程已成功完成。说明：程序运行中发生除零错误，由WHEN OTHERS 捕捉到，执行用户自己的输出语句显示错误信息，然后正常结束。在错误处理部分使用了预定义函数SQLCODE( ) 和 SQLERRM( ) 来进一步获得错误的代码和种类信息。预定义错误Oracle的系统错误很多，但只有一部分常见错误在标准包中予以定义。定义的错误可以在EXCEPTION 部分通过标准的错误名来进行判断，并进行异常处理。常见的系统预

43、定义异常如下所示。Sql 代码错 误 名 称 错误代码错 误 含 义CURSOR_ALREADY_OPEN ORA_06511 试图打开已经打开的游标INVALID_CURSOR ORA_01001 试图使用没有打开的游标DUP_VAL_ON_INDEX ORA_00001 保存重复值到惟一索引约束的列中ZERO_DIVIDE ORA_01476 发生除数为零的除法错误INVALID_NUMBER ORA_01722 试图对无效字符进行数值转换ROWTYPE_MISMATCH ORA_06504 主变量和游标的类型不兼容VALUE_ERROR ORA_06502 转换、截断或算术运算发生错误T

44、OO_MANY_ROWS ORA_01422 SELECT,INTO, 语句返回多于一行的数据NO_DATA_FOUND ORA_01403 SELECT,INTO, 语句没有数据返回TIMEOUT_ON_RESOURCE ORA_00051 等待资源时发生超时错误TRANSACTION_BACKED_OUT ORA_00060 由于死锁，提交失败STORAGE_ERROR ORA_06500 发生内存错误PROGRAM_ERROR ORA_06501 发生 PL/SQL内部错误NOT_LOGGED_ON ORA_01012 试图操作未连接的数据库LOGIN_DENIED ORA_01017

45、在连接时提供了无效用户名或口令比如，如果程序向表的主键列插入重复值，则将发生DUP_VAL_ON_INDEX错误。如果一个系统错误没有在标准包中定义，则需要在说明部分定义，语法如下：错误名 EXCEPTION; 定义后使用PRAGMA EXCEPTION_INIT 来将一个定义的错误同一个特别的Oracle 错误代码相关联，就可以同系统预定义的错误一样使用了。语法如下：PRAGMA EXCEPTION_INIT( 错误名， - 错误代码 ) ；名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - -

46、- 第 21 页，共 48 页 - - - - - - - - - 【训练 1】定义新的系统错误类型。Sql 代码SET SERVEROUTPUT ON 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值！ )

47、; WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE( 发生其他系统错误！); END; 执行结果为：Sql 代码无法插入NULL值！ PL/SQL 过程已成功完成。说明： NULL_INSERT_ERROR是自定义异常，同系统错误1400 相关联。自定义异常程序设计者可以利用引发异常的机制来进行程序设计，自己定义异常类型。可以在声明部分定义新的异常类型，定义的语法是：错误名 EXCEPTION; 用户定义的错误不能由系统来触发，必须由程序显式地触发，触发的语法是：RAISE 错误名；RAISE也可以用来引发模拟系统错误，比如，RAISE ZERO_DIVIDE将引发

48、模拟的除零错误。使用 RAISE_APPLICATION_ERROR函数也可以引发异常。该函数要传递两个参数，第一个是用户自定义的错误编号，第二个参数是用户自定义的错误信息。使用该函数引发的异常的编号应该在 20 000 和 20 999 之间选择。自定义异常处理错误的方式同前。【训练 1】插入新雇员，限定插入雇员的编号在70008000 之间。Java 代码SET SERVEROUTPUT ON DECLARE new_no NUMBER( 10); new_excp1 EXCEPTION; new_excp2 EXCEPTION; BEGIN new_no:=6789; INSERT IN

49、TO emp(empno,ename) VALUES(new_no, 小郑 ); 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 22 页，共 48 页 - - - - - - - - - IF new_no8000 THEN RAISE new_excp2; END IF; COMMIT; EXCEPTION WHEN new_excp1 THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE( 雇员编号小于7000 的下限！ ); WHEN new_excp2 T

50、HEN ROLLBACK; DBMS_OUTPUT.PUT_LINE( 雇员编号超过8000 的上限！ ); END; 执行结果为：雇员编号小于7000 的下限！PL/SQL 过程已成功完成。说明：在此例中，自定义了两个异常：new_excp1 和 new_excp2，分别代表编号小于7000和编号大于8000 的错误。在程序中通过判断编号大小，产生对应的异常，并在异常处理部分回退插入操作，然后显示相应的错误信息。【训练 2】使用 RAISE_APPLICATION_ERROR函数引发系统异常。Sql 代码SET SERVEROUTPUT ON DECLARE New_no NUMBER(10