Posted on 2008-12-10 15:30
詩特林 閱讀(827)
評論(0) 編輯 收藏 所屬分類:
Oracle
CREATE OR REPLACE FUNCTION
sum_string (tablename IN VARCHAR2,fieldname IN VARCHAR2,wherecondistion IN VARCHAR2)
RETURN VARCHAR2 AS
tmpsql VARCHAR2(4000);
tmparray Dbms_Sql.Varchar2_Table;
retval VARCHAR2(4000) DEFAULT '';
BEGIN
tmpsql := 'select '||fieldname||' from '||tablename||' '||wherecondistion;
EXECUTE IMMEDIATE tmpsql BULK COLLECT INTO tmparray ;
IF(tmparray.Count=0) then
RETURN '';
END IF;
retval:=tmparray(1);
FOR i IN 2..tmparray.count LOOP
retval := retval||','||tmparray(i);
END LOOP;
RETURN retval;
END;
/
SELECT fn_constr('Tab','tname','') FROM dual;
SELECT fn_constr('Tab','tname','where tabtype = ''TABLE'' ') FROM dual;