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
版权声明：本文为博客园博主 韩宗泽 原创，作者保留署名权！欢迎通过转载、演绎或其它传播方式来使用本文，但必须在明显位置给出作者署名和本文链接！本人初写博客，水平有限，若有不当之处，敬请批评指正，谢谢！