來自于: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個字元等。