比如:一个varchar2值:1,2,3
我想实现一函数,返回一个表:
字段
1
2
3并且:
insert into a(a) (select column_value from 自定义函数(url));
不会出现cannot access rows from a non-nested table item的错误!
我想实现一函数,返回一个表:
字段
1
2
3并且:
insert into a(a) (select column_value from 自定义函数(url));
不会出现cannot access rows from a non-nested table item的错误!
CREATE TABLE GL
( NM VARCHAR2(10)
)DECLARE LEN NUMBER;
I NUMBER;
STR VARCHAR2(20);
STR1 VARCHAR2(20);
STR2 VARCHAR2(20);
begin
STR := '10.25.23.65.2541.256';
STR1 := '.';
STR2 := '';
LEN := 1;
LEN := LENGTH(STR);
FOR I IN 1..LEN LOOP
IF SUBSTR(STR,I,1) <> STR1 THEN
STR2 := STR2 || SUBSTR(STR,I,1);
END IF;
IF SUBSTR(STR,I,1) = STR1 OR I = LEN THEN
INSERT INTO GL VALUES(STR2);
STR2 := '';
END IF;
END LOOP;
end;把程序里的参数STR和STR1改成输入值就可以了
CREATE OR REPLACE PROCEDURE ABC (STR IN VARCHAR2, STR1 IN VARCHAR2)
可以做个PIPELINED的函数,就可以实现你要的了
如下:
CREATE OR REPLACE TYPE VARCHAR2_SET IS TABLE OF VARCHAR2(100);CREATE OR REPLACE FUNCTION SPLIT_BY_TOKEN(IN_STR IN VARCHAR2,IN_TOKEN IN VARCHAR2)
RETURN VARCHAR2_SET DETERMINISTIC PIPELINED IS
V_TEMP_STR VARCHAR2(2000);
V_ELEMENT VARCHAR2(100);
V_I INTEGER;
BEGIN
V_TEMP_STR := IN_STR;
V_I := INSTR(V_TEMP_STR,IN_TOKEN);
WHILE V_I <> 0 LOOP
V_ELEMENT := SUBSTR(V_TEMP_STR,1,V_I-1);
PIPE ROW(V_ELEMENT);
V_TEMP_STR := SUBSTR(V_TEMP_STR,V_I+1);
V_I := INSTR(V_TEMP_STR,IN_TOKEN);
END LOOP;
IF V_TEMP_STR IS NOT NULL THEN
PIPE ROW(V_TEMP_STR);
END IF;
RETURN;
END;INSERT INTO A(A) SELECT COLUMN_VALUE FROM TABLE(CAST(SPLIT_BY_TOKEN('12.3.65.89.7.14','.') AS VARCHAR2_SET));
Thanks
Hima