commit; open CUR1; loop FETCH CUR1 INTO CPBH1, SL1; IF (CUR1%FOUND) THEN open CUR2; loop FETCH CUR2 INTO SBBH1, GS10, GS20, GSBH10; IF (CUR2%FOUND) THEN insert into MIS.T_5223 (SBBH, DDBH, GSBH, CPBH, GS1, GS2) values(SBBH1, IN1, GSBH10, CPBH1, GS10, GS20 * SL1); commit; ELSE EXIT; END IF; END LOOP; CLOSE CUR2; ELSE EXIT; END IF; END LOOP; CLOSE CUR1; 这是我的后续的程序(一小段),没什么语法错误,怎么调试的时候,(CUR1%FOUND) 和(CUR2%FOUND) 等条件总不满足,也就是说,游标是空的。我写的那些插入、查询(也就是我的执行部分)语句就跳过去了,循环一进入就从EXIT跳出去了。这真是莫名其妙啊!
OPEN CUR1; LOOP FETCH CUR1 INTO CPBH1, SL1; EXIT WHEN CUR_CUR1%NOTFOUND; END LOOP;
open cur1; fetch CUR1 INTO CPBH1, SL1; while cur1%found loop end loop;
最好游标不要定义,这样试试看。 CURSOR CUR1(C_INT VARCHAR2) IS SELECT distinct CPBH, SL FROM MIS.T_5211 where NO = C_INT order by CPBH;然后用的时候.以参数的形式传递 OPEN CUR1(IN1); LOOP FETCH CUR1 INTO CPBH1, SL1; EXIT WHEN CUR_CUR1%NOTFOUND; END LOOP;
游标在被打开的时候才会去找它的定义,执行它的查询部分。 使用动态游标的效率比较好。 type cur_type is ref cursor; c_mycur cur_type; ... begin open c_mycur for select ... loop exit when ... end loop; close c_mycur; ... end;
fetch CUR1 into 变量
close CUR1
……
open CUR1; loop
FETCH CUR1 INTO CPBH1, SL1; IF (CUR1%FOUND) THEN
open CUR2; loop
FETCH CUR2 INTO SBBH1, GS10, GS20, GSBH10; IF (CUR2%FOUND) THEN
insert into MIS.T_5223
(SBBH, DDBH, GSBH, CPBH, GS1, GS2)
values(SBBH1, IN1, GSBH10, CPBH1, GS10, GS20 * SL1); commit;
ELSE
EXIT;
END IF;
END LOOP; CLOSE CUR2;
ELSE
EXIT;
END IF;
END LOOP; CLOSE CUR1;
这是我的后续的程序(一小段),没什么语法错误,怎么调试的时候,(CUR1%FOUND) 和(CUR2%FOUND) 等条件总不满足,也就是说,游标是空的。我写的那些插入、查询(也就是我的执行部分)语句就跳过去了,循环一进入就从EXIT跳出去了。这真是莫名其妙啊!
LOOP
FETCH CUR1 INTO CPBH1, SL1;
EXIT WHEN CUR_CUR1%NOTFOUND;
END LOOP;
fetch CUR1 INTO CPBH1, SL1;
while cur1%found
loop
end loop;
CURSOR CUR1(C_INT VARCHAR2)
IS
SELECT distinct CPBH, SL
FROM MIS.T_5211
where NO = C_INT
order by CPBH;然后用的时候.以参数的形式传递
OPEN CUR1(IN1);
LOOP
FETCH CUR1 INTO CPBH1, SL1;
EXIT WHEN CUR_CUR1%NOTFOUND;
END LOOP;
使用动态游标的效率比较好。
type cur_type is ref cursor;
c_mycur cur_type;
...
begin
open c_mycur for select ...
loop
exit when
...
end loop;
close c_mycur;
...
end;