DBMS3.SQL

CREATE TABLE DEPARTMENT
(
  DEPT_NO VARCHAR2(2) PRIMARY KEY,
  DEPT_NAME VARCHAR2(25),
  DEPT_DESCRIPTION VARCHAR2(100)
);

-----

CREATE TABLE CATEGORY
(
  CAT_TYPE NUMBER(2) PRIMARY KEY,
  CAT_HRA NUMBER(5,2),
  CAT_DA NUMBER(5,2)
);

--------

CREATE TABLE EMPLOYEE
(
  EMP_NAME VARCHAR2(20),
  EMP_NO VARCHAR2(7) PRIMARY KEY,
  EMP_BASIC NUMBER(8,2) NOT NULL CHECK(EMP_BASIC >=5000),
  EMP_HRA NUMBER(8,2),
  EMP_DA NUMBER(8,2),
  EMP_DEDUCTIONS NUMBER(8,2),
  EMP_GROSS NUMBER(8,2),
  EMP_NET NUMBER(8,2),
  EMP_DOB DATE DEFAULT '01-JAN-70'
);

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

INSERT INTO DEPARTMENT VALUES ('1','CSE','COMPUTER SCIENCE AND ENGINEERING');
INSERT INTO DEPARTMENT VALUES ('2','EEE','ELECTRICAL AND ELECTRONICS ENGINEERING');
INSERT INTO DEPARTMENT VALUES ('3','ECE','ELECTRONICS AND COMMUNICATIONS ENGINEERING');
INSERT INTO DEPARTMENT VALUES ('4','CE','CIVIL ENGINEERING');
INSERT INTO DEPARTMENT VALUES ('5','ME','MECHANICAL ENGINEERING');

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

INSERT INTO CATEGORY VALUES (1,10,50);
INSERT INTO CATEGORY VALUES (2,12,65);
INSERT INTO CATEGORY VALUES (3,18,75);
INSERT INTO CATEGORY VALUES (4,22,95);

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

ALTER TABLE EMPLOYEE ADD (DEPT_NO VARCHAR2(2),CAT_TYPE NUMBER(2));

--------------
ALTER TABLE EMPLOYEE ADD CONSTRAINT EMP_DEP_FK FOREIGN KEY (DEPT_NO) REFERENCES DEPARTMENT(DEPT_NO);
--------------
ALTER TABLE EMPLOYEE ADD CONSTRAINT EMP_CAT_FK FOREIGN KEY (CAT_TYPE) REFERENCES CATEGORY(CAT_TYPE);
--------------

INSERT INTO EMPLOYEE (EMP_NAME,EMP_NO,EMP_BASIC,EMP_DEDUCTIONS,EMP_DOB,DEPT_NO,CAT_TYPE) VALUES ('RAJ','EMP01',25347,2342,'11-FEB-1980','1',1);
INSERT INTO EMPLOYEE (EMP_NAME,EMP_NO,EMP_BASIC,EMP_DEDUCTIONS,EMP_DOB,DEPT_NO,CAT_TYPE) VALUES ('VAMSI','EMP02',78565,6546,'11-JAN-1970','2',2);
INSERT INTO EMPLOYEE (EMP_NAME,EMP_NO,EMP_BASIC,EMP_DEDUCTIONS,EMP_DOB,DEPT_NO,CAT_TYPE) VALUES ('RAMESH','EMP03',84643,3242,'21-APR-1986','3',3);
INSERT INTO EMPLOYEE (EMP_NAME,EMP_NO,EMP_BASIC,EMP_DEDUCTIONS,EMP_DOB,DEPT_NO,CAT_TYPE) VALUES ('SHIVA','EMP04',75432,11123,'18-SEP-1992','4',4);
INSERT INTO EMPLOYEE (EMP_NAME,EMP_NO,EMP_BASIC,EMP_DEDUCTIONS,EMP_DOB,DEPT_NO,CAT_TYPE) VALUES ('CHANDU','EMP05',35243,8433,'11-NOV-1970','5',3);
INSERT INTO EMPLOYEE (EMP_NAME,EMP_NO,EMP_BASIC,EMP_DEDUCTIONS,EMP_DOB,DEPT_NO,CAT_TYPE) VALUES ('RAMU','EMP06',53532,4324,'11-FEB-1980','1',1);
INSERT INTO EMPLOYEE (EMP_NAME,EMP_NO,EMP_BASIC,EMP_DEDUCTIONS,EMP_DOB,DEPT_NO,CAT_TYPE) VALUES ('PREM','EMP07',78566,15324,'11-JAN-1970','2',2);
INSERT INTO EMPLOYEE (EMP_NAME,EMP_NO,EMP_BASIC,EMP_DEDUCTIONS,EMP_DOB,DEPT_NO,CAT_TYPE) VALUES ('HARI','EMP08',85534,3242,'21-APR-1986','3',3);
INSERT INTO EMPLOYEE (EMP_NAME,EMP_NO,EMP_BASIC,EMP_DEDUCTIONS,EMP_DOB,DEPT_NO,CAT_TYPE) VALUES ('PAVAN','EMP09',21342,4533,'18-SEP-1992','4',4);
INSERT INTO EMPLOYEE (EMP_NAME,EMP_NO,EMP_BASIC,EMP_DEDUCTIONS,EMP_DOB,DEPT_NO,CAT_TYPE) VALUES ('RAKESH','EMP10',72243,8833,'11-NOV-1970','5',3);

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

create or replace PROCEDURE PROC_EMP_SAL
IS
  HRA CATEGORY.CAT_HRA%TYPE;
  DA CATEGORY.CAT_DA%TYPE;
  EMP_HRA EMPLOYEE.EMP_HRA%TYPE;
  EMP_DA EMPLOYEE.EMP_DA%TYPE;
  EMP_GROSS EMPLOYEE.EMP_GROSS%TYPE;
  EMP_NET EMPLOYEE.EMP_NET%TYPE;
  QUE VARCHAR2(200);
BEGIN
    FOR X IN (SELECT EMP_NO,EMP_BASIC,EMP_DEDUCTIONS,CAT_TYPE FROM EMPLOYEE)
 	LOOP
    QUE := 'SELECT CAT_HRA FROM CATEGORY WHERE CAT_TYPE='||X.CAT_TYPE;
    EXECUTE IMMEDIATE QUE INTO HRA;
    QUE := 'SELECT CAT_DA FROM CATEGORY WHERE CAT_TYPE='||X.CAT_TYPE;
    EXECUTE IMMEDIATE QUE INTO DA;
    EMP_HRA := X.EMP_BASIC*HRA/100;
    EMP_DA := X.EMP_BASIC*DA/100;
    EMP_GROSS := X.EMP_BASIC+EMP_HRA+EMP_DA;
    EMP_NET := EMP_GROSS-X.EMP_DEDUCTIONS;
    DBMS_OUTPUT.PUT_LINE(X.EMP_NO || EMP_HRA);
    QUE := 'UPDATE EMPLOYEE SET EMP_HRA = ' || EMP_HRA || ' , EMP_DA= ' || EMP_DA || ' , EMP_GROSS= ' || EMP_GROSS || ' ,EMP_NET= ' || EMP_NET || 'WHERE EMP_NO=''' || X.EMP_NO || '''';
    EXECUTE IMMEDIATE QUE;
  END LOOP;
END;

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

EXEC PROC_EMP_SAL;