来自于:http://www.oracle.com/technetwork/issue-archive/2012/12-jul/o42asktom-1653097.html
SQL在解析时必须要知道字段的个数和类型,用pivot和decode都已经不能满足这个需求,这时必须上动态SQL了。
这里将Tom的例子改写了下:
create or replace procedure sal_sum_by_job( p_cursor in out sys_refcursor )
as
l_query long := 'select job';
begin
for x in (select distinct job from emp order by 1 )
loop
l_query := l_query ||
replace( q'|, sum(decode(job,'$X$',sal)) $X$|',
'$X$',
dbms_assert.simple_sql_name(x.job) );
end loop;
l_query := l_query || ' from emp group by job order by job';
open p_cursor for l_query;
end;
show errors;
variable x refcursor;
exec sal_sum_by_job(:x);
print :x;
注意:
dbms_assert.simple_sql_name要求job符合SQL命名规范: 不能以数字开头,仅允许_, 数字,字母,不能超过30个字符等。