您用的方法是在插入时用 A+日期+日期内记录的小流水号方法, 如若这是您必须使用的编码方法的话,请您参考我写过的一段控制并数据转换(数据割接)并发控制的存储过程。他使用 dbms_lock 包来自己定义锁来控制并发冲突。for help, call me on 022-23245196 or 13820980944create or replace procedure auto_trs2 ( id number) as stmt varchar2(200); v_count number(2); v_proc varchar2(32); v_block char(1); v_found boolean; v_flag boolean; v_lck varchar2(128); v_rtn number(38); v_stop char(1); v_enable char(1); v_truncate char(1) := 'N'; v_recursive char(1) := 'N'; cursor pre(a char) is select cmd,owner,tname from pk where proc=a and cls = 'B' order by seq; cursor post(b char) is select cmd,owner,tname from pk where proc=b and cls = 'A' order by seq; mold_dep varchar2(200) := 'call tool.p_list_dep2(:a,:b,:c,:d,:e)'; mold_pk_pre varchar2(200) := 'alter table [owner].[tname] modify primary key disable'; mold_pk_post varchar2(200) := 'alter table [owner].[tname] modify primary key using index nologging compute statistics enable exceptions into transfer2.exceptions'; begin rollback; dbms_lock.allocate_unique('trs2', v_lck); while true loop v_flag := false; while (not v_flag) loop begin set transaction isolation level serializable; v_rtn := dbms_lock.request(v_lck,dbms_lock.x_mode,dbms_lock.MAXWAIT,true); select stop into v_stop from tool.source; if v_stop = 'Y' then return; end if; select count(*) into v_count from t1_execute where stime is null; if v_count=0 then return; end if; update t1_execute set wtime=sysdate where proc in (select proc from v_ready2); select proc into v_proc from v_ready2 where proc in (select proc from v_ready1) and rownum<=1; update t1_execute set stime = sysdate, id=auto_trs2.id where proc = v_proc; commit; v_flag := true; exception when no_data_found then commit; dbms_lock.sleep(10); when others then rollback; dbms_lock.sleep(10); insert into debug values('H',id,'',sysdate); commit; end; end loop; -- 执行
dbms_output.put_line(''); dbms_output.put_line('--------------'); dbms_output.put_line(v_proc); -- pre execute dbms_output.put_line('pre execute:'); for c in pre(v_proc) loop if c.cmd = 'call' then dbms_output.put_line(c.owner || '.' || c.tname); v_enable := 'N'; execute immediate mold_dep using c.owner, c.tname, v_enable, v_truncate, v_recursive; elsif c.cmd = 'alter' then stmt := replace(mold_pk_pre,'[owner]',c.owner); stmt := replace(stmt,'[tname]',c.tname); dbms_output.put_line(stmt); execute immediate stmt; end if; end loop; stmt := 'call ' || v_proc || '()'; dbms_output.put_line(stmt); execute immediate stmt;
-- post execute dbms_output.put_line('post execute:'); for c in post(v_proc) loop if c.cmd = 'call' then dbms_output.put_line(c.owner || '.' || c.tname); v_enable := 'Y'; execute immediate mold_dep using c.owner, c.tname, v_enable, v_truncate, v_recursive; elsif c.cmd = 'alter' then stmt := replace(mold_pk_post,'[owner]',c.owner); stmt := replace(stmt,'[tname]',c.tname); dbms_output.put_line(stmt); execute immediate stmt; end if; end loop;
-- 被调用转换程序执行结束后,置执行完成时间 update t1_execute set etime = sysdate where proc = v_proc; commit;
-- 等待允许删除依赖关系(允许执行后续存储过程) v_flag := false; <<f_block>> while (not v_flag) loop select block into v_block from t1_execute where proc = v_proc; if upper(v_block) = 'Y' then dbms_lock.sleep(10); else exit f_block; end if; end loop; v_flag := false; while (not v_flag) loop begin -- begin transaction set transaction isolation level serializable; v_rtn := dbms_lock.request(v_lck,dbms_lock.x_mode,dbms_lock.MAXWAIT,true); -- 若某个正式表,其依赖的存储过程都执行完了,则将所有依赖于它的关系删掉 delete from t_ref r where not exists (select * from tar_depend d, t1_execute e where r.p_owner=upper(d.ora_owner) and r.p_tname=upper(d.ora_tname) and d.proc = e.proc and e.etime is null); v_flag := true; commit; exception when others then rollback; dbms_lock.sleep(10); insert into debug values('T',id,v_proc,sysdate); commit; end; end loop; end loop; end; /
orakiv(殷商人):好厉害,一写就这么多。还留下联系方式!有机会我们交流一下。
to orakiv(殷商人):哦,谢谢!给个电子邮件地址吧
怎么查看一条SQL语句的执行情况,如有没有执行成功??(在SQL/PL里) 比如update id_tbl set id=id+1 where to_char(l_date,'yymmdd')=to_char(sysdate,'yymmdd'); 我怎么查看update执行成功了呢??
我看到别人对这种情况(重号)是这么处理的: 1、建立一个序号表 2、在申请新的序列值的时候,先用Update将序列字段的值+1,但是不做commit,这样,相应的记录就被锁定; 3、判别SQLCode,如果成功,则说明这个序列值没有人使用,接着做4。不成功则提示稍候再试并Rollback; 4、用select取出序列值,+1后就是新的序列值; 5、把新序列值和相关数据进行对数据表的Insert,如果成功就接着做6。注意,对序号表的Update和对数据表的Insert是在一个事务里的,因此一旦对数据表的Insert操作失败而Rollback,则序号表的Update操作也被Rollback。这样一来,新的序列值并没有被真正commit到后台数据库里,还可以再一次使用,不会出现跳号的现象 6、commit。这样一来,数据表新增一条数据、序号表的相关序号+1。整个事务完成。针对上面的步骤:我写一些代码,不会写了: declare newid number(3); begin update id_tbl set id=id+1 where to_char(l_date,'yymmdd')=to_char(sysdate,'yymmdd'); if SQL%ROWCOUNT=0 then insert into id_tbl"(1,'232','dfd'); elsif sql%rowcount=1 then select id into newid from id_tbl; insert into logon_tbl values('A'||to_char(sysdate,'yymmdd')||to_char(newid,'fm000'),'232',sydate); ////////////////上面两句SQL的执行情况我怎么判断呢???如果执行成功则commit否则rollback! else rollback; end if; end;
如若这是您必须使用的编码方法的话,请您参考我写过的一段控制并数据转换(数据割接)并发控制的存储过程。他使用 dbms_lock 包来自己定义锁来控制并发冲突。for help, call me on 022-23245196 or 13820980944create or replace procedure auto_trs2
( id number)
as
stmt varchar2(200);
v_count number(2);
v_proc varchar2(32);
v_block char(1);
v_found boolean;
v_flag boolean;
v_lck varchar2(128);
v_rtn number(38);
v_stop char(1);
v_enable char(1);
v_truncate char(1) := 'N';
v_recursive char(1) := 'N';
cursor pre(a char) is select cmd,owner,tname from pk where proc=a and cls = 'B' order by seq;
cursor post(b char) is select cmd,owner,tname from pk where proc=b and cls = 'A' order by seq;
mold_dep varchar2(200) := 'call tool.p_list_dep2(:a,:b,:c,:d,:e)';
mold_pk_pre varchar2(200) := 'alter table [owner].[tname] modify primary key disable';
mold_pk_post varchar2(200) := 'alter table [owner].[tname] modify primary key using index nologging compute statistics enable exceptions into transfer2.exceptions';
begin
rollback;
dbms_lock.allocate_unique('trs2', v_lck);
while true loop
v_flag := false;
while (not v_flag) loop
begin
set transaction isolation level serializable;
v_rtn := dbms_lock.request(v_lck,dbms_lock.x_mode,dbms_lock.MAXWAIT,true);
select stop into v_stop from tool.source;
if v_stop = 'Y' then
return;
end if;
select count(*) into v_count from t1_execute where stime is null;
if v_count=0 then
return;
end if;
update t1_execute set wtime=sysdate where proc in (select proc from v_ready2);
select proc into v_proc from v_ready2 where proc in (select proc from v_ready1) and rownum<=1;
update t1_execute set stime = sysdate, id=auto_trs2.id where proc = v_proc;
commit;
v_flag := true;
exception
when no_data_found then
commit;
dbms_lock.sleep(10);
when others then
rollback;
dbms_lock.sleep(10);
insert into debug values('H',id,'',sysdate);
commit;
end;
end loop; -- 执行
dbms_output.put_line('');
dbms_output.put_line('--------------');
dbms_output.put_line(v_proc); -- pre execute
dbms_output.put_line('pre execute:');
for c in pre(v_proc) loop
if c.cmd = 'call' then
dbms_output.put_line(c.owner || '.' || c.tname);
v_enable := 'N';
execute immediate mold_dep using c.owner, c.tname, v_enable, v_truncate, v_recursive;
elsif c.cmd = 'alter' then
stmt := replace(mold_pk_pre,'[owner]',c.owner);
stmt := replace(stmt,'[tname]',c.tname);
dbms_output.put_line(stmt);
execute immediate stmt;
end if;
end loop; stmt := 'call ' || v_proc || '()';
dbms_output.put_line(stmt);
execute immediate stmt;
-- post execute
dbms_output.put_line('post execute:');
for c in post(v_proc) loop
if c.cmd = 'call' then
dbms_output.put_line(c.owner || '.' || c.tname);
v_enable := 'Y';
execute immediate mold_dep using c.owner, c.tname, v_enable, v_truncate, v_recursive;
elsif c.cmd = 'alter' then
stmt := replace(mold_pk_post,'[owner]',c.owner);
stmt := replace(stmt,'[tname]',c.tname);
dbms_output.put_line(stmt);
execute immediate stmt;
end if;
end loop;
-- 模拟执行
/*
dbms_lock.sleep(3); -- 用延迟 1 秒钟模拟执行过程
dbms_output.put_line(v_proc);
*/
-- 被调用转换程序执行结束后,置执行完成时间
update t1_execute set etime = sysdate where proc = v_proc;
commit;
-- 等待允许删除依赖关系(允许执行后续存储过程)
v_flag := false;
<<f_block>>
while (not v_flag) loop
select block into v_block from t1_execute where proc = v_proc;
if upper(v_block) = 'Y' then
dbms_lock.sleep(10);
else
exit f_block;
end if;
end loop; v_flag := false;
while (not v_flag) loop
begin
-- begin transaction
set transaction isolation level serializable;
v_rtn := dbms_lock.request(v_lck,dbms_lock.x_mode,dbms_lock.MAXWAIT,true); -- 若某个正式表,其依赖的存储过程都执行完了,则将所有依赖于它的关系删掉
delete from t_ref r
where not exists (select * from tar_depend d, t1_execute e
where r.p_owner=upper(d.ora_owner) and r.p_tname=upper(d.ora_tname)
and d.proc = e.proc and e.etime is null);
v_flag := true;
commit;
exception when others then
rollback;
dbms_lock.sleep(10);
insert into debug values('T',id,v_proc,sysdate);
commit;
end;
end loop;
end loop;
end;
/
比如update id_tbl set id=id+1
where to_char(l_date,'yymmdd')=to_char(sysdate,'yymmdd');
我怎么查看update执行成功了呢??
1、建立一个序号表
2、在申请新的序列值的时候,先用Update将序列字段的值+1,但是不做commit,这样,相应的记录就被锁定;
3、判别SQLCode,如果成功,则说明这个序列值没有人使用,接着做4。不成功则提示稍候再试并Rollback;
4、用select取出序列值,+1后就是新的序列值;
5、把新序列值和相关数据进行对数据表的Insert,如果成功就接着做6。注意,对序号表的Update和对数据表的Insert是在一个事务里的,因此一旦对数据表的Insert操作失败而Rollback,则序号表的Update操作也被Rollback。这样一来,新的序列值并没有被真正commit到后台数据库里,还可以再一次使用,不会出现跳号的现象
6、commit。这样一来,数据表新增一条数据、序号表的相关序号+1。整个事务完成。针对上面的步骤:我写一些代码,不会写了:
declare
newid number(3);
begin
update id_tbl set id=id+1
where to_char(l_date,'yymmdd')=to_char(sysdate,'yymmdd');
if SQL%ROWCOUNT=0 then
insert into id_tbl"(1,'232','dfd');
elsif sql%rowcount=1 then
select id into newid from id_tbl;
insert into logon_tbl values('A'||to_char(sysdate,'yymmdd')||to_char(newid,'fm000'),'232',sydate);
////////////////上面两句SQL的执行情况我怎么判断呢???如果执行成功则commit否则rollback!
else
rollback;
end if;
end;