--测试函数3 CREATE OR REPLACE FUNCTION FN_TEST3 (NUM IN VARCHAR2) RETURN VARCHAR2 IS TYPE VARCHAR2_ARR IS TABLE OF VARCHAR2(300); NUMARR VARCHAR2_ARR; TEMPNUM VARCHAR2(1000) := ''; TEMPCOUNT NUMBER := 0; BEGIN SELECT TRIM(REGEXP_SUBSTR(NUM,'(\w|[-])+',1,LEVEL)) BULK COLLECT INTO NUMARR FROM DUAL CONNECT BY LEVEL <= REGEXP_COUNT(NUM,'(\w|[-])+'); FOR I IN 1 .. NUMARR.COUNT LOOP IF NUMARR(I) IS NOT NULL THEN TEMPNUM := TEMPNUM || NUMARR(I); END IF; END LOOP; RETURN TEMPNUM; END; / --测试函数2 CREATE OR REPLACE FUNCTION FN_TEST2 (NUM IN VARCHAR2) RETURN NUMBER IS TYPE VARCHAR2_ARR IS TABLE OF VARCHAR2(300); NUMARR VARCHAR2_ARR; TEMPNUM VARCHAR2(1000) := ''; TEMPCOUNT NUMBER := 0; BEGIN SELECT TRIM(REGEXP_SUBSTR(NUM,'(\w|[-])+',1,LEVEL)) BULK COLLECT INTO NUMARR FROM DUAL CONNECT BY LEVEL <= REGEXP_COUNT(NUM,'(\w|[-])+'); FOR I IN 1 .. NUMARR.COUNT LOOP IF NUMARR(I) IS NOT NULL THEN TEMPNUM := TEMPNUM || NUMARR(I) || ','; TEMPCOUNT := TEMPCOUNT + 1; END IF; END LOOP; RETURN TEMPCOUNT; END; / --测试函数 CREATE OR REPLACE FUNCTION FN_TEST (NUM IN VARCHAR2) RETURN VARCHAR2 IS TYPE VARCHAR2_ARR IS TABLE OF VARCHAR2(300); NUMARR VARCHAR2_ARR; TEMPNUM VARCHAR2(1000) := ''; BEGIN SELECT TRIM(REGEXP_SUBSTR(NUM,'^.+',1,LEVEL)) BULK COLLECT INTO NUMARR FROM DUAL CONNECT BY LEVEL <= REGEXP_COUNT(NUM,'^.+'); FOR I IN 1 .. NUMARR.COUNT LOOP IF NUMARR(I) IS NOT NULL THEN TEMPNUM := TEMPNUM || NUMARR(I) || ','; END IF; END LOOP; RETURN TEMPNUM; END; /