動態呼叫儲存過程 sample:

-- requirement:

系統中有大量結構相同的過程，想透過動態呼叫簡化應用程式碼。

-- 例子說明：

-- procedure : p_emp , get a input parameter with default value , and result a cursor.

create or replace procedure p_emp( p_name in varchar2 default null, p_cur OUT Sys_Refcursor ) is
begin

OPEN P_CUR FOR SELECT ENAME FROM EMP WHERE eNAME LIKE '%'||P_NAME||'%' ;

end p_EMP;

-- demo code to calling a dynamic procedure.

set serverout on
declare
p_sql varchar2(4000);
p_cur1 sys_refcursor ;
p_proc varchar2(30);
p_retcur varchar2(30);
p_name varchar2(50) ;
begin
-- p_emp(p_cur => p_cur1) ;

p_proc := 'p_emp' ;
p_retcur := 'p_cur1' ;

p_sql := 'begin '||p_proc||'(p_cur => :cur1) ; end; ' ;
dbms_output.put_line ('procedure :'||p_sql) ;
-- This is the point . bind a procedure out variable .
execute immediate p_sql using p_cur1 ;

dbms_output.put_line ('emp name list :' ) ;

loop
fetch p_cur1 into p_name ;
exit when p_cur1%notfound ;
dbms_output.put_line ('name : '||p_name) ;

end loop;
dbms_output.put_line ('The end . ') ;
close p_cur1 ;
end;
/

[@more@]