For each string, execute a function/procedure

I'd like to loop through a list of strings and execute a 开发者_如何学JAVAfunction/procedure with each string as the argument.

What's the best alternative to the following generic code (since it's not legal):

set serveroutput on;  
begin
    FOR r IN ('The', 'Quick', 'brown', 'fox')
    LOOP
        dbms_output.put_line( r );
   END LOOP;
end;

I assume there might be pattern for this.

---

Just for completeness, a pure PL/SQL solution.

SQL> set serveroutput on
SQL>
SQL> declare
  2      my_array sys.dbms_debug_vc2coll
  3          := sys.dbms_debug_vc2coll('The', 'Quick', 'brown', 'fox');
  4  begin
  5     for r in my_array.first..my_array.last
  6      loop
  7          dbms_output.put_line( my_array(r) );
  8     end loop;
  9  end;
 10  /
The
Quick
brown
fox

PL/SQL procedure successfully completed.

SQL>

This uses the preclared sys.dbms_debug_vc2coll datatype, which has quite a generous definition ...

SQL> desc sys.dbms_debug_vc2coll
 sys.dbms_debug_vc2coll TABLE OF VARCHAR2(1000)

SQL>

... so, like Gary says, you may wish to declare your own. Especially if your strings are short and you have lots of them.

---

DECLARE
  -- 1. declare a list type
  TYPE STR_LIST_TYPE IS TABLE OF VARCHAR2(15);

  -- 2. declare the variable of the list
  V_STR_VALUES STR_LIST_TYPE;

  -- 3. optional variable to store single values
  V_STR_VALUE VARCHAR2(15);

BEGIN

  -- 4. initialize the list of values to be iterated in a for-loop
  V_STR_VALUES := STR_LIST_TYPE('String 1','String 2');

  -- 5. iterating over the values
  FOR INDX IN V_STR_VALUES.FIRST..V_STR_VALUES.LAST
  LOOP

    -- 6. accessing the value itself
    V_STR_VALUE := V_STR_VALUES(INDX);

  END LOOP;     
END;

---

I generally use my own collection type, but you can use the built-in sys.dbms_debug_vc2coll

select column_value from table(sys.dbms_debug_vc2coll('The', 'Quick', 'brown', 'fox'));

[I incorrectly had column_name not column_value. Thanks for the correction]

---

The answer here depends on where the strings come from. In a non 'database language' you would probably get the strings into an array somehow, and then loop over the array, as you have illustrated above. The question is, is that list of strings hardcoded, or are you selecting them from a database table?

OMG Ponies solution will work, but it involves a possibly needless select. You may be better using PLSQL table or varrays - as I said, it depends on how you get the strings into your program that you need to process. Here is an example using plsql tables:

declare
  type myarray is table of varchar2(255) index by binary_integer;
  v_array myarray;
begin
  v_array(v_array.count + 1) := 'The';  
  v_array(v_array.count + 1) := 'quick';
  v_array(v_array.count + 1) := 'brown';
  v_array(v_array.count + 1) := 'fox';
  for i in 1..v_array.count loop
    dbms_output.put_line(v_array(i));
  end loop; 
end;
/

---

Use:

SELECT package.your_function(x.col)
  FROM (SELECT 'The' AS col
          FROM DUAL
        UNION ALL
        SELECT 'Quick'
          FROM DUAL
        UNION ALL
        SELECT 'brown'
          FROM DUAL
        UNION ALL
        SELECT 'fox'
          FROM DUAL) x

Oracle 9i+, Using Subquery Factoring (AKA CTE)

---

WITH list AS (
  SELECT 'The' AS col
    FROM DUAL
  UNION ALL
  SELECT 'Quick'
    FROM DUAL
  UNION ALL
  SELECT 'brown'
    FROM DUAL
  UNION ALL
  SELECT 'fox'
   FROM DUAL)
SELECT package.your_function(x.col)
  FROM list x

---

set serveroutput on;  
begin
   dbms_output.put_line('The');
   dbms_output.put_line('Quick');
   dbms_output.put_line('brown');
   dbms_output.put_line('fox');
end;