Home
Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups
General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement |
|
A solution using DBMS_CRYPTO and DBMS_SCHEDULER |
| The |
CREATE SEQUENCE dim_date_seq;
CREATE TABLE dim_date (
dim_date_key INTEGER,
full_date DATE NOT NULL,
day_number VARCHAR2(2) NOT NULL,
month_long_desc VARCHAR2(9) NOT NULL,
month_number VARCHAR2(2) NOT NULL,
month_short_desc VARCHAR2(3) NOT NULL,
quarter_long_desc VARCHAR2(30) NOT NULL,
quarter_number INTEGER NOT NULL,
quarter_short_desc VARCHAR2(20) NOT NULL,
relative_day INTEGER,
relative_month INTEGER,
relative_month_short_desc VARCHAR2(10),
relative_quarter INTEGER,
relative_quarter_short_desc VARCHAR2(20),
relative_week INTEGER,
relative_week_short_desc VARCHAR2(20),
relative_year INTEGER,
relative_year_desc VARCHAR2(20),
relative_year_month_desc VARCHAR2(15),
week_long_desc VARCHAR2(20) NOT NULL,
week_month_number INTEGER NOT NULL,
week_short_desc VARCHAR2(10) NOT NULL,
week_year_number VARCHAR2(4) NOT NULL,
year_month_str VARCHAR2(8) NOT NULL,
year_number VARCHAR2(4) NOT NULL,
create_date DATE NOT NULL,
last_update_date DATE) NOT NULL;
ALTER TABLE dim_date
ADD CONSTRAINT pk_dim_date
PRIMARY KEY (dim_date_key);
ALTER TABLE dim_date
ADD CONSTRAINT uk_dim_date
UNIQUE (full_date); |
| L |
CREATE OR REPLACE PROCEDURE load_dim_date AUTHID DEFINER IS
d_data dim_date%ROWTYPE;
CurDate DATE := TO_DATE('01-NOV-2011');
BEGIN
FOR i IN 0 .. 999 LOOP
d_data.dim_date_key := dim_date_seq.NEXTVAL;
d_data.full_date := CurDate;
d_data.dim_date_key := dim_date_seq.NEXTVAL;
d_data.full_date := CurDate;
d_data.day_number := TO_CHAR(CurDate, 'DD');
d_data.month_long_desc := INITCAP(TO_CHAR(CurDate, 'MONTH'));
d_data.month_number := TO_CHAR(CurDate, 'MM');
d_data.month_short_desc := INITCAP(TO_CHAR(CurDate, 'MON'));
d_data.quarter_long_desc := 'Quarter ' || TO_CHAR(CurDate, 'Q') || ', ' ||
TO_CHAR(CurDate, 'YYYY');
d_data.quarter_number := TO_CHAR(CurDate, 'Q');
d_data.quarter_short_desc := 'Q' || TO_CHAR(CurDate, 'Q') || ' ' || TO_CHAR(CurDate, 'YYYY');
d_data.week_long_desc := 'Week ' || TO_CHAR(CurDate, 'WW');
d_data.week_month_number := TO_NUMBER(TO_CHAR(CurDate, 'WW'));
d_data.week_short_desc := 'W ' || TO_CHAR(CurDate, 'WW');
d_data.week_year_number := TO_CHAR(CurDate, 'WW');
d_data.year_month_str := TO_CHAR(CurDate, 'YYYY') || '/' || INITCAP(TO_CHAR(CurDate, 'MON'));
d_data.year_number := TO_CHAR(CurDate, 'YYYY');
d_data.create_date := CurDate;
d_data.last_update_date := CurDate;
INSERT INTO dim_date VALUES d_data;
CurDate := CurDate + 1;
END LOOP;
COMMIT;
END load_dim_date;
/ |
| L |
CREATE OR REPLACE PROCEDURE calc_relative_dim_date AUTHID DEFINER IS
CURSOR crcur IS
SELECT dim_date_key, full_date
FROM dim_date
FOR UPDATE;
vRelativeDay dim_date.relative_day%TYPE;
vRelativeweek dim_date.relative_week%TYPE;
CurDate DATE := TRUNC(SYSDATE);
WkBegJDate INTEGER := TO_NUMBER(TO_CHAR(CurDate-6, 'J'));
BEGIN
FOR crrec IN crcur LOOP
vRelativeDay := crrec.full_date-TRUNC(CurDate);
vRelativeWeek := FLOOR((TO_NUMBER(TO_CHAR(crrec.full_date, 'J') - WkBegJDate))/7);
UPDATE dim_date
SET relative_day = vRelativeDay,
relative_week = vRelativeWeek
WHERE CURRENT OF crcur;
END LOOP;
COMMIT;
END calc_relative_dim_date;
/ |
| A |
|
