Вернуть оператор SQL явного курсора

Question

Вернуть оператор SQL явного курсора

Взяв пример, показанный на форумах Oracle: Генерация Excel (XLS) с использованием plsql, я хотел бы создать файл Excel из набора данных запроса.

Приведенный там пример работает. Тем не менее, есть некоторые проблемы, с которыми я сталкиваюсь:

Мой SQL-запрос довольно длинный и превышает VARCHAR2 4000 символов.
Я хотел бы передать параметры в мой запрос.
Я хотел бы сделать это очень просто и без использования динамического SQL (если это возможно).

То, что я придумал, выглядит примерно так:

Определение пакета:

create or replace package tabletoexcel
as

  PROCEDURE run_query(p_fh  IN UTL_FILE.FILE_TYPE
                    , p_cur IN SYS_REFCURSOR);
  PROCEDURE start_workbook (p_fh  IN UTL_FILE.FILE_TYPE);
  PROCEDURE end_workbook (p_fh  IN UTL_FILE.FILE_TYPE);
  PROCEDURE start_worksheet(p_fh        IN UTL_FILE.FILE_TYPE
                         ,  p_sheetname IN VARCHAR2);
  PROCEDURE end_worksheet (p_fh  IN UTL_FILE.FILE_TYPE);
  PROCEDURE set_date_style (p_fh  IN UTL_FILE.FILE_TYPE);

end tabletoexcel;  

create or replace package body tabletoexcel
as

  PROCEDURE run_query(p_fh    UTL_FILE.FILE_TYPE
                    , p_sql IN VARCHAR2) IS
    v_v_val     VARCHAR2(4000);
    v_n_val     NUMBER;
    v_d_val     DATE;
    v_ret       NUMBER;
    c           NUMBER;
    d           NUMBER;
    col_cnt     INTEGER;
    f           BOOLEAN;
    rec_tab     DBMS_SQL.DESC_TAB;
    col_num     NUMBER;
  BEGIN
    c := DBMS_SQL.OPEN_CURSOR;
    -- parse the SQL statement
    DBMS_SQL.PARSE(c, p_sql, DBMS_SQL.NATIVE);
    -- start execution of the SQL statement
    d := DBMS_SQL.EXECUTE(c);
    -- get a description of the returned columns
    DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);

    -- bind variables to columns
    FOR j in 1..col_cnt
    LOOP
      CASE rec_tab(j).col_type
        WHEN 1 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,4000);
        WHEN 2 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_n_val);
        WHEN 12 THEN DBMS_SQL.DEFINE_COLUMN(c,j,v_d_val);
      ELSE
        DBMS_SQL.DEFINE_COLUMN(c,j,v_v_val,4000);
      END CASE;
    END LOOP;

    -- Output the column headers
    UTL_FILE.PUT_LINE(gv_file_type,'<ss:Row>');
    FOR j in 1..col_cnt
    LOOP
      UTL_FILE.PUT_LINE(gv_file_type,'<ss:Cell>');
      UTL_FILE.PUT_LINE(gv_file_type,'<ss:Data ss:Type="String">'||rec_tab(j).col_name||'</ss:Data>');
      UTL_FILE.PUT_LINE(gv_file_type,'</ss:Cell>');
    END LOOP;
    UTL_FILE.PUT_LINE(gv_file_type,'</ss:Row>');

    -- Output the data
    LOOP
      v_ret := DBMS_SQL.FETCH_ROWS(c);
      EXIT WHEN v_ret = 0;
      UTL_FILE.PUT_LINE(gv_file_type,'<ss:Row>');
      FOR j in 1..col_cnt
      LOOP
        CASE rec_tab(j).col_type
          WHEN 1 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
                      UTL_FILE.PUT_LINE(gv_file_type,'<ss:Cell>');
                      UTL_FILE.PUT_LINE(gv_file_type,'<ss:Data ss:Type="String">'||v_v_val||'</ss:Data>');
                      UTL_FILE.PUT_LINE(gv_file_type,'</ss:Cell>');
          WHEN 2 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_n_val);
                      UTL_FILE.PUT_LINE(gv_file_type,'<ss:Cell>');
                      UTL_FILE.PUT_LINE(gv_file_type,'<ss:Data ss:Type="Number">'||to_char(v_n_val)||'</ss:Data>');
                      UTL_FILE.PUT_LINE(gv_file_type,'</ss:Cell>');
          WHEN 12 THEN DBMS_SQL.COLUMN_VALUE(c,j,v_d_val);
                      UTL_FILE.PUT_LINE(gv_file_type,'<ss:Cell ss:StyleID="OracleDate">');
                      UTL_FILE.PUT_LINE(gv_file_type,'<ss:Data ss:Type="DateTime">'||to_char(v_d_val,'YYYY-MM-DD"T"HH24:MI:SS')||'</ss:Data>');
                      UTL_FILE.PUT_LINE(gv_file_type,'</ss:Cell>');
        ELSE
          DBMS_SQL.COLUMN_VALUE(c,j,v_v_val);
          UTL_FILE.PUT_LINE(gv_file_type,'<ss:Cell>');
          UTL_FILE.PUT_LINE(gv_file_type,'<ss:Data ss:Type="String">'||v_v_val||'</ss:Data>');
          UTL_FILE.PUT_LINE(gv_file_type,'</ss:Cell>');
        END CASE;
      END LOOP;
      UTL_FILE.PUT_LINE(gv_file_type,'</ss:Row>');
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR(c);
  END run_query;

  PROCEDURE start_workbook (p_fh    UTL_FILE.FILE_TYPE) IS
  BEGIN
    UTL_FILE.PUT_LINE(gv_file_type,'<?xml version="1.0"?>');
    UTL_FILE.PUT_LINE(gv_file_type,'<ss:Workbook xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">');
  END start_workbook;

  PROCEDURE end_workbook (p_fh    UTL_FILE.FILE_TYPE) IS
  BEGIN
    UTL_FILE.PUT_LINE(gv_file_type,'</ss:Workbook>');
  END end_workbook;
  --
  PROCEDURE start_worksheet(p_fh    UTL_FILE.FILE_TYPE
                        ,   p_sheetname IN VARCHAR2) IS
  BEGIN
    UTL_FILE.PUT_LINE(gv_file_type,'<ss:Worksheet ss:Name="'||p_sheetname||'">');
    UTL_FILE.PUT_LINE(gv_file_type,'<ss:Table>');
  END start_worksheet;

  PROCEDURE end_worksheet (p_fh    UTL_FILE.FILE_TYPE) IS
  BEGIN
    UTL_FILE.PUT_LINE(gv_file_type,'</ss:Table>');
    UTL_FILE.PUT_LINE(gv_file_type,'</ss:Worksheet>');
  END end_worksheet;
  --
  PROCEDURE set_date_style (p_fh    UTL_FILE.FILE_TYPE) IS
  BEGIN
    UTL_FILE.PUT_LINE(gv_file_type,'<ss:Styles>');
    UTL_FILE.PUT_LINE(gv_file_type,'<ss:Style ss:ID="OracleDate">');
    UTL_FILE.PUT_LINE(gv_file_type,'<ss:NumberFormat ss:Format="dd/mm/yyyy\ hh:mm:ss"/>');
    UTL_FILE.PUT_LINE(gv_file_type,'</ss:Style>');
    UTL_FILE.PUT_LINE(gv_file_type,'</ss:Styles>');
  END set_date_style;

end tabletoexcel;

Теперь мой анонимный блок будет выглядеть примерно так (обратите внимание на комментарии):

declare

    l_fh            UTL_FILE.FILE_TYPE;
    l_directory     VARCHAR2(30) := 'EMPLOYEE_FILE_DIR';
    l_filename      VARCHAR2(30) := 'myfile.xls';    
    l_sql_statement VARCHAR2(4000);    

    Cursor emp_cur (p_emp_no     varchar2
                   ,p_payroll_id number
                   ,p_bg_id      number)
    is
    select  *
    from    table_a
    where   employee_number     = nvl(p_emp_no, employee_number)
    and     payroll_id          = nvl(p_payroll_id, payroll_id);
    and     business_group_id   = p_bg_id
    -- This is the Really really long query but i just placed Table_A for Sample Purposes

BEGIN

    l_fh := UTl_file.FOPEN(upper(l_directory),l_filename,'w',32767);
    tabletoexcel.start_workbook (l_fh);
    tabletoexcel.set_date_style (l_fh);
    tabletoexcel.start_worksheet(l_fh, 'EMP');

    tabletoexcel.run_query(emp_cur('1', 2, 3));
    -- I'm sure this won't work, but i would like to pass something simple as this.

    tabletoexcel.end_worksheet (l_fh);
    tabletoexcel.end_workbook (l_fh);
    UTl_file.FCLOSE(l_fh);

END;

Можно ли передать явное определение курсора DBMS_SQL пакет? Я думаю, встроенная функция, которая возвращает оператор SQL явного курсора, решит эту проблему. Возможно Ref Refsor?

2

sql oracle sys-refcursor

Источник

user6859002 22 дек '16 в 11:03

1 ответ

Решение

Другие вопросы по тегам sql oracle sys-refcursor

user3027266 22 дек '16 в 12:15 2016-12-22 12:15 · Accepted Answer · 2016-12-22 12:15

Да, вы можете сделать это с помощью функции DBMS_SQL.TO_CURSOR_NUMBER. Ваша процедура будет выглядеть так:

PROCEDURE run_query(p_cur IN OUT SYS_REFCURSOR) IS
    ...
BEGIN

    c := DBMS_SQL.TO_CURSOR_NUMBER(p_cur);

    -- get a description of the returned columns
    DBMS_SQL.DESCRIBE_COLUMNS(c, col_cnt, rec_tab);
    ...

Тогда вы должны назвать это так:

declare

    l_cur SYS_REFCURSOR;

BEGIN

    OPEN l_cur FOR
       select  *
       from    table_a
       where   employee_number     = nvl(p_emp_no, employee_number)
       and     payroll_id          = nvl(p_payroll_id, payroll_id);
       and     business_group_id   = p_bg_id
       ...;

    tabletoexcel.run_query(l_cur);

Оператор OPEN FOR позволяет CLOB Как утверждение, так что нет никаких практических ограничений с точки зрения размера.

Поскольку во время разработки вы не знаете, какие столбцы будут выбраны (по крайней мере, я так полагаю), от них невозможно избавиться DBMS_SQL.DESCRIBE_COLUMNS а также DBMS_SQL.DEFINE_COLUMN, В противном случае вы можете использовать FETCH Statement вместо DBMS_SQL.FETCH_ROWS(c)