tag:blogger.com,1999:blog-8979358497537485726.post234190959878890957..comments2014-02-24T05:15:07.199-08:00Comments on oracle tips: oracle generate calendarНиколай Мишинhttp://www.blogger.com/profile/02168558551212889081noreply@blogger.comBlogger1125tag:blogger.com,1999:blog-8979358497537485726.post-49655905033246257832014-02-24T05:15:07.199-08:002014-02-24T05:15:07.199-08:00declare
CURSOR cur_tdbparams IS
WITH t AS
...declare<br /> CURSOR cur_tdbparams IS<br /> WITH t AS<br /> (SELECT TO_DATE('01.01.2013', 'dd.MM.YYYY') d1,<br /> TO_DATE('01.03.2013', 'dd.MM.YYYY') d2<br /> FROM DUAL),<br /> mon AS<br /> (SELECT DECODE(LEVEL, 1, d1, TRUNC(ADD_MONTHS(d1, LEVEL - 1), 'mm')) AS date_from,<br /> CASE<br /> WHEN ADD_MONTHS(TRUNC(d1, 'mm'), LEVEL) > d2 THEN<br /> d2<br /> ELSE<br /> LAST_DAY(ADD_MONTHS(d1, LEVEL - 1))<br /> END date_to<br /> FROM t<br /> CONNECT BY ADD_MONTHS(TRUNC(d1, 'mm'), LEVEL - 1) < d2)<br /> select date_to from mon;<br />BEGIN<br /> FOR var_tdbparams IN cur_tdbparams LOOP<br /> DBMS_OUTPUT.put_line(to_char(var_tdbparams.date_to, 'dd.mm.yyyy'));<br /> END LOOP;<br />END;Николай Мишинhttps://www.blogger.com/profile/02168558551212889081noreply@blogger.com