oracle字串分割函數

CREATE OR REPLACE TYPE mytable AS TABLE OF varchar2(100)

CREATE OR REPLACE FUNCTION SPLIT(SRC VARCHAR2, DELIMITER VARCHAR2)
  RETURN MYTABLE IS
  PSRC VARCHAR2(500);
  A    MYTABLE := MYTABLE();
  I    NUMBER := 1; -- 
  J    NUMBER := 1;
BEGIN
  PSRC := RTRIM(LTRIM(SRC, DELIMITER), DELIMITER);
  LOOP
    I := INSTR(PSRC, DELIMITER, J);
    --Dbms_Output.put_line(i); 
    IF I > 0 THEN
      A.EXTEND;
      A(A.COUNT) := TRIM(SUBSTR(PSRC, J, I - J));
      J := I + 1;
      --Dbms_Output.put_line(a(a.Count-1)); 
    END IF;
    EXIT WHEN I = 0;
  END LOOP;
  IF J < LENGTH(PSRC) THEN
    A.EXTEND;
    A(A.COUNT) := TRIM(SUBSTR(PSRC, J, LENGTH(PSRC) + 1 - J));
  END IF;
  RETURN A;
END;

CREATE OR REPLACE FUNCTION SPLIT(SRC VARCHAR2, DELIMITER VARCHAR2) RETURN MYTABLE IS
PSRC VARCHAR2(500); A MYTABLE := MYTABLE(); I NUMBER := 1; --
J NUMBER := 1;
BEGIN
PSRC := RTRIM(LTRIM(SRC, DELIMITER), DELIMITER); LOOP I := INSTR(PSRC, DELIMITER, J);
--Dbms_Output.put_line(i);
IF I > 0 THEN A.EXTEND; A(A.COUNT) := TRIM(SUBSTR(PSRC, J, I - J)); J := I + 1;
--Dbms_Output.put_line(a(a.Count-1));
END IF; EXIT WHEN I = 0;
END LOOP; IF J < LENGTH(PSRC) THEN A.EXTEND; A(A.COUNT) := TRIM(SUBSTR(PSRC, J, LENGTH(PSRC) + 1 - J));
END IF; RETURN A;
END;
/

數組作為SELECT IN的查詢條件

SELECT * FROM STUDENT WHERE ID IN (SELECT * FROM TABLE(CAST(SPLIT('001,002', ',') AS
MYTABLE))); SELECT * FROM STUDENT WHERE ID IN (SELECT * FROM TABLE(CAST(SPLIT('001,002', ',') AS
MYTABLE)));

SELECT * FROM STUDENT WHERE ID IN (SELECT ID FROM STUDENT WHERE ID = '001' UNION SELECT * FROM TABLE(CAST(SPLIT('001,002', ',') AS
MYTABLE))