DB procedure for TYPE1 and TYPE2 mappings (SCD)

Type 2

====== 

CREATE OR REPLACE PROCEDURE HR.TYPE2(EMP_ID INTEGER,E_NAME CHAR,SAL REAL)

IS

EMP_NO INTEGER;

C_FLAG CHAR;

REC_CNT INTEGER;

BEGIN

SELECT 'Y' INTO C_FLAG FROM DUAL;

SELECT COUNT(*) INTO REC_CNT FROM TYPE1 WHERE EMPID=EMP_ID;

IF REC_CNT >=2 THEN

DELETE FROM TYPE1 WHERE EMPID=EMPID AND CURRENTFLAG='N';

COMMIT;

END IF;

SELECT EMPID INTO EMP_NO FROM TYPE1 WHERE EMPID=EMP_ID;

IF EMP_NO IS NOT NULL THEN

UPDATE TYPE1 SET CURRENTFLAG='N'

WHERE EMPID=EMP_ID;

END IF;

IF EMP_NO IS NOT NULL THEN

INSERT INTO TYPE1 VALUES(EMP_ID,E_NAME,SAL,C_FLAG);

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

INSERT INTO TYPE1 VALUES(EMP_ID,E_NAME,SAL,C_FLAG);

END

TYPE2;

/

 ~~~~~~

Type1

===== 

CREATE OR REPLACE PROCEDURE HR.TYPE_HIST(EMP_ID INTEGER,E_NAME VARCHAR,SAL REAL)

IS

EMP_NO INTEGER;

BEGIN

SELECT EMPID INTO EMP_NO FROM TYPE1 WHERE EMPID=EMP_ID;

IF EMP_NO IS NOT NULL THEN

UPDATE TYPE1 SET EMPID=EMP_ID,ENAME=E_NAME,SALARY=SAL WHERE EMPID=EMP_ID;

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

INSERT INTO TYPE1 VALUES(EMP_ID,E_NAME,SAL);

END

TYPE_HIST;

/