oracle存储过程如何定义,动态判断条件sql如何书写

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;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值