處理多個字段串聯

CREATE OR REPLACE FUNCTION HRM_SUPPORT.char_table(a_char IN VARCHAR2,
a_delimiter IN VARCHAR2)
RETURN char_v_table
PIPELINED -- NOTE the pipelined keyword

/*
varsion 1.0
add user chunying
description :
*/

IS
v_char VARCHAR2(32767) := REPLACE(UPPER(a_char), '''', '');
v_length NUMBER;
v_variant VARCHAR2(1000);
BEGIN
v_length := LENGTH(v_char);

--dbms_output.put_line(to_char(v_length));
IF v_length IS NOT NULL THEN
FOR i IN 1 .. v_length LOOP
--dbms_output.put_line(to_char(i));
IF SUBSTR(v_char, i, 1) = UPPER(a_delimiter) THEN
PIPE ROW(char_v_format(v_variant));
v_variant := '';
ELSIF i = v_length THEN
v_variant := v_variant || SUBSTR(v_char, i, 1);
PIPE ROW(char_v_format(v_variant));
ELSE
v_variant := v_variant || SUBSTR(v_char, i, 1);
END IF;
--pipe row(i);
END LOOP;
ELSE
PIPE ROW(char_v_format(''));
END IF;

RETURN;
END;

CREATE OR REPLACE TYPE "CHAR_V_FORMAT" AS OBJECT (
char_list VARCHAR2 (1000)
)

CREATE OR REPLACE TYPE "CHAR_V_TABLE" AS TABLE OF char_v_format

简单函数

SELECT REGEXP_SUBSTR ('3,32',
'[^,]+',
1,
LEVEL,
chr(105))
AS str
FROM DUAL
CONNECT BY LEVEL <=
REGEXP_COUNT ('3,32', ',') + 1

  SELECT c1 dept_short_name,
         SUBSTR(MAX(SYS_CONNECT_BY_PATH(c2, ',')), 2) id
    FROM (SELECT c1,
                 c2,
                 rn,
                 LEAD(rn) OVER(PARTITION BY c1 ORDER BY rn) rn1
            FROM (SELECT c1, c2, ROW_NUMBER() OVER(ORDER BY c2) rn
                    FROM (select t.id c2, t.dept_short_name c1
                            FROM hrm_emp.npi_base_dept_info_t t)))
   START WITH rn1 IS NULL
  CONNECT BY rn1 = PRIOR rn
   GROUP BY c1;