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

时间:2024-01-20 23:17:27

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_2ex_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 中的事务处理知识可参考:

4、总结

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

本文链接http://www.cnblogs.com/hanzongze/p/oracle-plsql-2.html

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