oracle 存储过程插入个数达到某个值时方可插入

0 0

oracle 存储过程插入个数达到某个值时方可插入0

create or replace procedure proc_test is
V_TOTAL NUMBER(10);
begin
insert into test (remark, time, in, out)
    select zci.remark,
           zc.time+1/24/6,
           in,
           out
       from test_bak zc
      left join test_info zci on zc.id = zci.id
     where zci.remark is not null
     and zc.time > (select nvl(max(time)-1/24/6, sysdate-8)
                        from test);
      commit;

     select count(*) into V_TOTAL from test where time =
(select max(time) from test);
     if V_TOTAL <> 100 then
        delete from test where time = (select max(time) from test);
        commit;
     end if;
end proc_test;

以上是我写的存储过程，每次插入到test表中会有100条记录，是按时间来插入的，但会出现每次还没全部把100条记录插入到test表中，就已经无法插入（是按大于test表中最大时间方可插入）所以我做了个判断，当不到100条记录时，就把刚插入的那个时间点的记录都删除了可以不行啊要怎么弄

Oracle 存储过程

2012年11月28日 09:02

八岭书生
-2
0 1 7

1个答案按时间排序按投票排序

0 0

既然你的insert的原数据是来源那个子查询，那你就在那个子查询结果有100个的时候再去insert行不行呀？

create or replace procedure proc_test is
  V_TOTAL NUMBER(10);
begin
  select count(*)
    into V_TOTAL
    from (select zci.remark, zc.time + 1 / 24 / 6,in, out
            from test_bak zc
            left join test_info zci
              on zc.id = zci.id
           where zci.remark is not null
             and zc.time >
                 (select nvl(max(time) - 1 / 24 / 6, sysdate - 8) from test));

  if V_TOTAL = 100 then
    insert into test
      (remark, time, in, out)
      select zci.remark, zc.time + 1 / 24 / 6,in, out
        from test_bak zc
        left join test_info zci
          on zc.id = zci.id
       where zci.remark is not null
         and zc.time >
             (select nvl(max(time) - 1 / 24 / 6, sysdate - 8) from test);
    commit;
  end if;
end proc_test;

2012年11月28日 09:46