Oracle数据库自定义函数练习20181031

--测试函数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;
/