Oracle PL/SQL 中のカーソルの読み取り一貫性

PL/SQL の読取り一貫性を確認

script 1 (cursorでselect)

t2の全行を 1秒おきに1行 fetchすることを、3回ループする

[oracle@db01 20220702]$ cat loop_in_cursor.sql

set line 200 pages 1000 tab off time on timing on serveroutput on
drop table t2 purge;
create table t2 (c1 number(10), c2 varchar2(40));

insert into t2
select level, to_char(sysdate , 'yyyy/mm/dd hh24:mi:ss')
from dual connect by level <= 10;
commit;

declare
        vSQL varchar2(200);
        vC1 number(19);
        vC2 varchar2(40);
        cursor c is
                select
                        c1,
                        c2
                from
                        t2
                ;
begin
        for i in 1..3 loop
                dbms_output.put_line('---- loop ' || to_char(i));
                open c;
                        loop
                                fetch c into vC1, vC2;
                                exit when c%notfound;
                                        dbms_output.put_line(to_char(vC1) || ', ' || vC2);
                                dbms_session.sleep(1);
                        end loop;
                close c;
        end loop;
end;
/

quit;

script 2 (update)

t2 を1行ずつ、1秒間隔で、全行更新するまでループする

[oracle@db01 20220702]$ cat loop_update.sql

set line 200 pages 1000 tab off time on timing on serveroutput on
begin
        for i in 1..20 loop

                update t2 set c2 = to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') where c1 = i;
                commit;
                dbms_session.sleep(1);

        end loop;
end;
/

結果

script 実行中に、script 2を実行する。 script 1 のloop 1回目は、最初に投入したデータを表示 script 1 のloop 2回目は、script 2 が t2 を途中まで更新したデータを表示 script 1 のloop 3回目は、script 2 が t2 を最後まで表示したデータを表示

→ カーソルがオープンしたタイミングのデータが表示される

• script 1 結果

[oracle@db01 20220702]$ sqlplus inserter/oracle@pdb1 @loop_in_cursor.sql

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jul 2 06:21:12 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Sat Jul 02 2022 06:20:11 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0


Table dropped.

Elapsed: 00:00:00.01

Table created.

Elapsed: 00:00:00.01

10 rows created.

Elapsed: 00:00:00.00

Commit complete.

Elapsed: 00:00:00.00
---- loop 1
1, 2022/07/02 06:21:12
2, 2022/07/02 06:21:12
3, 2022/07/02 06:21:12
4, 2022/07/02 06:21:12
5, 2022/07/02 06:21:12
6, 2022/07/02 06:21:12
7, 2022/07/02 06:21:12
8, 2022/07/02 06:21:12
9, 2022/07/02 06:21:12
10, 2022/07/02 06:21:12
---- loop 2
1, 2022/07/02 06:21:16
2, 2022/07/02 06:21:17
3, 2022/07/02 06:21:18
4, 2022/07/02 06:21:19
5, 2022/07/02 06:21:20
6, 2022/07/02 06:21:21
7, 2022/07/02 06:21:22
8, 2022/07/02 06:21:12
9, 2022/07/02 06:21:12
10, 2022/07/02 06:21:12
---- loop 3
1, 2022/07/02 06:21:16
2, 2022/07/02 06:21:17
3, 2022/07/02 06:21:18
4, 2022/07/02 06:21:19
5, 2022/07/02 06:21:20
6, 2022/07/02 06:21:21
7, 2022/07/02 06:21:22
8, 2022/07/02 06:21:23
9, 2022/07/02 06:21:24
10, 2022/07/02 06:21:25

PL/SQL procedure successfully completed.

Elapsed: 00:00:30.74
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@db01 20220702]$

• script 2 結果

[oracle@db01 20220702]$ sqlplus inserter/oracle@pdb1 @loop_update.sql

SQL*Plus: Release 19.0.0.0.0 - Production on Sat Jul 2 06:21:16 2022
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Sat Jul 02 2022 06:21:12 +00:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0


PL/SQL procedure successfully completed.

Elapsed: 00:00:20.48
06:21:36 SQL>