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
/*
函數(shù)功能：獲取單個(gè)屬性將其拼成一行
參數(shù)說明：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;

?

?

oracle pl/sql 中的動(dòng)態(tài)函數(shù)