返回显式游标的SQL语句 [英] Return the SQL Statement of an Explicit Cursor
本文介绍了返回显式游标的SQL语句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
以Oracle论坛中显示的示例为例:生成excel (xls)使用plsql ，我想从查询的数据集中生成一个excel文件.
Taking the example shown in Oracle Forums: Generating excel(xls) using plsql, i would like to generate an excel file from a query's data set.
此处提供的示例有效.但是，我遇到了一些挑战:
The example provided there works. however, there are some challenges i'm encountering:
- 我的SQL查询很长，超过了
VARCHAR24000个字符的限制. - 我想将参数传递给查询.
- 我想以一种非常简单的方式做到这一点，并且不使用Dynamic SQL(如果可能).
- My SQL query is quite long and exceeds the
VARCHAR24000 Character limitation. - I would like to pass parameters to my query.
- I would like to do it in a very simple manner and without using Dynamic SQL (if possible).
我的想法如下:
包装定义:
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;
现在，我的匿名屏蔽最好是在下面(请注意注释):
now, my anonymous block would be preferably something like below (please take note of the comments):
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语句的内置函数将解决此问题.也许是基准光标?
Is it possible to pass an explicit cursor definition to a DBMS_SQL package?
I'm thinking a Built-In Function that Returns the SQL Statement of an Explicit Cursor would solve this problem. Perhaps a Ref Cursor?
推荐答案
Yes, you can do that with DBMS_SQL.TO_CURSOR_NUMBER function. You Procedure will look like this:
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);
...
然后您必须这样称呼它:
Then you have to call it like this:
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作为语句，因此在大小方面没有实际限制.
OPEN FOR Statement allows CLOB as statement, so there is no practical limit in terms of size.
由于您在设计时不知道将选择哪个列(至少我认为是这样)，所以无法摆脱DBMS_SQL.DESCRIBE_COLUMNS和DBMS_SQL.DEFINE_COLUMN.否则，您可以使用 FETCH语句代替DBMS_SQL.FETCH_ROWS(c)
Since you don't know at design time which columns will be selected (at least I assume so) there is no way to get rid of DBMS_SQL.DESCRIBE_COLUMNS and DBMS_SQL.DEFINE_COLUMN. Otherwise you can use FETCH Statement instead of DBMS_SQL.FETCH_ROWS(c)
这篇关于返回显式游标的SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
