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代码

declare
   i int := 1;
   begin
   while i <= SELECT TRUNC(TO_DATE( '2008-01-17', 'yyyy-MM-dd')-TO_DATE( '2007-01-01', 'yyyy-MM-dd')) FROM DUAL 
   loop
      insert into est_date
     (v1,v2,v3,v4,v5,v6,v7,v8,v9)
     (select to_char(to_date('2007-01-01','yyyy-MM-dd')+i,'yyyy-MM-dd'),
             to_char(to_date('2007-01-01','yyyy-MM-dd')+i,'yyyy-MM'),
         to_char(to_date('2007-01-01','yyyy-MM-dd')+i,'MM'),
         to_char(to_date('2007-01-01','yyyy-MM-dd')+i,'q'),
         to_char(to_date('2007-01-01','yyyy-MM-dd')+i,'ww'),
         to_char(to_date('2007-01-01','yyyy-MM-dd')+i,'d'),
         to_char(add_months(to_date('2007-01-01','yyyy-MM-dd')+i,-1),'yyyy-MM-dd'),
         last_day(to_date('2007-01-01','yyyy-MM-dd')+i)
      from dual
     );
     i := i + 1;
  end loop;
  end;