Oracle 行转列

1. 行转列，以, 分隔

SELECT regexp_substr(你要转的字符串,'[^,]+',1,rownum) str
  FROM dual
    CONNECT BY rownum <= (LENGTH(你要转的字符串) - LENGTH(REGEXP_REPLACE(你要转的字符串, ',','')) + 1)

2. 将行转列语句提取成一个Function

//header
TYPE r_str IS RECORD(
  str VARCHAR2( 100 ) 
);
TYPE t_str IS TABLE OF r_str;

FUNCTION get_separate_str(
    p_str IN VARCHAR2 DEFAULT NULL
) RETURN t_str PIPELINED;

//body
FUNCTION get_separate_str(
    p_str IN VARCHAR2 DEFAULT NULL )
  RETURN t_str PIPELINED
AS
  rec_out r_str;
BEGIN
  FOR rec IN
  (SELECT regexp_substr(p_str,'[^,]+',1,rownum) str
  FROM dual
    CONNECT BY rownum <= (LENGTH(p_str) - LENGTH(REGEXP_REPLACE(p_str, ',','')) + 1)
  )
  LOOP
    rec_out.str := rec.str;
    PIPE ROW (rec_out);
  END LOOP;
  RETURN;
END get_separate_str;