PL/SQL

[Buck Turgidson]

I am trying to write a PL/SQL to generate year-begin dates for the past 10
years, based on sysdate. It is not working properly. I don't write a lot
of PL/SQL. So any help would be appreciated

DECLARE
YEARS INTEGER := 10;
PRIOR_YEAR DATE := SYSDATE;
CURSOR PS_CURSOR IS
SELECT
TRUNC(TRUNC(TO_DATE(PRIOR_YEAR),'YEAR') - 1,'YEAR') AS "YR_BEGIN"
FROM DUAL;
PS_REC PS_CURSOR%ROWTYPE;
BEGIN
OPEN PS_CURSOR;
LOOP
FETCH PS_CURSOR INTO PS_REC;
EXIT WHEN YEARS = 0;
YEARS := YEARS - 1;
PRIOR_YEAR := PS_REC.YR_BEGIN;
DBMS_OUTPUT.put_line(PRIOR_YEAR);
PRIOR_YEAR := PRIOR_YEAR - 1;
END LOOP;
CLOSE PS_CURSOR;
END;

01-JAN-1999
01-JAN-1999
01-JAN-1999
01-JAN-1999
01-JAN-1999
01-JAN-1999
01-JAN-1999
01-JAN-1999
01-JAN-1999
01-JAN-1999

PL/SQL procedure successfully completed.

[Russ Fleming]

When the cursor is declared, prior_year is sysdate. The cursor will not
"re-declare" with prior_year's new value.

Use a cursor with variable and open cursor using the variable with in the
loop.

[Russ Fleming]

SQLWKS> DECLARE
2> YEARS INTEGER := 10;
3> PRIOR_YEAR DATE := SYSDATE;
4>
5> BEGIN
6> LOOP
7> SELECT TRUNC(TRUNC(TO_DATE(PRIOR_YEAR),'YEAR') ,'YEAR') YR_BEGIN
8> INTO PRIOR_YEAR
9> FROM DUAL;
10>
11> EXIT WHEN YEARS = 0;
12> YEARS := YEARS - 1;
13> DBMS_OUTPUT.put_line(PRIOR_YEAR);
14> PRIOR_YEAR := PRIOR_YEAR - 1;
15> END LOOP;
16> END;
17>
Statement processed.
01-JAN-00
01-JAN-99
01-JAN-98
01-JAN-97
01-JAN-96
01-JAN-95
01-JAN-94
01-JAN-93
01-JAN-92
01-JAN-91

[a...@norfes.ru]

Hi !

>I am trying to write a PL/SQL to generate year-begin dates for the past 10

...

Why You are using CURSOR ? Look at this

DECLARE
YEARS INTEGER := 10;

PRIOR_YEAR DATE := TRUNC(TO_DATE(SYSDATE),'YEAR');
PS_REC DATE;
BEGIN
DBMS_OUTPUT.enable();
LOOP
PS_REC := TRUNC(PRIOR_YEAR - 1,'YEAR');

EXIT WHEN YEARS = 0;

YEARS := YEARS - 1;

PRIOR_YEAR := PS_REC;
DBMS_OUTPUT.put_line(PRIOR_YEAR);

PRIOR_YEAR := PRIOR_YEAR - 1;

END LOOP;
END;

[J]

try followings

declare
v_year date := sysdate;
begin
for i in 1..10 loop
dbms_output.put_line(to_char(trunc(v_year,'YYYY'),'YYYY-MM-DD'));
v_year := add_months(v_year, -12);
end loop;
end;
/

"Buck Turgidson" <jcman...@worldnet.att.net> wrote in message
news:VpUw4.10073$vd7.5...@bgtnsc04-news.ops.worldnet.att.net...

> I am trying to write a PL/SQL to generate year-begin dates for the past 10

> years, based on sysdate. It is not working properly. I don't write a lot
> of PL/SQL. So any help would be appreciated
>
>
>

> DECLARE
> YEARS INTEGER := 10;

> PRIOR_YEAR DATE := SYSDATE;
> CURSOR PS_CURSOR IS
> SELECT
> TRUNC(TRUNC(TO_DATE(PRIOR_YEAR),'YEAR') - 1,'YEAR') AS "YR_BEGIN"
> FROM DUAL;
> PS_REC PS_CURSOR%ROWTYPE;
> BEGIN
> OPEN PS_CURSOR;
> LOOP
> FETCH PS_CURSOR INTO PS_REC;

> EXIT WHEN YEARS = 0;
> YEARS := YEARS - 1;

> PRIOR_YEAR := PS_REC.YR_BEGIN;

> DBMS_OUTPUT.put_line(PRIOR_YEAR);
> PRIOR_YEAR := PRIOR_YEAR - 1;
> END LOOP;

[Jonathan Gennick]

Use TRUNC to truncate the date to Jan 1. Then use ADD_MONTHS
to subtract the required number of months. The following
example subtracts 120 months, or 10 years:

1* select add_months(trunc(sysdate,'year'),-120) from dual
SQL> /

ADD_MONTH
---------
01-JAN-90

There may be other approaches, but this should work.

Jonathan

_____________________________________________________
jona...@gennick.com
http://gennick.com
Brighten the Corner Where You Are