how to write loop for a record type in oracle

Question

I have the following statement which compiles fine in my package:

package header:

   TYPE role_user_type IS RECORD (
      ROLE_ID        some_table.ROLE_ID%TYPE,
      SUBGROUP       some_table.USER_ID%TYPE
   );

body:

ROLE_USER_REC                  MY_PACKAGE.ROLE_USER_TYPE;

SELECT B.USER_ID, B.ROLE INTO ROLE_USER_REC
FROM some_table where user_id like 'M%'

what is the skeleton for looping through ROLE_USER_REC? can we even loop through it?

Answer 1

There is nothing to loop.

role_user_type defines a single record, that you can access via:

dbms_output.put_line( role_user_rec.role_id || ', ' || role_user_rec.subgroup );

Your SELECT ... INTO will fail as soon as more than one row is returned.

---

If you need to store several of those records, you can use nested tables like
TYPE role_user_tab IS TABLE OF role_user_type:

Example:

DECLARE
  TYPE role_user_type IS RECORD (
     ROLE_ID        VARCHAR2(10),
     SUBGROUP       VARCHAR2(10)
  );
  TYPE role_user_tab IS TABLE OF role_user_type;
  role_user_rec role_user_tab;
BEGIN
  SELECT 'A', 'B'
  BULK COLLECT INTO role_user_rec
  FROM dual;
  FOR i IN role_user_rec.FIRST .. role_user_rec.LAST LOOP
    dbms_output.put_line( role_user_rec(i).role_id || ', ' || role_user_rec(i).subgroup );
  END LOOP;
END;

Answer 2

You can use cursors for this

FOR i in (/* Your Select query*/)
loop
/* You can use value of the column fetched from select query like i.column_name
and use it which ever way you want */
end loop;