sql - 返回显式游标的SQL语句

Question

以 Oracle 论坛中显示的示例:Generating excel(xls) using plsql ，我想从查询的数据集生成一个 Excel 文件。

那里提供的示例有效。然而，我遇到了一些挑战:

1. 我的 SQL 查询相当长，超出了 VARCHAR2 4000 个字符的限制。
2. 我想将参数传递给我的查询。
3. 我希望以一种非常简单的方式完成此操作，并且不使用动态 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;  

现在，我的匿名 block 最好是像下面这样的(请注意评论):

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_COLUMNS 和 DBMS_SQL.DEFINE_COLUMN。否则你可以使用 FETCH Statement而不是DBMS_SQL.FETCH_ROWS(c)