按这个分解成 a=1 b=2 c=3存储过程一个参数传入1,2,3,4,5类似的一个字符串,然后要把1 2 3 4 5插入表 比如 insert into table(colum)values(1); insert into table(colum)values(2); insert into table(colum)values(3); insert into table(colum)values(4); insert into table(colum)values(5); 请问如何做?是否要用拆分函数,然后用动态sql?-------------------------------- select sname from test;SNAME --------- 1,2,3,4,5SQL> SQL> select substr(','||t1.sname||',', 2 instr(','||t1.sname||',',',',1,rn)+1, 3 instr(','||t1.sname||',',',',1,rn+1)-instr(','||t1.sname||',',',',1,rn)-1) as new_name 4 from test t1, 5 ( 6 select rownum rn 7 from all_objects 8 where rownum <= 10 9 )t2 10 where instr(','||t1.sname||',',',',1,rn+1) > 0;NEW_NAME ----------- 1 2 3 4 5 ----------------------------然后把上面的作为子查询 select substr(col , 1 , instr(col , '=') - 1) , substr(col , instr(col , '=') + 1 , len(col)) from (上面那个查询) t
假设表 professor 中列 course_id 为 a=1,b=2,c=3 select substr(','||t1.course_id||',', instr(','||t1.course_id||',',',',1,rn)+1, instr(','||t1.course_id||',',',',1,rn+1)-instr(','||t1.course_id||',',',',1,rn)-1) as new_name from professor t1, ( select rownum rn from all_objects where rownum <= 10 )t2 where instr(','||t1.course_id||',',',',1,rn+1) > 0 得到 a=1 b=2 c=3 然后看着办吧
create or replace type tbl_str as table of varchar2(4000); / create or replace function to_table(pv_str varchar2,pv_split varchar2) return tbl_str as ltab tbl_str := tbl_str(); pos integer := 0; ls varchar2(4000) := pv_str; begin pos := instr(ls,pv_split); while pos > 0 loop ltab.extend; ltab(ltab.count) := substr(ls,1,pos - 1); ls := substr(ls,pos + length(pv_split)); pos := instr(ls,pv_split); end loop; ltab.extend; ltab(ltab.count) := ls; return ltab; end; / SQL> select substr(column_value,1,instr(column_value,'=')-1) code, 2 substr(column_value,instr(column_value,'=')+1) value 3 from table(cast(to_table('a=1,b=2,c=3',',') as tbl_str));CODE VALUE -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- a 1 b 2 c 3
create table tb(col varchar2(50)) insert into tb values('a=1,b=2,c=3')select substr(col , 1 , instr(col , '=') - 1) code , substr(col , instr(col , '=') + 1 , length(col)) value from ( select substr(','||t1.col||',', instr(','||t1.col||',',',',1,rn)+1, instr(','||t1.col||',',',',1,rn+1)-instr(','||t1.col||',',',',1,rn)-1) as col from tb t1,(select rownum rn from all_objects)t2 where instr(','||t1.col||',',',',1,rn+1) > 0 ) t
drop table tb/* CODE VALUE ------ -------- a 1 b 2 c 3 3 rows selected. */
a=1
b=2
c=3存储过程一个参数传入1,2,3,4,5类似的一个字符串,然后要把1 2 3 4 5插入表
比如
insert into table(colum)values(1);
insert into table(colum)values(2);
insert into table(colum)values(3);
insert into table(colum)values(4);
insert into table(colum)values(5); 请问如何做?是否要用拆分函数,然后用动态sql?--------------------------------
select sname from test;SNAME
---------
1,2,3,4,5SQL>
SQL> select substr(','||t1.sname||',',
2 instr(','||t1.sname||',',',',1,rn)+1,
3 instr(','||t1.sname||',',',',1,rn+1)-instr(','||t1.sname||',',',',1,rn)-1) as new_name
4 from test t1,
5 (
6 select rownum rn
7 from all_objects
8 where rownum <= 10
9 )t2
10 where instr(','||t1.sname||',',',',1,rn+1) > 0;NEW_NAME
-----------
1
2
3
4
5
----------------------------然后把上面的作为子查询
select substr(col , 1 , instr(col , '=') - 1) , substr(col , instr(col , '=') + 1 , len(col)) from
(上面那个查询) t
instr(','||t1.course_id||',',',',1,rn)+1,
instr(','||t1.course_id||',',',',1,rn+1)-instr(','||t1.course_id||',',',',1,rn)-1)
as new_name
from professor t1,
(
select rownum rn
from all_objects
where rownum <= 10
)t2
where instr(','||t1.course_id||',',',',1,rn+1) > 0
得到
a=1
b=2
c=3
然后看着办吧
create or replace type tbl_str as table of varchar2(4000);
/
create or replace function to_table(pv_str varchar2,pv_split varchar2) return tbl_str
as
ltab tbl_str := tbl_str();
pos integer := 0;
ls varchar2(4000) := pv_str;
begin
pos := instr(ls,pv_split);
while pos > 0 loop
ltab.extend;
ltab(ltab.count) := substr(ls,1,pos - 1);
ls := substr(ls,pos + length(pv_split));
pos := instr(ls,pv_split);
end loop;
ltab.extend;
ltab(ltab.count) := ls;
return ltab;
end;
/
SQL> select substr(column_value,1,instr(column_value,'=')-1) code,
2 substr(column_value,instr(column_value,'=')+1) value
3 from table(cast(to_table('a=1,b=2,c=3',',') as tbl_str));CODE VALUE
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
a 1
b 2
c 3
insert into tb values('a=1,b=2,c=3')select substr(col , 1 , instr(col , '=') - 1) code , substr(col , instr(col , '=') + 1 , length(col)) value from
(
select substr(','||t1.col||',',
instr(','||t1.col||',',',',1,rn)+1,
instr(','||t1.col||',',',',1,rn+1)-instr(','||t1.col||',',',',1,rn)-1) as col
from tb t1,(select rownum rn from all_objects)t2
where instr(','||t1.col||',',',',1,rn+1) > 0
) t
drop table tb/*
CODE VALUE
------ --------
a 1
b 2
c 3 3 rows selected.
*/