.Net程序员学用Oracle系列(27)：PLSQL 之游标、异常和事务

• 1、游标
  • 1.1、游标属性
  • 1.2、隐式游标
  • 1.3、游标处理及案例
• 2、异常
  • 2.1、异常类别
  • 2.2、异常函数
  • 2.3、异常处理及案例
• 3、事务
  • 3.1、开始事务、结束事务
  • 3.2、自治事务
  • 3.3、事务处理及案例
• 4、总结

1、游标

游标是一种数据处理机制，它提供了在结果集中依次浏览一行或多行数据的能力。游标就相当于是一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理。Oracle 中的游标分为显示游标和隐式游标两种。

1.1、游标属性

每个游标和游标变量都有四个属性：%FOUND、%ISOPEN、%NOTFOUND 和 %ROWCOUNT。当附加到游标或游标变量时，这些属性返回有关执行数据操作语句的状态信息。

1、%ISOPEN：如果游标处于打开状态，则 cursor_name%ISOPEN 返回 TRUE；否则返回FALSE。

2、%FOUND：在游标被打开之后，第一次提取数据行之前，cursor_name%FOUND 会返回 NULL。此后，如果最近一次提取返回了一行数据，则返回 TRUE，如果最近一次提取未能返回一行数据，则返回 FALSE。

3、%NOTFOUND：在游标被打开之后，第一次提取数据行之前，cursor_name%NOTFOUND 返回NULL。此后，如果最近一次提取返回了一行数据，则返回 FALSE，如果最近一次提取未能返回一行数据，则返回 TRUE。

4、%ROWCOUNT：在游标被打开之后，第一次提取数据行之前，cursor_name%ROWCOUNT 返回 0。此后，它返回到目前为止已提取的行数。如果最近一次提取返回了一行，则该数字会递增。

1.2、隐式游标

显示游标主要用于处理查询语句，尤其是查询结果为多条记录的情况，需要由程序员显式地声明一个游标来单独处理这些行。而对于 SELECT ... INTO ...、INSERT、DELETE、UPDATE 等语句，Oracle 系统会自动地为这些操作设置隐式游标，并为该游标的取名为 SQL。隐式游标的相关操作均由 Oracle 系统自动完成，无需用户进行任何诸如打开或关闭之类的处理。

用户可以通过隐式游标的名称和属性来了解操作的状态和结果，进而控制程序的流程。但需要注意的是，通过 SQL 游标名只能访问前一个 DML 操作或单行 SELECT 操作的游标属性（所以通常得在执行完操作之后，立马使用 SQL 游标名来访问属性）。因为在隐式游标的工作区中，所存放的数据是与用户自定义的显示游标无关的、最新处理的一条 SQL 语句所包含的数据。

关于单行 SELECT 操作的隐式游标，还 3 点需要注意：

• 1、每一个 单行 SELECT 隐式游标必须得有一个 INTO 子句。
• 2、INTO 子句后接收数据的变量的数据类型要与对应列的数据类型一致。
• 3、隐式游标一次只能返回一条数据。

示例：

DECLARE
  v_course_id demo.t_course.course_id%TYPE := 7;
BEGIN
  DBMS_OUTPUT.PUT_LINE('SQL%ROWCOUNT: '||SQL%ROWCOUNT); -- NULL
  UPDATE demo.t_course t SET t.course_desc='7' WHERE t.course_id=v_course_id;
  DBMS_OUTPUT.PUT_LINE('SQL%ROWCOUNT: '||SQL%ROWCOUNT); -- 1
  
  IF SQL%ISOPEN THEN  -- false
    DBMS_OUTPUT.PUT_LINE('SQL%ISOPEN is true.');
  END IF;
  
  IF SQL%FOUND THEN -- true
    DBMS_OUTPUT.PUT_LINE('SQL%FOUND is true.');
  END IF;
  
  IF SQL%NOTFOUND THEN -- false
    DBMS_OUTPUT.PUT_LINE('SQL%NOTFOUND is true.');
  END IF;
END;

1.3、游标处理及案例

在使用游标之前，必须先声明游标。可以在声明的时候给游标取一个名字，并将其与特定的查询想关联，还可以同时为游标指定一个返回类型。在通过游标提取数据之前，还得先打开游标，然后通过游标提取行。当处理完所有行之后，应立即关闭游标变量，以免造成资源浪费。

示例 1（遍历输出所有课程名称）：

DECLARE
  TYPE course_type IS REF CURSOR RETURN demo.t_course%ROWTYPE; -- 定义游标类型
  v_cursor course_type; -- 定义一个游标 v_cursor
  v_course demo.t_course%ROWTYPE; -- 定义一个表示 t_course 表中行的变量
BEGIN
  IF NOT v_cursor%ISOPEN THEN -- 如果游标不是打开状态，如果试图打开一个已打开的游标时将会出现错误，所以应该要判断一下
    OPEN v_cursor FOR SELECT t.* FROM demo.t_course t; -- 打开游标，这里得确保 SELECT 列表和游标变量的数据类型一致
    LOOP
      FETCH v_cursor INTO v_course; -- 从游标中提取行
      EXIT WHEN v_cursor%NOTFOUND; -- 如果找不到数据行了就退出循环
      DBMS_OUTPUT.PUT_LINE('课程名称：'||v_course.course_name); -- 打印被提取的数据
    END LOOP;
  END IF;
  CLOSE v_cursor; -- 关闭游标，如果试图关闭一个已关闭的游标时也会出现错误
END;

示例 2（遍历输出所有课程名称，并分段显示）：

DECLARE
  v_cursor SYS_REFCURSOR;
  v_course_name demo.t_course.course_name%TYPE;
BEGIN
  OPEN v_cursor FOR SELECT t.course_name FROM demo.t_course t;
  LOOP
    FETCH v_cursor INTO v_course_name;
    EXIT WHEN v_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_cursor%ROWCOUNT||'：'||v_course_name);
    IF v_cursor%ROWCOUNT=2 THEN
      DBMS_OUTPUT.PUT_LINE('--- 已输出两行记录 ---');
    END IF;
  END LOOP;
  CLOSE v_cursor;
END;

上例中的 SYS_REFCURSOR 是 Oracle 系统提供的，用于传递游标变量，与使用自定义的“REF CURSOR”并无本质区别

示例 3（输出指定课程的名称和描述）：

DECLARE
  v_course_id demo.t_course.course_id%TYPE := 1;
  v_course_name demo.t_course.course_name%TYPE;
  v_course_desc demo.t_course.course_desc%TYPE;
  CURSOR v_cursor IS 
    SELECT t.course_name,t.course_desc FROM demo.t_course t WHERE t.course_id=v_course_id;
BEGIN
  OPEN v_cursor;
  FETCH v_cursor INTO v_course_name,v_course_desc;
  IF v_cursor%FOUND THEN
    DBMS_OUTPUT.PUT_LINE('名称：'||v_course_name||CHR(10)||'描述：'||v_course_desc);
  END IF;
  CLOSE v_cursor;
END;

示例 4（输出指定课程的相关信息）：

DECLARE
  CURSOR v_cursor RETURN demo.t_course%ROWTYPE IS 
    SELECT t.* FROM demo.t_course t WHERE t.course_id=2;
  v_course t_course%ROWTYPE;
BEGIN
  OPEN v_cursor;
  FETCH v_cursor INTO v_course;
  IF v_cursor%FOUND THEN
    DBMS_OUTPUT.PUT_LINE(v_course.course_id||CHR(10)||v_course.course_name||CHR(10)||v_course.course_desc);
  END IF;
  CLOSE v_cursor;
END;

示例 5（创建一个提取信息的程序包，用于根据条件提取不同表中的信息）：

CREATE PACKAGE pkg_take_info AS
  TYPE cursor_type IS REF CURSOR;
  PROCEDURE sp_take_info(v_cursor IN OUT cursor_type,choice INT);
END pkg_take_info;

CREATE PACKAGE BODY pkg_take_info AS
  PROCEDURE sp_take_info(v_cursor IN OUT cursor_type,choice INT) IS
  BEGIN
    IF choice=1 THEN
      OPEN v_cursor FOR SELECT t.* FROM demo.t_course t;
    ELSIF choice=2 THEN
      OPEN v_cursor FOR SELECT t.* FROM demo.t_staff t;
    ELSIF choice=3 THEN
      OPEN v_cursor FOR SELECT t.* FROM demo.t_field_enum t;
    END IF;
    CLOSE v_cursor;
  END;
END pkg_take_info;

2、异常

异常（PL/SQL 运行时错误）可能源自设计错误、编码错误、硬件故障以及许多其它来源。我们往往无法预期所有可能的异常，但可以编写异常处理模块，让程序继续有效运行。

2.1、异常类别

• 内部定义异常：运行时系统会自动引发内部定义的异常。典型的内部定义异常如 ORA-00060（在等待资源时检测到死锁）和 ORA-27102（内存不足）。内部定义的异常总是有一个错误代码，但没有名称。
• 预定义异常：预定义的异常也是内部定义的异常，但 PL/SQL 给它取了名字。例如，ORA-06500（PL/SQL：存储错误）就有预定义名称 STORAGE_ERROR。
• 用户自定义异常：可以在任何 PL/SQL 匿名块、子程序或程序包的声明部分声明自己的异常。例如，可以声明一个名为余额不足的异常来标记已透支的银行帐户，并在异常处理模块中抛出该异常。

2.2、异常函数

1、错误代码 SQLCODE 函数

在异常处理程序中，SQLCODE 函数返回正在处理的异常的数字代码。在异常处理程序之外，SQLCODE 返回 0。对于内部定义的异常，数字代码是有关 Oracle 数据库错误的编号。除“no data found”数字代码为 +100 之外，该数字一般是负数。对于用户自定义的异常，数字代码默认为 +1 或与EXCEPTION_INIT编译指示异常相关联的错误代码。在 SQL 语句无法调用 SQLCODE。

2、错误消息 SQLERRM 函数

SQLERRM 函数返回与正在处理的异常的错误代码相关联的错误消息。

语法：

SQLERRM [(error_code)]

示例：

BEGIN
  DBMS_OUTPUT.PUT_LINE(SQLERRM);         -- ORA-0000: normal, successful completion
  DBMS_OUTPUT.PUT_LINE(SQLERRM(100));    -- ORA-01403: 未找到任何数据
  DBMS_OUTPUT.PUT_LINE(SQLERRM(-60));    -- ORA-00060: 等待资源时检测到死锁
  DBMS_OUTPUT.PUT_LINE(SQLERRM(-27102)); -- ORA-27102: 内存不足
  DBMS_OUTPUT.PUT_LINE(SQLERRM(-6500));  -- ORA-06500: PL/SQL: 存储错误
END;

2.3、异常处理及案例

定义异常

语法：

exception_name EXCEPTION;

如上所述，定义用户自定义异常的方法非常简单，只需给出异常名称即可。另外，还可以通过EXCEPTION_INIT将该名称分配给内部定义的异常，具体用法可参考：《Oracle Database PL/SQL Language Reference: EXCEPTION_INIT Pragma》。

抛出异常

PL/SQL 中的 RAISE 与 C# 中的 throw 相似，RAISE 语句的作用是在 Oracle 中抛出异常。只要在异常处理模块之外，定义好用户自定义异常并指定异常名称，就可以通过 RAISE 语句明确引发该异常。如果省略异常名称，则 RAISE 语句将会重新检查当前异常。

语法：

RAISE [ exception_name ];

任何 PL/SQL 块（包括匿名块、子程序和程序包）都可以有一个异常处理模块，用来处理一或多个异常。对于命名异常，可以编写一个特定的异常处理程序，而不是使用 OTHERS 异常来处理它。特定的异常处理程序比 OTHERS 异常处理程序更有效，因为后者还必须调用一个函数来确定它正在处理哪个异常。

如果没有异常处理程序，您必须检查可能发生的每一个可能的错误，然后处理它。很容易忽略可能的错误或可能发生的地方，特别是如果错误不能立即被检测到（例如，坏数据可能无法检测，直到在计算中使用它）。这会导致大量错误处理代码散布在整个程序中。

使用异常处理程序，您不需要知道每一个可能的错误或其可能发生的任何地方。您只需在每个可能发生错误的程序段中包含一个异常处理部分。在异常处理部分，您可以包括特定和未知错误的异常处理程序。如果块中的任何位置（包括子块内）发生错误，则异常处理程序处理它。错误处理代码在块的异常处理部分中被隔离。

异常模块语法：

EXCEPTION
  WHEN ex_name_1 THEN statements_1;
  WHEN ex_name_2 OR ex_name_3 THEN statements_2;
  WHEN OTHERS THEN statements_3;

当块中可执行部分出现异常时，可执行部分停止执行，异常处理部分开始执行。例如，出现异常ex_name_1，则statements_1会被运行；如果出现异常ex_name_2或ex_name_3，则statements_2会被运行。如果在没有异常处理程序的块中引发异常，则异常传播。也就是说，异常在连续的封闭块中重现本身，直到被处理，如果没有被处理，则 PL/SQL 会向调用者或主机环境返回未处理的异常错误。

示例(ZERO_DIVIDE)：

DECLARE
  stock_price NUMBER(18,6) := 8; -- 股票市场价格
  net_earnings NUMBER(18,6) := 0; -- 净收益
  pe_ratio NUMBER(18,6); -- 市盈率
BEGIN
  pe_ratio := stock_price/net_earnings; -- 除数为零
  DBMS_OUTPUT.PUT_LINE('Price/earnings ratio: '||pe_ratio);
EXCEPTION
  WHEN ZERO_DIVIDE THEN
    DBMS_OUTPUT.PUT_LINE('Zero earnings!');
    pe_ratio := NULL;
END;

示例(NO_DATA_FOUND)：

DECLARE
  v_course_id demo.t_course.course_id%TYPE := 8;
  v_course_name demo.t_course.course_name%TYPE;
  v_course_desc demo.t_course.course_desc%TYPE;
BEGIN
  SELECT t.course_name,t.course_desc INTO v_course_name,v_course_desc 
  FROM demo.t_course t 
  WHERE t.course_id=v_course_id;
  
  DBMS_OUTPUT.PUT_LINE('课程名称：'||v_course_name);
  DBMS_OUTPUT.PUT_LINE('课程说明：'||v_course_desc);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No such course!');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('异常：'||SQLCODE||': '||SUBSTR(SQLERRM,1,64));
    RAISE;
END;

示例(VALUE_ERROR)：

BEGIN
  DECLARE
    credit_limit NUMBER(5) := 200000;
  BEGIN
    NULL;
  END;
EXCEPTION
  WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('赋值时出错！');
END;

示例（用户自定义异常）：

DECLARE
  v_amount NUMBER(18,6) := 1.00;
  v_due_date DATE := TO_DATE('2017-01-07','yyyy-mm-dd');
  no_money EXCEPTION;
  past_due EXCEPTION;
BEGIN
  IF v_amount<=0 THEN
    RAISE no_money;
  END IF;
  
  IF v_due_date<demo.fn_today THEN
    RAISE past_due;
  END IF;
  
EXCEPTION
  WHEN no_money THEN
    DBMS_OUTPUT.PUT_LINE('没钱了！');
  WHEN past_due THEN
    DBMS_OUTPUT.PUT_LINE('逾期了！');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('异常：'||SQLCODE||': '||SUBSTR(SQLERRM,1,64));
    RAISE;
END;

更多 Oracle 异常处理细节及案例，请参考：《Oracle Database PL/SQL Language Reference: PL/SQL Error Handling》。

3、事务

事务是数据库区别于文件系统的特性之一。事务会把数据库从一种一致状态转变为另一种一致状态。Oracle 中的事务体现了所有必要的 ACID 特性。ACID 是以下 4 个词的缩写。

• 原子性(Atomicity)：事务中的所有动作要么都发生，要么都不发生。
• 一致性(Consistency)：事务将数据库从一种一致状态转变为下一种一致状态。
• 隔离性(Isolation)：一个事务的影响在该事务提交前对其它事务都不可见。
• 持久性(Durability)：事务一旦提交，其结果就是永久性的。

事务是包含一个或多个 SQL 语句的逻辑单元。事务中所有 SQL 语句的效果可以是全部提交（应用于数据库）或全部回滚（从数据库撤消）。

3.1、开始事务、结束事务

在 Oracle 中不需要用专门的语句来“开始事务”。事务会在遇到第一个可执行的 SQL 语句时处隐式开始。也可以使用 SET TRANSACTION 或 DBMS_TRANSACTION 包来显式地开始一个事务，但这一步并非是必须的。

当执行了不带 SAVEPOINT 子句的 COMMIT（提交）或 ROLLBACK（回滚）时，事务就会被显式地结束。当执行了 DDL 或 DCL 语句时，事务就会被自动提交，也就是隐式地结束。如果用户断开与 Oracle 的连接，当前事务自动提交。如果用户进程异常终止，当前事务自动回滚。如用户退出 SQLPlus 会话时，若没有提交或回滚事务，SQLPlus 会自动为用户提交。当系统崩溃时事务也会被隐式提交。我们不能过度依赖这些隐式行为，因为将来这些行为可能会改变。

提交意味着用户明确或暗示地要求将事务中的更改设置为永久性。当用户发出 COMMIT 语句时，会发出显式请求。在应用程序正常终止或 DDL 操作完成后，会发生隐式请求。只有事务提交之后，事务的 SQL 语句所做的更改才会变得永久可见。在事务提交后发出的查询将看到已提交的更改。

Oracle 中的 DDL 语句具有原子性，不过只是在语句级保证原子性，如果操作成功则提交，否则回滚 DDL 操作。提交所有未完成的工作，结束当前的所有事务。只要执行了 DDL 语句，就可以将现有的事务立即提交，并完成后面的 DDL 命令，这些 DDL 命令可能提交从而得到持久的结果，也可能因为出现错误而回滚。尽管 DDL 并不违反 ACID 概念，但 DDL 语句会提交的这一点确实需要注意。

ORACLE 服务器会执行隐式的存储点。如果在执行过程中的任何时候，一旦 SQL 语句运行出错，就会回滚该语句的所有效果。回滚的效果就好像是该语句从未被运行过。回滚意味着撤消对未提交事务中的SQL语句执行的数据的任何更改。Oracle 使用 undo 表空间（或回滚段）来存储旧值。重做日志包含更改记录。Oracle 允许您回滚整个未提交的事务。还可以将未提交事务的尾部部分回滚到称为保存点的标记。

3.2、自治事务

自治事务(autonomous transaction)允许你创建一个“事务中的事务”，它能独立于其父事务提交或回滚。利用自治事务，可以挂起当前执行的事务，开始一个新事务，完成一些工作，然后提交或回滚，所有这些都不影响当前所执行事务的状态。换句话说，自治事务允许从某个事务中调用另一个独立的事务。一旦被调用，自治事务就完全独立于调用它的主事务。在自治事务中，看不到主事务中发生的任何未提交的更改，并且不与主事务共享任何锁或资源。

在自治事务中还可以调用另一个自治事务，除了资源限制外，对于可以调用多少层级的自治事务没有限制。自治事务与其调用的事务之间可能会出现死锁，当 Oracle 检测到这种死锁时会返回错误，应用程序开发人员应极力避免这种死锁的发生。当一个自治块调用另一个自治块或其自身时，被调用的块不会与调用块共享任何事务上下文。然而，当自主块调用非自主块（即，未声明为自主事务的块）时，被调用块继承了调用自主块的事务上下文。

自动事务对于实现需要独立执行的操作非常有用，无论调用事务是否提交或回滚，例如记录错误日志或信息型消息，从而可以独立于父事务完成提交。

语法（声明为自治事务）：

PRAGMA AUTONOMOUS_TRANSACTION;

3.3、事务处理及案例

COMMIT：结束当前会话事务，并使得已做的所有修改成为永久性的。

语法：

COMMIT [WORK] [COMMENT clause] [WRITE clause] [FORCE clause];

默认是 COMMIT WORK WRITE WAIT IMMEDIATE，表示同步提交，如果明确写出 COMMIT NOWAIT 则表示异步提交。PL/SQL 一直都透明的使用异步提交，而流行的 API(ODBC 和 JDBC) 默认会自动提交事务。如果应用需要与人交互，就应当使用同步提交。对于面向客户的在线应用，不能把异步提交做为改善性能的手段。异步应用只适用于面向批处理的应用，也就是那些出现故障时能自动重启的应用。交互式应用在出现故障时无法自动重启，必须由人来重新执行事务。

ROLLBACK：回滚当前会话事务，并撤销所有未提交的修改。

语法：

ROLLBACK [WORK] [TO [SAVEPOINT] savepoint_name | FORCE 'string'];

SAVEPOINT：允许你在事务上下文中创建保存点，一个事务可以有多个保存点，将一个事务分为多个较小的部分。保存点只在事务执行过程中有效，事务结束即被释放。保存点在应用程序中同样有用。如果一个过程包含几个函数，那么可以在每个函数开始之前创建一个保存点。然后，如果函数失败，则在函数开始之前很容易将数据返回到其状态，并使用修改后的参数重新运行该函数或执行恢复操作。

ROLLBACK TO ：这个语句与 SAVEPOINT 一起使用，可以把事务回滚到保存点，而不回滚该保存点之前的任何工作。

SET TRANSACTION：允许你设置不同的事务属性，如事务的名称、隔离级别以及事务是只读的还是可读写的。SET TRANSACTION 语句执行的操作仅影响当前事务，而不影响其它用户或其它事务。

语法：

SET TRANSACTION [READ ONLY | READ WRITE]
                [ISOLATION LEVEL [SERIALIZE | READ COMMITED]
                [USE ROLLBACK SEGMENT 'segment_name']
                [NAME 'transaction_name'];

示例 1：

BEGIN
  INSERT INTO t3(f1) VALUES(1); -- 第一条修改数据的语句即事务的开始
  SAVEPOINT p1; -- 创建一个标记点
  INSERT INTO t3(f1) VALUES(2);
  ROLLBACK TO p1; -- 回滚到 p1，这样 p1 之后数据 2 就丢失了，只剩下 1
  INSERT INTO t3(f1) VALUES(3);
  COMMIT; -- 提交事务，所有保存点均失效，表中数据 1 和 3
END;

示例 2：

BEGIN
  SET TRANSACTION READ WRITE NAME 'tran1';
  SAVEPOINT p1; -- 创建一个标记点
  INSERT INTO t3(f1) VALUES(4);
  COMMIT; -- 提交事务
  INSERT INTO t3(f1) VALUES(5);
  ROLLBACK; -- 回滚事务，表中数据 1、3、4
END;

想要了解更多 Oracle 中的事务处理知识可参考：

• 《Oracle Database Concepts: Transaction Management》
• 《Oracle Database Administrator's Guide: Distributed Transactions Concepts》
• 《Oracle Database Administrator's Guide: Managing Distributed Transactions》。

4、总结

本文主要讲述了 PL/SQL 中的游标、异常和事务的基本概念和基本用法，且特别地讲述了 Oracle 中的两个非常特殊的事物——隐式游标和自治事务。

本文链接：http://www.cnblogs.com/hanzongze/p/oracle-plsql-2.html
版权声明：本文为博客园博主 韩宗泽 原创，作者保留署名权！欢迎通过转载、演绎或其它传播方式来使用本文，但必须在明显位置给出作者署名和本文链接！本人初写博客，水平有限，若有不当之处，敬请批评指正，谢谢！