Using the Basic LOOP and EXIT WHEN Statements

The basic LOOP statement repeats a sequence of statements. Its syntax is:

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

At least one statement must be an EXIT statement; otherwise, the LOOP statement runs indefinitely.

The EXIT WHEN statement (the EXIT statement with its optional WHEN clause) exits a loop when a condition is TRUE and transfers control to the end of the loop.

In the eval_frequency function, in the last iteration of the WHILE LOOP statement, the last computed value usually exceeds the maximum salary.

WHILE LOOP

Change the WHILE LOOP statement to a basic LOOP statement that includes an EXIT WHEN statement, as shown in the following example.

Using the EXIT WHEN Statement

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:');

        LOOP
          sal := sal * (1 + sal_raise);
          EXIT WHEN sal > sal_max;
          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 Basic LOOP and EXIT WHEN Statements

Related Topics