PL/SQL - Can you access certain record in a cursor by index?

Question

I am trying to write a test, and given a group of reviews, I want to set 1 of them to 'Status A' and all of the rest to 'Status B'. I know for PL/SQL, there is a FOR LOOP syntax for a CURSOR, and I got it so it can process all of the reviews, but is there a way to access a specific record in that cursor?

I think what would solve my problem is a way to access the Cursor by Index, similar to how you can access an array by index in other languages. Is there a way to do that in PL/SQL?

I am still quite new to the syntax of PL/SQL and cursors, so I will write out what I am wanting to do in pseudocode.

Pseudocode

CURSOR c_reviewer_ids IS
    SELECT id FROM reviewer_tbl
    WHERE course_id = 123;
v_first_id   reviewer_tbl.id%TYPE;

FOR i in 0..c_reviewer_ids.length
{
   IF i == 0
   {
      v_first_id := c_reviewer_ids(i);

      UPDATE reviewer_tbl
      SET status = 'Status A'
      WHERE id = c_reviewer_ids(i);
   }
   ELSE
   {
      UPDATE reviewer_tbl
      SET status = 'Status B'
      WHERE id = c_reviewer_ids(i);
   }
}

I was able to make a CURSOR FOR LOOP, but it handles every record the same, and I want to do something special for just one record. This is what I currently have:

CURSOR FOR LOOP (doesn't handle special case)

  CURSOR c_reviewer_ids IS
    SELECT id FROM reviewer_tbl
    WHERE course_id = 123;
  v_reviewer_id   reviewer_tbl.id%TYPE;

  FOR l_reviewer_id IN c_reviewer_ids 
  LOOP
      --Set the status for all reviewers.
      UPDATE reviewer_tbl
      SET status = 'Status B'
      WHERE reviewer_id = l_reviewer_id.id;

      --Save one of the ids; for this particular test, it doesn't matter if it is first or not
      v_reviewer_id := l_reviewer_id.id;
  END LOOP;

Solution 1

Using this from the Oracle documentation, I found a way to use an INDEX-BY table that most closely matches what I wanted in my pseudocode. It creates the cursor, and then puts it to a temporary table, which can then be indexed.

After writing the code out, I realised that the cursor may not be that necessary, since it is immediately closed. In that case, the cursor can be omitted and only the INDEX BY table is used (similar to what Carlo wrote in his answer).

Using index-by table ALONG WITH cursor

DEFINE
    CURSOR c_reviewer_ids IS
        SELECT id FROM reviewer_tbl
        WHERE course_id = 123;

    --table type
    TYPE reviewer_id_tbl_type IS TABLE OF c_reviewer_ids%ROWTYPE INDEX BY PLS_INTEGER;
    --actual table
    t_reviewer_ids reviewer_id_tbl_type;

    v_first_id   reviewer_tbl.id%TYPE;
BEGIN
    --Fetch the cursor into the indexed table
    OPEN c_reviewer_ids;
    FETCH c_reviewer_ids BULK COLLECT INTO t_reviewer_ids;
    CLOSE c_reviewer_ids;  --cursor won't be used anymore

    --For loop
    FOR i IN 1..t_reviewer_ids.COUNT()  LOOP
       IF i = 1 THEN
          v_first_id := t_reviewer_ids(i).id;

          UPDATE reviewer_tbl
          SET status = 'Status A'
          WHERE id = t_reviewer_ids(i).id;
       ELSE
          UPDATE reviewer_tbl
          SET status = 'Status B'
          WHERE id = t_reviewer_ids(i).id;
       END IF;
    END LOOP;
END;

Using index-by table INSTEAD OF cursor

DEFINE
    --table type
    TYPE reviewer_id_tbl_type IS TABLE OF reviewer_tbl.id%TYPE INDEX BY PLS_INTEGER;
    --actual table
    t_reviewer_ids reviewer_id_tbl_type;

    v_first_id   reviewer_tbl.id%TYPE;
BEGIN
    --Fetch the query into the indexed table
    SELECT id
    BULK COLLECT INTO t_reviewer_ids
    FROM reviewer_tbl
    WHERE course_id = 123;

    --For loop; Notice that .id is not needed anymore
    FOR i IN 1..t_reviewer_ids.COUNT()  LOOP
       IF i = 1 THEN
          v_first_id := t_reviewer_ids(i); 

          UPDATE reviewer_tbl
          SET status = 'Status A'
          WHERE id = t_reviewer_ids(i);
       ELSE
          UPDATE reviewer_tbl
          SET status = 'Status B'
          WHERE id = t_reviewer_ids(i);
       END IF;
    END LOOP;
END;

Solution 2

You can load all the query results into an actual memory array using a "BULK COLLECT INTO" clause and then use regular index-based access to that array.

This is not memory efficient (so be aware that you shouldn't do this if you are processing tons of records), but it works:

See this example where I load in a memory array the first 100 records of the "DICT" system view:

 declare
    type MEMTABLE_TYPE is 
        TABLE OF DICT%ROWTYPE index by binary_integer;

    myarray MEMTABLE_TYPE;
 begin
   select * 
   BULK COLLECT INTO myarray -- this loads the whole query result into the array
   from DICT
   where rownum < 100;

   -- scan all the array: 
   for c in 1..myarray.count loop
      dbms_output.put_line(myarray(c).table_name ||' -> ' || myarray(c).comments);
   end loop;             

   -- access directly the fifth element  
   dbms_output.put_line(myarray(5).table_name ||' -> ' || myarray(5).comments);
 end;   

Anyway you shouldn't abuse this: unless you need to access the data multiple times (so keeping it in memory instead of re-executing the query could speed up things) you should try to use a regular cursor.

Solution 3

You could use a BOOLEAN to tell you if you've processed the "first" row from the cursor, in a manner similar to the following:

DECLARE
  CURSOR c_reviewer_ids IS
    SELECT id FROM reviewer_tbl
      WHERE course_id = 123;
  v_reviewer_id   reviewer_tbl.id%TYPE;
  bFirst_row      BOOLEAN := TRUE;
BEGIN
  FOR l_reviewer_id IN c_reviewer_ids 
  LOOP
    IF bFirst_row THEN
      bFirst_row := FALSE;

      UPDATE reviewer_tbl
        SET status = 'Status A'
        WHERE id = l_reviewer_id.ID;
    ELSE
      --Set the status for all other reviewers.
      UPDATE reviewer_tbl
        SET status = 'Status B'
        WHERE reviewer_id = l_reviewer_id.ID;
    END IF;

    --Save one of the ids; for this particular test, it doesn't matter if it is first or not
    v_reviewer_id := l_reviewer_id.id;
  END LOOP;
END;

Best of luck.

EDIT

If you want to access the fifth row you could do something like:

DECLARE
  v_reviewer_id   reviewer_tbl.id%TYPE;
BEGIN
  FOR l_reviewer_id IN (SELECT id, RN
                          FROM (SELECT ID, ROWNUM AS RN
                                  FROM reviewer_tbl
                                  WHERE course_id = 123)
                          WHERE RN = 5)
  LOOP
      UPDATE reviewer_tbl
        SET status = 'Status A'
        WHERE id = l_reviewer_id.ID;

    --Save one of the ids; for this particular test, it doesn't matter if it is first or not
    v_reviewer_id := l_reviewer_id.id;
  END LOOP;
END;

Best of luck.

Comments

[Dan Bracuk]

Why do you need a curser at all? Would a case construct not do the trick?

[plasmaTonic]

@dan-bracuk, I do not know the Reviewer IDs before the test, so I would not be able to use them as conditions to switch on in the case statement. I only care to switch based on whether one record in the select result has been handled differently. A boolean flag would be enough for my specific case... Perhaps the combination of a boolean and the case would work...

[plasmaTonic]

Thank you - this was able to handle my specific problem. :) I am still curious though if there is a more general solution... for instance, what if I needed to access the 5th row? Is there a way to access the record of index 5?

[William Robertson]

There is no 5th row until five rows have been fetched. The complete result set doesn't exist in some internal store waiting for you to access it. You could add a computed row number to the cursor using the row_number() analytic function, if that helps.

[Bob Jarvis - Слава Україні]

See my edit to the answer. Also note that in the absence of an ORDER BY clause the concept of "fifth row" is arbitrary. Best of luck.