Using the WHILE LOOP Statement

The WHILE LOOP statement repeats a sequence of statements while a condition is TRUE. Its syntax is:

WHILE condition LOOP
  statement [, statement ]...
END LOOP;

Note: If the statements between LOOP and END LOOP never cause condition to become FALSE, the WHILE LOOP statement runs indefinitely.

Suppose that the eval_frequency function uses the WHILE LOOP statement instead of the FOR LOOP statement, and ends after the proposed salary exceeds the maximum salary for the JOB_ID.

Change the eval_frequency function as shown in bold font in the following example. (For information about the procedures that print sthe strings, DBMS_OUTPUT.PUT_LINE, see Oracle Database PL/SQL Packages and Types Reference.)

WHILE LOOP Statement that Computes Salary to Maximum

FUNCTION eval_frequency (emp_id IN EMPLOYEES.EMPLOYEE_ID%TYPE)
  RETURN PLS_INTEGER
AS
  h_date      EMPLOYEES.HIRE_DATE%TYPE;
  today       EMPLOYEES.HIRE_DATE%TYPE;
  eval_freq   PLS_INTEGER;
  j_id        EMPLOYEES.JOB_ID%TYPE;
  sal         EMPLOYEES.SALARY%TYPE;
  sal_raise   NUMBER(3,3) := 0;
  sal_max     JOBS.MAX_SALARY%TYPE;

BEGIN
  SELECT SYSDATE INTO today FROM DUAL;

  SELECT HIRE_DATE INTO h_date
  FROM EMPLOYEES
  WHERE EMPLOYEE_ID = emp_id;

  IF ((h_date + (INTERVAL '120' MONTH)) < today) THEN
    eval_freq := 1;

    SELECT JOB_ID INTO j_id
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = emp_id;

    SELECT SALARY INTO sal
    FROM EMPLOYEES
    WHERE EMPLOYEE_ID = emp_id;

    SELECT MAX_SALARY INTO sal_max
    FROM JOBS
    WHERE JOB_ID = j_id;

    CASE j_id
      WHEN 'PU_CLERK' THEN sal_raise := 0.08;
      WHEN 'SH_CLERK' THEN sal_raise := 0.07;
      WHEN 'ST_CLERK' THEN sal_raise := 0.06;
      WHEN 'HR_REP'   THEN sal_raise := 0.05;
      WHEN 'PR_REP'   THEN sal_raise := 0.05;
      WHEN 'MK_REP'   THEN sal_raise := 0.04;
      ELSE NULL;
    END CASE;

    IF (sal_raise != 0) THEN
      BEGIN
        DBMS_OUTPUT.PUT_LINE('If salary ' || sal || ' increases by ' ||
          ROUND((sal_raise * 100),0) ||
          '% each year, it will be:');

        WHILE sal <= sal_max LOOP
          sal := sal * (1 + sal_raise);
          DBMS_OUTPUT.PUT_LINE(ROUND(sal, 2);
        END LOOP;

        DBMS_OUTPUT.PUT_LINE('Maximum salary for this job is ' || sal_max);
      END;
    END IF;
  ELSE
    eval_freq := 2;
  END IF;
 
  RETURN eval_freq;
END eval_frequency;

Using the WHILE LOOP Statement

Related Topics