返回显式游标的SQL语句

Question

以Oracle论坛中显示的示例：使用plsql生成excel（xls），我想从查询的数据集中生成一个excel文件。

此处提供的示例有效。但是，我遇到了一些挑战：

1. 我的SQL查询很长，超过了VARCHAR24000个字符的限制。
2. 我想将参数传递给我的查询。
3. 我想以一种非常简单的方式做到这一点，并且不使用Dynamic 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语句的内置函数将解决此问题。也许是基准光标？

Answer 1

是的，您可以使用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_COLUMNSand
DBMS_SQL.DEFINE_COLUMN。否则，您可以使用FETCH语句代替DBMS_SQL.FETCH_ROWS(c)