oracle function及应用

CREATE OR REPLACE FUNCTION "FIND_IN_SET" (piv_str1 varchar2, piv_str2 varchar2, p_sep varchar2 := ',')

RETURN NUMBER IS

l_idx number:=0;

str varchar2(500);

piv_str varchar2(500) := piv_str2;

res number:=0;

BEGIN

IF instr(piv_str, p_sep, 1) = 0 THEN

IF piv_str = piv_str1 THEN

res:= 1;

END IF;

ELSE

LOOP

l_idx := instr(piv_str,p_sep);

IF l_idx > 0 THEN

str:= substr(piv_str,1,l_idx-1);

IF str = piv_str1 THEN

res:= 1;

EXIT;

END IF;

piv_str := substr(piv_str,l_idx+length(p_sep));

ELSE

IF piv_str = piv_str1 THEN

res:= 1;

END IF;

EXIT;

END IF;

END LOOP;

END IF;

RETURN res;

END FIND_IN_SET;

CREATE OR REPLACE FUNCTION "GETAPPEARCHILDLIST" (rootcode IN VARCHAR)

RETURN VARCHAR

AS

RESULT_CODES VARCHAR2(2000);

BEGIN

select wmsys.wm_concat(CODE) INTO RESULT_CODES

FROM (select CODE from PRE_APPEAR_STRATEGY start with code=rootcode connect by prior code=PARENT_CODE);

RETURN RESULT_CODES;

END;

END GETAPPEARCHILDLIST;