游标是一种数据处理机制,它提供了在结果集中依次浏览一行或多行数据的能力。游标就相当于是一个指针,它可以指定结果中的任何位置,然后允许用户对指定位置的数据进行处理。Oracle 中的游标分为显示游标和隐式游标两种。
每个游标和游标变量都有四个属性:%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。此后,它返回到目前为止已提取的行数。如果最近一次提取返回了一行,则该数字会递增。
显示游标主要用于处理查询语句,尤其是查询结果为多条记录的情况,需要由程序员显式地声明一个游标来单独处理这些行。而对于 SELECT ... INTO ...、INSERT、DELETE、UPDATE 等语句,Oracle 系统会自动地为这些操作设置隐式游标,并为该游标的取名为 SQL。隐式游标的相关操作均由 Oracle 系统自动完成,无需用户进行任何诸如打开或关闭之类的处理。
用户可以通过隐式游标的名称和属性来了解操作的状态和结果,进而控制程序的流程。但需要注意的是,通过 SQL 游标名只能访问前一个 DML 操作或单行 SELECT 操作的游标属性(所以通常得在执行完操作之后,立马使用 SQL 游标名来访问属性)。因为在隐式游标的工作区中,所存放的数据是与用户自定义的显示游标无关的、最新处理的一条 SQL 语句所包含的数据。
关于单行 SELECT 操作的隐式游标,还 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(遍历输出所有课程名称):
- DECLARE
- TYPE course_type IS REF CURSOR RETURN demo.t_course%ROWTYPE; -- 定义游标类型
- cursor1 course_type; -- 定义一个游标 cursor1
- v_course demo.t_course%ROWTYPE; -- 定义一个表示 t_course 表中行的变量
- BEGIN
- IF NOT cursor1%ISOPEN THEN -- 如果游标不是打开状态,如果试图打开一个已打开的游标时将会出现错误,所以应该要判断一下
- OPEN cursor1 FOR SELECT t.* FROM demo.t_course t; -- 打开游标,这里得确保 SELECT 列表和游标变量的数据类型一致
- LOOP
- FETCH cursor1 INTO v_course; -- 从游标中提取行
- EXIT WHEN cursor1%NOTFOUND; -- 如果找不到数据行了就退出循环
- DBMS_OUTPUT.PUT_LINE('课程名称:'||v_course.course_name); -- 打印被提取的数据
- END LOOP;
- END IF;
- CLOSE cursor1; -- 关闭游标,如果试图关闭一个已关闭的游标时也会出现错误
- END;
示例 2(遍历输出所有课程名称,并分段显示):
- DECLARE
- cursor1 SYS_REFCURSOR;
- v_course_name demo.t_course.course_name%TYPE;
- BEGIN
- OPEN cursor1 FOR SELECT t.course_name FROM demo.t_course t;
- LOOP
- FETCH cursor1 INTO v_course_name;
- EXIT WHEN cursor1%NOTFOUND;
- DBMS_OUTPUT.PUT_LINE(cursor1%ROWCOUNT||':'||v_course_name);
- IF cursor1%ROWCOUNT=2 THEN
- DBMS_OUTPUT.PUT_LINE('--- 已输出两行记录 ---');
- END IF;
- END LOOP;
- CLOSE cursor1;
- 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 cursor1 IS
- SELECT t.course_name,t.course_desc FROM demo.t_course t WHERE t.course_id=v_course_id;
- BEGIN
- OPEN cursor1;
- FETCH cursor1 INTO v_course_name,v_course_desc;
- IF cursor1%FOUND THEN
- DBMS_OUTPUT.PUT_LINE('名称:'||v_course_name||CHR(10)||'描述:'||v_course_desc);
- END IF;
- CLOSE cursor1;
- END;
示例 4(输出指定课程的相关信息):
- DECLARE
- CURSOR cursor1 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 cursor1;
- FETCH cursor1 INTO v_course;
- IF cursor1%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 cursor1;
- END;
示例 5(创建一个提取信息的程序包,用于根据条件提取不同表中的信息):
- CREATE PACKAGE pkg_take_info AS
- TYPE cursor_type IS REF CURSOR;
- PROCEDURE sp_take_info(cursor1 IN OUT cursor_type,choice INT);
- END pkg_take_info;
- CREATE PACKAGE BODY pkg_take_info AS
- PROCEDURE sp_take_info(cursor1 IN OUT cursor_type,choice INT) IS
- BEGIN
- IF choice=1 THEN
- OPEN cursor1 FOR SELECT t.* FROM demo.t_course t;
- ELSIF choice=2 THEN
- OPEN cursor1 FOR SELECT t.* FROM demo.t_staff t;
- ELSIF choice=3 THEN
- OPEN cursor1 FOR SELECT t.* FROM demo.t_field_enum t;
- END IF;
- CLOSE cursor1;
- END;
- END pkg_take_info;
异常(PL/SQL 运行时错误)可能源自设计错误、编码错误、硬件故障以及许多其它来源。我们往往无法预期所有可能的异常,但可以编写异常处理模块,让程序继续有效运行。
1、错误代码 SQLCODE 函数
在异常处理程序中,SQLCODE 函数返回正在处理的异常的数字代码。在异常处理程序之外,SQLCODE 返回 0。对于内部定义的异常,数字代码是有关 Oracle 数据库错误的编号。除 "no data found" 数字代码为 +100 之外,该数字一般是负数。对于用户自定义的异常,数字代码默认为 +1 或与
编译指示异常相关联的错误代码。在 SQL 语句无法调用 SQLCODE。
- EXCEPTION_INIT
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;
定义异常
语法:
- exception_name EXCEPTION;
如上所述,定义用户自定义异常的方法非常简单,只需给出异常名称即可。另外,还可以通过
将该名称分配给内部定义的异常,具体用法可参考:《Oracle Database PL/SQL Language Reference: EXCEPTION_INIT Pragma》。
- EXCEPTION_INIT
抛出异常
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
会被运行。如果在没有异常处理程序的块中引发异常,则异常传播。也就是说,异常在连续的封闭块中重现本身,直到被处理,如果没有被处理,则 PL/SQL 会向调用者或主机环境返回未处理的异常错误。
- statements_2
示例 (
):
- 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('未知错误!');
- 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('出现未知异常!');
- RAISE;
- END;
更多 Oracle 异常处理细节及案例,请参考:《Oracle Database PL/SQL Language Reference: PL/SQL Error Handling》。
事务是数据库区别于文件系统的特性之一。事务会把数据库从一种一致状态转变为另一种一致状态。Oracle 中的事务体现了所有必要的 ACID 特性。ACID 是以下 4 个词的缩写。
事务是包含一个或多个 SQL 语句的逻辑单元。事务中所有 SQL 语句的效果可以是全部提交(应用于数据库)或全部回滚(从数据库撤消)。
在 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 允许您回滚整个未提交的事务。还可以将未提交事务的尾部部分回滚到称为保存点的标记。
自治事务 (autonomous transaction) 允许你创建一个 "事务中的事务",它能独立于其父事务提交或回滚。利用自治事务,可以挂起当前执行的事务,开始一个新事务,完成一些工作,然后提交或回滚,所有这些都不影响当前所执行事务的状态。换句话说,自治事务允许从某个事务中调用另一个独立的事务。一旦被调用,自治事务就完全独立于调用它的主事务。在自治事务中,看不到主事务中发生的任何未提交的更改,并且不与主事务共享任何锁或资源。
在自治事务中还可以调用另一个自治事务,除了资源限制外,对于可以调用多少层级的自治事务没有限制。自治事务与其调用的事务之间可能会出现死锁,当 Oracle 检测到这种死锁时会返回错误,应用程序开发人员应极力避免这种死锁的发生。当一个自治块调用另一个自治块或其自身时,被调用的块不会与调用块共享任何事务上下文。然而,当自主块调用非自主块(即,未声明为自主事务的块)时,被调用块继承了调用自主块的事务上下文。
自动事务对于实现需要独立执行的操作非常有用,无论调用事务是否提交或回滚,例如记录错误日志或信息型消息,从而可以独立于父事务完成提交。
语法(声明为自治事务):
- PRAGMA AUTONOMOUS_TRANSACTION;
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
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 中的事务处理知识可参考:
本文主要讲述了 PL/SQL 中的游标、异常和事务的基本概念和基本用法,且特别地讲述了 Oracle 中的两个非常特殊的事物——隐式游标和自治事务。
来源: http://www.cnblogs.com/hanzongze/p/Oracle-plsql-2.html