Seeding a Calendar Table
While working on one of the labs, my students wanted a quick way to seed their CALENDAR
table. Here’s a small script to seed twenty years of a calendar.
-- Conditionally drop the table. BEGIN FOR i IN (SELECT TABLE_NAME FROM user_tables WHERE TABLE_NAME = 'MOCK_CALENDAR') LOOP EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name||' CASCADE CONSTRAINTS'; END LOOP; END; / -- Create the table. CREATE TABLE mock_calendar ( short_month VARCHAR2(3) , long_month VARCHAR2(9) , start_date DATE , end_date DATE ); -- Seed the table with 10 years of data. DECLARE -- Create local collection data types. TYPE smonth IS TABLE OF VARCHAR2(3); TYPE lmonth IS TABLE OF VARCHAR2(9); -- Declare month arrays. short_month SMONTH := smonth('JAN','FEB','MAR','APR','MAY','JUN' ,'JUL','AUG','SEP','OCT','NOV','DEC'); long_month LMONTH := lmonth('January','February','March','April','May','June' ,'July','August','September','October','November','December'); -- Declare base dates. start_date DATE := '01-JAN-79'; end_date DATE := '31-JAN-79'; -- Declare years. years NUMBER := 20; BEGIN -- Loop through years and months. FOR i IN 1..years LOOP FOR j IN 1..short_month.COUNT LOOP INSERT INTO mock_calendar VALUES ( short_month(j) , long_month(j) , add_months(start_date,(j-1)+(12*(i-1))) , add_months(end_date,(j-1)+(12*(i-1)))); END LOOP; END LOOP; END; / -- Format set break for output. SET PAGESIZE 16 -- Format column output. COL short_month FORMAT A5 HEADING "Short|Month" COL long_month FORMAT A9 HEADING "Long|Month" COL start_date FORMAT A9 HEADING "Start|Date" COL end_date FORMAT A9 HEADING "End|Date" SELECT * FROM mock_calendar; |
The output will look like this with a page break by year:
Short Long Start End Month Month Date Date ----- --------- --------- --------- JAN January 01-JAN-79 31-JAN-79 FEB February 01-FEB-79 28-FEB-79 MAR March 01-MAR-79 31-MAR-79 APR April 01-APR-79 30-APR-79 MAY May 01-MAY-79 31-MAY-79 JUN June 01-JUN-79 30-JUN-79 JUL July 01-JUL-79 31-JUL-79 AUG August 01-AUG-79 31-AUG-79 SEP September 01-SEP-79 30-SEP-79 OCT October 01-OCT-79 31-OCT-79 NOV November 01-NOV-79 30-NOV-79 DEC December 01-DEC-79 31-DEC-79 |