ORACLE <http://duyiwuer.javaeye.com/blog/356754> 的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用法
这个题还需各位高手。
参考答案如下:
Sql代码
1. select *
2. from factory
3. select to_char(to_date('20080602','yyyymmdd') -1,'yyyymmdd')
4. from dual
5. select
to_char(add_months(to_date(f.cur_date,'yyyymmdd'),-12),'yyyymmdd')
6. from dual
7. create table temp
8. as
9. select cur_date,(select
10. sum(case when cur_date=f.cur_date then
cur_perout
11. else 0
12. end)
13. from factory) a,
14. (select
15. sum(case when
cur_date=to_char(to_date(f.cur_date,'yyyymmdd') -1,'yyyymmdd')
16. then cur_perout else 0
17. end)
18. from factory) b ,
19. (select
20. sum(case when
substr(cur_date,1,6)=substr(f.cur_date,1,6) then cur_perout
21. else 0
22. end)
23. from factory) c,
24. (select
25. sum(case when
substr(cur_date,1,4)=substr(f.cur_date,1,4) then cur_perout
26. else 0
27. end)
28. from factory) d,
29. (select
30. sum(case when
cur_date=to_char(add_months(to_date(f.cur_date,'yyyymmdd'),-12),'yyyymmdd')
31. then cur_perout else 0
32. end)
33. from factory) e
34.
35. from factory f
36. where rownum <1
37. -- where cur_date='20080602';
38. begin
39. for temstr in (select * from factory) loop
40. insert into temp
41. select temstr.cur_date,--游标(不带字段/常量)
42. sum(case when cur_date=temstr.cur_date then cur_perout
43. else 0
44. end),
45. sum(case when
cur_date=to_char(to_date(temstr.cur_date,'yyyymmdd') -1,'yyyymmdd')
46. then cur_perout
47. else 0
48. end),
49. sum(case when
substr(cur_date,1,6)=substr(temstr.cur_date,1,6) then cur_perout
50. else 0
51. end),
52. sum(case when
substr(cur_date,1,4)=substr(temstr.cur_date,1,4) then cur_perout
53. else 0
54. end),
55. sum(case when
cur_date=to_char(add_months(to_date(temstr.cur_date,'yyyymmdd'),-12),'yyyymm
dd') then cur_perout
56. else 0
57. end)
58. from factory;
59. end loop;
60. end;
61. select * from temp