關於 pipe row 應用, split or merge 字串
Oralce 有種 pipelining 的技術, 可以執行 SQL 時可以產生一個虛擬 Table,
這項技術可用來彌補 function 傳值時的限制
程式碼:
-- 宣告類型 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; |