Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 PIVOT (Crosstab)  [new]
Alexus12
Member

Откуда:
Сообщений: 2868
Функция а-ля PivotTable в Excel, возвращает текст запроса для перекрестной таблицы

вызов:

select PK_CROSSTAB.pivotsql('select * from scott.emp','deptno','job','count','deptno') from dual

результат:

SELECT deptno,count(DECODE(job,'A',deptno,null)) as A,count(DECODE(job,'B',deptno,null)) as B,count(DECODE(job,'C',deptno,null)) as C FROM ( select * from scott.emp) GROUP BY deptno ORDER BY deptno

DEPTNO   A  B  C
1            5  0  3 
2            0  2  1


Код:


type array_varchar2 is table of varchar2(255);

  Function PivotSQL (
   p_query in varchar2,
   p_rowfields in varchar2,
   p_columnfield in varchar2,
   p_function in varchar2,
   p_functionfield in varchar2
 ) return varchar2 
  as
    l_max_cols number;
    l_query long;
    l_columnnames array_varchar2 :=array_varchar2();
    l_cursor refcursor;
  begin
  --check params 
  IF instr(p_columnfield,',')>0 THEN
      raise_application_error (-20001, 'Can use only 1 columnfield');  
  ELSIF upper(p_function) not in ('SUM','AVG','COUNT','MIN','MAX') THEN
      raise_application_error (-20001, 'Can use only standard aggregate functions'); 
  END IF;
  
    -- figure out the column names we must support
    if (p_columnfield is not null) then
        OPEN l_cursor for 'SELECT DISTINCT ' || p_columnfield || ' FROM (' || p_query || ')';
            LOOP
                l_columnnames.EXTEND;
                FETCH l_cursor into l_columnnames(l_columnnames.COUNT);
                --dbms_output.put_line('l_columnnames:'||l_columnnames(l_columnnames.COUNT));
                EXIT WHEN l_cursor%NOTFOUND;
            END LOOP;
        CLOSE l_cursor;            
    
--      execute immediate 'SELECT DISTINCT ' || p_columnfield || ' FROM (' || p_query || ')'   bulk collect into l_columnnames ;

    else
      raise_application_error (-20001, 'Cannot figure out max cols');
    end if;

    -- Now, construct the query that can answer the question for us...
    l_query := 'SELECT ' || p_rowfields ;

    for i in 1 .. l_columnnames.count-1 loop
      l_query := l_query || ',' || p_function ||  '(DECODE(' || p_columnfield || ',''' || l_columnnames(i) || ''','|| p_functionfield ||',null)) as '|| l_columnnames(i) ;
    end loop;

    l_query := l_query || ' FROM ( ' || p_query || ')';
    
    l_query := l_query || ' GROUP BY ' || p_rowfields || ' ORDER BY ' || p_rowfields;

    
-- and return it
--dbms_output.put_line('l_query:'||l_query);   
    return l_query;

  end;


прошу комментариев по оптимизации
ORA 8.1.7
22 мар 07, 15:02    [3929196]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT (Crosstab)  [new]
VasyakinM
Member

Откуда: Москва
Сообщений: 371
ну-ну FAQ
22 мар 07, 15:18    [3929290]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT (Crosstab)  [new]
Alexus12
Member

Откуда:
Сообщений: 2868
FAQ написан на примере Кайта, мой - подпример примера
22 мар 07, 15:50    [3929497]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT (Crosstab)  [new]
Alexus12
Member

Откуда:
Сообщений: 2868
финал:

--==============================
  Function PivotSQL (
  --вернуть текст запроса для получения crosstab 
   p_query in varchar2,
   p_rowfields in varchar2,
   p_columnfield in varchar2,
   p_function in varchar2,
   p_functionfield in varchar2
 ) return varchar2 
  as
    l_max_cols number;
    l_query long;
    l_columnnames array_varchar2 :=array_varchar2();
    l_cursor refcursor;
    tmp long;
  begin
  --check params 
  IF instr(p_columnfield,',')>0 THEN
      raise_application_error (-20001, 'Can use only 1 columnfield');  
  ELSIF upper(p_function) not in ('SUM','AVG','COUNT','MIN','MAX') THEN
      raise_application_error (-20001, 'Can use only standard aggregate functions'); 
  END IF;
  
    -- figure out the column names we must support
    if (p_columnfield is not null) then
        tmp:='SELECT DISTINCT ' || p_columnfield || ' FROM (' || p_query || ') ORDER BY ' || p_columnfield;
        dbms_output.put_line('columns cursor:'||tmp);
        OPEN l_cursor for tmp;
            LOOP
                l_columnnames.EXTEND;
                FETCH l_cursor into l_columnnames(l_columnnames.COUNT);
                --dbms_output.put_line('l_columnnames:'||l_columnnames(l_columnnames.COUNT));
                EXIT WHEN l_cursor%NOTFOUND;
            END LOOP;
        CLOSE l_cursor;            
    
--      execute immediate 'SELECT DISTINCT ' || p_columnfield || ' FROM (' || p_query || ')'   bulk collect into l_columnnames ;

    else
      raise_application_error (-20001, 'Cannot figure out max cols');
    end if;

    -- Now, construct the query that can answer the question for us...
    l_query :=  'SELECT ' || p_rowfields ;

    for i in 1 .. l_columnnames.count-1 loop
      l_query := l_query || ',' || p_function ||  '(DECODE(' || p_columnfield || ',''' || l_columnnames(i) || ''','|| p_functionfield ||',null)) as '|| l_columnnames(i) ;
    end loop;

    l_query := l_query ||  ' FROM (' || p_query || ')';
    
    l_query := l_query  || ' GROUP BY ' || p_rowfields  || ' ORDER BY ' || p_rowfields;

    
-- and return it
--dbms_output.put_line('l_query:'||l_query);   
    return l_query;

  end;

вызов под владельцем, пакет и таблицы у владельца - все работает
вызов под админом, пакет и таблицы у владельца - все работает

вызов под админом, пакет у админа, таблицы у владельца - НЕ РАБОТАЕТ!

ошибка на строке: "OPEN l_cursor for tmp;"
ORA-00942: table or view does not exist ORA-06512: at "TEST.PK_CROSSTAB", line 133


что не так?
22 мар 07, 17:48    [3930304]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT (Crosstab)  [new]
Elic
Member

Откуда:
Сообщений: 29990
Alexus12
вызов под админом, пакет у админа, таблицы у владельца - НЕ РАБОТАЕТ!
что не так?
RTFM ТОП №4
22 мар 07, 18:01    [3930402]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT (Crosstab)  [new]
Alexus12
Member

Откуда:
Сообщений: 2868
большое спасибо, рулит
PACKAGE PK_CROSSTAB AUTHID CURRENT_USER as

....====

  Function PivotSQL (
  --вернуть текст запроса для получения crosstab 
   p_query in varchar2,
   p_rowfields in varchar2,
   p_columnfield in varchar2,
   p_function in varchar2,
   p_functionfield in varchar2
 ) return varchar2 
  as
    l_max_cols number;
    l_query long;
    l_columnnames array_varchar2 :=array_varchar2();
    l_cursor refcursor;
    tmp long;
  begin
  --check params 
  IF instr(p_columnfield,',')>0 THEN
      raise_application_error (-20001, 'Can use only 1 columnfield');  
  ELSIF upper(p_function) not in ('SUM','AVG','COUNT','MIN','MAX') THEN
      raise_application_error (-20001, 'Can use only standard aggregate functions'); 
  END IF;
  
    -- figure out the column names we must support
    if (p_columnfield is not null) then
        tmp:='SELECT DISTINCT ' || p_columnfield || ' FROM (' || p_query || ') ORDER BY ' || p_columnfield;
        dbms_output.put_line('columns cursor:'||tmp);
        OPEN l_cursor for tmp;
            LOOP
                l_columnnames.EXTEND;
                FETCH l_cursor into l_columnnames(l_columnnames.COUNT);
                --dbms_output.put_line('l_columnnames:'||l_columnnames(l_columnnames.COUNT));
                EXIT WHEN l_cursor%NOTFOUND;
            END LOOP;
        CLOSE l_cursor;            
    
--      execute immediate 'SELECT DISTINCT ' || p_columnfield || ' FROM (' || p_query || ')'   bulk collect into l_columnnames ;

    else
      raise_application_error (-20001, 'Cannot figure out max cols');
    end if;

    -- Now, construct the query that can answer the question for us...
    l_query :=  'SELECT ' || p_rowfields ;

    for i in 1 .. l_columnnames.count-1 loop
      l_query := l_query || ',' || p_function ||  '(DECODE(' || p_columnfield || ',''' || l_columnnames(i) || ''','|| p_functionfield ||',null)) as "'|| l_columnnames(i)||'" ' ; --" для строк с пробелами
    end loop;

    l_query := l_query ||  ' FROM (' || p_query || ')';
    
    l_query := l_query  || ' GROUP BY ' || p_rowfields  || ' ORDER BY ' || p_rowfields;

    
-- and return it
--dbms_output.put_line('l_query:'||l_query);   
    return l_query;
--Поскольку вполне вероятно, что
--в условии запроса есть константы, мы включаем опцию cursor_sharing перед анализом
--запроса, чтобы принудительно использовались связываемые переменные, а затем отключаем ее. 
 /*   execute immediate 'alter session set cursor_sharing=force';
    open p_cursor for l_query;
    execute immediate 'alter session set cursor_sharing=exact';
*/
  end;


22 мар 07, 18:51    [3930606]     Ответить | Цитировать Сообщить модератору
 Re: PIVOT (Crosstab)  [new]
Alexus12
Member

Откуда:
Сообщений: 2868
Обновление кода - добавлена Procedure FormatParam - человеческое форматирование столбцов (+ исправление ошибки при формате дат, если PIVOT по датам)

PACKAGE PK_CROSSTAB as
type refcursor is ref cursor;

Procedure ReturnRefCur (var_SQL in varchar2, p_cursor in out refcursor);--вернуть выборку по SQL

type array is table of varchar2(30);

  Function PivotSQL (
  --вернуть текст запроса для получения crosstab 
   p_query in varchar2,--
   p_rowfields in varchar2,
   p_columnfield in varchar2,
   p_function in varchar2,
   p_functionfield in varchar2
 ) return varchar2;

....
----------
PACKAGE BODY PK_CROSSTAB as

  Function PivotSQL (
  --вернуть текст запроса для получения crosstab 
   p_query in varchar2,--
   p_rowfields in varchar2,
   p_columnfield in varchar2,
   p_function in varchar2,
   p_functionfield in varchar2
 ) return varchar2 
  as
    l_max_cols number;
    l_query long;
    l_columnnames array_varchar2 :=array_varchar2();
    l_cursor refcursor;
    tmp long;
    --dbms_sql types:
    l_theCursor integer default dbms_sql.open_cursor;--get col types
    l_colCnt number default 0;
    l_descTbl dbms_sql.desc_tab;
    col_num number;
    l_columnfieldtype number;
    --decode names    
    o_decode varchar2(50);
    o_col varchar2(50);
    
  begin
  --check params 
  IF instr(p_columnfield,',')>0 THEN
      raise_application_error (-20001, 'Can use only 1 columnfield');  
  ELSIF upper(p_function) not in ('SUM','AVG','COUNT','MIN','MAX') THEN
      raise_application_error (-20001, 'Can use only standard aggregate functions'); 
  END IF;
  
  /* Шаг 2: проанализировать запрос, чтобы можно было получить описание его результатов. */
  dbms_sql.parse(l_theCursor, p_query, dbms_sql.native);
  /* Шаг З: получаем описание результатов запроса. */
  dbms_sql.describe_columns(l_theCursor, l_colCnt, l_descTbl);

/*
* Following loop could simply be for j in 1..col_cnt loop.
* Here we are simply illustrating some of the PL/SQL table
* features.
*/
col_num := l_descTbl.first;
loop
exit when (col_num is null);
--find column field type
if l_descTbl(col_num).col_name=upper(p_columnfield) then
 l_columnfieldtype:=l_descTbl(col_num).col_type;
--dbms_output.put_line('Col#:'||col_num||' Name:'||l_descTbl(col_num).col_name||' Type:'||l_descTbl(col_num).col_type);
end if;

col_num := l_descTbl.next(col_num);
end loop;

--return 'test ok';
  
    -- figure out the column names we must support for horizontal cross
    if (p_columnfield is not null) then
        tmp:='SELECT DISTINCT ' || p_columnfield || ' FROM (' || p_query || ') ORDER BY ' || p_columnfield;
--        dbms_output.put_line('columns cursor:'||tmp);
        OPEN l_cursor for tmp;
            LOOP
                l_columnnames.EXTEND;
                FETCH l_cursor into l_columnnames(l_columnnames.COUNT);
                --dbms_output.put_line('l_columnnames:'||l_columnnames(l_columnnames.COUNT));
                EXIT WHEN l_cursor%NOTFOUND;
            END LOOP;
        CLOSE l_cursor;            
    
--      execute immediate 'SELECT DISTINCT ' || p_columnfield || ' FROM (' || p_query || ')'   bulk collect into l_columnnames ;

    else
      raise_application_error (-20001, 'Cannot figure out max cols');
    end if;

    -- Now, construct the query that can answer the question for us...
    l_query :=  'SELECT ' || p_rowfields ;

    for i in 1 .. l_columnnames.count-1 loop
    FormatParam(l_columnnames(i),l_columnfieldtype, o_decode, o_col);--format params
      l_query := l_query || ',' || p_function ||  '(DECODE(' || p_columnfield || ',' || o_decode || ','|| p_functionfield ||',null)) as "'|| o_col ||'" ' ; --" для строк с пробелами
    end loop;

    l_query := l_query ||  ' FROM (' || p_query || ')';
    
    l_query := l_query  || ' GROUP BY ' || p_rowfields  || ' ORDER BY ' || p_rowfields;

/* Step 9: закрываем курсор, чтобы освободить ресурсы. */
 dbms_sql.close_cursor(l_theCursor);    
    
-- and return it
--dbms_output.put_line('l_query:'||l_query);   
    return l_query;
--Поскольку вполне вероятно, что
--в условии запроса есть константы, мы включаем опцию cursor_sharing перед анализом
--запроса, чтобы принудительно использовались связываемые переменные, а затем отключаем ее. 
 /*   execute immediate 'alter session set cursor_sharing=force';
    open p_cursor for l_query;
    execute immediate 'alter session set cursor_sharing=exact';
*/

  EXCEPTION
      WHEN OTHERS THEN
        /* Step 9: закрываем курсор, чтобы освободить ресурсы. */
          dbms_sql.close_cursor(l_theCursor);  
          raise_application_error (-20001,'Ошибка в PivotSQL:' || SQLERRM);
  end;

--=========================
  
Procedure FormatParam (var_data in varchar2, var_type in number, out_decode in out varchar2, out_col in out varchar2)
--форматировать параметр в соотв с типом для PivotSQL
--принять текст параметра и его тип 
-- выдать строки для decode и имени колонки
/* типы dbms_sql.describe_columns :
   DATE     Type:12
   Varchar2 Type:1
   Number   Type:2
*/
IS   

 BEGIN

IF var_data is null THEN--если в колонку выпал null
out_decode:='NULL';
out_col:='==NULL==';
--данный case не перепутается с текстовым значением 'NULL' столбца varchar - будет две разных колонки

ELSIF var_type = 1 THEN -- Varchar2
out_decode:=''''||var_data||'''';--add quotes
out_col:=substr(var_data,1,30);

ELSIF var_type = 2 THEN --Number 
out_decode:=var_data;--do nothing
out_col:=substr(var_data,1,30);

ELSIF var_type = 12 THEN --DATE
out_decode:='to_date('''||var_data||''')';--format as internal date
out_col:=to_char(to_date(var_data),'YYYY-MM-DD');

ELSE
out_decode:='== UNDEFINED TYPE:'||var_type;
out_col:='== UNDEFINED TYPE';
END IF;
            
    
  EXCEPTION
      WHEN OTHERS THEN
           raise_application_error (-20001,'Ошибка в FormatParam:' || SQLERRM);
   
   END;  
  
Procedure ReturnRefCur (var_SQL in varchar2, p_cursor in out refcursor)
--вернуть выборку по SQL
IS
 BEGIN

open p_cursor for var_SQL;            
  
  EXCEPTION
      WHEN OTHERS THEN
          raise_application_error (-20001,'Ошибка в ReturnRefCur:' || SQLERRM);
   
END;  

вопрос:
в блоке

col_num := l_descTbl.first;
loop
exit when (col_num is null);
--find column field type
if l_descTbl(col_num).col_name=upper(p_columnfield) then
 l_columnfieldtype:=l_descTbl(col_num).col_type;
--dbms_output.put_line('Col#:'||col_num||' Name:'||l_descTbl(col_num).col_name||' Type:'||l_descTbl(col_num).col_type);
end if;
можно ли получить единственную нужную запись без перебора?
11 май 07, 14:01    [4126033]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить