CODEWITHSUNDEEP
oracle-databasefor-loopselectplsql
Varun Rao
Varun Rao

Reputation: 801

PL/SQL cannot loop select statement for missing values

In HR Schema, I'm writing a PL/SQL block to fetch and display all employees having employee_id from 150 to 200.

declare
     v_c    number(3) := 150 ;
     v_fn   varchar2(150);
begin
     for i in  v_c .. v_c + 50
     loop
     select first_name into v_fn from employees where employee_id = i;
     dbms_output.put_line(i || ' ' || v_fn);
     end loop;    
end;

However it works fine if there is data for all employee_id from 150 to 200.

Suppose I am missing data for employee_id = 160 then this is the output.

Error report -
ORA-01403: no data found
ORA-06512: at line 7
01403. 00000 -  "no data found"
*Cause:    No data was found from the objects.
*Action:   There was no data from the objects which may be due to end of fetch.
150 Peter
151 David
152 Peter
153 Christopher
154 Nanette
155 Oliver
156 Janette
157 Patrick
158 Allan
159 Lindsey

How do I skip 160's error and display the other employees till employee_id = 200?

When the select query fails, The execution of the loop must continue.

Note: I hopelessly tried to use a GOTO in EXCEPTION.

Upvotes: 0

Views: 1195

Answers (3)

Varun Rao
Varun Rao

Reputation: 801

Implementation using Cursors

DECLARE
  c_id         employees.employee_id%type;
  c_first_name employees.first_name%type;
  v_end        NUMBER(3);
  CURSOR c_max_id
  IS
    SELECT MAX(employee_id) 
    FROM employees;
  CURSOR c_employees ( v_start NUMBER,v_end NUMBER )
  IS
    SELECT employee_id,first_name
    FROM employees
    WHERE employee_id BETWEEN v_start AND v_end;
BEGIN
  OPEN c_max_id;
  LOOP
    FETCH c_max_id INTO v_end;
    EXIT
  WHEN c_max_id%NOTFOUND;
    OPEN c_employees(150, v_end);
    LOOP
      FETCH c_employees INTO c_id, c_first_name;
      EXIT
    WHEN c_employees%notfound;
      dbms_output.put_line(c_id || ' ' || c_first_name);
    END LOOP;
    CLOSE c_employees;
  END LOOP;
  CLOSE c_max_id;
END;

Upvotes: 0

Elkhan Ibrahimov
Elkhan Ibrahimov

Reputation: 234

You can try like that

DECLARE
  v_c  NUMBER(3) := 150;
  v_fn VARCHAR2(150);
BEGIN
  FOR i IN v_c .. v_c + 50
  LOOP
    BEGIN
      SELECT first_name INTO v_fn FROM employees WHERE employee_id = i;
      dbms_output.put_line(i || ' ' || v_fn);
    EXCEPTION
    WHEN no_data_found THEN
      dbms_output.put_line('Employee not found, ID: ' || i);
      CONTINUE;
    END;
  END LOOP;
END;

Upvotes: 1

Avrajit Roy
Avrajit Roy

Reputation: 3303

Easiest way to do this is to use FOR loop with SELECT statement as shown below. Hope this helps.

BEGIN
  FOR i IN
  (SELECT employee_id,first_name FROM employees WHERE employee_id BETWEEN 150 AND 200
  )
  LOOP
    dbms_output.put_line(i.employee_id||' '||i.first_name);
  END LOOP;
END;

Upvotes: 1

Related Questions