立即执行，不显示动态选择语句的记录 [英] execute immediate not showing records of Dynamic Select statement
问题描述
我创建了一个匿名块，该块正在动态创建Select语句.当我执行块时，它仅显示已完成的匿名块，但不显示SQL输出.
declare
sql_stmt clob;
pivot_clause clob;
begin
select listagg('''' || TO_CHAR(PERIOD_NAME,'MON-YY') || ''' as "' || TO_CHAR(PERIOD_NAME,'MON-YY') || '"', ',')
within group (order by PERIOD_NAME)
into pivot_clause
from ( select TO_DATE(PERIOD_NAME,'MON-YYYY') PERIOD_NAME
from table1
where request_id=<id>
group by TO_DATE(PERIOD_NAME,'MON-YYYY')
order by TO_DATE(PERIOD_NAME,'MON-YYYY') ASC );
sql_stmt := 'select * from (select PERIOD_NAME, depreciation
from table1) pivot (sum(depreciation) for PERIOD_NAME in (' || pivot_clause || '))';
execute immediate sql_stmt;
end;
由于您事先不知道结构，因此由于动态转到结果集中的未知列数，您可以使用ref游标检索动态查询的结果.
这使用SQL * Plus/SQL Developer/SQLcl绑定变量;
variable rc refcursor;
declare
sql_stmt clob;
pivot_clause clob;
begin
select listagg('''' || TO_CHAR(PERIOD_NAME,'MON-YY') || ''' as "' || TO_CHAR(PERIOD_NAME,'MON-YY') || '"', ',')
within group (order by PERIOD_NAME)
into pivot_clause from (select TO_DATE(PERIOD_NAME,'MON-YYYY') PERIOD_NAME
from table1
where request_id=<id>
GROUP BY TO_DATE(PERIOD_NAME,'MON-YYYY')
order by TO_DATE(PERIOD_NAME,'MON-YYYY') ASC);
sql_stmt := 'select * from (select PERIOD_NAME, depreciation
from table1) pivot (sum(depreciation) for PERIOD_NAME in (' || pivot_clause || '))';
open :rc for sql_stmt;
end;
/
print rc
variable rc refcursor;
将客户端绑定变量的变量和数据类型声明为参考游标.然后，它使用打印结果集:
print rc
不同的客户端/IDE将需要不同的语法.您也可以通过JDBC执行类似的操作.您还可以使用一个返回sys_refcursor的函数.但这取决于您的最终目标是什么.
顺便说一句，此刻所有透视的总计都将为空；您的最终查询需要以
sql_stmt := 'select * from (select to_char(to_date(PERIOD_NAME, ''MON-YYYY''), ''MON-YY'') as PERIOD_NAME, depreciation
from table1) pivot (sum(depreciation) for PERIOD_NAME in (' || pivot_clause || '))';
格式与数据透视子句查找的格式相同，例如
sql_stmt := 'select * from (select to_char(to_date(PERIOD_NAME, ''MON-YYYY''), ''MON-YY'') as PERIOD_NAME, depreciation
from table1) pivot (sum(depreciation) for PERIOD_NAME in (' || pivot_clause || '))';
尽管将原始格式保留在ivot子句中会稍微简单一些:
declare
sql_stmt clob;
pivot_clause clob;
begin
select listagg('''' || PERIOD_NAME || ''' as "' || TO_CHAR(PERIOD_DATE,'MON-YY') || '"', ',')
within group (order by PERIOD_DATE)
into pivot_clause from (select distinct PERIOD_NAME, TO_DATE(PERIOD_NAME,'MON-YYYY') PERIOD_DATE
from table1
where request_id=<id>);
sql_stmt := 'select * from (select PERIOD_NAME, depreciation
from table1) pivot (sum(depreciation) for PERIOD_NAME in (' || pivot_clause || '))';
open :rc for sql_stmt;
end;
/
带有虚拟表和数据:
create table table1 (request_id, period_name, depreciation) as
select 1, 'JAN-2018', 42 from dual
union all select 1, 'FEB-2018', 11 from dual
union all select 1, 'MAR-2018', 22 from dual
union all select 1, 'MAR-2018', 33 from dual
union all select 2, 'MAR-2018', 44 from dual;
运行任一版本并执行print rc都会显示:
JAN-18 FEB-18 MAR-18
---------- ---------- ----------
42 11 99
I created Anonymous block which is creating Select statement dynamically. when I execute block its only showing anonymous block completed but not showing SQL output.
declare
sql_stmt clob;
pivot_clause clob;
begin
select listagg('''' || TO_CHAR(PERIOD_NAME,'MON-YY') || ''' as "' || TO_CHAR(PERIOD_NAME,'MON-YY') || '"', ',')
within group (order by PERIOD_NAME)
into pivot_clause
from ( select TO_DATE(PERIOD_NAME,'MON-YYYY') PERIOD_NAME
from table1
where request_id=<id>
group by TO_DATE(PERIOD_NAME,'MON-YYYY')
order by TO_DATE(PERIOD_NAME,'MON-YYYY') ASC );
sql_stmt := 'select * from (select PERIOD_NAME, depreciation
from table1) pivot (sum(depreciation) for PERIOD_NAME in (' || pivot_clause || '))';
execute immediate sql_stmt;
end;
As you don't know the structure in advance, because of the dynamic pivot to an unknown number of columns in the result set, you could use a ref cursor to retrieve the result of the dynamic query.
This uses SQL*Plus/SQL Developer/SQLcl bind variables;
variable rc refcursor;
declare
sql_stmt clob;
pivot_clause clob;
begin
select listagg('''' || TO_CHAR(PERIOD_NAME,'MON-YY') || ''' as "' || TO_CHAR(PERIOD_NAME,'MON-YY') || '"', ',')
within group (order by PERIOD_NAME)
into pivot_clause from (select TO_DATE(PERIOD_NAME,'MON-YYYY') PERIOD_NAME
from table1
where request_id=<id>
GROUP BY TO_DATE(PERIOD_NAME,'MON-YYYY')
order by TO_DATE(PERIOD_NAME,'MON-YYYY') ASC);
sql_stmt := 'select * from (select PERIOD_NAME, depreciation
from table1) pivot (sum(depreciation) for PERIOD_NAME in (' || pivot_clause || '))';
open :rc for sql_stmt;
end;
/
print rc
variable rc refcursor;
declares the variable and data type of the client bind variable, as a reference cursor. Then rather than using execute immediate it does open for with your dynamic statement:
open :rc for sql_stmt;
which opens the ref cursor with the results of that query. (Notice the : at the start of :rc, indicating that is a bind variable reference not a local PL/SQL variable).
Then outside the block you can print the result set with:
print rc
Different clients/IDEs will need different syntax. You could do something similar over JDBC too. You could also have a function that returns a sys_refcursor. But it depends what your end goal for this is.
Incidentally, at the moment you'll get null for all the pivoted totals; your final query needs to get PERIOD_NAME in the same format the pivot clause is looking for, e.g.
sql_stmt := 'select * from (select to_char(to_date(PERIOD_NAME, ''MON-YYYY''), ''MON-YY'') as PERIOD_NAME, depreciation
from table1) pivot (sum(depreciation) for PERIOD_NAME in (' || pivot_clause || '))';
though it woudl be slightly simpler to leave the original format in the pivot clause instead:
declare
sql_stmt clob;
pivot_clause clob;
begin
select listagg('''' || PERIOD_NAME || ''' as "' || TO_CHAR(PERIOD_DATE,'MON-YY') || '"', ',')
within group (order by PERIOD_DATE)
into pivot_clause from (select distinct PERIOD_NAME, TO_DATE(PERIOD_NAME,'MON-YYYY') PERIOD_DATE
from table1
where request_id=<id>);
sql_stmt := 'select * from (select PERIOD_NAME, depreciation
from table1) pivot (sum(depreciation) for PERIOD_NAME in (' || pivot_clause || '))';
open :rc for sql_stmt;
end;
/
With a dummy table and data:
create table table1 (request_id, period_name, depreciation) as
select 1, 'JAN-2018', 42 from dual
union all select 1, 'FEB-2018', 11 from dual
union all select 1, 'MAR-2018', 22 from dual
union all select 1, 'MAR-2018', 33 from dual
union all select 2, 'MAR-2018', 44 from dual;
running either version and doing print rc shows:
JAN-18 FEB-18 MAR-18
---------- ---------- ----------
42 11 99
这篇关于立即执行，不显示动态选择语句的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
