现有:
name ny code
aa 200607 6,7
bb 200608 4,5,6,7,8,9------------------------
怎样拆分成如下格式的纪录,最好使用SQL语句完成——结果的类型如下:
name ny code
aa 200607 6
aa 200607 7
bb 200608 4
bb 200608 5
bb 200608 6
bb 200608 7
bb 200608 8
bb 200608 9其实最主要的目的是解决去除逗号,并且按照逗号原来分割的内容,分别取出。
解决方案 »
- exp/imp问题
- 从MS-SQL迁移到ORACLE最好的办法?
- oracle中 用拼sql语句实现插入数据后返回当前自增长ID号,出现怪问题,求高手解决
- 这样的情况下,数据存到数据库里,怎么样比较快?
- 这样写合呼语法吗?
- select * from FOR UPDATE 和 select * from FOR UPDATE NOWAIT 有什么区别?
- 求助!!!存储过程中NUMBER类型的OUT参数返回值为0!
- 用sys和internal无法登录是怎么回事?
- 请问能否实现下列要求的sql语句?谢谢!
- plsql developer里怎么不能查看表中的数据
- 时间加天数的问题
- 这个需求的sql真的这么难吗?是我水平不够还是...?
type array_str is table of varchar2(100)
index by binary_integer;
myarray array_str;
v_str varchar2(4000) default str;
begin
for i in 0 .. length(str)-length(replace(str,',',''))+1 loop if instr(v_str,',') = 0 then
myarray(i+1) := v_str ;
else
myarray(i+1) := substr(v_str,0,instr(v_str,',')-1);
end if;
v_str := substr(v_str,instr(v_str,',')+1);
end loop;
for i in 0 ..length(str)-length(replace(str,',','')) loop
dbms_output.put_line(myarray(i+1));
end loop ;
end substring_array;
SQL> exec substring_array('asddd,aa,dfds,fsdsf,sdfaf,dsfsaf,ssss,dffds,fsdfd');asddd
aa
dfds
fsdsf
sdfaf
dsfsaf
ssss
dffds
fsdfdPL/SQL procedure successfully completed
SQL> create table tmp
2 (
3 name varchar2(40),
4 ny varchar2(10),
5 str varchar2(100)
6 )
7 /Table created.SQL> insert into tmp values ('aa', '200607', '6,7');1 row created.SQL> insert into tmp values ('bb', '200608', '4,5,6,7,8,9');1 row created.SQL> select * from tmp
2 /NAME NY STR
---------------------------------------- ---------- ------------------------------------------------
aa 200607 6,7
bb 200608 4,5,6,7,8,9SQL> create or replace procedure substring_array(name varchar2, ny varchar2, str in varchar2) as
2 type array_str is table of varchar2(100)
3 index by binary_integer;
4 myarray array_str;
5 v_str varchar2(4000) default str;
6 begin
7 for i in 0 .. length(str)-length(replace(str,',',''))+1 loop
8
9 if instr(v_str,',') = 0 then
10 myarray(i+1) := v_str ;
11 else
12 myarray(i+1) := substr(v_str,0,instr(v_str,',')-1);
13 end if;
14 v_str := substr(v_str,instr(v_str,',')+1);
15 end loop;
16
17 for i in 0 ..length(str)-length(replace(str,',','')) loop
18 dbms_output.put_line(name || ' ' || ny || ' ' || myarray(i+1));
19 end loop ;
20 end substring_array;
21 /Procedure created.SQL> declare
2 name varchar2(40);
3 ny varchar2(10);
4 str varchar2(100);
5 begin
6 for c1 in (select name,ny,str from tmp)
7 loop
8 substring_array(c1.name,c1.ny,c1.str);
9 end loop;
10 end;
11 /
aa 200607 6
aa 200607 7
bb 200608 4
bb 200608 5
bb 200608 6
bb 200608 7
bb 200608 8
bb 200608 9PL/SQL procedure successfully completed.SQL>