实现序列自增，增量插入数据

create or replace package stu_pkg as
procedure stu_q;

procedure stu_p;

end stu_pkg;
/
create or replace package body stu_pkg as

/*========================================================================================
EFFECT:维表数据插入临时表
TARGET_TABLE:student_tmp
AUTHOR:Gorkor
DATE:2021-8-2
======================================================================================*/
procedure stu_q is

v_count number;
v_id number;

BEGIN

select count(*) into v_count from student_t;  

for i in 1 .. v_count-1 loop

select max(t.stu_id) into v_id from student t;  

  insert into student_tmp
    (stu_id, stu_name, update_date)
    select case
             when s.stu_name = t.stu_name then
              t.stu_id
             else
              v_id + 1
           end as stu_id,
           s.stu_name,
           sysdate
      from student_t s, student t
     where s.stu_name = t.stu_name(+);

end loop;

commit;

end stu_q;

/*========================================================================================
EFFECT:维表数据插入事实表
TARGET_TABLE:student
AUTHOR:Gorkor
DATE:2021-8-2
======================================================================================*/
procedure stu_p is

BEGIN

merge into student t

using (select s.stu_id, s.stu_name 
  from student_tmp s where s.rowid =
    (select max(s.rowid)from student_tmp t where s.stu_id =t.stu_id)) e

on (e.stu_name = t.stu_name)

when matched then
  update set t.stu_id = e.stu_id, t.update_date = sysdate
  
when not matched then
  
  insert
    (stu_id, stu_name, update_date)
  values
    (e.stu_id, e.stu_name, sysdate);

commit;

end stu_P;

end stu_pkg;
/