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','active) from dual;