选择多行的单行作为数组的元素

I have a table with 100 columns called like col_1, col_2, .. col_100 is there a way to select values of that columns of single into an array of 100 elements?

我有一个100列的表,名为col_1,col_2,.. col_100有没有办法选择单列的值为100个元素的数组?

(Oracle 10.2)

4 个解决方案

#1

Here's a brute force method. There's probably a more elegant way, or at least one that will cut down on typing. The example uses five columns rather than 100.

这是一种蛮力方法。可能有一种更优雅的方式,或者至少有一种可以减少打字的方式。该示例使用五列而不是100列。

DECLARE
  -- Change VARCHAR2(10) in the next line to your col_1 .. col_100 type
  TYPE My100Array IS TABLE OF VARCHAR2(10) INDEX BY PLS_INTEGER;
  myVals My100Array;
  indx NUMBER;
BEGIN
   SELECT 'These', 'are', 'the', 'column', 'values'
     INTO myVals(1), myVals(2), myVals(3), myVals(4), myVals(5)
     FROM DUAL;
   FOR INDX IN 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE(indx || ': ' || myVals(indx));
   END LOOP;
END;

Here's the output when I run this:

这是我运行时的输出:

1: These
2: are
3: the
4: column
5: values

Of course, this will be a bit tough with 100 columns, but once you get the query out of the way you'll have the array as you want it.

当然,对于100列来说这会有点困难,但是一旦你得到了查询,就可以得到你想要的数组。

#2

you could just select them like:

你可以选择它们像:

SQL> create type foo as table of number; -- or varray, as you wish.
  2  /

Type created.

SQL> select foo(l.a, l.b, l.c) foo from your_tab l;

FOO
-----------------
FOO(1, 2, 3)

etc..

#3

Another example:

DECLARE 
   CURSOR c_data IS
   SELECT * FROM scott.emp; -- replace emp table with your_table

   TYPE t_source_tab IS TABLE OF scott.emp%ROWTYPE;
   l_tab    t_source_tab;
 BEGIN
   SELECT * BULK COLLECT INTO l_tab FROM scott.emp;

   -- display values in array --
   FOR i IN l_tab.FIRST ..l_tab.LAST 
   LOOP
      DBMS_OUTPUT.PUT_LINE (l_tab(i).hiredate ||chr(9)||l_tab(i).empno  ||chr(9)||l_tab(i).ename);
   END LOOP;   
 END;
 /

#4

sounds like you want to unpivot your data..

听起来你想要取消你的数据..

unfortunately UNPIVOT was only added in 11g (not 10.2) you could manually unpivot but one of the other solutions would work better i think.

不幸的是UNPIVOT只是在11g(而不是10.2)中添加,你可以手动取消,但我认为其中一种解决方案会更好用。

However, if you were on 11g or later you could try this

但是,如果您使用11g或更高版本,则可以试试这个

create table my_table (col1 number,col2 number, col3 number);
Table MY_TABLE created.

insert into my_table values (4,5,6);
1 row inserted.

select * from my_table;
      COL1       COL2       COL3
---------- ---------- ----------
         4          5          6

select val from my_table unpivot ( val for col in ( col1,col2,col3));

       VAL
----------
         4
         5
         6

from there is trivial to select into an single column array

从那里可以轻松选择单列数组

DECLARE 
   CURSOR c_data IS
   select val from my_table unpivot ( val for col in ( col1,col2,col3));


   TYPE t_source_tab IS TABLE OF c_data%ROWTYPE;
   l_tab    t_source_tab;
 BEGIN

    open c_data;
    fetch c_data bulk collect into l_tab;
    close c_data;

   -- display values in array --
   FOR i IN l_tab.FIRST ..l_tab.LAST 
   LOOP
      DBMS_OUTPUT.PUT_LINE (l_tab(i).val);
   END LOOP;   
 END;
 /

#1