今天因为功能需求对以前编写的oracle存储过程做了一个修改：
CREATE OR REPLACE PROCEDURE drivingrankings
(
par_BASE_OIL_WARE in number,
par_PARAMETER_ONE in number,
par_PARAMETER_TOW in number,
par_BASE_WARM_UP in number,
par_PARAMETER_THREE in number,
par_EACH_SPEED_UP_OIL_WARE in number)
AS
avg_mileage number;
abc_rec b_energy_driving_data%rowtype;
avg_consumption number;
avg_acceleration_num number;
avg_acceleration number;
paraA number(16,2);
paraB number(16,2);
paraC number(16,2);
result number(16,2);
cursor abc_cur is select tbox_sn,avg(total_mileage),avg(total_consumption),avg(acceleration_num),owner,model_code
from B_ENERGY_DRIVING_DATA group by owner,TBOX_SN,model_code order by owner,tbox_sn,model_code;
BEGIN
avg_mileage := 0.0;
avg_consumption :=0.0;
avg_acceleration :=0.0;
paraA :=0.0;
paraB :=0.0;
paraC :=0.0;
result :=0.0;
open abc_cur;
Loop
Fetch abc_cur into abc_rec.tbox_sn,avg_mileage,avg_consumption,avg_acceleration_num,abc_rec.owner,abc_rec.model_code;
Exit when abc_cur%notfound;
paraA:=((0.01 * par_BASE_OIL_WARE * avg_mileage / avg_consumption) - par_PARAMETER_ONE) / par_PARAMETER_ONE;
paraB:=(par_PARAMETER_TOW - (avg_consumption - par_BASE_WARM_UP) / 60) / par_PARAMETER_TOW;
paraC:=(par_PARAMETER_THREE - (100 * avg_acceleration * par_EACH_SPEED_UP_OIL_WARE / avg_consumption)) / par_PARAMETER_THREE;
result:=(paraA + paraB + paraC) * 100 / 3;
if(result >= 100 ) then
begin
result:= 99;
end;
end if;
if(result <1) then
begin
result:= 1;
end;
end if;
update b_energy_driving_rankings set environmental_score = result WHERE cruuent_user = abc_rec.owner;
end loop;
Exception
when others then
close abc_cur;
Dbms_Output.put_line(Sqlerrm);
if abc_cur%isopen then
close abc_cur;
end if;
END;
需要将update替换为insert into 操作， 在做insert into操作时需要先对表数据进行删除。在SQL中删除表数据可以用delete 和 TRUNCATE;二者的区别在于
delet删除数据不会释放内存空间，truncate删除数据会释放内存空间。
SQL 删除语法为: delete table_name, truncate table tab1;
结果我傻傻的在存储过程当中也这样写:
BEGIN
avg_mileage := 0.0;
avg_consumption :=0.0;
avg_acceleration :=0.0;
paraA :=0.0;
paraB :=0.0;
paraC :=0.0;
result :=0.0;
truncate table tab1;
如此写法编译的时候并不会报错，但查看存储过程状态为Ivalid(我是小菜,原谅我);网上例子一看才知道需要先定义变量，然后执行CREATE OR REPLACE PROCEDURE drivingrankings()
AS
drop_tab1 varchar2(2048);
BEGIN
drop_tab1 := 'TRUNCATE TABLE b_energy_driving_rankings';
execute immediate drop_tab1;
end；
(看来存储过程还是和直接sql不一样)
第二个问题就是标题问题，资源正忙或资源无效
这个问题是在将update操作修改为insert 操作之后出现的。出现问题时存储过程:
open abc_cur;
Loop
Fetch abc_cur into abc_rec.tbox_sn,avg_mileage,avg_consumption,avg_acceleration_num,abc_rec.owner,abc_rec.model_code;
Exit when abc_cur%notfound;
paraA:=((0.01 * par_BASE_OIL_WARE * avg_mileage / avg_consumption) - par_PARAMETER_ONE) / par_PARAMETER_ONE;
paraB:=(par_PARAMETER_TOW - (avg_consumption - par_BASE_WARM_UP) / 60) / par_PARAMETER_TOW;
paraC:=(par_PARAMETER_THREE - (100 * avg_acceleration * par_EACH_SPEED_UP_OIL_WARE / avg_consumption)) / par_PARAMETER_THREE;
result:=(paraA + paraB + paraC) * 100 / 3;
if(result >= 100 ) then
begin
result:= 99;
end;
end if;
if(result <1) then
begin
result:= 1;
end;
end if;
insert into b_energy_driving_rankings(CRUUENT_USER,MODEL_CODE,ENVIRONMENTAL_SCORE,TBOX_SN)
values(abc_rec.owner,abc_rec.model_code,result,abc_rec.tbox_sn);
end loop;
测试的时候反复调用几次存储过程，就会提示资源正忙，有时候调用一次在调用就会提示资源正忙。查看消息游标无法close。很疑惑，以前update的时候是没问题
应该还是在做insert 操作时出问题了，还是麻烦了度娘一下，才发现远了在insert之后没有 commit;
修改代码如下:
insert into b_energy_driving_rankings(CRUUENT_USER,MODEL_CODE,ENVIRONMENTAL_SCORE,TBOX_SN)
values(abc_rec.owner,abc_rec.model_code,result,abc_rec.tbox_sn);
commit;
就没有在报资源正忙错误。有点疑惑 没有commit为何数据可以保存到数据库，为什么没commit就会出现该错误!