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;

 

 

转载于:https://www.cnblogs.com/BradMiller/archive/2010/05/25/1743965.html