Chinaunix首页 | 论坛 | 博客
  • 博客访问: 971532
  • 博文数量: 584
  • 博客积分: 2293
  • 博客等级: 大尉
  • 技术积分: 3045
  • 用 户 组: 普通用户
  • 注册时间: 2006-03-28 11:15
文章分类

全部博文(584)

文章存档

2012年(532)

2011年(47)

2009年(5)

我的朋友

分类:

2012-08-13 17:39:31

    本文测试在pl/sql编程中,更新游标数据的2种方式以及并发条件下各种方式的实际表现。2种方式的效率问题不在此文讨论之列!

  

一、环境准备

数据库:Oracle10.2.0.4

测试工具:PL/SQL Developer9

 

二、数据准备

我们使用oracle自带的演示用户scott登录数据库,为清楚看到数据变化,执行以下语句:

 

SQL> update emp t set t.sal=1000;

 14 rows updated

 SQL> commit;

 Commit complete

Emp表的sal字段初始化为1000

查询emp表:

SELECT t.empno, t.ename,t.job, t.sal, ROWID rd from emp t;

结果如图1所示:

1

Sal字段已经全部更新为1000

 

三、创建存储过程ps_cursor_for_update

CREATE OR REPLACE PROCEDURE ps_cursor_for_update

(p_sal PLS_INTEGER) IS

  CURSOR c IS

    SELECT t.empno, t.ename, t.hiredate, ROWID rd

      FROM EMP t

     WHERE t.sal = 1000

       FOR UPDATE;

  v_emp_record c%ROWTYPE;

  v_rows       PLS_INTEGER := 0;

BEGIN

  OPEN c;

  LOOP

    FETCH c

      INTO v_emp_record;

    EXIT WHEN c%NOTFOUND;

    UPDATE EMP SET sal = p_sal WHERE CURRENT OF c;

    v_rows := SQL%ROWCOUNT;

    dbms_output.put_line(v_rows);

  END LOOP;

  COMMIT;

  CLOSE c;

END ps_cursor_for_update;

 

四、测试ps_cursor_for_update

PL/SQL Developer工具具有很强大的plsql调试功能,我们使用两个test窗口进行模拟并发执行的情况

在编辑存储过程的界面,在打开游标的代码行加入一个断点:

2

在存储过程ps_cursor_for_update上点击右键,打开两个test窗口:

窗口1中,输入参数填写2000

窗口2中,输入参数填写3000,如图34所示:

 

3

4

两个窗口分别点击 start bugger按钮,开始调试,并点击run按钮,分别运行到打开游标的一行,并在窗口2中进行单步调试,运行到如图5所示位置:

5

 

此时窗口1中,开始单步调试,发现状态栏处于运行中(图7),但调试光标始终停在断点行(图6

6

7

说明游标打开的数据已经被窗口2的进程锁定,所以窗口1的进程无法打开数据

下面把窗口2的断点去掉,并点击run按钮使此过程执行完毕,可以发现,此时窗口1中,代码已经执行到了原断点位置的下一行:

8

结论:窗口2执行了commit语句,PL/SQL过程结束,并解锁操作的数据,使窗口1的过程得以打开游标。

查询emp表的数据:

9

发现sal字段已经更新为3000

注意,打开游标的sql条件中,sal字段是1000,所以,继续运行窗口1,完成PL/SQL过程,再次查看emp数据:

10

发现sql字段还是3000

结论:由于窗口2锁定了emp数据,并将sal字段更新为3000,使得窗口1在窗口2执行完毕而打开游标时,sal1000的数据已经不存在了,所以窗口1PL/SQL过程,没有打开任何数据,也就没有更新任何数据。

 

由此可知,在打开游标的select语句中,使用for update子句,能在并发条件下有效地保证数据的正确。

 

五、创建存储过程ps_cursor_no_for_update

CREATE OR REPLACE PROCEDURE ps_cursor_no_for_update(p_sal PLS_INTEGER) IS

  CURSOR c IS

    SELECT t.empno, t.ename, t.hiredate, ROWID rd

      FROM EMP t

     WHERE t.sal = 1000;

  v_emp_record c%ROWTYPE;

  v_rows       PLS_INTEGER := 0;

BEGIN

  OPEN c;

  LOOP

    FETCH c

      INTO v_emp_record;

    EXIT WHEN c%NOTFOUND;

    UPDATE EMP SET sal = p_sal WHERE ROWID = v_emp_record.rd;

    v_rows := SQL%ROWCOUNT;

    dbms_output.put_line(v_rows);

  END LOOP;

  COMMIT;

  CLOSE c;

END ps_cursor_no_for_update;

 

此过程游标的查询条件仍未sal=1000,而打开游标的select语句,没有for update子句,游标数据使用rowid作为唯一标识作更新操作

 

六、测试ps_cursor_no_for_update

 

先将emp表的sal字段初始化为1000

SQL> update emp t set t.sal=1000;

 14 rows updated

 SQL> commit;

 Commit complete

Emp表的sal字段初始化为1000

查询emp表:

SELECT t.empno, t.ename,t.job, t.sal, ROWID rd from emp t;

结果如下:

11

Sal字段已经全部更新为1000

 

编辑存储过程ps_cursor_no_for_update的界面中,设置断点如图12所示:

12

 

分别打开2test窗口,设置输入参数为20003000

窗口1和窗口2分别启动断点调试,并点击run按钮运行,可发现两个窗口都运行到了断点所在位置:

13

这时,我们在窗口1中单步调试,运行到输出语句一行:

14

 

Ok,窗口1停在这里,在窗口2进行相同的动作,发现窗口2的光标仍停在update语句一行,也就是断点所在行;

 

结论:PL/SQL过程中,打开游标时未使用for update子句时,如果两个进程同时调用该过程,则游标可以同时打开,但在第一个update语句执行后,其它进程则进入等待状态。

 

好,我们继续将窗口1的过程执行完毕,然后查询emp的数据:

15

数据已经成功更新为2000

再看一下窗口2,调试的光标已经移动到了下一行:

16

说明窗口1的过程执行完毕,被update语句锁住的数据已经解锁,所以窗口的过程可以继续执行了;

 

将窗口2的过程执行结束,再次查看emp数据:

17

Sal字段已经更新为3000,说明窗口2PL/SQL过程更新成功了

 

七、结论

结论:在PL/SQL过程中打开游标,在未使用for update子句的条件下,多个进程更新相同数据时,可能会出现数据的不一致性,所以在具体实施过程中,要根据具体需求来判断,是否需要使用for update子句。

 

阅读(662) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~