Oracle：如何从动态查询中获取数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16845795/
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
Oracle : how to fetch data from dynamic query?
提问by Frank
I have a program to generate dynamic query string based on input. This query may select from any tables or joined tables in my DB, and the column names and number of columns are unknown.
我有一个程序可以根据输入生成动态查询字符串。此查询可以从我的数据库中的任何表或联接表中进行选择，并且列名和列数未知。
Now with this query string as the only input, I want to fetch all data from the result and output them line by line, is there any way to do this ?
现在将此查询字符串作为唯一输入，我想从结果中获取所有数据并逐行输出它们，有什么办法可以做到这一点？
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Thank Thinkjet for the reference. I have solved the problem, to help the others, here is the piece of code I used:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 感谢Thinkjet的参考。我已经解决了这个问题，为了帮助其他人，这是我使用的一段代码：
DECLARE
v_curid NUMBER;
v_desctab DBMS_SQL.DESC_TAB;
v_colcnt NUMBER;
v_name_var VARCHAR2(10000);
v_num_var NUMBER;
v_date_var DATE;
v_row_num NUMBER;
p_sql_stmt VARCHAR2(1000);
BEGIN
v_curid := DBMS_SQL.OPEN_CURSOR;
p_sql_stmt :='SELECT * FROM emp';
DBMS_SQL.PARSE(v_curid, p_sql_stmt, DBMS_SQL.NATIVE);
DBMS_SQL.DESCRIBE_COLUMNS(v_curid, v_colcnt, v_desctab);
-- Define columns:
FOR i IN 1 .. v_colcnt LOOP
IF v_desctab(i).col_type = 2 THEN
DBMS_SQL.DEFINE_COLUMN(v_curid, i, v_num_var);
ELSIF v_desctab(i).col_type = 12 THEN
DBMS_SQL.DEFINE_COLUMN(v_curid, i, v_date_var);
ELSE
DBMS_SQL.DEFINE_COLUMN(v_curid, i, v_name_var, 50);
END IF;
END LOOP;
v_row_num := dbms_sql.execute(v_curid);
-- Fetch rows with DBMS_SQL package:
WHILE DBMS_SQL.FETCH_ROWS(v_curid) > 0 LOOP
FOR i IN 1 .. v_colcnt LOOP
IF (v_desctab(i).col_type = 1) THEN
DBMS_SQL.COLUMN_VALUE(v_curid, i, v_name_var);
ELSIF (v_desctab(i).col_type = 2) THEN
DBMS_SQL.COLUMN_VALUE(v_curid, i, v_num_var);
ELSIF (v_desctab(i).col_type = 12) THEN
DBMS_SQL.COLUMN_VALUE(v_curid, i, v_date_var);
END IF;
END LOOP;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(v_curid);
END;
/
采纳答案by ThinkJet
回答by Robert Co
If you are building your string within PL/SQL, you can run it with EXECUTE IMMEDIATE.<- link. Use the BULK COLLECT INTOand output the collection.
如果您在 PL/SQL 中构建您的字符串，您可以使用EXECUTE IMMEDIATE.<- 链接运行它。使用BULK COLLECT INTO并输出集合。
回答by alpertandogan
<PRE>
DECLARE
RUN_S CLOB;
IGNORE NUMBER;
SOURCE_CURSOR NUMBER;
PWFIELD_COUNT NUMBER DEFAULT 0;
L_DESCTBL DBMS_SQL.DESC_TAB2;
Z_NUMBER NUMBER;
BEGIN
RUN_S := ' SELECT 1 AS VAL1,
2 AS VAL2,
CURSOR (SELECT 11 AS VAL11,
12 AS VAL12
FROM DUAL) AS CUR1,
CURSOR (SELECT 11 AS VAL11,
12 AS VAL12
FROM DUAL) AS CUR2
FROM DUAL';
SOURCE_CURSOR := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(SOURCE_CURSOR, RUN_S, DBMS_SQL.NATIVE);
DBMS_SQL.DESCRIBE_COLUMNS2(SOURCE_CURSOR, PWFIELD_COUNT, L_DESCTBL); -- get record structure
FOR I IN 1 .. PWFIELD_COUNT LOOP
DBMS_OUTPUT.PUT_LINE('Col ' || I || ' Type:' || L_DESCTBL(I).COL_TYPE);
IF L_DESCTBL(I).COL_TYPE = 2 THEN
DBMS_SQL.DEFINE_COLUMN(SOURCE_CURSOR, I, Z_NUMBER);
END IF;
NULL;
END LOOP;
IGNORE := DBMS_SQL.EXECUTE(SOURCE_CURSOR);
LOOP
IF DBMS_SQL.FETCH_ROWS(SOURCE_CURSOR) > 0 THEN
FOR I IN 1 .. PWFIELD_COUNT LOOP
IF L_DESCTBL(I).COL_TYPE IN (2) THEN
DBMS_SQL.COLUMN_VALUE(SOURCE_CURSOR, I, Z_NUMBER);
DBMS_OUTPUT.PUT_LINE('Col ' || I || ' Value:' || Z_NUMBER);
END IF;
END LOOP;
ELSE
EXIT;
END IF;
END LOOP;
END;
</PRE>