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;
|