SQL in steps

Let us assume we have a request to show results from a query that is too massive to run on-line. Let us also assume that we may distinguish in this query a series on countries / persons that must be run.

In pseudo code, we have the situation that we may have to run: select * from complicated that could be translated into something like: for employees select * from lesscomplicated(employee).

This can be solved in a PLSQL programme that contains a LOOP. Two possible solutions are given below:

create or replace PROCEDURE FORLOOP AS
    CURSOR c1 IS SELECT * FROM HR.EMPLOYEES;
    i NUMBER:= 0;
    v_totalName varchar2(80);
 BEGIN
   FOR e_rec IN c1 LOOP
     i:= i+1;
     select DEPARTMENTS.department_name into v_totalName from HR.DEPARTMENTS where e_rec.department_id = DEPARTMENTS.department_id;
     dbms_output.put_line(i||chr(9)||e_rec.EMPLOYEE_ID||chr(9)||e_rec.LAST_NAME||chr(9)|| v_totalName);
   END LOOP;
 END;

The idea is that we have a series of employees. For each of these employees, we have a simple query for one employee. This simple query for one employee runs fast. The loop guarantees that all employees are considered and all results are returned. A

An alternative programme is:

create or replace PROCEDURE FORLOOP AS
    CURSOR c1 IS SELECT * FROM HR.EMPLOYEES;
    e_rec c1%ROWTYPE;
    i NUMBER:= 0;
    v_totalName varchar2(80);
 BEGIN
   OPEN c1;
   i:=0;
   LOOP
     FETCH c1 into e_rec;
     EXIT WHEN c1%NOTFOUND;
     i:= i+1;
     select DEPARTMENTS.department_name into v_totalName from HR.DEPARTMENTS where e_rec.department_id = DEPARTMENTS.department_id;
     dbms_output.put_line(i||chr(9)||e_rec.EMPLOYEE_ID||chr(9)||e_rec.LAST_NAME||chr(9)|| v_totalName);
   END LOOP;
 END;