oracle oracle中如何从查询字符串中进行选择
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16056473/
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
How to select from query string in oracle
提问by hsuk
Lets assume, I have a string that holds a query string.
让我们假设，我有一个包含查询字符串的字符串。
How can I select the rows from that query string in oracle ?
如何从 oracle 中的查询字符串中选择行？
I tried execute immediatebut it returns nothing.
我试过了，execute immediate但它什么也没返回。
declare
hello varchar(30000);
begin
hello:='select * from test_table';
execute immediate hello;
end;
回答by Vincent Malgrat
You would use a dynamic cursor.
您将使用动态游标。
Here's an example with SQL*Plus:
这是一个示例SQL*Plus：
SQL> var dyn_cur refcursor
SQL> DECLARE
2 l_sql_query VARCHAR2(1000);
3 BEGIN
4 -- complex function that returns a query:
5 l_sql_query := 'SELECT 1, dummy FROM dual';
6 OPEN :dyn_cur FOR l_sql_query;
7 END;
8 /
PL/SQL procedure successfully completed.
SQL> print dyn_cur
1 DUM
---------- ---
1 X
You can use dynamic cursors in PL/SQL procedures and packages:
您可以在 PL/SQL 过程和包中使用动态游标：
SQL> CREATE OR REPLACE PROCEDURE prc_dyn_cur(p_dyn_cursor OUT SYS_REFCURSOR) IS
2 BEGIN
3 OPEN p_dyn_cursor FOR 'SELECT 1, dummy FROM dual';
4 END;
5 /
Procedure created.
SQL> exec prc_dyn_cur(:dyn_cur);
PL/SQL procedure successfully completed.
SQL> print dyn_cur
1 DUM
---------- ---
1 X
回答by haki
declare
hello varchar(30000);
type tb is table of test_table$rowtype;
mytb tb;
begin
hello:='select * from test_table';
execute immediate hello bulk collect into mytb;
-- now you got all og youe data in the "array" mytb
end;
notice that this solution takes into account that you know what table you are selecting from. plus, i think you should describe what exactly it is you are trying to achieve.
请注意，此解决方案考虑到您知道要从哪个表中选择。另外，我认为你应该描述一下你想要达到的目标。
回答by Pirate X
CREATE OR REPLACE PROCEDURE query_executer (string_query IN VARCHAR)
IS
c1 SYS_REFCURSOR;
v_last_name employees.last_name%TYPE; -- Selecting last_name
BEGIN
OPEN c1 FOR string_query; -- Opening c1 for the select statement
LOOP
FETCH c1 INTO v_last_name;
DBMS_OUTPUT.put_line (v_last_name);
EXIT WHEN (C1%NOTFOUND);
END LOOP;
END;
SET SERVEROUTPUT ON
EXECUTE query_executer('select last_name from employees');
OUTPUT
输出
Procedure created.
Abel
Ande
Atkinso
PL/SQL procedure successfully completed.
