oracle 按列名动态访问游标
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4970008/
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
Access cursor by column name dynamically
提问by no_ripcord
Can I access a cursor's column dynamically? I mean by name? something like this:
我可以动态访问游标的列吗？我是说名字？像这样：
declare
v_cursor := select * from emp;
begin
FOR reg IN v_cursor LOOP
dbms_output.put_line(**reg['column_name_as_string']**);
end loop;
end;
I know the bold part is notPL/SQL, but I'm looking for something like that and can't find it anywhere.
我知道粗体部分不是PL/SQL，但我正在寻找类似的东西，但在任何地方都找不到。
Thanks!
谢谢！
回答by Vincent Malgrat
You can use the package DBMS_SQLto create and access cursors with dynamic queries.
您可以使用该包DBMS_SQL通过动态查询创建和访问游标。
However it's not really straightforward to access a column by name because the DBMS_SQLpackage uses positioning and in a dynamic query we may not know the order of the columns before the execution.
然而，按名称访问列并不是很简单，因为DBMS_SQL包使用定位，并且在动态查询中，我们可能不知道执行前列的顺序。
Furthermore, in the context of this question, it appears that we may not know which column we want to display at compile time, we will assume that the column we want to display is given as a parameter.
此外，在这个问题的上下文中，似乎我们可能不知道在编译时要显示哪一列，我们假设要显示的列是作为参数给出的。
We can use DBMS_SQL.describe_columnsto analyze the columns of a SELECTquery after it has been parsed to build a dynamic mapping of the columns. We will assume that all columns can be cast into VARCHAR2since we want to display them with DBMS_OUTPUT.
我们可以在解析查询后使用它DBMS_SQL.describe_columns来分析SELECT查询的列，以构建列的动态映射。我们假设所有的列都可以转换成，VARCHAR2因为我们想用DBMS_OUTPUT.
Here's an example:
下面是一个例子：
SQL> CREATE OR REPLACE PROCEDURE display_query_column(p_query VARCHAR2,
2 p_column VARCHAR2) IS
3 l_cursor INTEGER;
4 l_dummy NUMBER;
5 l_description_table dbms_sql.desc_tab3;
6 TYPE column_map_type IS TABLE OF NUMBER INDEX BY VARCHAR2(32767);
7 l_mapping_table column_map_type;
8 l_column_value VARCHAR2(4000);
9 BEGIN
10 l_cursor := dbms_sql.open_cursor;
11 dbms_sql.parse(l_cursor, p_query, dbms_sql.native);
12 -- we build the column mapping
13 dbms_sql.describe_columns3(l_cursor, l_dummy, l_description_table);
14 FOR i IN 1 .. l_description_table.count LOOP
15 l_mapping_table(l_description_table(i).col_name) := i;
16 dbms_sql.define_column(l_cursor, i, l_column_value, 4000);
17 END LOOP;
18 -- main execution loop
19 l_dummy := dbms_sql.execute(l_cursor);
20 LOOP
21 EXIT WHEN dbms_sql.fetch_rows(l_cursor) <= 0;
22 dbms_sql.column_value(l_cursor, l_mapping_table(p_column), l_column_value);
23 dbms_output.put_line(l_column_value);
24 END LOOP;
25 dbms_sql.close_cursor(l_cursor);
26 END;
27 /
Procedure created
We can call this procedure with a query known only at run-time:
我们可以使用仅在运行时已知的查询来调用此过程：
SQL> set serveroutput on
SQL> exec display_query_column('SELECT * FROM scott.emp WHERE rownum < 5', 'ENAME');
SMITH
ALLEN
WARD
JONES
PL/SQL procedure successfully completed
SQL> exec display_query_column('SELECT * FROM scott.emp WHERE rownum < 5', 'EMPNO');
7369
7499
7521
7566
PL/SQL procedure successfully completed
Use caution with dynamic SQL: it has the same privileges as the user and can therefore execute any DML and DDLstatement allowed for this schema.
谨慎使用动态 SQL：它与用户具有相同的权限，因此可以执行此模式允许的任何 DML和 DDL语句。
For instance, the above procedure could be used to create or drop a table:
例如，上述过程可用于创建或删除表：
SQL> exec display_query_column('CREATE TABLE foo(id number)', '');
begin display_query_column('CREATE TABLE foo(id number)', ''); end;
ORA-01003: aucune instruction analysée
ORA-06512: à "SYS.DBMS_SQL", ligne 1998
ORA-06512: à "APPS.DISPLAY_QUERY_COLUMN", ligne 13
ORA-06512: à ligne 1
SQL> desc foo
Name Type Nullable Default Comments
---- ------ -------- ------- --------
ID NUMBER Y
回答by Dave Costa
It's probably easiest to make the query dynamic if you can.
如果可以，使查询动态化可能是最简单的。
DECLARE
v_cursor SYS_REFCURSOR;
dynamic_column_name VARCHAR2(30) := 'DUMMY';
column_value VARCHAR2(32767);
BEGIN
OPEN v_cursor FOR 'SELECT ' || dynamic_column_name || ' FROM dual';
LOOP
FETCH v_cursor INTO column_value;
EXIT WHEN v_cursor%NOTFOUND;
dbms_output.put_line( column_value );
END LOOP;
CLOSE v_cursor;
END;
If you really want to have a hardcoded SELECT *and dynamically select a column from that by name, I think you could do that using DBMS_SQL as Vincent suggests, but it will be somewhat more complex.
如果您真的想要硬编码SELECT *并按名称从其中动态选择一列，我认为您可以像 Vincent 建议的那样使用 DBMS_SQL 来做到这一点，但它会更复杂一些。
回答by tbone
You mean something like:
你的意思是这样的：
declare
cursor sel_cur is
select * from someTable;
begin
for rec in sel_cur
loop
dbms_output.put_line('col1: ' || rec.col1);
end loop;
end;
