oracle中替代函数,替代Oracle函数wmsys_wm_concat()函数

create or replace function postgresql(p_tname varchar2, p_colname varchar2)

return clob is

v_tmp    varchar2(200);

v_result clob;

v_cum    sys_refcursor;

begin

open v_cum for 'select ' || p_colname || ' from ' || p_tname;

loop

fetch v_cum

into v_tmp;

exit when v_cum%notfound;

v_result := v_result || v_tmp;

end loop;

dbms_output.put_line(v_result);

return v_result;

end;

select postgresql('emp','ename') from dual

2.sys_connect_by_path

3.create or replace TYPE zh_concat_im

AUTHID CURRENT_USER AS OBJECT

(

CURR_STR VARCHAR2(32767),

STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh_concat_im) RETURN NUMBER,

MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT zh_concat_im,

P1 IN VARCHAR2) RETURN NUMBER,

MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN zh_concat_im,

RETURNVALUE OUT VARCHAR2,

FLAGS IN NUMBER)

RETURN NUMBER,

MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT zh_concat_im,

SCTX2 IN   zh_concat_im) RETURN NUMBER

);

/

create or replace TYPE BODY zh_concat_im

IS

STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT zh_concat_im)

RETURN NUMBER

IS

BEGIN

SCTX := zh_concat_im(NULL) ;

RETURN ODCICONST.SUCCESS;

END;

MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT zh_concat_im,

P1 IN VARCHAR2)

RETURN NUMBER

IS

BEGIN

IF(CURR_STR IS NOT NULL) THEN

CURR_STR := CURR_STR || ':' || P1;

ELSE

CURR_STR := P1;

END IF;

RETURN ODCICONST.SUCCESS;

END;

MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN zh_concat_im,

RETURNVALUE OUT VARCHAR2,

FLAGS IN NUMBER)

RETURN NUMBER

IS

BEGIN

RETURNVALUE := CURR_STR ;

RETURN ODCICONST.SUCCESS;

END;

MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT zh_concat_im,

SCTX2 IN zh_concat_im)

RETURN NUMBER

IS

BEGIN

IF(SCTX2.CURR_STR IS NOT NULL) THEN

SELF.CURR_STR := SELF.CURR_STR || ':' || SCTX2.CURR_STR ;

END IF;

RETURN ODCICONST.SUCCESS;

END;

END;

/

函数：

create or replace FUNCTION zh_concat(P1 VARCHAR2)

RETURN VARCHAR2 AGGREGATE USING zh_concat_im ;

修改标红部门，可调整字段间分隔符

本文出自：亿恩科技【www.enkj.com】