Translation from Java to Oracle SQL: use every element into a loop as result of a SELECT statement [closed]

Tags: , ,



It’s my first time using Oracle function. I’m trying to translate this piece of code in Java to Oracle SQL.

 It's present a SELECT statement before the loop
        for (int i = 0; i < checkList.size(); i++) {
                        
                      String check= checkList.get(i);
        }

My translation:

CURSOR C1 IS 
 ... select statement ... 
BEGIN
    FOR vItems IN C1.COUNT LOOP 
    -- I don't know how to continue, taking the first value of the select 
    END LOOP;
END;

Answer

Suppose you want to do something for each EMPNO in a table, EMP, in schema SCOTT. (Almost every Oracle database installation has a standard schema, SCOTT; one of the tables is EMP, with primary key EMPNO. This is often used for testing and illustrations.)

To do something with each value of EMPNO in a loop, in PL/SQL, you would do something like this:

declare
  cursor c1 is select empno from scott.emp;
begin
  for rec in c1 loop
    dbms_output.put_line(rec.empno);  -- or more generally, "DO SOMETHING"
  end loop;
end;
/

Note that there is no “array index” here – you reference “records” directly. (By the way, if you want the rows to be processed in some specific order, you include that in an order by clause directly in the select statement!)

Although you can write this much more compactly (no equivalent in Java, I think):

begin
  for rec in (select empno from scott.emp) loop
    dbms_output.put_line(rec.empno);
  end loop;
end;
/


Source: stackoverflow