oracle 存储过程基础
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;
最新文章
- 时隔一年再读到the star
- Python多版本共存之pyenv
- [转载]android的消息处理机制（图+源码分析）——Looper,Handler,Message
- ShadowGun Deadzone 放出 GM Kit Mod 包
- [BZOJ 3564] [SHOI2014] 信号增幅仪 【最小圆覆盖】
- [C++ Basic] Const 用法
- 201521123111《Java程序设计》第6周学习总结
- java 可变參数
- BZOJ 2286: [Sdoi2011消耗战 [DP 虚树]
- SVN服务器搭建和配置使用详解
- Oracle根据符合条件的数据循环批量更新
- php中一个"异类"语法： $a && $b = $c; 【转载】
- 图->连通性->无向图的连通分量和生成树
- mysql的in和not in的用法（特别注意not in结果集中不能有null）
- perl hash array 嵌套 push
- LY.JAVA面向对象编程.内部类
- mysql分组GROUP BY常用sql
- 1、JUC--volatile 关键字-内存可见性
- 前端框架 Vue 初探
- Linux 环境 HTTP 服务器