SQL & PL/SQL

Hi,

I need a FOR LOOP cursor that scans and updates all pro-rata column in table EMPLOYEE(child) based on what pay classification all employees are on in the CLASSIFICATION(parent) table.

DECLARE
BEGIN

IF employee.emp_type = 'FT' THEN
UPDATE employee
SET employee.pro_rata = ((classification.yearly_pay/52)*52)
WHERE employee.empid = v_empid AND classification.class_id = employee.class_id;
END IF;

IF employee.emp_type = 'PT1' THEN
UPDATE employee
SET employee.pro_rata = ((classification.yearly_pay/39)*52)
WHERE employee.empid = v_empid AND classification.class_id = employee.class_id;
END IF;

IF employee.emp_type = 'PT2' THEN
UPDATE employee
SET employee.pro_rata = ((classification.yearly_pay/21)*52)
WHERE employee.empid = v_empid AND classification.class_id = employee.class_id;
END IF;
END;
/


How do I create a cursor that cuts across these two table

See tables and data

CREATE TABLE CLASSIFICATION(
CLASS_ID VARCHAR2(6) NOT NULL,
CLASS_TYPE VARCHAR2(10),
DESCRIPTION VARCHAR2(30) NOT NULL,
YEARLY_PAY NUMBER(8),
HOURLY_RATE NUMBER,
WEEKDAY_OVER_TIME NUMBER,
WEEKEND_OVER_TIME NUMBER,
CONSTRAINT PK_CLASS_ID PRIMARY KEY (CLASS_ID));

INSERT INTO CLASSIFICATION VALUES('PR1','PERMANENT','MANAGER',45000,'','',NULL);

INSERT INTO CLASSIFICATION VALUES('PR2','PERMANENT','ADMIN ASSISTANT',22000,'',1.5,NULL);

INSERT INTO CLASSIFICATION VALUES('PR3','PERMANENT','CONTROLLER',32000,'',1.5,NULL);

INSERT INTO CLASSIFICATION VALUES('PR4','PERMANENT','CASH OFFICER',22000,'',1.5,NULL);

INSERT INTO CLASSIFICATION VALUES('PR5','PERMANENT','CLEANERS',16000,'',1.5,NULL);

INSERT INTO CLASSIFICATION VALUES('PR6','PERMANENT','ADMIN OFFICER',22000,'',1.5,NULL);

INSERT INTO CLASSIFICATION VALUES('PR7','PERMANENT','WAREHOUSE ATTENDANT',20000,'',1.5,NULL);

INSERT INTO CLASSIFICATION VALUES('PR8','PERMANENT','WINDOWS DRESSER',22000,'',1.5,NULL);

INSERT INTO CLASSIFICATION VALUES('PR9','PERMANENT','DIRECTOR',60000,'','',NULL);

INSERT INTO CLASSIFICATION VALUES('PR10','PERMANENT','DEPUTY DIRECTOR',52000,'','',NULL);

INSERT INTO CLASSIFICATION VALUES('PR11','PERMANENT','SALES ASSISTANT',21000,'',1.5,NULL);


INSERT INTO CLASSIFICATION VALUES('TEMP2','TEMP STAFF','ADMIN ASSISTANT','',16.50,'',NULL);

INSERT INTO CLASSIFICATION VALUES('TEMP3','TEMP STAFF','CONTROLLER','',29.00,'',NULL);

INSERT INTO CLASSIFICATION VALUES('TEMP4','TEMP STAFF','CASH OFFICER','',19.00,'',NULL);

INSERT INTO CLASSIFICATION VALUES('TEMP5','TEMP STAFF','CLEANERS','',10.00,'',NULL);

INSERT INTO CLASSIFICATION VALUES('TEMP6','TEMP STAFF','ADMIN OFFICER','',20.00,'',NULL);

INSERT INTO CLASSIFICATION VALUES('TEMP7','TEMP STAFF','WAREHOUSE ATTENDANT','',18.00,'',NULL);

INSERT INTO CLASSIFICATION VALUES('TEMP8','TEMP STAFF','WINDOWS DRESSER','',18.50,'',NULL);

INSERT INTO CLASSIFICATION VALUES('TEMP11','TEMP STAFF','SALES ASSISTANT','',16.00,'',NULL);
--------------------------------------------------------------------------------------------------------------

CREATE TABLE EMPLOYEE(
EMPID NUMBER(5) NOT NULL,
SURNAME VARCHAR2(30) NOT NULL,
FNAME VARCHAR2(30) NOT NULL,
GENDER CHAR(1) NOT NULL,
DOB DATE NOT NULL,
EMP_TYPE VARCHAR2(20) NOT NULL,
ANNUAL_WEEKS_REQD NUMBER(2),
PRO_RATA_WAGES NUMBER(7,2),
HOLIDAY_ENTLMENT NUMBER(2),
SICK_LEAVE_ENTLMENT NUMBER(2),
HIRE_DATE DATE NOT NULL,
END_DATE DATE,
ACCNO NUMBER(8) NOT NULL,
BANKNAME VARCHAR2(20) NOT NULL,
BRANCH VARCHAR2(20) NOT NULL,
ACCOUNTNAME VARCHAR2(20),
CLASS_ID VARCHAR2(6),
CONSTRAINT CK_HIRE_END CHECK (HIRE_DATE < END_DATE),
CONSTRAINT CK_HIRE_DOB CHECK (HIRE_DATE >= ADD_MONTHS(DOB, 12 * 18)),
CONSTRAINT CK_EMP_TYPE CHECK (EMP_TYPE IN ('FT','PT1','PT2','PT3','HOURLY')),
CONSTRAINT CK_EMP_GENDER CHECK (GENDER IN ('M','F')),
CONSTRAINT FK_EMP_CLASS FOREIGN KEY (CLASS_ID) REFERENCES CLASSIFICATION(CLASS_ID),
CONSTRAINT PK_EMP PRIMARY KEY (EMPID));


CREATE SEQUENCE SEQ_EMPID START WITH 1;


INSERT INTO EMPLOYEE VALUES(
SEQ_EMPID.NEXTVAL,'RICHARD','BRANDON','M','25-DEC-1966','FT',52,22000.00,28,14,'10-JAN-2005',NULL,90823227,'NATWEST','BROMLEY','DEPOSIT','PR2');


INSERT INTO EMPLOYEE VALUES(
SEQ_EMPID.NEXTVAL,'BOYCE','CODD','M','15-JAN-1972','PT1','','','','','12-JAN-2005',NULL,72444091,'LLOYDS','KENT','CURRENT','PR8');


INSERT INTO EMPLOYEE VALUES(
SEQ_EMPID.NEXTVAL,'ALHAJA','BROWN','F','20-MAY-1970','HOURLY','','','','','21-JUN-2000',NULL,09081900,'ABBEY','ESSEX','CURRENT','TEMP2');


INSERT INTO EMPLOYEE VALUES(
SEQ_EMPID.NEXTVAL,'RON','ATKINSON','M','10-AUG-1955','PT3','','','','','12-JAN-2005','26-MAR-2006',01009921,'HALIFAX','KENT','SAVINGS','PR6');


INSERT INTO EMPLOYEE VALUES(
SEQ_EMPID.NEXTVAL,'CHAMPI','KANE','F','01-JAN-1965','PT2','','','','','12-JAN-2004',NULL,98120989,'HSBC','ILFORD','CURRENT','PR4');


INSERT INTO EMPLOYEE VALUES(
SEQ_EMPID.NEXTVAL,'NED','VED','M','15-JAN-1980','HOURLY','','','','','29-DEC-2005',NULL,90812300,'WOOLWICH','LEWISHAM','CURRENT','TEMP6');


INSERT INTO EMPLOYEE VALUES(
SEQ_EMPID.NEXTVAL,'JILL','SANDER','F','22-MAR-1971','FT','','','','','30-NOV-2003',NULL,23230099,'BARCLAYS','PENGE','DEPOSIT','PR1');

Any contribution would be appreciated

many thanks

Cube60