CREATE OR REPLACE PROCEDURE COPY_CHAPTER AS o_chapter_id varchar2(64); n_chapter_id varchar2(64); tmp_chapter_id varchar2(64);CURSOR cursor_chapter IS SELECT chapter_id FROM chapterinfo ;
BEGIN OPEN cursor_chapter; LOOP FETCH cursor_chapter INTO o_chapter_id; EXIT WHEN cursor_chapter%NOTFOUND;
SELECT getsequence INTO n_chapter_id FROM dual;
INSERT INTO chapterinfo (chapter_id, parent_id, layer_type) SELECT n_chapter_id,'', layer_type FROM chapterinfo WHERE chapter_id = o_chapter_id;
SELECT chapter_id INTO tmp_chapter_id FROM chapterinfo WHERE chapter_id = n_chapter_id; END LOOP; CLOSE cursor_chapter;
实测数据:CREATE TABLE T152 ( ID NUMBER(2), MyDesc VARCHAR2(20) ); 存储过程:CREATE OR REPLACE PROCEDURE ProcTest IS CURSOR vCur IS SELECT * FROM T152; vRecord T152%ROWTYPE; BEGIN INSERT INTO T152 VALUES(1, 'A'); INSERT INTO T152 VALUES(2, 'B');
FOR r IN vCur LOOP DBMS_OUTPUT.PUT_LINE('[' || r.id || ', ' || r.MyDesc || ']'); END LOOP; END ProcTest; 测试脚本:begin -- Call the procedure proctest; end; 结果:可以找到数据。
同一事务里,你insert 数据到表里不用commit 是可以看到插入表里的数据的。 CREATE OR REPLACE PROCEDURE COPY_CHAPTER AS o_chapter_id varchar2(64); n_chapter_id varchar2(64); tmp_chapter_id varchar2(64); v_layer_type chapterinfo.layer_type%type; CURSOR cursor_chapter IS SELECT chapter_id,layer_type FROM chapterinfo ;
BEGIN OPEN cursor_chapter; LOOP FETCH cursor_chapter INTO o_chapter_id,v_layer_type; EXIT WHEN cursor_chapter%NOTFOUND;
SELECT getsequence INTO n_chapter_id FROM dual;
INSERT INTO chapterinfo (chapter_id, parent_id, layer_type) values (n_chapter_id,' ',v_layer_type) SELECT chapter_id INTO tmp_chapter_id FROM chapterinfo WHERE chapter_id = n_chapter_id; END LOOP; CLOSE cursor_chapter;
END ; ---- PS: 上面是我帮你修改的过程,我不知道你为什么要这样insert的,如果是业务逻辑需要你这样处理的话,我帮你改的还需要你自己用数据去测试。 INSERT INTO chapterinfo (chapter_id, parent_id, layer_type) SELECT n_chapter_id,'', layer_type FROM chapterinfo WHERE chapter_id = o_chapter_id;
补充,INSERT语句后面缺了个; INSERT INTO chapterinfo (chapter_id, parent_id, layer_type) values (n_chapter_id,' ',v_layer_type);
问题找到了,是因为我表结构的问题 INSERT INTO chapterinfo (chapter_id, parent_id, layer_type) SELECT n_chapter_id,'', layer_type FROM chapterinfo WHERE chapter_id = o_chapter_id; 存储过程里定义的变量: o_chapter_id,也刚好是表的一个字段,就是这个问题导致了这个语句select的是空,也就没有插入到数据谢谢各位的回复,啦啦啦啦啦啦~~~~~~
o_chapter_id varchar2(64);
n_chapter_id varchar2(64);
tmp_chapter_id varchar2(64);CURSOR cursor_chapter IS
SELECT chapter_id FROM chapterinfo ;
BEGIN
OPEN cursor_chapter;
LOOP
FETCH cursor_chapter INTO o_chapter_id;
EXIT WHEN cursor_chapter%NOTFOUND;
SELECT getsequence INTO n_chapter_id FROM dual;
INSERT INTO chapterinfo
(chapter_id, parent_id, layer_type)
SELECT n_chapter_id,'', layer_type
FROM chapterinfo
WHERE chapter_id = o_chapter_id;
SELECT chapter_id INTO tmp_chapter_id FROM chapterinfo WHERE chapter_id = n_chapter_id;
END LOOP;
CLOSE cursor_chapter;
END ;
最后的查询语句查不出来刚插入的数据?是要在insert 后面加COMMIT??
为什么呢???第一次写存储过程,搞了两天了。。
(
ID NUMBER(2),
MyDesc VARCHAR2(20)
);
存储过程:CREATE OR REPLACE PROCEDURE ProcTest
IS
CURSOR vCur IS SELECT * FROM T152;
vRecord T152%ROWTYPE;
BEGIN
INSERT INTO T152 VALUES(1, 'A');
INSERT INTO T152 VALUES(2, 'B');
FOR r IN vCur LOOP
DBMS_OUTPUT.PUT_LINE('[' || r.id || ', ' || r.MyDesc || ']');
END LOOP;
END ProcTest;
测试脚本:begin
-- Call the procedure
proctest;
end;
结果:可以找到数据。
CREATE OR REPLACE PROCEDURE COPY_CHAPTER AS
o_chapter_id varchar2(64);
n_chapter_id varchar2(64);
tmp_chapter_id varchar2(64);
v_layer_type chapterinfo.layer_type%type;
CURSOR cursor_chapter IS
SELECT chapter_id,layer_type FROM chapterinfo ;
BEGIN
OPEN cursor_chapter;
LOOP
FETCH cursor_chapter INTO o_chapter_id,v_layer_type; EXIT WHEN cursor_chapter%NOTFOUND;
SELECT getsequence INTO n_chapter_id FROM dual;
INSERT INTO chapterinfo
(chapter_id, parent_id, layer_type)
values (n_chapter_id,' ',v_layer_type) SELECT chapter_id INTO tmp_chapter_id FROM chapterinfo WHERE chapter_id = n_chapter_id;
END LOOP;
CLOSE cursor_chapter;
END ; ----
PS:
上面是我帮你修改的过程,我不知道你为什么要这样insert的,如果是业务逻辑需要你这样处理的话,我帮你改的还需要你自己用数据去测试。
INSERT INTO chapterinfo
(chapter_id, parent_id, layer_type)
SELECT n_chapter_id,'', layer_type
FROM chapterinfo
WHERE chapter_id = o_chapter_id;
INSERT INTO chapterinfo
(chapter_id, parent_id, layer_type)
values (n_chapter_id,' ',v_layer_type);
(chapter_id, parent_id, layer_type)
SELECT n_chapter_id,'', layer_type
FROM chapterinfo
WHERE chapter_id = o_chapter_id;
存储过程里定义的变量: o_chapter_id,也刚好是表的一个字段,就是这个问题导致了这个语句select的是空,也就没有插入到数据谢谢各位的回复,啦啦啦啦啦啦~~~~~~