oracle动态函数,oracle pl/sql 中的动态函数

1.execute immediate

create or replace function getrealtypeforreport(p_stanid number,p_id number,p_infoname varchar2) return varchar2

is

v_string                 varchar2(200);

v_returninfo             varchar2(200);

v_acttabname             stanactive.acttabname%type;

v_actpkcol               stanactive.actpkcol%type;

begin

select acttabname,actpkcol into v_acttabname,v_actpkcol from stanactive where stanid=p_stanid;

v_string:='select realtype from vw_'||v_acttabname||' where '||v_actpkcol||' = '||p_id;

execute immediate v_string into v_returninfo;

return v_returninfo;

end;

/

show err;

2. Open ttdscur for v_sql

create or replace function getfieldstring(p_expectfield varchar2,p_expecttable varchar2,p_expectcause varchar2) return varchar2

is

/*

函数功能：获取单个属性将其拼成一行

参数说明：p_expectfield   查询字段

p_expecttable   查询主表

p_expectcause   查询条件

*/

v_sql    varchar2(4000):='';

type ttds_cursor is ref cursor;

ttdscur     ttds_cursor;

ttdigital    varchar2(4000);

v_cnt                   number;

v_return  varchar2(4000):='';

begin

v_sql:='select '||p_expectfield||' from '||p_expecttable||' where '||p_expectcause;

v_cnt:=0;

Open ttdscur for v_sql;

loop

fetch ttdscur into ttdigital;

exit when ttdscur%notfound;

if v_cnt=0 then

v_return:=ttdigital;

else

v_return:=v_return||','||ttdigital;

end if;

v_cnt:=v_cnt+1;

end loop;

Close ttdscur;

return v_return;

end getfieldstring;

/

show err;

举例：

select getfieldstring('certno','rightcert','activeid=@id and certtypeid in (3)') from dual;