将单行结果集转换为关联数组

Question

假设我有以下查询：

select 1 foo, 2 bar, 3 baz from dual;

基本上，该查询总是返回一行。我需要从中创建一个关联数组，其中之一：

arr('foo') = 1;
arr('bar') = 2;
arr('baz') = 3;

我知道结果集只有一行。我既不知道列数量也不知道列名称。

有什么想法吗？

谢谢。

更新：

我的一个朋友发现了一个漂亮而优雅的涉及 XML 的解决方案：

  SELECT
   XMLTYPE(EXTRACT(VALUE(T), '/*') .GETSTRINGVAL()) .GETROOTELEMENT() NODE,
   EXTRACTVALUE(COLUMN_VALUE, '/*') NODEVALUE
    FROM
   TABLE(XMLSEQUENCE(XMLTYPE((CURSOR
   (
      --this is the query that needs to be transformed
      SELECT
       *
        FROM
       some_table
       WHERE some_table.id = 123

   )
   )) .EXTRACT('/ROWSET/ROW/*'))) T;

Answer 1

这是一个简单的包，它从键值对表中填充关联数组。这是最简单的情况。

create or replace package dynaa as
    procedure pop;
    procedure print;
end;
/

create or replace package body dynaa as

    type aa is table of number
        index by varchar2(30);
    this_aa aa;

    procedure pop
    is
    begin
        for r in ( select * from t42 )
        loop
            this_aa (r.id) := r.col1;
        end loop;
    end pop    ;

    procedure print
    is
        idx varchar2(30);
    begin
        idx := this_aa.first();
        while idx is not null
        loop
            dbms_output.put_line(idx ||'='||this_aa(idx));
            idx := this_aa.next(idx);
        end loop;
    end print;
end;
/

而且它工作得非常巧妙......

SQL> set serveroutput on
SQL>
SQL> exec dynaa.pop

PL/SQL procedure successfully completed.

SQL> exec dynaa.print
DAISY HEAD MAISIE=6969
FOX IN SOCKS=4242
MR KNOX=2323

PL/SQL procedure successfully completed.

SQL>

但是您想要的更复杂：在不知道查询投影的情况下动态填充数组。以下是一个极其简单的实现，如果相关表包含单行，则该实现将起作用。它可以轻松扩展以处理具有多行的表。

因此，我们重载了 POP() 过程：

procedure pop
    ( tabname user_tab_columns.table_name%type );

procedure pop
    ( tabname user_tab_columns.table_name%type );
is
    n number;
begin
    for r in ( select column_name 
               from user_tab_columns
               where table_name = tabname)
    loop
        execute immediate 'select '||r.column_name||' from '||tabname into n;
        this_aa (r.column_name) := n;
    end loop;
end pop    ;

因此，这也有效：

SQL> exec dynaa.pop('T23')

PL/SQL procedure successfully completed.

SQL>
SQL> exec dynaa.print
COL1=2323
COL2=4242
COL3=6969

PL/SQL procedure successfully completed.

SQL>

我不太担心以如此糟糕的方式初始化数组对性能的影响。鉴于记录保留在缓存中，这还不算太糟糕。如果您频繁调用 POP() 以至于性能确实成为一个问题，那么您可能不应该首先使用关联数组：常规查找或结果集缓存都将是更好的选择。

Answer 2

一个非常简短的答案是使用 DBMS_SQL 包查看旧式（Oracle 9 之前）动态 SQL。您可以使用 DESCRIBE_COLUMNS 获取列数（和数据类型），并获取 DESCRIBE_COLUMNS 返回的 PL/SQL 表中每个条目的 COLUMN_VALUE code> 单独获取每个列值。

之后，将一行转换为关联数组就非常容易了。