首页 > 数据库 > Oracle > 正文

ORACLE-字符拆分函返回果集

2019-11-02 14:29:55
字体:
来源:转载
供稿:网友

 ORACLE不能像MSSQL那支持直接返回表型,所以要先建一自定型。用到的是嵌套表(Nested Table)。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 -- Nested Table
动态图片[www.62-6.com]
CREATE OR REPLACE TYPE split_str IS TABLE OF VARCHAR(100); /   -- Function CREATE OR REPLACE FUNCTION fn_Split (     p_Str VARCHAR2,     p_Delimiter VARCHAR2 ) RETURN split_str PIPELINED AS     v_Str VARCHAR(4000) := p_Str;     v_Index NUMBER;     v_SubLength NUMBER; BEGIN     -- p_Delimiter is null     IF p_Delimiter IS NULL THEN         FOR x IN 1..LENGTH(v_Str)         LOOP             PIPE ROW(SUBSTR(V_Str,x,1));         END LOOP;         RETURN;     END IF;          v_index := INSTR(v_Str,p_Delimiter);     WHILE v_Index <> 0     LOOP         PIPE ROW(SUBSTR(v_Str,1,v_Index-1));         v_SubLength := LENGTH(v_Str) - (v_index + LENGTH(p_Delimiter) - 1);         v_Str := SUBSTR(v_Str,-v_SubLength,v_SubLength);         v_index := INSTR(v_Str,p_Delimiter);     END LOOP;     PIPE ROW(v_Str);     RETURN; END; /   -- 用(第二方式更好) SELECT fn_split('[email protected]@[email protected]@it','@@') FROM dual; SELECT * FROM Table(fn_split('[email protected]@[email protected]@it','@@'));
发表评论 共有条评论
用户名: 密码:
验证码: 匿名发表