OraclePLSQL--LOOP ---PL/SQL

LOOP

DECLARE

TYPE EmpRecord

IS RECORD (ssn           employee.ssn%TYPE,

LName         employee.LName%TYPE,

DName         department.DName%TYPE,

BonusPayment  NUMBER(6));

InactiveEmp EmpRecord;

BEGIN

FOR i IN 1..3 LOOP

SELECT essn, LName, DName, 0

INTO InactiveEmp

FROM employee, department, works_on

WHERE employee.dno = department.dnumber

AND employee.ssn = works_on.essn

AND hours = (SELECT MIN(hours) FROM works_on)

AND ROWNUM <= 1;

UPDATE department

SET MgrSSN = NULL

WHERE MgrSSN = InactiveEmp.ssn;

UPDATE employee

SET SuperSSN = NULL

WHERE SuperSSN = InactiveEmp.ssn;

DELETE FROM dependent

WHERE essn = InactiveEmp.ssn;

DELETE FROM works_on

WHERE essn = InactiveEmp.ssn;

DELETE FROM employee

WHERE ssn = InactiveEmp.ssn;

COMMIT;

dbms_output.put_line ('Inactive employee has been transferred: ' ||

InactiveEmp.LName);

END LOOP;

END;

Inactive employee has been transferred: Wallace

Inactive employee has been transferred: English

Inactive employee has been transferred: Naraan