-- 使用dbms_lock同步并发启动时间

/**
买珠还椟,虽然CBO还没搞明白，也能学点其它技巧。
在看Jonathan Lewis的CBO基础时，看到一个控制并发的技巧，摘录下来，以后做开发时做个参考
让5个会话的插入操作，并发执行
源代码如下：
*/

create table t1(
date_ord date constraint t1_dto_nn not null,
seq_ord number(6) constraint t1_sqo_nn not null,
small_vc varchar2(10)
)
pctfree 90
pctused 10
;

drop sequence t1_seq;
create sequence t1_seq;

create or replace procedure t1_load(i_tag varchar2) as

m_date date;

begin
-- 同步并发启动时间
dbms_output.put_line(
dbms_lock.request(
1,dbms_lock.s_mode, release_on_commit => true
)
);
commit;
for i in 0..25 loop
m_date := trunc(sysdate) + i;

for j in 1..200 loop
insert into t1 values(
m_date,
t1_seq.nextval,
i_tag || j
);
commit;
dbms_lock.sleep(0.01);
end loop;
end loop;
end;
/

begin
dbms_output.put_line(
dbms_lock.request(
1,dbms_lock.x_mode,
release_on_commit=>true
)
);
end;
/

prompt
prompt From five different sessions, execute a call to procedure
prompt t1_load with a different tag for each session, e.g.
prompt execute t1_LOAD('a')
prompt
prompt These will all suspend, waiting to acquire a share lock that
prompt this progam is holding in exclusive mode. When all five sessions
prompt have called the procedure, press return in this screen to allow them
prompt to continue

accept x

commit;