How to loop through keys values | Oracle |-CodePudding

I am stuck with this approach not getting. How do I need to go with it.

I have table which as column -> KEYS. This as more then 500 keys

Like below the data would be :

KEYS 
12RTY
UIIJ9
GHSJ8
HJSKI

I should not be making use of cursor need to loop one by one values from column : KEYS to my PLSQL logic

This is how I want to convert that data to comma separated format and used in my loop to read one by one value (key1,key2,key3....)

My code :

DECLARE 
      v_name varchar2(10 char); 
BEGIN
     FOR v_counter in 1..(key1,key2,key3..) LOOP
          select NAME into v_name from EMP where KEY=v_counter;
          DBMS_OUTPUT.PUT_LINE(v_name)
     END;
END;

I would have made use of list_agg but only 10 values can be created as comma separated. More then that it throws error like exceeded 4000

CodePudding user response：

You can use a cursor:

DECLARE 
  v_name varchar2(10 char); 
BEGIN
  FOR v_row IN (SELECT key FROM your_key_table)
  LOOP
    select NAME into v_name from EMP where KEY=v_row.key;
    DBMS_OUTPUT.PUT_LINE(v_name);
  END LOOP;
END;
/

But you could combine the two queries into one:

DECLARE 
  v_name varchar2(10 char); 
BEGIN
  FOR v_row IN (SELECT e.name
                FROM   your_key_table ykt
                       INNER JOIN emp e
                       ON (ykt.key = e.key)
                ORDER BY ykt.key)
  LOOP
    DBMS_OUTPUT.PUT_LINE(v_row.name);
  END LOOP;
END;
/

db<>fiddle here

CodePudding user response：

Instead of using a cursor, you can use BULK COLLECT to store all of the keys into a collection, then loop through that collection selecting and printing the data you are looking for.

DECLARE
    TYPE keys_t IS TABLE OF your_schema.key_table.keys%TYPE;

    l_keys   keys_t;
    l_name   your_schema.emp.name%TYPE;
BEGIN
    SELECT keys
      BULK COLLECT INTO l_keys
      FROM your_schema.key_table;

    FOR i IN 1 .. l_keys.COUNT
    LOOP
        SELECT NAME
          INTO l_name
          FROM EMP
         WHERE KEY = l_keys (i);

        DBMS_OUTPUT.PUT_LINE (l_name);
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
         NULL; --No employee exists with that key
    END LOOP;
END;

If you are just trying to tie the keys to EMPs, it would probably make more sense to just do a join.