关闭

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

标签: 存储过程
189人阅读 评论(0) 收藏 举报
分类:
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;

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:78853次
    • 积分:1700
    • 等级:
    • 排名:千里之外
    • 原创:89篇
    • 转载:87篇
    • 译文:0篇
    • 评论:7条