存储过程一个参数传入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?
比如
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?
2006-01-12
2006-02-13
2006-03-15
2006-11-11
2003-1-1
declare @Days varchar(4000)
declare @tmpDay varchar(10)
set @Days='2006-01-12|2006-02-13|2006-03-15|2006-11-11|2003-1-1'
set @tmpDay=''
declare @i int
set @i=0
while @i<len(@Days)
begin
set @i=@i+1
if SUBSTRING(@Days,@i,1)='|'
begin
print left(@Days,@i-1)
set @Days=SUBSTRING(@Days,@i+1,len(@Days))
set @i=0
end
end
print @Days
输出结果:
2006-01-12
2006-02-13
2006-03-15
2006-11-11
2003-1-1一字段信息包括如下:李三|李三@d.com|公司|单位地址|
我将查询用(Select)只列出李三及单位地址的方法?create table #t(c1 varchar(100))
insert into #t select 'li4|33@com|mircrosoft|china|'
insert into #t select 'zhang3|[email protected]|IBM|USA|'
insert into #t select '李三|李三@d.com|公司|单位地址|'
select substring(c1,1,charindex('|',c1)-1) as name,
reverse(substring(reverse(c1) , 2 , charindex('|' , reverse(c1) , 2) - 2)) as address from #tdrop table #tname address
----- -------
li4 china
zhang3 USA
李三 单位地址(所影响的行数为 3 行)
declare @s varchar(20)
set @s='06G512753-08-01'
select
parsename(t.s,3) part1,
parsename(t.s,2) part2,
parsename(t.s,1) part3
from
(select replace(@s,'-','.') as s) t
--分解字符号串
declare @str varchar(8000)
set @str = '2006-01-12|2006-02-13|2006-03-15|2006-11-11|2003-1-1'
declare @sql varchar(8000)
set @sql='select '''+replace(@str,'|',''' union all select ''')+''''
exec( @sql )----------
2006-01-12
2006-02-13
2006-03-15
2006-11-11
2003-1-1
declare @str varchar(8000)
set @str='1,2,3,4,5,6,7,8,55'set @str='select '+replace(@str,',',' union all select ')exec(@str)
--分解字符串并将数据插入表中。
create table tb(col varchar(20))
go
declare @str varchar(8000)
set @str='国家地理,美国国家地理,华夏地理,中国国家地理,旅游,摄影'set @str='insert into tb select * from (select col = ''' + replace(@str , ',' , ''' union all select ''')
set @str = @str + ''')t'exec(@str)select * from tbdrop table tb/*
col
--------------------
国家地理
美国国家地理
华夏地理
中国国家地理
旅游
摄影(所影响的行数为 6 行)
*/
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
SQL> --创建type
SQL> create or replace type tbl_str as table of varchar2(4000);
2 /Type createdSQL> --创建function
SQL> create or replace function to_table(pv_str varchar2,pv_split varchar2) return tbl_str
2 as
3 ltab tbl_str := tbl_str();
4 pos integer := 0;
5 ls varchar2(4000) := pv_str;
6 begin
7 pos := instr(ls,pv_split);
8 while pos > 0 loop
9 ltab.extend;
10 ltab(ltab.count) := substr(ls,1,pos - 1);
11 ls := substr(ls,pos + length(pv_split));
12 pos := instr(ls,pv_split);
13 end loop;
14 ltab.extend;
15 ltab(ltab.count) := ls;
16 return ltab;
17 end;
18 /Function createdSQL> --创建测试表
SQL> create table test_tbl(colum number);Table createdSQL> --查询,无记录
SQL> select * from test_tbl; COLUM
----------SQL> --插入,使用to_table函数,以逗号分隔'1,2,3,4,5'成5rows。
SQL> insert into test_tbl
2 select column_value from table(cast(to_table('1,2,3,4,5',',') as tbl_str));5 rows insertedSQL> --查询,有记录
SQL> select * from test_tbl; COLUM
----------
1
2
3
4
5SQL> --删除测试表
SQL> drop table test_tbl;Table droppedSQL> --删除type
SQL> drop type tbl_str;Type droppedSQL> --删除function
SQL> drop function to_table;Function droppedSQL>
v_s1 VARCHAR2 (25) := '1,2,3,4,5,6,7,8,9,10';
v_s2 VARCHAR2 (20) := '';
BEGIN
LOOP
v_s2 := SUBSTR (v_s1, 1, INSTR (v_s1, ',') - 1);
DBMS_OUTPUT.put_line (v_s2);
v_s1 := SUBSTR (v_s1, INSTR (v_s1, ',') + 1, LENGTH (v_s1)); IF INSTR (v_s1, ',') = 0
THEN
DBMS_OUTPUT.put_line (v_s1);
EXIT;
END IF;
END LOOP;
END;试试这样子。
输出:
1
2
3
4
5
6
7
8
9
10