空间管理您的位置: 51Testing软件测试网 » Mr.南柯 » 日志

PLSQL中显示游标使用的4个步骤

上一篇 / 下一篇 2012-09-21 09:18:13 / 个人分类：数据库

查看( 3739 ) / 评论( 0 ) / 评分( 0 / 0 )

t U`I)F VKI)C M0　　本例简单介绍一下游标处理的4个过程中涉及到的加锁，一致性读的问题.

　　显式游标处理需四个 PL/SQL步骤：

:G8Irv9\H'D\5e ~0　　1、定义/声明游标：就是定义一个游标名，以及与其相对应的SELECT 语句。

(FX2xKWe9Um8P0　　格式：

    CURSOR cursor_name[(parameter[, parameter]…)]
           [RETURN datatype]
    IS
        select_statement;

　　游标参数只能为输入参数，其格式为：51Testing软件测试网$V t/G"va

　　parameter_name [IN] datatype [{:= | DEFAULT} expression]

M/Qe[X0　　在指定数据类型时，不能使用长度约束。如NUMBER(4),CHAR(10) 等都是错误的。

　　[RETURN datatype]是可选的，表示游标返回数据的数据。如果选择，则应该严格与select_statement中的选择列表在次序和数据类型上匹配。一般是记录数据类型或带“%ROWTYPE”的数据。

T&S$vpy"R0　　2、打开游标：就是执行游标所对应的SELECT 语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集合。如果游标查询语句中带有FOR UPDATE选项，OPEN 语句还将锁定数据库表中游标结果集合对应的数据行。

8z(n G.dj0　　格式：

　　OPEN cursor_name[([parameter =>] value[, [parameter =>] value]…)];

　　在向游标传递参数时，可以使用与函数参数相同的传值方法，即位置表示法和名称表示法。PL/SQL 程序不能用OPEN 语句重复打开一个游标。

]H&?zQ8a|D0　　3、提取游标数据：就是检索结果集合中的数据行，放入指定的输出变量中。

,w4I8~;V[C_6Hf{0　　格式：

　　FETCH cursor_name INTO {variable_list | record_variable };

g O1jkuG^&z0　　执行FETCH语句时，每次返回一个数据行，然后自动将游标移动指向下一个数据行。当检索到最后一行数据时，如果再次执行FETCH语句，将操作失败，并将游标属性%NOTFOUND置为TRUE。所以每次执行完FETCH语句后，检查游标属性%NOTFOUND就可以判断FETCH语句是否执行成功并返回一个数据行，以便确定是否给对应的变量赋了值。

Y7asG@0v7JX0　　4关闭游标：当提取和处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占用的系统资源，并使该游标的工作区变成无效，不能再使用FETCH 语句取其中数据。关闭后的游标可以使用OPEN 语句重新打开。

.us.dvT0　　格式：51Testing软件测试网kd-`z+^

!M5?["gl-N2Ljd0　　CLOSE cursor_name;51Testing软件测试网!Q'i^YZM

　　下面用一个例子来说明：

CREATE OR REPLACE PROCEDURE testcur IS
"C|c_}0NPn[0  CURSOR c_cursor(r_no NUMBER DEFAULT 5)IS
    SELECT ISBN, TITLE
3p&|3J7E3lv[ _0      FROM BOOKS
     WHERE rownum < r_no51Testing软件测试网.j%T#F+r|$Ec
       FOR UPDATE;
f)XZ:jk.b0  v_isbn BOOKS.ISBN%TYPE;
4} HGv)v1y[/[0  v_title   BOOKS.TITLE%TYPE;

BEGIN
AEz\ Aa?6e0  --OPEN c_cursor(5);
8R9sU!qZB}+y%a0  OPEN c_cursor(r_no=>7);
N0F v[{'Rmq-gQc0  DBMS_OUTPUT.PUT_LINE('opened cursor'); --afater open
h`8SH!ZI4_0  --DBMS_LOCK.SLEEP(30);

  FETCH c_cursor
Ew4eLf0    INTO v_isbn, v_title;
6c:]|\ @.w2?o,|0  DBMS_OUTPUT.PUT_LINE('fetched cursor');
Z&h9Xo^sz0  --DBMS_LOCK.SLEEP(30);
V4\RoT6uXs;o"OG0  WHILE c_cursor%FOUND LOOP
(^5{M)wRu0    DBMS_OUTPUT.PUT_LINE(v_isbn || '---' || to_char(v_title));51Testing软件测试网Q'SXw yq0b
    FETCH c_cursor
#]6e Np1H[(d0      INTO v_isbn, v_title;
G)c6b`C:me0  END LOOP;
+PN5q-zAq*u0  CLOSE c_cursor;
Gwc`._/o] `#P0  DBMS_OUTPUT.PUT_LINE('closed cursor');
&^s W:Q!VHGg(N0  ROLLBACK;
U:d#^me+i b x0  DBMS_OUTPUT.PUT_LINE('ended transaction');
END testcur;51Testing软件测试网nK3o0PB/v

　　定义/声明游标的时候只是把SQL与游标关联，在open的时候才和表发生查询关系，如果有for update会有

　　加锁发生，如在本例中，OPEN c_cursor后，通过debug的方式停止在'--afater open'，通过

Kf3vR)K0　　select * from v$lock where sid in (select distinct sid from v$session where username='PLSQL')可以看到有加锁的记录，本例是以PLSQL用户登录的.在fetch的过程中，Oracle会保证一致性读，可以通过将For update去掉，在fetch的过程中在另外一个session中更新并提交数据来观察.大家可以参考我的另外一篇blog http：//blog.csdn.net/kkdelta/article/details/7183981模拟了这个过程.close cursor并不会释放for update获得的锁，事务结束才释放。51Testing软件测试网t;T0`5e#x!\

　　另外，最先我是想用DBMS_OUTPUT.PUT_LINE和DBMS_LOCK.SLEEP配合来观察加锁和释放锁的现象，结果是 DBMS_OUTPUT.PUT_LINE要在整个存储过程执行完之后才会输出，后来只好在PL/SQL Developer中通过debug的方式观察，大家要注意了。51Testing软件测试网lp9n&~I&LO

　　在sqlplus中使用游标可以用以下方式：

　　variable c refcursor;

　　exec open :c for SELECT ISBN, TITLE FROM BOOKS WHERE rownum < 5 FOR UPDATE;

fJV)?i0Ut#]n8ZS:RO0　　print c;

　　rollback;

O*Q1g`ED4h0N0　　同样exec open 的时候会加锁，最后需要显示结束事务才能释放锁。

　　PLSQL中还有一个称为游标变量的概念，与游标不同的是，游标变量是动态的，而游标是静态的。游标只能与指定的查询相连，即固定指向一个查询的内存处理区域，而游标变量则可与不同的查询语句相连，它可以指向不同查询语句的内存处理区域（但不能同时指向多个内存处理区域，在某一时刻只能与一个查询语句相连）。

　　声明一个游标变量的需要注意的步骤：

　　1、定义一个REF CURSOU数据类型，如：

　　TYPE ref_cursor_type IS REF CURSOR;

8r)r~ @.^{V0　　2、声明一个该数据类型的游标变量，如：51Testing软件测试网:f,B"sf8aP

.q/l\Dn:H6`,n0　　cv_ref REF_CURSOR_TYPE；51Testing软件测试网qJ]r1u&[

%_ r8X1A#d0　　3、在打开的时候和一个SQL语句关联：

CP(ND?6O_p0　　OPEN cursor_variable_name

]'? ~6\A|4a&a[0　　FOR select_statement;

d?.W_nD [.y]0　　后面这和游标的使用相同了，如下例：

DECLARE
    TYPE refcur_type IS REF CURSOR;
    book_refcur refcur_type ;
    v_isbn varchar2(100);
    v_title   varchar2(100);
BEGIN
    OPEN book_refcur FOR
    SELECT ISBN, TITLE FROM BOOKS WHERE rownum<3;

    FETCH book_refcur INTO v_isbn,v_title;
    WHILE book_refcur%FOUND LOOP
       DBMS_OUTPUT.PUT_LINE(v_isbn||': '||v_title);
    FETCH book_refcur INTO v_isbn,v_title;
    END LOOP;
    CLOSE book_refcur;

    -- open cursoe with another sql statement
    DBMS_OUTPUT.PUT_LINE('------- open cursoe with another sql statement');
    OPEN book_refcur FOR
    SELECT ISBN, category FROM BOOKS WHERE rownum<3;

    FETCH book_refcur INTO v_isbn,v_title;
    WHILE book_refcur%FOUND LOOP
       DBMS_OUTPUT.PUT_LINE(v_isbn||': category is: '||v_title);
    FETCH book_refcur INTO v_isbn,v_title;
    END LOOP;
    CLOSE book_refcur;
END;

收藏举报

TAG:

查看全部评论

bob123654

不要让那些真正对你好的人，慢慢的从你的生活中消失，无论爱情还是友情，都需要用心经营。

PLSQL中显示游标使用的4个步骤

用户菜单

我的栏目

标题搜索

日历

我的存档

数据统计

RSS订阅