Minnu's Blog on Informatica & Data warehouse concepts

Archives

Wednesday, July 4, 2007

On way to PL/SQL Type 2 dimension Implementation

This is in order to develop a PL/SQL procedure in order to achieve TYPE 2 (effective date range) on emp table, taking sal as the changing attribute.......here emp table is the source & emp_new is the target.....first start with basic structure & later in the 2nd step alter the emp_new table & add 2 more coloumns Valid_from, Valid_till (date) ... then after in 3rd try add 1 more coloumn WH_KEY (number) ........

Basic prog to start with ...

SQL> desc emp_new;
----------------------------------------- -------- ---------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
-----------------------------------------
create or replace procedure mypro AS
var1 number;
var2 varchar2(10);
var3 varchar2(9);
var4 number;
var5 date;
var6 number;
var7 number;
var8 number;
var51 date;

Cursor MYCURSE IS select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp;

begin

OPEN MYCURSE;

LOOP

FETCH MYCURSE INTO var1, var2, var3, var4, var5, var6, var7, var8;

Exit when MYCURSE%NOTFOUND;

var51 := to_date(var5,'DD-MON-YYYY');

insert into emp_new values(var1, var2, var3, var4, var51, var6, var7, var8);

END LOOP;

CLOSE MYCURSE;

Commit;

End;
/

----------------------------------------------------------------

Working out a little more.....Updating changed sal, valid_till field ....Just go through

--------

create or replace procedure mypro AS
var1 number;
var2 varchar2(10);
var3 varchar2(9);
var4 number;
var5 date;
var6 number;
var7 number;
var8 number;
var51 date;

vart1 number;
vart2 varchar2(10);
vart3 varchar2(9);
vart4 number;
vart5 date;
vart6 number;
vart7 number;
vart8 number;
vart51 date;
vara date;
varb date;

Cursor MYCURSE_SRC IS select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp;
Cursor MYCURSE_TGT IS Select empno, ename, job, mgr, hiredate, sal, comm, deptno, valid_from, valid_till from emp_new where empno = var1 AND valid_till is NULL;

begin
OPEN MYCURSE_SRC;
LOOP
FETCH MYCURSE_SRC INTO var1, var2, var3, var4, var5, var6, var7, var8;

OPEN MYCURSE_TGT;
FETCH MYCURSE_TGT INTO vart1, vart2, vart3, vart4, vart5, vart6, vart7, vart8, vara, varb;

Exit when MYCURSE_SRC%NOTFOUND;

var51 := to_date(var5,'DD-MON-YYYY');

IF MYCURSE_TGT%FOUND THEN

IF Var6 = vart6 THEN
vart7 := var7;
ELSE
update emp_new set valid_till = sysdate where empno = var1;
insert into emp_new values(var1, var2, var3, var4, var51, var6, var7, var8, sysdate, NULL);
END IF;

ELSE

insert into emp_new values(var1, var2, var3, var4, var51, var6, var7, var8, sysdate, NULL);

END IF;

CLOSE MYCURSE_TGT;

END LOOP;

CLOSE MYCURSE_SRC;
Commit;
End;
/

-----------------------------------
Now almost the final thing....Adding WH_key using seq gen.... but remember...

Within a procedure or Function you are not technically allowed to perform DDL statements. But you can use a dynamic SQL statement to overcome this. First you have to drop the existing sequence since it is schema specific just like a table, view, or index. Then create the sequence over again. I would create the sequence manually first so you don't get an error within your procedure. stmt := 'DROP SEQUENCE YOUR_SEQ_NAME';

execute immediate stmt; stmt := 'CREATE SEQUENCE YOUR_SEQ_NAME START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE';

The above commands are issued in your SQL*PLUS window but not inside the procedure...still you can use the values created by it inside the procedure .... my seq gen is named as WH_KEY

------------------
create or replace procedure mypro AS

var1 number;
var2 varchar2(10);
var3 varchar2(9);
var4 number;
var5 date;
var6 number;
var7 number;
var8 number;
var51 date;

vart1 number;
vart2 varchar2(10);
vart3 varchar2(9);
vart4 number;
vart5 date;
vart6 number;
vart7 number;
vart8 number;
vart51 date;
vara date;
varb date;

Cursor MYCURSE_SRC IS select empno, ename, job, mgr, hiredate, sal, comm, deptno from emp;
Cursor MYCURSE_TGT IS Select empno, ename, job, mgr, hiredate, sal, comm, deptno, valid_from, valid_till from emp_new where empno = var1 AND valid_till is NULL;

begin

OPEN MYCURSE_SRC;

LOOP

FETCH MYCURSE_SRC INTO var1, var2, var3, var4, var5, var6, var7, var8;

OPEN MYCURSE_TGT;

FETCH MYCURSE_TGT INTO vart1, vart2, vart3, vart4, vart5, vart6, vart7, vart8, vara, varb;

Exit when MYCURSE_SRC%NOTFOUND;

var51 := to_date(var5,'DD-MON-YYYY');

IF MYCURSE_TGT%FOUND THEN
IF Var6 = vart6 THEN
vart7 := var7; ELSE
update emp_new set valid_till = sysdate where empno = var1;
insert into emp_new values(WH_KEY.NextVal, var1, var2, var3, var4, var51, var6, var7, var8, sysdate, NULL);
END IF;
ELSE
insert into emp_new values(WH_KEY.NextVal, var1, var2, var3, var4, var51, var6, var7, var8, sysdate, NULL);
END IF;

CLOSE MYCURSE_TGT;

END LOOP;

CLOSE MYCURSE_SRC;

Commit;
End;
/
--------------------------------------------------------------------------------------------
Finally now lets make the program a bit small ....avoiding out the large number of variables & introducing %rowtype functionality on 1 or 2 variables....

0 comments: