CREATE OR REPLACE FUNCTION MY_FUN(COL VARCHAR2) RETURN VARCHAR2 IS
STR VARCHAR2(4000);
I NUMBER;
V_COL VARCHAR2(4000);
V_SUBSTR VARCHAR2(4000);
V_COUNT NUMBER;
IGNORE_STR VARCHAR2(4000) := 'SUM,DECODE,AVG'; --忽略列表
BEGIN
V_COL := UPPER(COL);
FOR I IN 1 .. LENGTH(V_COL) LOOP
V_SUBSTR := REGEXP_SUBSTR(V_COL, '[A-Z_]+', 1, I);
IF V_SUBSTR IS NOT NULL THEN
--检测匹配出来的字段是不是存在忽略字段列表
SELECT COUNT(1)
INTO V_COUNT
FROM (SELECT REGEXP_SUBSTR(IGNORE_STR, '[^,]+', 1, LEVEL) AS COL
FROM DUAL
CONNECT BY LEVEL <= LENGTH(IGNORE_STR) -
LENGTH(REPLACE(IGNORE_STR, ',')) + 1)
WHERE COL = V_SUBSTR;
IF V_COUNT = 0 THEN
STR := STR || ',' || V_SUBSTR;
END IF;
ELSE
EXIT;
END IF;
END LOOP;
RETURN LTRIM(STR, ',');
END;