create table a(b varchar2(20));declare ir varchar2(20);
begin
ir :='123,123';
insert into a(b)
select * from table(split(ir));
end;split函数是根据逗号分隔字符串的函数
begin
ir :='123,123';
insert into a(b)
select * from table(split(ir));
end;split函数是根据逗号分隔字符串的函数
create type is table of ...;
CREATE OR REPLACE TYPE F_SPLITTOVARCHAR_TEMPOBJ
AS OBJECT (StringElement VARCHAR2(200));
CREATE OR REPLACE TYPE F_SPLITTOVARCHAR_TEMPTAB
AS TABLE OF F_SPLITTOVARCHAR_TEMPOBJ;
我的数据库版本是9201
(
strValueString IN VARCHAR2 DEFAULT NULL
)
RETURN F_SPLITTOVARCHAR_TEMPTAB PIPELINED AS
V_F_SPLITTOVARCHAR_TEMPOBJ F_SPLITTOVARCHAR_TEMPOBJ;V_strValueString_ VARCHAR2(8000) := strValueString;
V_strFlag_ VARCHAR2(10) := ',';
V_intLocation NUMBER(10,0);
V_intValueID VARCHAR2(200);
V_intLength NUMBER(10,0);
BEGIN
BEGIN Split.V_intLength := LENGTHB(TRIM(Split.V_strFlag_));
WHILE LENGTHB(Split.V_strValueString_) > 0 LOOP
Split.V_intLocation := INSTRB(Split.V_strValueString_ , Split.V_strFlag_ , 1);
IF Split.V_intLocation > 0 THEN
Split.V_intValueID := SUBSTRB(Split.V_strValueString_,1,Split.V_intLocation - 1);
Split.V_strValueString_ := SUBSTRB(Split.V_strValueString_ , Split.V_intLocation + Split.V_intLength ); ELSE
Split.V_intValueID := Split.V_strValueString_;
Split.V_strValueString_ := ''; END IF ; V_F_SPLITTOVARCHAR_TEMPOBJ := F_SPLITTOVARCHAR_TEMPOBJ(Split.V_intValueID);
PIPE ROW(V_F_SPLITTOVARCHAR_TEMPOBJ);
END LOOP; RETURN;
END;
END Split;
SELECT * from table(split('123,456'));
123
456PS:这个管道函数的写法精简下,使用系统自带的存储过程SYS.DBMS_UTILITY.COMMA_TO_TABLE将逗号分隔的字符串直接转换为表类型
LS,我是想问下为什么会报"无法从非嵌套表项访问行"的错误,无法执行.
是不是跟版本有关,我Oracle的9201
SELECT * from table(split('123,456'));
SELECT * from table(CAST (split('123,456') AS F_SPLITTOVARCHAR_TEMPTAB));