create or replace procedure Product5()
is
v_ID int;
cursor mycursor is select ID from jfsky.Product5;
begin
open mycursor;
loop
fetch mycursor into v_ID;
insert into jfsky.Product4(title,ID)
select trim(regexp_substr(title, '[^,]+', 1, lv)) title,ID from (
select distinct title,ID, level lv from (
select title,ID,length(title) - length(replace(title, ',', '')) + 1 rn
from JFSKY.Product5 where ID=:v_ID) connect by level <= rn ) order by title;
commit;
end loop;
close mycursor;
end Product5;
表Product5,Product4结构一样,表Product5中的title字段中“,”号,就是把这样的分行插入到Product4表中,insert ..select .....我已经测试过没问题,, where ID=:v_ID变量
过程与函数无所谓,只要实现功能,先谢过各位大侠了
is
v_ID int;
cursor mycursor is select ID from jfsky.Product5;
begin
open mycursor;
loop
fetch mycursor into v_ID;
insert into jfsky.Product4(title,ID)
select trim(regexp_substr(title, '[^,]+', 1, lv)) title,ID from (
select distinct title,ID, level lv from (
select title,ID,length(title) - length(replace(title, ',', '')) + 1 rn
from JFSKY.Product5 where ID=:v_ID) connect by level <= rn ) order by title;
commit;
end loop;
close mycursor;
end Product5;
表Product5,Product4结构一样,表Product5中的title字段中“,”号,就是把这样的分行插入到Product4表中,insert ..select .....我已经测试过没问题,, where ID=:v_ID变量
过程与函数无所谓,只要实现功能,先谢过各位大侠了
解决方案 »
- 数据库范式问题
- ORACLE 警报日志怎么看?
- 在charindex()函数在oracle中怎么用?
- 奇怪了!~~这个超级简单的SQL语句,效率却超级低...
- 关于一个视图的问题!!!!!!!!急!!!在线等!!!!!
- 初学 解决语法的年龄计算
- 急!请问oracle817报这个错误“ORA-01033: Message 1033 not found; No message file for product=RDBMS,facility=ORA”是什么意思,如何
- 问一句代码
- 统计问题,高手请进。
- oracle 存储过程 Error: PLS-00103: 出现符号 "GETALLPAYMENTAPPLY"在需要下列之一时: if
- oracle10使用sqllder进行数据导入,在控制文件中如何设置默认值
- ORA -00600 [kcblasm_1],[103]
CURSOR CUR_MYCURSOR IS
SELECT ID FROM JFSKY.PRODUCT5;
BEGIN
FOR V_CUR IN CUR_MYCURSOR LOOP
INSERT INTO JFSKY.PRODUCT4
(TITLE, ID)
SELECT TRIM(REGEXP_SUBSTR(TITLE, '[^,]+', 1, LV)) TITLE, ID
FROM (SELECT DISTINCT TITLE, ID, LEVEL LV
FROM (SELECT TITLE,
ID,
LENGTH(TITLE) - LENGTH(REPLACE(TITLE, ',', '')) + 1 RN
FROM JFSKY.PRODUCT5
WHERE ID = V_CUR.ID)
CONNECT BY LEVEL <= RN)
ORDER BY TITLE;
COMMIT;
END LOOP;
CLOSE V_CURSOR;
END;
/不是很明白你的想法,看这样成否?
--已测试OK
create or replace procedure Product5 --没有参数就不要括号
is
begin
for rs in (select id from product5) loop
insert into Product4(title,ID)
select trim(regexp_substr(title, '[^,]+', 1, lv)) title,ID from (
select distinct title,ID, level lv from (
select title,ID,length(title) - length(replace(title, ',', '')) + 1 rn
from Product5 where ID=rs.id) connect by level <= rn ) order by title;
commit;
end loop;
end Product5;