OraclePLSQL--Conditional Loop PL/SQL

Conditional Loop

DECLARE

    TYPE EmployeeRecord

      IS RECORD (ssn           Employee.ssn%TYPE,

                 LName         Employee.LName%TYPE,

                 DName         department.DName%TYPE,

                 BonusPayment  NUMBER(10));

    InactiveEmployee     EmployeeRecord;

    EmployeesRemaining    PLS_INTEGER;

BEGIN

  SELECT COUNT(*)

  INTO EmployeesRemaining

  FROM Employee;

  WHILE EmployeesRemaining >= 9 LOOP

    SELECT essn, LName, DName, 2

    INTO InactiveEmployee

    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 = InactiveEmployee.ssn;

    UPDATE Employee

    SET SuperSSN = NULL

    WHERE SuperSSN = InactiveEmployee.ssn;

    DELETE FROM dependent

    WHERE essn = InactiveEmployee.ssn;

    DELETE FROM works_on

    WHERE essn = InactiveEmployee.ssn;

    DELETE FROM Employee

    WHERE ssn = InactiveEmployee.ssn;

    COMMIT;

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

                           InactiveEmployee.LName);

  END LOOP;

END;

anonymous block completed

note: as there is no error so it means that in our database we do not have 9 or more than 9 employees.

Ø  Now we run the codes again but now we replace the 9 with 8 in  “WHILE EmployeesRemaining >= 9 LOOP

DECLARE

    TYPE EmployeeRecord

      IS RECORD (ssn           Employee.ssn%TYPE,

                 LName         Employee.LName%TYPE,

                 DName         department.DName%TYPE,

                 BonusPayment  NUMBER(10));

    InactiveEmployee     EmployeeRecord;

    EmployeesRemaining    PLS_INTEGER;

BEGIN

  SELECT COUNT(*)

  INTO EmployeesRemaining

  FROM Employee;

  WHILE EmployeesRemaining >= 8 LOOP

    SELECT essn, LName, DName, 2

    INTO InactiveEmployee

    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 = InactiveEmployee.ssn;

    UPDATE Employee

    SET SuperSSN = NULL

    WHERE SuperSSN = InactiveEmployee.ssn;

    DELETE FROM dependent

    WHERE essn = InactiveEmployee.ssn;

    DELETE FROM works_on

    WHERE essn = InactiveEmployee.ssn;

    DELETE FROM Employee

    WHERE ssn = InactiveEmployee.ssn;

    COMMIT;

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

                           InactiveEmployee.LName);

  END LOOP;

END;

*Cause:    No data was found from the objects.

*Action:   There was no data from the objects which may be due to end of fetch.

Inactive Employee has been transferred: Shah

Inactive Employee has been transferred: Waqar

Inactive Employee has been transferred: Saaad

Inactive Employee has been transferred: Wardhaa

Inactive Employee has been transferred: Hiraa

Inactive Employee has been transferred: Khan

Inactive Employee has been transferred: Waseem