已知:
上个月第一个星期天的日期:select last_day(add_months(sysdate,-1))-to_char(last_day(add_months(sysdate,-1)),'d')+1 last_sunday from dual
上个月最后一个星期天的日期:select decode(to_char(trunc(add_months(sysdate,-1),'mm'),'d'),1,trunc(add_months(sysdate,-1),'mm'),next_day(trunc(add_months(sysdate,-1),'mm'),1)) first_sunday from dual 问题: 怎样把这两个作为参数放在类似下面的这种查询中,并把查询结果保存到文件里select * from tbl_test where startdate between to_date(上个月的第一个星期天) and to_date(上个月的最后一个星期天)我觉得这应该用存储过程,不知道思路对不对,请高手指教, 谢谢!
from dual;
trunc(trunc(sysdate,'MM')-1,'d') as d2 -- 上个月的最后一个星期天的日期
from dual;
where startdate>=trunc(add_months(trunc(sysdate,'MM'),-1)+6,'d')
and startdate<trunc(trunc(sysdate,'MM')-1,'d'); -- 如果包括上个月最后一个星期天的数据的话,就加1:trunc(trunc(sysdate,'MM')-1,'d')+1
spool 路劲
DECLARE
first_sunday DATE;
last_sunday DATE;
rs tbl_test%ROWTYPE;
TYPE c IS REF CURSOR;
cur c;
BEGIN
select decode(to_char(trunc(add_months(sysdate,-1),'mm'),'d'),1,trunc(add_months(sysdate,-1),'mm'),next_day(trunc(add_months(sysdate,-1),'mm'),1)) INTO first_sunday from dual ;
select last_day(add_months(sysdate,-1))-to_char(last_day(add_months(sysdate,-1)),'d')+1 INTO last_sunday from dual ;
OPEN cur for 'select * from tbl_test where startdate between '||first_sunday ||' and '||last_sunday;
LOOP
FETCH cur INTO rs ;
EXIT WHEN cur%NOTFOUND;
Dbms_Output.put_line(rs.col1||' '||rs.col2||' '||rs.col3||' '||rs.col4 ........);
END LOOP;
CLOSE cur;
END;
/
spool OFF
SET serveroutput ON; --遗漏了这个
spool 路劲 ;DECLARE
first_sunday DATE;
last_sunday DATE;
rs tbl_test%ROWTYPE;
TYPE c IS REF CURSOR;
cur c;
BEGIN
select decode(to_char(trunc(add_months(sysdate,-1),'mm'),'d'),1,trunc(add_months(sysdate,-1),'mm'),next_day(trunc(add_months(sysdate,-1),'mm'),1)) INTO first_sunday from dual ;
select last_day(add_months(sysdate,-1))-to_char(last_day(add_months(sysdate,-1)),'d')+1 INTO last_sunday from dual ;
OPEN cur for 'select * from tbl_test where startdate between '||first_sunday ||' and '||last_sunday;
LOOP
FETCH cur INTO rs ;
EXIT WHEN cur%NOTFOUND;
Dbms_Output.put_line(rs.col1||' '||rs.col2||' '||rs.col3||' '||rs.col4 ........);
END LOOP;
CLOSE cur;
END;
/spool OFF
这样不就行了 不知道理解得对不对