SQL> select to_char(to_date('2011-06-09','yyyy-mm-dd'),'day') day from dual 2 /
DAY ------ 星期四
--2011年6月份的日历 SQL> select sum(decode(to_char(everyday,'dy'),'星期日',to_char(everyday,'dd'))) as 星期日, 2 sum(decode(to_char(everyday,'dy'),'星期一',to_char(everyday,'dd'))) as 星期一, 3 sum(decode(to_char(everyday,'dy'),'星期二',to_char(everyday,'dd'))) as 星期二, 4 sum(decode(to_char(everyday,'dy'),'星期三',to_char(everyday,'dd'))) as 星期三, 5 sum(decode(to_char(everyday,'dy'),'星期四',to_char(everyday,'dd'))) as 星期四, 6 sum(decode(to_char(everyday,'dy'),'星期五',to_char(everyday,'dd'))) as 星期五, 7 sum(decode(to_char(everyday,'dy'),'星期六',to_char(everyday,'dd'))) as 星期六 8 from(select to_date('20061101','yyyymmdd') + level - 1 as everyDay 9 from dual 10 connect by level <= (last_day(to_date('20110601','yyyymmdd')) - to_date('20110601','yyyymmdd') +1) 11 ) 12 group by ceil((to_char(everyday,'dd')+(to_char(to_date('20061101','yyyymmdd'),'d')-1))/7);
CREATE OR REPLACE PROCEDURE month_per_day( i_year in varchar default null, i_month in varchar default '01', o_days OUT SYS_REFCURSOR) IS v_sql varchar2(4000); v_first_day varchar2(10); BEGIN IF i_year IS NOT NULL THEN BEGIN v_first_day := i_year||'-'||i_month||'-01'; END; ELSE BEGIN v_first_day := to_char(sysdate,'YYYY')||'-'||i_month||'-01'; END; END IF; open o_days FOR 'SELECT to_date(:v_first_day,''YYYY-MM-DD'')+level-1 AS perDay, to_char(to_date(:v_first_day,''YYYY-MM-DD'')+level-1,''Day'') AS weekDay FROM dual CONNECT BY level <=(last_day(to_date(substr(:v_first_day,1,10),''YYYY-MM-DD''))-to_date(substr(:v_first_day,1,10),''YYYY-MM-DD'')+1) ' using v_first_day, v_first_day, v_first_day, v_first_day; END; /set serveroutput on; var c_cur refcursor; exec month_per_day('2010','06',:c_cur); print c_cur;
groups@SZTYORA> CREATE OR REPLACE PROCEDURE month_per_day( 2 i_year in varchar default null, 3 i_month in varchar default '01', 4 o_days OUT SYS_REFCURSOR) 5 IS 6 v_sql varchar2(4000); 7 v_first_day varchar2(10); 8 BEGIN 9 IF i_year IS NOT NULL THEN 10 BEGIN 11 v_first_day := i_year||'-'||i_month||'-01'; 12 END; 13 ELSE 14 BEGIN 15 v_first_day := to_char(sysdate,'YYYY')||'-'||i_month||'-01'; 16 END; 17 END IF; 18 open o_days FOR 'SELECT to_date(:v_first_day,''YYYY-MM-DD'')+level-1 AS perDay, 19 to_char(to_date(:v_first_day,''YYYY-MM-DD'')+level-1,''Day'') AS weekDay 20 FROM dual CONNECT BY level <=(last_day(to_date(substr(:v_first_day,1,10),''YYYY-MM-DD''))-to_date(substr(:v_first_day,1,10),''YYYY-MM-DD'')+1) ' 21 using v_first_day, v_first_day, v_first_day, v_first_day; 22 -- open o_days FOR 'SELECT sysdate FROM dual'; 23 24 END; 25 /过程已创建。groups@SZTYORA> set serveroutput on; groups@SZTYORA> var c_cur refcursor; groups@SZTYORA> exec month_per_day('2010','06',:c_cur);PL/SQL 过程已成功完成。groups@SZTYORA> print c_cur;PERDAY WEEKDAY ------------------- ------------------------ 2010-06-01 00:00:00 星期二 2010-06-02 00:00:00 星期三 2010-06-03 00:00:00 星期四 2010-06-04 00:00:00 星期五 2010-06-05 00:00:00 星期六 2010-06-06 00:00:00 星期日 2010-06-07 00:00:00 星期一 2010-06-08 00:00:00 星期二 2010-06-09 00:00:00 星期三 2010-06-10 00:00:00 星期四 2010-06-11 00:00:00 星期五 2010-06-12 00:00:00 星期六 2010-06-13 00:00:00 星期日 2010-06-14 00:00:00 星期一 2010-06-15 00:00:00 星期二 2010-06-16 00:00:00 星期三 2010-06-17 00:00:00 星期四 2010-06-18 00:00:00 星期五 2010-06-19 00:00:00 星期六 2010-06-20 00:00:00 星期日 2010-06-21 00:00:00 星期一 2010-06-22 00:00:00 星期二 2010-06-23 00:00:00 星期三 2010-06-24 00:00:00 星期四 2010-06-25 00:00:00 星期五 2010-06-26 00:00:00 星期六 2010-06-27 00:00:00 星期日 2010-06-28 00:00:00 星期一 2010-06-29 00:00:00 星期二 2010-06-30 00:00:00 星期三已选择30行。
SELECT SUM(DECODE(TO_CHAR(EVERYDAY, 'dy'), '星期日', TO_CHAR(EVERYDAY, 'dd'))) AS 星期日, SUM(DECODE(TO_CHAR(EVERYDAY, 'dy'), '星期一', TO_CHAR(EVERYDAY, 'dd'))) AS 星期一, SUM(DECODE(TO_CHAR(EVERYDAY, 'dy'), '星期二', TO_CHAR(EVERYDAY, 'dd'))) AS 星期二, SUM(DECODE(TO_CHAR(EVERYDAY, 'dy'), '星期三', TO_CHAR(EVERYDAY, 'dd'))) AS 星期三, SUM(DECODE(TO_CHAR(EVERYDAY, 'dy'), '星期四', TO_CHAR(EVERYDAY, 'dd'))) AS 星期四, SUM(DECODE(TO_CHAR(EVERYDAY, 'dy'), '星期五', TO_CHAR(EVERYDAY, 'dd'))) AS 星期五, SUM(DECODE(TO_CHAR(EVERYDAY, 'dy'), '星期六', TO_CHAR(EVERYDAY, 'dd'))) AS 星期六 FROM (SELECT TO_DATE('20110501', 'yyyymmdd') + LEVEL - 1 AS EVERYDAY FROM DUAL CONNECT BY LEVEL <= (LAST_DAY(TO_DATE('20110501', 'yyyymmdd')) - TO_DATE('20110501', 'yyyymmdd') + 1)) GROUP BY CEIL((TO_CHAR(EVERYDAY, 'dd') + (TO_CHAR(TO_DATE('20110501', 'yyyymmdd'), 'd') - 1)) / 7) ORDER BY 星期六; 不加顺序有问题,但是语句是正常的
传入月份 直接输出 这个月的每一天和日期列表。 解决方案:--建立表类型 CREATE OR REPLACE TYPE "TYPE_TABLE" as table of varchar2(30); --建立获得日期方法,传递月份参数,参数格式如'2011-06'或'201106' CREATE OR REPLACE FUNCTION F_GET_MY_DATE(in_month VARCHAR2) RETURN TYPE_TABLE AS LIST_TABLE TYPE_TABLE := TYPE_TABLE(); v_bgn_date VARCHAR2(10) := in_month || '-01'; --当月第一天 v_end_date VARCHAR2(10) := TO_CHAR(last_day(TO_DATE(v_bgn_date, 'yyyy-mm-dd')), 'yyyy-mm-dd'); --当月最后一天 BEGIN FOR J IN 0 .. (TO_DATE(v_end_date, 'yyyy-mm-dd') - TO_DATE(v_bgn_date, 'yyyy-mm-dd')) LOOP LIST_TABLE.EXTEND; --可变数组自增 LIST_TABLE(LIST_TABLE.COUNT) := TO_CHAR(TO_DATE(v_bgn_date,'yyyy-mm-dd'),'yyyy-mm-dd') || ',' || to_char(to_date(v_bgn_date,'yyyy-mm-dd'),'day');--赋值日期和星期 v_bgn_date := TO_CHAR(TO_DATE(v_bgn_date, 'yyyy-mm-dd') + 1,'yyyy-mm-dd'); END LOOP; RETURN LIST_TABLE; END; --测试执行 SELECT column_value FROM TABLE(f_get_my_date('201106')); --结果如下: 2011-06-01,星期三 2011-06-02,星期四 2011-06-03,星期五 2011-06-04,星期六 2011-06-05,星期日 2011-06-06,星期一 2011-06-07,星期二 2011-06-08,星期三 2011-06-09,星期四 2011-06-10,星期五 2011-06-11,星期六 2011-06-12,星期日 2011-06-13,星期一 2011-06-14,星期二 2011-06-15,星期三 2011-06-16,星期四 2011-06-17,星期五 2011-06-18,星期六 2011-06-19,星期日 2011-06-20,星期一 2011-06-21,星期二 2011-06-22,星期三 2011-06-23,星期四 2011-06-24,星期五 2011-06-25,星期六 2011-06-26,星期日 2011-06-27,星期一 2011-06-28,星期二 2011-06-29,星期三 2011-06-30,星期四本机测试通过。
-- 用 connect by 撒
SQL> select to_char(to_date('2011-06-09','yyyy-mm-dd'),'day') day from dual
2 /
DAY
------
星期四
--2011年6月份的日历
SQL> select sum(decode(to_char(everyday,'dy'),'星期日',to_char(everyday,'dd'))) as 星期日,
2 sum(decode(to_char(everyday,'dy'),'星期一',to_char(everyday,'dd'))) as 星期一,
3 sum(decode(to_char(everyday,'dy'),'星期二',to_char(everyday,'dd'))) as 星期二,
4 sum(decode(to_char(everyday,'dy'),'星期三',to_char(everyday,'dd'))) as 星期三,
5 sum(decode(to_char(everyday,'dy'),'星期四',to_char(everyday,'dd'))) as 星期四,
6 sum(decode(to_char(everyday,'dy'),'星期五',to_char(everyday,'dd'))) as 星期五,
7 sum(decode(to_char(everyday,'dy'),'星期六',to_char(everyday,'dd'))) as 星期六
8 from(select to_date('20061101','yyyymmdd') + level - 1 as everyDay
9 from dual
10 connect by level <= (last_day(to_date('20110601','yyyymmdd')) - to_date('20110601','yyyymmdd') +1)
11 )
12 group by ceil((to_char(everyday,'dd')+(to_char(to_date('20061101','yyyymmdd'),'d')-1))/7);
星期日 星期一 星期二 星期三 星期四 星期五 星期六
---------- ---------- ---------- ---------- ---------- ---------- ----------
1 2 3 4
5 6 7 8 9 10 11
12 13 14 15 16 17 18
19 20 21 22 23 24 25
26 27 28 29 30
2 (SELECT add_months(trunc(SYSDATE, 'yyyy'), &n - 1) s_date FROM dual)
3 SELECT s_date + LEVEL - 1, to_char(s_date + LEVEL - 1, 'day')
4 FROM tt
5 CONNECT BY LEVEL <= last_day(s_date) - s_date + 1;
输入 n 的值: 6
原值 2: (SELECT add_months(trunc(SYSDATE, 'yyyy'), &n - 1) s_date FROM dual)
新值 2: (SELECT add_months(trunc(SYSDATE, 'yyyy'), 6 - 1) s_date FROM dual)S_DATE+LEVEL-1 TO_CHAR(S
------------------- ---------
2011-06-01 00:00:00 星期三
2011-06-02 00:00:00 星期四
2011-06-03 00:00:00 星期五
2011-06-04 00:00:00 星期六
2011-06-05 00:00:00 星期日
2011-06-06 00:00:00 星期一
2011-06-07 00:00:00 星期二
2011-06-08 00:00:00 星期三
2011-06-09 00:00:00 星期四
2011-06-10 00:00:00 星期五
2011-06-11 00:00:00 星期六
2011-06-12 00:00:00 星期日
2011-06-13 00:00:00 星期一
2011-06-14 00:00:00 星期二
2011-06-15 00:00:00 星期三
2011-06-16 00:00:00 星期四
2011-06-17 00:00:00 星期五
2011-06-18 00:00:00 星期六
2011-06-19 00:00:00 星期日
2011-06-20 00:00:00 星期一
2011-06-21 00:00:00 星期二
2011-06-22 00:00:00 星期三
2011-06-23 00:00:00 星期四
2011-06-24 00:00:00 星期五
2011-06-25 00:00:00 星期六
2011-06-26 00:00:00 星期日
2011-06-27 00:00:00 星期一
2011-06-28 00:00:00 星期二
2011-06-29 00:00:00 星期三
2011-06-30 00:00:00 星期四已选择30行。SQL>
i_year in varchar default null,
i_month in varchar default '01',
o_days OUT SYS_REFCURSOR)
IS
v_sql varchar2(4000);
v_first_day varchar2(10);
BEGIN
IF i_year IS NOT NULL THEN
BEGIN
v_first_day := i_year||'-'||i_month||'-01';
END;
ELSE
BEGIN
v_first_day := to_char(sysdate,'YYYY')||'-'||i_month||'-01';
END;
END IF;
open o_days FOR 'SELECT to_date(:v_first_day,''YYYY-MM-DD'')+level-1 AS perDay,
to_char(to_date(:v_first_day,''YYYY-MM-DD'')+level-1,''Day'') AS weekDay
FROM dual CONNECT BY level <=(last_day(to_date(substr(:v_first_day,1,10),''YYYY-MM-DD''))-to_date(substr(:v_first_day,1,10),''YYYY-MM-DD'')+1) '
using v_first_day, v_first_day, v_first_day, v_first_day;
END;
/set serveroutput on;
var c_cur refcursor;
exec month_per_day('2010','06',:c_cur);
print c_cur;
2 i_year in varchar default null,
3 i_month in varchar default '01',
4 o_days OUT SYS_REFCURSOR)
5 IS
6 v_sql varchar2(4000);
7 v_first_day varchar2(10);
8 BEGIN
9 IF i_year IS NOT NULL THEN
10 BEGIN
11 v_first_day := i_year||'-'||i_month||'-01';
12 END;
13 ELSE
14 BEGIN
15 v_first_day := to_char(sysdate,'YYYY')||'-'||i_month||'-01';
16 END;
17 END IF;
18 open o_days FOR 'SELECT to_date(:v_first_day,''YYYY-MM-DD'')+level-1 AS perDay,
19 to_char(to_date(:v_first_day,''YYYY-MM-DD'')+level-1,''Day'') AS weekDay
20 FROM dual CONNECT BY level <=(last_day(to_date(substr(:v_first_day,1,10),''YYYY-MM-DD''))-to_date(substr(:v_first_day,1,10),''YYYY-MM-DD'')+1) '
21 using v_first_day, v_first_day, v_first_day, v_first_day;
22 -- open o_days FOR 'SELECT sysdate FROM dual';
23
24 END;
25 /过程已创建。groups@SZTYORA> set serveroutput on;
groups@SZTYORA> var c_cur refcursor;
groups@SZTYORA> exec month_per_day('2010','06',:c_cur);PL/SQL 过程已成功完成。groups@SZTYORA> print c_cur;PERDAY WEEKDAY
------------------- ------------------------
2010-06-01 00:00:00 星期二
2010-06-02 00:00:00 星期三
2010-06-03 00:00:00 星期四
2010-06-04 00:00:00 星期五
2010-06-05 00:00:00 星期六
2010-06-06 00:00:00 星期日
2010-06-07 00:00:00 星期一
2010-06-08 00:00:00 星期二
2010-06-09 00:00:00 星期三
2010-06-10 00:00:00 星期四
2010-06-11 00:00:00 星期五
2010-06-12 00:00:00 星期六
2010-06-13 00:00:00 星期日
2010-06-14 00:00:00 星期一
2010-06-15 00:00:00 星期二
2010-06-16 00:00:00 星期三
2010-06-17 00:00:00 星期四
2010-06-18 00:00:00 星期五
2010-06-19 00:00:00 星期六
2010-06-20 00:00:00 星期日
2010-06-21 00:00:00 星期一
2010-06-22 00:00:00 星期二
2010-06-23 00:00:00 星期三
2010-06-24 00:00:00 星期四
2010-06-25 00:00:00 星期五
2010-06-26 00:00:00 星期六
2010-06-27 00:00:00 星期日
2010-06-28 00:00:00 星期一
2010-06-29 00:00:00 星期二
2010-06-30 00:00:00 星期三已选择30行。
'星期日',
TO_CHAR(EVERYDAY, 'dd'))) AS 星期日,
SUM(DECODE(TO_CHAR(EVERYDAY, 'dy'),
'星期一',
TO_CHAR(EVERYDAY, 'dd'))) AS 星期一,
SUM(DECODE(TO_CHAR(EVERYDAY, 'dy'),
'星期二',
TO_CHAR(EVERYDAY, 'dd'))) AS 星期二,
SUM(DECODE(TO_CHAR(EVERYDAY, 'dy'),
'星期三',
TO_CHAR(EVERYDAY, 'dd'))) AS 星期三,
SUM(DECODE(TO_CHAR(EVERYDAY, 'dy'),
'星期四',
TO_CHAR(EVERYDAY, 'dd'))) AS 星期四,
SUM(DECODE(TO_CHAR(EVERYDAY, 'dy'),
'星期五',
TO_CHAR(EVERYDAY, 'dd'))) AS 星期五,
SUM(DECODE(TO_CHAR(EVERYDAY, 'dy'),
'星期六',
TO_CHAR(EVERYDAY, 'dd'))) AS 星期六
FROM (SELECT TO_DATE('20110501', 'yyyymmdd') + LEVEL - 1 AS EVERYDAY
FROM DUAL
CONNECT BY LEVEL <= (LAST_DAY(TO_DATE('20110501', 'yyyymmdd')) -
TO_DATE('20110501', 'yyyymmdd') + 1))
GROUP BY CEIL((TO_CHAR(EVERYDAY, 'dd') +
(TO_CHAR(TO_DATE('20110501', 'yyyymmdd'), 'd') - 1)) / 7)
ORDER BY 星期六; 不加顺序有问题,但是语句是正常的
解决方案:--建立表类型
CREATE OR REPLACE TYPE "TYPE_TABLE" as table of varchar2(30);
--建立获得日期方法,传递月份参数,参数格式如'2011-06'或'201106'
CREATE OR REPLACE FUNCTION F_GET_MY_DATE(in_month VARCHAR2)
RETURN TYPE_TABLE AS
LIST_TABLE TYPE_TABLE := TYPE_TABLE();
v_bgn_date VARCHAR2(10) := in_month || '-01'; --当月第一天
v_end_date VARCHAR2(10) := TO_CHAR(last_day(TO_DATE(v_bgn_date,
'yyyy-mm-dd')),
'yyyy-mm-dd'); --当月最后一天
BEGIN
FOR J IN 0 .. (TO_DATE(v_end_date, 'yyyy-mm-dd') -
TO_DATE(v_bgn_date, 'yyyy-mm-dd'))
LOOP
LIST_TABLE.EXTEND; --可变数组自增
LIST_TABLE(LIST_TABLE.COUNT) := TO_CHAR(TO_DATE(v_bgn_date,'yyyy-mm-dd'),'yyyy-mm-dd') || ',' ||
to_char(to_date(v_bgn_date,'yyyy-mm-dd'),'day');--赋值日期和星期
v_bgn_date := TO_CHAR(TO_DATE(v_bgn_date, 'yyyy-mm-dd') + 1,'yyyy-mm-dd');
END LOOP;
RETURN LIST_TABLE;
END;
--测试执行
SELECT column_value FROM TABLE(f_get_my_date('201106'));
--结果如下:
2011-06-01,星期三
2011-06-02,星期四
2011-06-03,星期五
2011-06-04,星期六
2011-06-05,星期日
2011-06-06,星期一
2011-06-07,星期二
2011-06-08,星期三
2011-06-09,星期四
2011-06-10,星期五
2011-06-11,星期六
2011-06-12,星期日
2011-06-13,星期一
2011-06-14,星期二
2011-06-15,星期三
2011-06-16,星期四
2011-06-17,星期五
2011-06-18,星期六
2011-06-19,星期日
2011-06-20,星期一
2011-06-21,星期二
2011-06-22,星期三
2011-06-23,星期四
2011-06-24,星期五
2011-06-25,星期六
2011-06-26,星期日
2011-06-27,星期一
2011-06-28,星期二
2011-06-29,星期三
2011-06-30,星期四本机测试通过。