select с произвольным количеством столбцов

hover82
Дата: 24.11.2009 18:55:42
Существует ли возможность сформировать select чтобы результат был с переменным количеством столбцов. Например:

select t.* from emp t;

Здесь количество столбцов явно не указано и зависит только от их количества в таблице emp.
Но мне нужно сформировать запрос чтобы количество было равно некой переменой XX, и при каждом выполнении запроса могло изменятся.

Примеры с созданием таблицы на XX столбцов не предлагать ;)
orawish
Дата: 24.11.2009 19:00:36
hover82,

для начала определитесь - о чем вы..
т.е. кто вам должен тоё сформировать
DENIS_PR
Дата: 24.11.2009 19:40:05
hover82
Существует ли возможность сформировать select чтобы результат был с переменным количеством столбцов. Например:

select t.* from emp t;

Здесь количество столбцов явно не указано и зависит только от их количества в таблице emp.
Но мне нужно сформировать запрос чтобы количество было равно некой переменой XX, и при каждом выполнении запроса могло изменятся.

Примеры с созданием таблицы на XX столбцов не предлагать ;)

Ток динамическим запросом, насколько я понимаю и то, только при том условии, что названия столбцов вы будете знать ))
mmar
Дата: 24.11.2009 19:56:26
DENIS_PR
hover82
Существует ли возможность сформировать select чтобы результат был с переменным количеством столбцов. Например:

select t.* from emp t;

Здесь количество столбцов явно не указано и зависит только от их количества в таблице emp.
Но мне нужно сформировать запрос чтобы количество было равно некой переменой XX, и при каждом выполнении запроса могло изменятся.

Примеры с созданием таблицы на XX столбцов не предлагать ;)

Ток динамическим запросом, насколько я понимаю и то, только при том условии, что названия столбцов вы будете знать ))

зачем знать?
получаем список столбцов таблицы и обрезаем лишние до значения переменной XX:) ну и дальше динамический sql
DENIS_PR
Дата: 24.11.2009 20:25:23
mmar

зачем знать?
получаем список столбцов таблицы и обрезаем лишние до значения переменной XX:) ну и дальше динамический sql

"Я его слепила из того, что было..."
Согласен.
andrey_anonymous
Дата: 24.11.2009 20:30:51
SQL> set linesize 1000
SQL> var x number;
SQL> var s varchar2(1000);
SQL> var rc refcursor;
SQL> prompt 4 столбца
4 столбца
SQL> exec :x := 4;

PL/SQL procedure successfully completed.

SQL> exec :s:='select dummy xx';for i in 1..(:x-1) loop :s:=:s||',dummy xx'||i; end loop;:s:=:s||' from dual'; open :rc for :s;

PL/SQL procedure successfully completed.

SQL> print rc;

X X X X
- - - -
X X X X

SQL> 
SY
Дата: 24.11.2009 21:22:42
Another possibilty is ODCI interface:

CREATE OR REPLACE
  TYPE  natural_vector_type
    AUTHID CURRENT_USER
    AS OBJECT(
              fetch_more varchar2(1),
              tmt        SYS.ANYTYPE,
              num_cols   INTEGER,
              STATIC FUNCTION ODCITableStart(
                                             sctx     IN OUT natural_vector_type,
                                             num_cols IN     INTEGER
                                            ) RETURN PLS_INTEGER,
              STATIC FUNCTION ODCITablePrepare(
                                               sctx     OUT natural_vector_type,
                                               tf_info  IN  SYS.ODCITabFuncInfo,
                                               num_cols IN  INTEGER
                                              ) RETURN PLS_INTEGER,
              MEMBER FUNCTION ODCITableFetch(
                                             self   IN OUT natural_vector_type,
                                             nrows  IN     NUMBER,
                                             objSet OUT    SYS.ANYDATASET
                                            ) RETURN PLS_INTEGER,
              MEMBER FUNCTION ODCITableClose(
                                             self IN natural_vector_type
                                            ) RETURN PLS_INTEGER,
              STATIC FUNCTION ODCITableDescribe(
                                                rtype    OUT SYS.ANYTYPE,
                                                num_cols IN  INTEGER
                                               ) RETURN PLS_INTEGER
             )
/
CREATE OR REPLACE
  TYPE BODY natural_vector_type
    IS
      STATIC FUNCTION ODCITableStart(
                                     sctx     IN OUT natural_vector_type,
                                     num_cols IN     INTEGER
                                    ) RETURN PLS_INTEGER
        IS
        BEGIN
            RETURN ODCICONST.SUCCESS;
      END;
      STATIC FUNCTION ODCITablePrepare(
                                       sctx     OUT natural_vector_type,
                                       tf_info  IN  SYS.ODCITabFuncInfo,
                                       num_cols IN  INTEGER
                                      ) RETURN PLS_INTEGER
        IS    
            rtn number ;
            prec     PLS_INTEGER;
            scale    PLS_INTEGER;
            len      PLS_INTEGER;
            csid     PLS_INTEGER;
            csfrm    PLS_INTEGER;
            elem_typ SYS.ANYTYPE;    
            aname    VARCHAR2(30) ;
        BEGIN
            rtn:=tf_info.RetType.GetAttreleminfo(
                                                 1,
                                                 prec,
                                                 scale,
                                                 len,
                                                 csid,
                                                 csfrm,
                                                 elem_typ,
                                                 aname
                                                );
            sctx:= natural_vector_type('Y',elem_typ,num_cols);
            RETURN ODCICONST.SUCCESS;    
      END;
      MEMBER FUNCTION ODCITableFetch(
                                     self   IN OUT natural_vector_type,
                                     nrows  IN     NUMBER,
                                     objSet OUT    SYS.ANYDATASET
                                   ) RETURN PLS_INTEGER
        IS
            elem_typ SYS.ANYTYPE;
        BEGIN
            SYS.ANYDATASET.BeginCreate(
                                       SYS.DBMS_TYPES.TYPECODE_OBJECT,
                                       tmt,
                                       objSet
                                      );
            IF fetch_more = 'Y'
              THEN
                fetch_more := 'N';
                objSet.Addinstance;          
                objSet.PieceWise;
                FOR i IN 1..self.num_cols LOOP  
                  objSet.SetNumber(
                                   i,
                                   CASE
                                     WHEN i = self.num_cols
                                       THEN
                                         TRUE
                                       ELSE
                                         FALSE
                                   END
                                  );              
                END LOOP;           
                objSet.Endcreate;            
              ELSE
                objSet:=null;
            END IF;
            RETURN ODCICONST.SUCCESS;
      END;
      MEMBER FUNCTION ODCITableClose(
                                     self IN natural_vector_type
                                    ) RETURN PLS_INTEGER
        IS
        BEGIN
            RETURN ODCICONST.SUCCESS;
      END;
      STATIC FUNCTION ODCITableDescribe(
                                        rtype    OUT SYS.ANYTYPE,
                                        num_cols IN  INTEGER
                                       ) RETURN PLS_INTEGER
        IS
            mt SYS.ANYTYPE;
            tmt SYS.ANYTYPE;
        BEGIN
            SYS.ANYTYPE.BeginCreate(
                                    SYS.DBMS_TYPES.TYPECODE_OBJECT,
                                    mt
                                   );    
            FOR i IN 1..num_cols LOOP
              mt.AddAttr(
                         'C' || i,
                         SYS.DBMS_TYPES.TYPECODE_NUMBER,
                         NULL,
                         NULL,
                         NULL,
                         NULL,
                         NULL
                        );
            END LOOP;  
            mt.EndCreate;
            SYS.ANYTYPE.BeginCreate(
                                    SYS.DBMS_TYPES.TYPECODE_NAMEDCOLLECTION,
                                    tmt
                                   );    
            tmt.SetInfo(
                        NULL,
                        NULL,
                        NULL,
                        NULL,
                        NULL,
                        mt,
                        DBMS_TYPES.TYPECODE_OBJECT,
                        0
                       );
            tmt.EndCreate;
            rtype:=tmt;
            RETURN ODCICONST.SUCCESS;
      END;
END;
/
 CREATE OR REPLACE
  FUNCTION natural_vector(num_cols INTEGER) RETURN ANYDATASET
    PIPELINED
      USING natural_vector_type;
/

Now:

SQL> select * from TABLE(natural_vector(5));

        C1         C2         C3         C4         C5
---------- ---------- ---------- ---------- ----------
         1          2          3          4          5

SQL> select * from TABLE(natural_vector(5))
  2  /

        C1         C2         C3         C4         C5
---------- ---------- ---------- ---------- ----------
         1          2          3          4          5

SQL> select * from TABLE(natural_vector(1))
  2  /

        C1
----------
         1

SQL> select * from TABLE(natural_vector(12))
  2  /

        C1         C2         C3         C4         C5         C6         C7
---------- ---------- ---------- ---------- ---------- ---------- ----------
        C8         C9        C10        C11        C12
---------- ---------- ---------- ---------- ----------
         1          2          3          4          5          6          7
         8          9         10         11         12


SQL> 

However, keep in mind ODCI interface, is lenked to parser and number of columns returned is determined by ODCITableDescribe which is called at parse time and therefore does not know variable values (so it treats variable values as NULL). So you can not do something like:

SQL> variable x number
SQL> exec :x := 4;

PL/SQL procedure successfully completed.

SQL> select * from TABLE(natural_vector(:x))
  2  /
select * from TABLE(natural_vector(:x))
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SCOTT.NATURAL_VECTOR_TYPE", line 94
ORA-06512: at line 4


SQL> 

SY.
orawish
Дата: 25.11.2009 11:11:24
11g+
with t as (select 0 as nu from dual)
select a.* from t pivot(sum(null) for nu
in 
(1 as a
,2 as b
,3 as c
,4 as d
,5 as e
,6 as f
-- ..
)) a;
генерить нужное количество элементов в in - лист(причём, нужного вида), оч.просто
hover82
Дата: 25.11.2009 11:14:22
Спасибо SY !
Имено то что мне надо !!!
hover82
Дата: 25.11.2009 19:02:00
а может еще поскажете как сохранить возвращаемый функцией natural_vector(x) обьект anydataset в таблицу T1

create table T1
(
ID NUMBER not null,
VAL ANYDATASET
)

?