Oracle分割字符串返回数组
Posted onOracle分割字符串返回数组
view plaincopy to clipboardprint?
CREATE OR REPLACE TYPE mytable AS TABLE OF varchar2(100)
/
view plaincopy to clipboardprint?
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的查询条件
view plaincopy to clipboardprint?
- 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)));
view plaincopy to clipboardprint?
SELECT /* FROM student WHERE id IN
(
SELECT id FROM student WHERE id='001'
UNION
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)) );