關於 pipe row 應用, split or merge 字串

  -- 宣告類型
  CREATE OR REPLACE TYPE xx_split_tbl IS TABLE OF VARCHAR2 (4000);
 
  -----------------------------------------------------------
  -- 傳入字串 + 分隔符號, 傳回 單一欄位的 table, 欄位名: column_value
  -----------------------------------------------------------
  FUNCTION get_split_tbl(p_list VARCHAR2, p_del VARCHAR2 DEFAULT ';')
    RETURN xx_split_tbl
    PIPELINED IS
    v_idx  PLS_INTEGER;
    v_list VARCHAR2(32767) := p_list;
  BEGIN
    LOOP
      v_idx := INSTR(v_list, p_del);
      IF v_idx > 0 THEN
        PIPE ROW(SUBSTR(v_list, 1, v_idx - 1));
        v_list := SUBSTR(v_list, v_idx + LENGTH(p_del));
      ELSE
        PIPE ROW(v_list);
        EXIT;
      END IF;
    END LOOP;
    RETURN;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE;
  END get_split_tbl;
 
  -----------------------------------------------------------
  -- 傳入單一欄位的 table + 分隔符號, 傳回組合的字串
  -----------------------------------------------------------
  FUNCTION get_merge_str(p_cursor SYS_REFCURSOR,
                         p_del    VARCHAR2 DEFAULT ';') RETURN VARCHAR2 IS
    v_value  VARCHAR2(32767);
    v_result VARCHAR2(32767);
  BEGIN
    LOOP
      FETCH p_cursor
        INTO v_value;
      EXIT WHEN p_cursor%NOTFOUND;
      IF v_result IS NOT NULL THEN
        v_result := v_result || p_del;
      END IF;
      v_result := v_result || v_value;
    END LOOP;
    CLOSE p_cursor;
    RETURN v_result;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE;
  END get_merge_str;

1. 字串拆解成 TABLE
SELECT column_value
  FROM TABLE(xxutlpkg.get_split_tbl('01;02;03;04;09;11;12', ';'));
 
2. TABLE 某欄位組成 字串+分隔符號
SELECT xxutlpkg.get_merge_str(CURSOR (SELECT wafer_id
                                 FROM xxwip_clot_line l
                                WHERE l.sub_lot_id = 'FP8312.1'
                                ORDER BY wafer_id),
                              ';')
  FROM dual;