Question

oracle存储过程中定义游标

我想在begin之后定义游标，一直会报错，把定义放在begin之前就没事。该如何在begin之后定义啊？
create or replace procedure SP_TEST as
title1_value varchar2(30);
number1_value integer:=0;
begin
declare c_1 cursor for SELECT city_name,COUNT(city_name) FROM view_alarm_KZ1 GROUP BY city_name;
open c_1;
LOOP
FETCH c_1 INTO title1_value,number1_value;
EXIT WHEN c_1%NOTFOUND;
INSERT INTO TFA_ALARM_COUNTER_KZ(TITLE1,Collection_Time,Kz1) VALUES(title1_value,sysdate,number1_value);
END LOOP;
CLOSE c_1;
COMMIT;
end;
我是想动态定义游标

Answer 1

你为什么要放到begin之后呢？正确写法应该是：

create or replace procedure SP_TEST as
title1_value varchar2(30);
number1_value integer:=0;
CURSOR c_1 IS SELECT city_name,COUNT(city_name) FROM view_alarm_KZ1 GROUP BY city_name;
begin
open c_1;
LOOP
FETCH c_1 INTO title1_value,number1_value;
EXIT WHEN c_1%NOTFOUND;
INSERT INTO TFA_ALARM_COUNTER_KZ(TITLE1,Collection_Time,Kz1) VALUES(title1_value,sysdate,number1_value);
END LOOP;
CLOSE c_1;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
end;

Answer 2

放到begin里面不要declare，在过程和函数里面申明变量貌似不要declare,否则会报错

Answer 3

游标的声明要放在DECLARE中的