ORACLE 的FOR循环、游标、时间值函数、转换函数题目

 

 题目:假如输入变量是cur_date  varchar2
    统计以下变量:
        今天的产量  cur_date
        昨天的产量  to_char(to_date(cur_date,'yyyymmdd')-1,'yyyymmdd')
        本月的产量  substr(cur_date,1,6)
        本年的产量  substr(cur_date,1,4)
        去年同期的产量 to_char(add_months(to_date(cur_date,'yyyymmdd'),-12),'yyyymmdd')
    1)用基本SQL来实现

    用FOR循环游标来实现
    考核的知识点:FOR循环,游标,时间值函数,转换函数,DECODE/CASE用法

这个题还需各位高手。

参考答案如下:

select *
    from factory
    select to_char(to_date('20080602','yyyymmdd') -1,'yyyymmdd')
    from dual  
    select to_char(add_months(to_date(f.cur_date,'yyyymmdd'),-12),'yyyymmdd')
    from dual
    create table temp
    as  
    select cur_date,(select 
                     sum(case when cur_date=f.cur_date then cur_perout
                     else 0
                     end)
                     from factory) a,
                     (select 
                     sum(case when cur_date=to_char(to_date(f.cur_date,'yyyymmdd') -1,'yyyymmdd')
                     then cur_perout else 0
                     end)
                     from factory) b ,
                     (select 
                     sum(case when substr(cur_date,1,6)=substr(f.cur_date,1,6) then cur_perout
                     else 0
                     end)
                     from factory) c,
                     (select
                      sum(case when substr(cur_date,1,4)=substr(f.cur_date,1,4) then cur_perout
                      else 0
                     end)
                     from factory) d,
                     (select
                     sum(case when cur_date=to_char(add_months(to_date(f.cur_date,'yyyymmdd'),-12),'yyyymmdd')
                        then cur_perout else 0
                     end)
                     from factory) e
                     
    from factory f
    where rownum <1
   -- where cur_date='20080602';
begin 
  for temstr in (select * from factory) loop
  insert into temp
  select temstr.cur_date,--游标(不带字段/常量)
         sum(case when cur_date=temstr.cur_date then cur_perout
             else 0
             end),
         sum(case when cur_date=to_char(to_date(temstr.cur_date,'yyyymmdd') -1,'yyyymmdd') 
         then cur_perout
             else 0
             end),
         sum(case when substr(cur_date,1,6)=substr(temstr.cur_date,1,6) then cur_perout
             else 0
             end),
         sum(case when substr(cur_date,1,4)=substr(temstr.cur_date,1,4) then cur_perout
             else 0
             end),
         sum(case when cur_date=to_char(add_months(to_date(temstr.cur_date,'yyyymmdd'),-12),'yyyymmdd') then cur_perout
             else 0
             end)
         from factory; 
  end loop;
end;
  select * from temp
    

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值