PROCEDURE ACT1 AS BEGIN INSERT INTO BB( BB_CD, BB_NM) VALUES( CUR1_REC.AA_CD, CUR1_REC.AA_NM); END ACT1; 这个存储过程是在主存储过程定义的部分,不能取到CUR1_REC变量的值,它不是全局变量。他的作用域只在for循环中
没见过你这么写的……CREATE OR REPLACE PROCEDURE E82 AS CURSOR CUR1 IS SELECT AA_CD, AA_NM FROM AA WHERE AA_MM = '1'; CUR1_REC CUR1%ROWTYPE; curs_aa cur1_rec; BEGIN open curs_aa; fetch cur1 into cur1_rec; exit when curs_aa%notfound; INSERT INTO BB( BB_CD, BB_NM) VALUES( CUR1_REC.AA_CD, CUR1_REC.AA_NM); END LOOP; close curs_aa; END E82; 随便写的,不知道对不对,你运行下看看……
果然错了:( CREATE OR REPLACE PROCEDURE E82 AS CURSOR CUR1 IS SELECT AA_CD, AA_NM FROM AA WHERE AA_MM = '1'; CUR1_REC CUR1%ROWTYPE; curs_aa cur1_rec; BEGIN open curs_aa; fetch cur1 into cur1_rec; exit when curs_aa%notfound; INSERT INTO BB( BB_CD, BB_NM) VALUES( curs_aa.AA_CD, curs_aa.AA_NM); END LOOP; close curs_aa; END E82;
楼主 , 以下代码我已经在oracle8.1.6中试过, 成功! CREATE OR REPLACE PROCEDURE E82 AS CURSOR CUR1 IS SELECT AA_CD, AA_NM FROM AA WHERE AA_MM = '1'; CUR1_REC CUR1%ROWTYPE; PROCEDURE ACT1 AS BEGIN INSERT INTO BB( BB_CD, BB_NM) VALUES( CUR1_REC.AA_CD, CUR1_REC.AA_NM); END ACT1; BEGIN open cur1; loop fetch cur1 into cur1_rec; exit when cur1%notfound; act1; END LOOP; close cur1; END E82; ———————————————————— declare begin e82; end; 结果成功插入一条记录。
CREATE OR REPLACE PROCEDURE E83 AS CURSOR CUR1 IS SELECT num, p_name, re FROM jtest WHERE num = '1'; CUR1_REC CUR1%ROWTYPE;BEGIN open CUR1; loop fetch cur1 into CUR1_REC; exit when cur1%notfound; INSERT INTO jtest1( num, p_name, re) VALUES( CUR1_rec.num, CUR1_rec.p_name, CUR1_rec.re); end loop; close cur1; END E83; ———————————————————————————————————— 也可以,不过这个恐怕不符合你的本意吧 ^_^
步入边城(编程)说得对,我也调试了,也通了,总算明白了:FOR rec1 IN cur1 LOOP ....END LOOP这种形式打开的游标,rec1只能在for循环中使用,而OPEN cur1 FETCH....这种方式打开只要rec1是全局的,子过程中就可以引用。
BEGIN
INSERT INTO BB(
BB_CD,
BB_NM)
VALUES(
CUR1_REC.AA_CD,
CUR1_REC.AA_NM);
END ACT1;
这个存储过程是在主存储过程定义的部分,不能取到CUR1_REC变量的值,它不是全局变量。他的作用域只在for循环中
CURSOR CUR1
IS
SELECT AA_CD,
AA_NM
FROM AA
WHERE AA_MM = '1';
CUR1_REC CUR1%ROWTYPE;
curs_aa cur1_rec;
BEGIN
open curs_aa;
fetch cur1 into cur1_rec;
exit when curs_aa%notfound; INSERT INTO BB(
BB_CD,
BB_NM)
VALUES(
CUR1_REC.AA_CD,
CUR1_REC.AA_NM);
END LOOP;
close curs_aa;
END E82;
随便写的,不知道对不对,你运行下看看……
CREATE OR REPLACE PROCEDURE E82 AS
CURSOR CUR1
IS
SELECT AA_CD,
AA_NM
FROM AA
WHERE AA_MM = '1';
CUR1_REC CUR1%ROWTYPE;
curs_aa cur1_rec;
BEGIN
open curs_aa;
fetch cur1 into cur1_rec;
exit when curs_aa%notfound; INSERT INTO BB(
BB_CD,
BB_NM)
VALUES(
curs_aa.AA_CD,
curs_aa.AA_NM);
END LOOP;
close curs_aa;
END E82;
CREATE OR REPLACE PROCEDURE E82 AS
CURSOR CUR1
IS
SELECT AA_CD,
AA_NM
FROM AA
WHERE AA_MM = '1';
CUR1_REC CUR1%ROWTYPE; PROCEDURE ACT1 AS
BEGIN
INSERT INTO BB(
BB_CD,
BB_NM)
VALUES(
CUR1_REC.AA_CD,
CUR1_REC.AA_NM);
END ACT1;
BEGIN
open cur1;
loop
fetch cur1 into cur1_rec;
exit when cur1%notfound;
act1;
END LOOP;
close cur1;
END E82;
————————————————————
declare
begin
e82;
end;
结果成功插入一条记录。
CURSOR CUR1
IS
SELECT num,
p_name,
re
FROM jtest
WHERE num = '1';
CUR1_REC CUR1%ROWTYPE;BEGIN
open CUR1;
loop
fetch cur1 into CUR1_REC;
exit when cur1%notfound;
INSERT INTO jtest1(
num,
p_name,
re)
VALUES(
CUR1_rec.num,
CUR1_rec.p_name,
CUR1_rec.re);
end loop;
close cur1;
END E83;
————————————————————————————————————
也可以,不过这个恐怕不符合你的本意吧 ^_^