SQL 对于每个字符串，执行一个函数/过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2242024/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
For each string, execute a function/procedure
提问by OwlCity
I'd like to loop through a list of strings and execute a function/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.
我认为这可能有模式。
回答by APC
Just for completeness, a pure PL/SQL solution.
只是为了完整性，一个纯 PL/SQL 解决方案。
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_vc2colldatatype, which has quite a generous definition ...
这使用了预先声明的sys.dbms_debug_vc2coll数据类型，它具有相当丰富的定义......
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.
...所以，就像加里说的，你可能希望声明你自己的。特别是如果您的字符串很短并且您有很多字符串。
回答by Duc Tran
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;
回答by Gary Myers
I generally use my own collection type, but you can use the built-in sys.dbms_debug_vc2coll
我一般使用我自己的集合类型，但是你可以使用内置的 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]
[我错误地使用了 column_name 而不是 column_value。谢谢指正】
回答by Stephen ODonnell
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:
OMG Ponies 解决方案将起作用，但它涉及可能不必要的选择。您可能会更好地使用 PLSQL 表或 varrays - 正如我所说，这取决于您如何将字符串放入您需要处理的程序中。下面是一个使用 plsql 表的例子：
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;
/
回答by OMG Ponies
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)
Oracle 9i+，使用子查询因式分解 (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
回答by Jeffrey Kemp
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;
