如何通过ID或索引号引用列 [英] How to Refer to a Column by ID or Index Number

问题描述

在Oracle PL/SQL中，我运行了一个查询，并试图逐一读取每一行的每一列，以便可以将它们与定界符(硬格式要求)连接在一起.该脚本用于大小各异的多个表上，因此事先不知道列数.我用过

In Oracle PL/SQL, I have run a query and am trying to read through each column for each row one by one so I can concatenate them together with a delimiter (hard format requirement). The script is used on multiple tables of varying sizes, so the number of columns is not known in advance. I used

SELECT COUNT(column_name) INTO NumColumns FROM all_tabs_cols
WHERE table_name = Table_Array(i);

其中Table_Array已被定义.这是在for循环的中间，已经成功地使我获得了总计的列数. Table_Cursor是SELECT *语句.在此之后，我尝试做类似的事情

where Table_Array has already been defined. This is in the middle of a for loop and has successfully gotten me a total number of columns. Table_Cursor is a SELECT * statement. After this I am trying to do something like

FOR j IN 0..NumColumns-1 LOOP
    FETCH TABLE_CURSOR.column(j) INTO DataValue;
    DBMS_OUTPUT.PUT(DataValue || '/');
END LOOP

上面是伪代码.它说明了我所追求的概念.我对PL/SQL不太了解，无法知道如何连续获取这样的值.我也担心在执行此操作时意外地移动光标.我该如何完成这项任务?

The above is pseudo code. It illustrates the concept I am after. I do not know PL/SQL well enough to know how to get a value like this out of a row. I am also worried about accidentally advancing the cursor while doing this. How can I accomplish this task?

推荐答案

您必须使用某种形式的动态SQL.这是一个简单的示例:

You must use some form of dynamic SQL. Here is a quick example:

它将构建SQL语句，该语句将从所需的表中选择'/'分隔的列.然后，它使用动态SQL运行该SQL语句.

It builds the SQL statement that will select the '/' separated columns from the table you want. Then it uses dynamic SQL to run that SQL statement.

DECLARE
  p_table_name VARCHAR2(30) := 'DBA_OBJECTS';
  l_sql VARCHAR2(32000);

  TYPE varchar2tab IS TABLE OF VARCHAR2(32000);
  l_array varchar2tab;

BEGIN
  SELECT 'SELECT ' || listagg(column_name,' ||''/''||') within group ( order by column_id ) || ' FROM ' || owner || '.' || table_name || ' WHERE ROWNUM <= 100'
  INTO l_sql
  FROM dba_tab_columns
  where table_Name = 'DBA_OBJECTS'
  group by owner, table_Name;

  EXECUTE IMMEDIATE l_sql BULK COLLECT INTO l_array;

  FOR i in l_array.first .. l_array.last LOOP
    dbms_output.put_line(l_array(i));
  END LOOP;
END;

这篇关于如何通过ID或索引号引用列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！