create or replace procedure update_CarryoverArchivers(bizsysname in varchar,
year in number default 2015) is
begin
declare
num number;
objSql varchar(1000);
i number;
begin
num := -1;
select count(*) into num from bd_doc_type where iyear = year;
if num >= 1 then
null;
end if;
if num = 0 then
objSql := 'insert into BD_DOC_TYPE select sdoc_type_code,sdoc_type_name,sshort_text,smudole,' || year ||
',stype,sprinttemplatecode, ''' || year ||
'/01/01 16:51:32'' from BD_DOC_TYPE where iyear=' ||
to_char(year - 1);
/*dbms_output.put_line(objSql);*/
execute immediate objSql;
end if;
num := -1;
select count(*) into num from sm_billtype where iyear = year;
if num >= 1 then
null;
end if;
if num = 0 then
objSql := 'insert into sm_billtype select scode,sname,sbilltypetitle,' || year ||
',''' || year ||
'/12/12 15:03:40'' from sm_billtype where iyear=' ||
to_char(year - 1);
/* dbms_output.put_line(objSql);*/
execute immediate objSql;
end if;
num := -1;
select count(*) into num from bd_fyearstruct_m where iyear = year;
if num >= 1 then
null;
end if;
if num = 0 then
objSql := 'insert into ' || bizsysname || '.BD_FYEARSTRUCT_M (IYEAR, DY_STARTDATE, DY_ENDDATE, IPERIODNUM, BCLOSED, BACTIVEYEAR, TS)
values (' || year || ', ''' || year || '0101'', ''' || year ||
'1231'', 12, ''n'', ''Y'', ''' || year ||
'/01/01 20:33:38'')';
dbms_output.put_line(objSql);
execute immediate objSql;
dbms_output.put_line('xxx');
end if;
num := -1;
i := 1;
select count(*) into num from bd_fyearstruct_s where iyear = year;
if num >= 1 then
null;
end if;
if num = 0 then
while i <= 12 loop
objSql := ' insert into ' || bizsysname || '.BD_FYEARSTRUCT_S (SPERIODCODE, IYEAR, DP_BEGINDATE, DP_ENDDATE, SPERIODNAME, BCLOSED, BACITVE, TS)
values (''' || year || '-0' || to_char(i) || ''',' || year ||
', ''' || year || '0' || to_char(i) ||
'01'',to_char(add_months(to_date(''' || year || '0' || to_char(i) ||
'01'', ''yyyymmdd''), 1)-1,''yyyymmdd''),''第' || to_char(i) ||
'期'', ''N'', null, ''1999/05/25 11:33:53'') ';
dbms_output.put_line(objSql);
execute immediate objSql;
i := i + 1;
end loop;
end if;
num := -1;
select count(*) into num from sm_billrule where iyear = year;
dbms_output.put_line(num);
if num >= 1 then
null;
end if;
if num = 0 then
objSql := 'insert into sm_billrule select SID, SBILLTYPECODE, BIFSHOWBILLTITLE, BYEAR, BMONTH, BDAY, BIFORDERNUMBER, INUMBERLEN, BIFSHOWOBJID, SPERIODCODE, SCORPCODE,' || year ||
' from sm_billrule where iyear=' || to_char(year - 1);
dbms_output.put_line(objSql);
execute immediate objSql;
end if;
commit;
Exception
When others then
dbms_output.put_line('你出错了'||sqlcode||sqlerrm);
Rollback;
end;
end update_CarryoverArchivers;
---------
存储过程的执行
begin
update_CarryoverArchivers;
end;