Live ORACLE

DROP TABLE temp CASCADE CONSTRAINTS PURGE;

CREATE TABLE temp (
tempid   NUMBER(6),
tempsal  NUMBER(8,2),
tempname VARCHAR2(25));


DECLARE

sal             employees.salary%TYPE := 0;

 mgr_id          employees.manager_id%TYPE;

 lname           employees.last_name%TYPE;

 starting_empid  employees.employee_id%TYPE := 120;

BEGIN

SELECT manager_id

INTO   mgr_id

FROM   employees

WHERE  employee_id = starting_empid;



WHILE sal <= 15000 LOOP -- loop until sal > 15000

  SELECT salary,

         manager_id,

         last_name

  INTO   sal,mgr_id,lname

  FROM   employees

  WHERE  employee_id = mgr_id;

END LOOP;



INSERT INTO TEMP

VALUES     (NULL,

            sal,

            lname); -- insert NULL for tempid



COMMIT;

EXCEPTION

WHEN no_data_found THEN

  INSERT INTO TEMP

  VALUES     (NULL,

              NULL,

              'Not found'); -- insert NULLs



  COMMIT;

END;

/

Using WHILE-LOOP for Control