Oracle日期函数和循环总结
一，日期相关的函数
Select to_char(sysdate,'Q') from dual;--指定日期的季度
Select to_char(sysdate,'MM') from dual;--月份
Select to_char(sysdate,'WW') from dual;--当年第几周
Select to_char(sysdate,'W') from dual ;--本月第几周
Select to_char(sysdate,'DD') from dual;--当月第几天
Select to_char(sysdate,'D') from dual;--周内第几天
Select to_char(sysdate,'DY') from duaL;--星期几
Select last_day(sysdate) from dual;--本月最后一天
Select add_months(sysdate,2) from dual;--当前日期d后推n个月
select months_between(sysdate,to_date('2012-11-12','yyyy-mm-dd'))from dual;--日期f和s间相差月数
SELECT (next_day(sysdate,1)+1) FROM dual;--指定的日期之后的第一个工作日的日期
select to_char(add_months(last_day(sysdate),-1),'yyyy-MM-dd') LastDay from dual;--上月末天
select to_char(add_months(sysdate,-1),'yyyy-MM-dd') PreToday from dual;--上月今天
select to_char(add_months(last_day(sysdate)+1,-2),'yyyy-MM-dd') firstDay from dual;--上月第一天
select to_char(sysdate,'ww') from dual group by to_char(sysdate,'ww');--按照每周进行统计
select to_char(sysdate,'mm') from dual group by to_char(sysdate,'mm');--按照每月进行统计
select to_char(sysdate,'q') from dual group by to_char(sysdate,'q');--按照每季度进行统计
二，循环
编写循环控制结构时，用户可以使用基本循环，WHILE循环和FOR循环等三种类型的循环语句，下面分别介绍使用这三种循环语句的方法。
1.基本循环
LOOP
statement1;
......
EXIT [WHEN condition];
END LOOP;
当使用基本循环时，无论是否满足条件，语句至少会被执行一次，当condition为TRUE时，会退出循环，并执行END LOOP后的相应操作。当编写基本循环时一定要包含EXIT语句，否则会陷入死循环。另外还应该定义循环控制变量，并且在循环体内修改循环控制变量的值。示例：
SQL> declare
2 i int:=1;
3 begin
4 loop
5 insert into testloop values(i);
6 exit when i=10;
7 i:=i+1;
8 end loop;
9 end;
10 /
2.WHILE循环
基本循环至少要执行一次循环体内的语句，而对于WHILE循环来说，只有条件为TRUE时，才会执行循环体内的语句。WHILE循环以WHILE ...LOOP开始，以END LOOP结束。
WHILE condition LOOP
statement1;
statement2;
.....
END LOOP;
当condition为TRUE时，执行循环体内的语句，而当condition为FALSE或NULL时，会退出循环，并执行END LOOP后的语句。当使用WHILE循环时，应该定义循环控制变量，并在循环体内改变循环控制变量的值。示例：
SQL> declare
2 i int:=1;
3 begin
4 while i<=10 loop
5 insert into testloop values(i);
6 i:=i+1;
7 end loop;
8 end;
9 /
3.FOR循环
当使用基本循环或WHILE循环时，需要定义循环控制变量，并且循环控制变量不仅可以使用NUMBER类型，也可以使用其他数据类型。当使用FOR循环时，ORACLE会隐含定义循环控制变量。
FOR counter in [REVERSE] lower_bound. .upper_bound LOOP
statement1;
statement2;
.......
END LOOP;
counter是循环控制变量，并且该变量由oracle隐含定义，不需要显式定义。lower_bound和upper_bound分别对应于循环控制变量的下界值和上界值，默认情况下，当使用FOR循环时，每次循环时循环控制变量会自动增1.如果指定REVERSE选项，那么每次循环时循环控制变量会自动减1。示例：
SQL> begin
2 for i in reverse 1..10 loop
3 insert into testloop values(i);
4 end loop;
5 end;
三，综合案例
建一个这样的表
create table test_date
(
v1 varchar2(40),
v2 varchar2(40),
v3 varchar2(40),
v4 varchar2(40),
v5 varchar2(40),
v6 varchar2(40),
v7 varchar2(40),
v8 varchar2(40),
v9 varchar2(40)
);
要求写一个存储过程
往这个测试表中插入数据
从2007年1月1日到2008年1月17日
循环插入
|
V1 |
V2 |
V2 |
V4(季度） |
V5（一年中的第几周） |
V6（星期几） |
V7(是否周六日) |
V8（上一月） |
V9（本月最后一天） |
|
2007年01月01日 |
200701 |
01 |
1 |
1 |
1 |
0 |
200612 |
20070131 |
|
2007年01月02日 |
200701 |
01 |
1 |
1 |
2 |
0 |
200612 |
20070131 |
|
2007年01月03日 |
200701 |
01 |
1 |
1 |
3 |
0 |
200612 |
20070131 |
|
2007年01月04日 |
200701 |
01 |
1 |
1 |
4 |
0 |
200612 |
20070131 |
|
2007年01月05日 |
200701 |
01 |
1 |
1 |
5 |
0 |
200612 |
20070131 |
|
2007年01月06日 |
200701 |
01 |
1 |
1 |
6 |
1 |
200612 |
20070131 |
|
2007年01月07日 |
200701 |
01 |
1 |
1 |
7 |
1 |
200612 |
20070131 |
|
2007年01月08日 |
200701 |
01 |
1 |
2 |
1 |
0 |
200612 |
20070131 |
最终sql代码
1 declare 2 i int := 1; 3 begin 4 while i <= SELECT TRUNC(TO_DATE( '2008-01-17', 'yyyy-MM-dd')-TO_DATE( '2007-01-01', 'yyyy-MM-dd')) FROM DUAL 5 loop 6 insert into est_date 7 (v1,v2,v3,v4,v5,v6,v7,v8,v9) 8 (select to_char(to_date('2007-01-01','yyyy-MM-dd')+i,'yyyy-MM-dd'), 9 to_char(to_date('2007-01-01','yyyy-MM-dd')+i,'yyyy-MM'), 10 to_char(to_date('2007-01-01','yyyy-MM-dd')+i,'MM'), 11 to_char(to_date('2007-01-01','yyyy-MM-dd')+i,'q'), 12 to_char(to_date('2007-01-01','yyyy-MM-dd')+i,'ww'), 13 to_char(to_date('2007-01-01','yyyy-MM-dd')+i,'d'), 14 to_char(add_months(to_date('2007-01-01','yyyy-MM-dd')+i,-1),'yyyy-MM-dd'), 15 last_day(to_date('2007-01-01','yyyy-MM-dd')+i) 16 from dual 17 ); 18 i := i + 1; 19 end loop; 20 end;
