|
-
Given my previous definition of table MY_DATES, you can use the following procedure:
SQL> CREATE OR REPLACE PROCEDURE insert_dates (p_year IN INTEGER)
2 AS
3 v_cntr INTEGER := 0;
4 v_start_date DATE := TRUNC(TO_DATE(p_year, 'YYYY'), 'YYYY');
5 v_end_date DATE := TRUNC(TO_DATE(p_year+1, 'YYYY'), 'YYYY');
6 r_dates my_dates%ROWTYPE;
7 BEGIN
8 LOOP
9 r_dates.my_date := v_start_date + v_cntr;
10 EXIT WHEN r_dates.my_date >= v_end_date;
11 v_cntr := v_cntr + 1;
12 r_dates.my_month := TO_CHAR(r_dates.my_date,'MONTH');
13 r_dates.my_day := TO_CHAR(r_dates.my_date,'DD');
14 r_dates.cntr := v_cntr;
15 INSERT INTO my_dates (my_date, my_month, my_day, cntr)
16 VALUES (r_dates.my_date, r_dates.my_month, r_dates.my_day, r_dates.cntr);
17 END LOOP;
18 DBMS_OUTPUT.PUT_LINE(v_cntr || ' rows inserted.');
19 END;
20 /
Procedure created.
SQL> set serveroutput on
SQL> exec insert_dates(2000);
366 rows inserted.
PL/SQL procedure successfully completed.
SQL> exec insert_dates(2001)
365 rows inserted.
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL>
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|