create or replace function fn_StringToTable (:=str in varchar(4000)) return :=table1 table (FieldName varchar(100)) begin declare :=in_Index as int, :=strTemp as varchar(100) set :=in_Index = instr(',',:=str)
while (:=in_Index > 0 or length(:=str) > 0) begin if :=in_Index > 0 begin set :=strTemp = rtrim(ltrim(Left(:=str,:=in_Index - 1))) set @str = rtrim(ltrim(Right(:=str,Length(:=str)-:=in_Index))) end else begin set :=strTemp = :=str set :=str = '' end insert :=Table1(FieldName) values(:=strTemp) set :=in_Index = instr(',',:=str) end return end GO 这是自己瞎改的没学过oracle到处都是错!!!
create or replace function fn_StringToTable(str varchar2) return t_table as rs t_table:= t_table(); begin for rec IN (WITH A AS (SELECT str A FROM DUAL) SELECT rownum as rn,DECODE(B,0,SUBSTR(A,C),SUBSTR(A,C,B-C)) as FieldName FROM ( SELECT A,B,(LAG(B,1,0) OVER(ORDER BY LV))+1 C FROM( SELECT A,INSTR(A,',',1,LEVEL) B,LEVEL LV FROM A CONNECT BY LEVEL <=(LENGTH(A) - LENGTH(REPLACE(A,',','')))+1 ) )) loop rs.extend; rs(rec.rn) := obj_StringToTable(rec.FieldName); --rs(rs.count).name := rs(rs.count).name || 'xxxx'; end loop; return rs; end fn_StringToTable; 怕有人撞上同样问题,将弄出来的发一下
create or replace type obj_StringToTable as object ( FieldName varchar2(4000) );类型
function fn_StringToTable
(:=str in varchar(4000))
return :=table1 table
(FieldName varchar(100))
begin
declare :=in_Index as int,
:=strTemp as varchar(100)
set :=in_Index = instr(',',:=str)
while (:=in_Index > 0 or length(:=str) > 0)
begin
if :=in_Index > 0
begin
set :=strTemp = rtrim(ltrim(Left(:=str,:=in_Index - 1)))
set @str = rtrim(ltrim(Right(:=str,Length(:=str)-:=in_Index)))
end
else
begin
set :=strTemp = :=str
set :=str = ''
end
insert :=Table1(FieldName)
values(:=strTemp) set :=in_Index = instr(',',:=str) end
return
end
GO 这是自己瞎改的没学过oracle到处都是错!!!
function fn_StringToTable(str varchar2)
return t_table
as
rs t_table:= t_table();
begin
for rec IN (WITH A AS (SELECT str A FROM DUAL)
SELECT rownum as rn,DECODE(B,0,SUBSTR(A,C),SUBSTR(A,C,B-C)) as FieldName FROM
(
SELECT A,B,(LAG(B,1,0) OVER(ORDER BY LV))+1 C
FROM(
SELECT A,INSTR(A,',',1,LEVEL) B,LEVEL LV FROM A
CONNECT BY LEVEL <=(LENGTH(A) - LENGTH(REPLACE(A,',','')))+1
)
)) loop
rs.extend;
rs(rec.rn) := obj_StringToTable(rec.FieldName);
--rs(rs.count).name := rs(rs.count).name || 'xxxx';
end loop;
return rs;
end fn_StringToTable; 怕有人撞上同样问题,将弄出来的发一下
type obj_StringToTable as object
(
FieldName varchar2(4000)
);类型