create or replace procedure prc_sjsb_sbzq(in_monthSbzq in varchar2,in_fixedAssetSbzq in varchar2,
in_weekSbzq in varchar2,in_startDate varchar2,in_endDate varchar2,in_seasonSbzq in varchar2 ) is
v_firstMonth varchar(10); ---每年第一个月
v_count_gdzctz number; ---固定资产投资总数
v_count_gysc number; ---工业生产总数
v_count_zdxm number; ---重大项目数
v_count_zdqy number; ----重点企业数
v_count_cyjjq number; ----产业集聚区数
v_count_msb number; ----煤上报数
v_count_dlsb number; ----电力上报数
v_count_ysb number; ----油上报数
v_count_qsb number; ----气上报数
v_count_tlhysb number; ----铁路货运上报数
v_count_spfsc number; ----商品房市场
v_count_zsyz number; ----招商引资数
v_count_dwmy number; ----对外贸易数
v_count_czsr number; ----财政收入数
v_count_jryx number; ----金融运行数
v_count_jmxfsp number; ----居民消费水平数
v_count_zygypjg number; ----主要工业品价格
v_count_lq number; ----两区上报数
v_count_zhqk number; ----综合情况
begin
select to_char(sysdate,'MM') into v_firstMonth from dual; ---取当前月份
----固定资产
if v_firstMonth <>'01'or v_firstMonth<> '1' then --1月份不插入数据判断开始
select count(1) into v_count_gdzctz from T_SJSB_GDZCTZ where sbzq=in_fixedAssetSbzq;
if v_count_gdzctz =0 then ---没有本月数据,则插入
begin
insert into T_SJSB_GDZCTZ(sbzq,sbzt) values(in_fixedAssetSbzq,'0');
end;
end if;
--工业生产
select count(1) into v_count_gysc from T_SJSB_GYSC where sbzq=in_monthSbzq;
if v_count_gysc =0 then ---没有本月数据,则插入
begin
insert into T_SJSB_GYSC(sbzq,sbzt) values(in_monthSbzq,'0');
end;
end if;
--产业集聚区
select count(1) into v_count_cyjjq from T_SJSB_CYJJQ where sbzq=in_monthSbzq;
if v_count_cyjjq =0 then ---没有本月数据,则插入
begin
insert into T_SJSB_CYJJQ(sbzq,sbzt) values(in_monthSbzq,'0');
end;
end if;
end if; --1月份不插入数据判断结束
--重大项目
select count(1) into v_count_zdxm from T_SJSB_ZDXM where sbzq=in_monthSbzq;
if v_count_zdxm =0 then ---没有本月数据,则插入
begin
insert into T_SJSB_ZDXM(sbzq,sbzt) values(in_monthSbzq,'0');
end;
end if;
--重点企业
select count(1) into v_count_zdqy from T_SJSB_ZDQY where sbzq=in_monthSbzq;
if v_count_zdqy =0 then ---没有本月数据,则插入
begin
insert into T_SJSB_ZDQY(sbzq,sbzt) values(in_monthSbzq,'0');
end;
end if;
--煤上报
select count(1) into v_count_msb from T_SJSB_MDYQY_MSB where sbzq=in_monthSbzq;
if v_count_msb =0 then ---没有本月数据,则插入
begin
insert into T_SJSB_MDYQY_MSB(sbzq,sbzt) values(in_monthSbzq,'0');
end;
end if;
--电力上报
select count(1) into v_count_dlsb from T_SJSB_MDYQY_DLSB where sbzq=in_monthSbzq;
if v_count_dlsb =0 then ---没有本月数据,则插入
begin
insert into T_SJSB_MDYQY_DLSB(sbzq,sbzt) values(in_monthSbzq,'0');
end;
end if;
--油上报
select count(1) into v_count_ysb from T_SJSB_MDYQY_YSB where sbzq=in_monthSbzq;
if v_count_ysb =0 then ---没有本月数据,则插入
begin
insert into T_SJSB_MDYQY_YSB(sbzq,sbzt) values(in_monthSbzq,'0');
end;
end if;
--气上报
select count(1) into v_count_qsb from T_SJSB_MDYQY_QSB where sbzq=in_monthSbzq;
if v_count_qsb =0 then ---没有本月数据,则插入
begin
insert into T_SJSB_MDYQY_QSB(sbzq,sbzt) values(in_monthSbzq,'0');
end;
end if;
--运上报
select count(1) into v_count_tlhysb from T_SJSB_MDYQY_TLHYSB where sbzq=in_monthSbzq;
if v_count_tlhysb =0 then ---没有本月数据,则插入
begin
insert into T_SJSB_MDYQY_TLHYSB(sbzq,sbzt) values(in_monthSbzq,'0');
end;
end if;
--商品房市场
select count(1) into v_count_spfsc from T_SJSB_SPFSCQK where sbzq=in_monthSbzq;
if v_count_spfsc =0 then ---没有本月数据,则插入
begin
insert into T_SJSB_SPFSCQK(sbzq,sbzt) values(in_monthSbzq,'0');
end;
end if;
--招商引资
select count(1) into v_count_zsyz from T_SJSB_ZSYZ where sbzq=in_monthSbzq;
if v_count_zsyz =0 then ---没有本月数据,则插入
begin
insert into T_SJSB_ZSYZ(sbzq,sbzt) values(in_monthSbzq,'0');
end;
end if;
--对外贸易
select count(1) into v_count_dwmy from T_SJSB_DWMY where sbzq=in_monthSbzq;
if v_count_dwmy =0 then ---没有本月数据,则插入
begin
insert into T_SJSB_DWMY(sbzq,sbzt) values(in_monthSbzq,'0');
end;
end if;
--财政收入
select count(1) into v_count_czsr from T_SJSB_CZSR where sbzq=in_monthSbzq;
if v_count_czsr =0 then ---没有本月数据,则插入
begin
insert into T_SJSB_CZSR(sbzq,sbzt) values(in_monthSbzq,'0');
end;
end if;
--金融运行
select count(1) into v_count_jryx from T_SJSB_JRYX where sbzq=in_monthSbzq;
if v_count_jryx =0 then ---没有本月数据,则插入
begin
insert into T_SJSB_JRYX(sbzq,sbzt) values(in_monthSbzq,'0');
end;
end if;
--居民消费水平
select count(1) into v_count_jmxfsp from T_SJSB_JMXFSP where sbzq=in_monthSbzq;
if v_count_jmxfsp =0 then ---没有本月数据,则插入
begin
insert into T_SJSB_JMXFSP(sbzq,sbzt) values(in_monthSbzq,'0');
end;
end if;
--两区上报
select count(1) into v_count_lq from T_SJSB_LQ where sbzq=in_monthSbzq;
if v_count_lq =0 then ---没有本月数据,则插入
begin
insert into T_SJSB_LQ(sbzq,sbzt) values(in_monthSbzq,'0');
end;
end if;
--主要工业平价格
select count(1) into v_count_zygypjg from T_SJSB_ZYGYPJG where sbzq=in_weekSbzq;
if v_count_zygypjg =0 then ---没有本月数据,则插入
begin
insert into T_SJSB_ZYGYPJG(sbzq,startdate,enddate,sbzt) values(in_weekSbzq,in_startDate,in_endDate,'0');
end;
end if;
--综合情况
select count(1) into v_count_zhqk from T_SJSB_ZHQK where sbzq=in_seasonSbzq;
if v_count_zhqk =0 then ---没有本月数据,则插入
begin
insert into T_SJSB_ZHQK(sbzq,sbzt) values(in_seasonSbzq,'0');
end;
end if;
end prc_sjsb_sbzq;
oracle存储过程如何定义,动态判断条件sql如何书写
最新推荐文章于 2021-04-09 02:24:19 发布