--触发器代码:
create or replace trigger seal_use_apply_trigger
after insert on seal_use_apply
for each row
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
commit;
seal_use_apply_procedure;
commit;
exception
--异常处理代码块
when no_data_found then
dbms_output.put_line('发生系统异常:未找到有效的数据!');
END
seal_use_apply_trigger;
/
存储过程代码:
create or replace procedure seal_use_apply_procedure is
begin
declare
TYPE c1 IS REF CURSOR;
v_apply_id varchar2(50);
v_gw_year number(12);
v_gw_year_number number(12);
v_gapply_type varchar2(50);
v_temp_cursor c1;
v_gw_year_num_now number(12);
begin
commit;
-- Test statements here
OPEN v_temp_cursor
FOR
select apply_id,gw_year,gw_year_number,apply_type from
(select m.*,row_number() over (partition by m.apply_type,m.gw_year,m.gw_year_number order by m.apply_type,m.gw_year,m.gw_year_number) as group_idx
from seal_use_apply m
where m.gw_year||m.gw_year_number||m.apply_type in
(
select gw_year||gw_year_number||apply_type from (
select count(1) as coun , t.gw_year,t.gw_year_number,t.apply_type from seal_use_apply t
group by (t.gw_year,t.gw_year_number,t.apply_type) ) where coun>1
)
order by m.apply_type,m.gw_year,m.gw_year_number) where group_idx =1;
LOOP
fetch v_temp_cursor into v_apply_id,v_gw_year,v_gw_year_number,v_gapply_type;
EXIT WHEN v_temp_cursor%NOTFOUND;
select max(y.gw_year_number) into v_gw_year_num_now from seal_use_apply y where y.apply_type = v_gapply_type and y.gw_year = v_gw_year;
update seal_use_apply w set w.gw_year_number = (v_gw_year_num_now+1) where w.apply_id = v_apply_id;
commit;
dbms_output.put_line(v_apply_id);
dbms_output.put_line(v_gw_year_num_now);
commit;
END LOOP;
CLOSE v_temp_cursor;
end;
end seal_use_apply_procedure;
/