SQL调优问题（急急急，十万火急）

[foreverxuqiang]

各位大神们，小弟哭求神人们指点下面哪个函数欠佳可以进行调优啊？这是工作要求的，要是今天在弄不出来工作难保啊，求求各位神人了。

create or replace package body INVENTORY_CONTROL as
function MODIFY_INV_GRP(
O_ERR_MESSAGE OUT FROG.VAR_4000%TYPE,
I_INV_GRP_SEQ IN INVENTORY_GRP.INV_GRP_SEQ%TYPE,
I_INV_GRP_NAME IN INVENTORY_GRP.INV_GRP_NAME%TYPE,
I_ALERT_TYPE IN INVENTORY_GRP.ALERT_TYPE%TYPE,
I_STATUS IN INVENTORY_GRP.STATUS%TYPE,
I_USER_NUMBER IN WUSER.USER_NUMBER%TYPE,
I_CUS_NUMBER IN WCUSTOMER.CUS_NUMBER%TYPE,
I_SECURITY_PRIORITY IN INVENTORY_GRP.SECURITY_PRIORITY%TYPE,
I_OPEN_REPO_ENABLE IN INVENTORY_GRP.OPEN_REPO_ENABLE%TYPE,
I_OPEN_REPO_PRIORITY IN INVENTORY_GRP.OPEN_REPO_PRIORITY%TYPE,
I_KEEP_REPO_ENABLE IN INVENTORY_GRP.KEEP_REPO_ENABLE%TYPE,
I_KEEP_REPO_PRIORITY IN INVENTORY_GRP.KEEP_REPO_PRIORITY%TYPE,
I_OP_TYPE IN FROG.FLAG%TYPE -- opration type, A-add, M-modify, D-delete
) return FROG.ERROR_CODE%TYPE is
-- add by arnes, 20111102
max_inv_grp_seq INVENTORY_GRP.INV_GRP_SEQ%TYPE;
cnt number;
begin
O_ERR_MESSAGE := NULL;
select max(g.inv_grp_seq)+1 into max_inv_grp_seq
from inventory_grp g;
if max_inv_grp_seq is null then
max_inv_grp_seq := 1;
end if;

case I_OP_TYPE
when 'A' then -- add inventory group
select count(*) into cnt
from inventory_grp p
where p.cus_number = I_CUS_NUMBER
and p.status = 'U';
if cnt >= 1 and I_STATUS = 'U' then
O_ERR_MESSAGE := '通用群組多于1个.';
return 1;
end if;
select count(*) into cnt
from inventory_grp p
where p.cus_number = I_CUS_NUMBER
and p.inv_grp_name = I_INV_GRP_NAME;
if cnt >= 1 then
O_ERR_MESSAGE := '库存群組重名.';
return 2;
end if;
if trim(I_INV_GRP_NAME) is null then
O_ERR_MESSAGE := '库存群組名称不能为空';
return 5;
end if;

insert into inventory_grp(inv_grp_seq, user_number, cus_number, inv_grp_name,
parent_grp, init_date, alert_type, open_repo_enable, open_repo_priority,
modify_user, modify_time, status, note, security_priority, keep_repo_enable, keep_repo_priority)
values(max_inv_grp_seq, I_USER_NUMBER, I_CUS_NUMBER, I_INV_GRP_NAME,
max_inv_grp_seq, sysdate, I_ALERT_TYPE, I_OPEN_REPO_ENABLE,
I_OPEN_REPO_PRIORITY, I_USER_NUMBER, sysdate, I_STATUS, null, I_SECURITY_PRIORITY,
I_KEEP_REPO_ENABLE, I_KEEP_REPO_PRIORITY);

insert into inventory_rule(INV_GRP_SEQ, IVD, IVP, IVT_BO, IVT_CR_B, IVT_CR_S,
IVT_OR_B, IVT_OR_S, IVT_UW_B, IVT_UW_S, NEED_REVERSE, IVT_KR_B, IVT_KR_S)
values(max_inv_grp_seq, 'IVD_BO_B', 1, 1, 0, 0, 0, 0, 0, 0, 'N', 0, 0);
insert into inventory_rule(INV_GRP_SEQ, IVD, IVP, IVT_BO, IVT_CR_B, IVT_CR_S,
IVT_OR_B, IVT_OR_S, IVT_UW_B, IVT_UW_S, NEED_REVERSE, IVT_KR_B, IVT_KR_S)
values(max_inv_grp_seq, 'IVD_CR_S', 0, 0, 0, 0, 0, 0, 0, 0, 'Y', 0, 0);
insert into inventory_rule(INV_GRP_SEQ, IVD, IVP, IVT_BO, IVT_CR_B, IVT_CR_S,
IVT_OR_B, IVT_OR_S, IVT_UW_B, IVT_UW_S, NEED_REVERSE, IVT_KR_B, IVT_KR_S)
values(max_inv_grp_seq, 'IVD_OR_S', 1, 0, 0, 0, 0, 1, 0, 0, 'N', 0, 0);
insert into inventory_rule(INV_GRP_SEQ, IVD, IVP, IVT_BO, IVT_CR_B, IVT_CR_S,
IVT_OR_B, IVT_OR_S, IVT_UW_B, IVT_UW_S, NEED_REVERSE, IVT_KR_B, IVT_KR_S)
values(max_inv_grp_seq, 'IVD_KR_S', 1, 0, 0, 0, 0, 0, 0, 0, 'N', 0, 1);
insert into inventory_rule(INV_GRP_SEQ, IVD, IVP, IVT_BO, IVT_CR_B, IVT_CR_S,
IVT_OR_B, IVT_OR_S, IVT_UW_B, IVT_UW_S, NEED_REVERSE, IVT_KR_B, IVT_KR_S)
values(max_inv_grp_seq, 'IVD_UW_B', 0, 0, 0, 0, 0, 0, 0, 0, 'Y', 0, 0);
insert into inventory_rule(INV_GRP_SEQ, IVD, IVP, IVT_BO, IVT_CR_B, IVT_CR_S,
IVT_OR_B, IVT_OR_S, IVT_UW_B, IVT_UW_S, NEED_REVERSE, IVT_KR_B, IVT_KR_S)
values(max_inv_grp_seq, 'IVD_UW_S', 0, 0, 0, 0, 0, 0, 0, 0, 'Y', 0, 0);

cnt := ins_inv_rule(O_ERR_MESSAGE, max_inv_grp_seq, I_SECURITY_PRIORITY, 'IVT_BO');
if I_OPEN_REPO_ENABLE = 'Y' then
if I_OPEN_REPO_PRIORITY between 1 and 9 then
cnt := ins_inv_rule(O_ERR_MESSAGE, max_inv_grp_seq, I_OPEN_REPO_PRIORITY, 'IVT_OR_S');
else
O_ERR_MESSAGE := '未定义库存优先级.';
rollback;
return 6;
end if;
end if;
if I_KEEP_REPO_ENABLE = 'Y' then
if I_KEEP_REPO_PRIORITY between 1 and 9 then
cnt := ins_inv_rule(O_ERR_MESSAGE, max_inv_grp_seq, I_KEEP_REPO_PRIORITY, 'IVT_KR_S');
else
O_ERR_MESSAGE := '未定义库存优先级.';
rollback;
return 6;
end if;
end if;

when 'M' then -- modify inventory group
select count(*) into cnt
from inventory_grp p
where p.cus_number = I_CUS_NUMBER
and p.status = 'U';
if cnt >= 2 and I_STATUS = 'U' then
O_ERR_MESSAGE := '通用群組多于1.';
return 1;
end if;
select count(*) into cnt
from inventory_grp p
where p.cus_number = I_CUS_NUMBER
and p.inv_grp_seq = I_INV_GRP_SEQ;
if cnt = 0 then
O_ERR_MESSAGE := '找不到对应库存群組.';
return 3;
end if;
select count(*) into cnt
from inventory_grp g
where g.cus_number = I_CUS_NUMBER
and g.inv_grp_name = I_INV_GRP_NAME
and g.inv_grp_seq != I_INV_GRP_SEQ;
if cnt >=1 then
O_ERR_MESSAGE :='库存群组重名！';
return 2;
end if;
if trim(I_INV_GRP_NAME) is null then
O_ERR_MESSAGE := '库存群組名称不能為空';
return 5;
end if;

update inventory_grp g set g.inv_grp_name = I_INV_GRP_NAME,
g.alert_type = I_ALERT_TYPE,
g.status = I_STATUS,
g.security_priority = I_SECURITY_PRIORITY,
g.open_repo_enable = I_OPEN_REPO_ENABLE,
g.open_repo_priority = I_OPEN_REPO_PRIORITY,
g.keep_repo_enable = I_KEEP_REPO_ENABLE,
g.keep_repo_priority = I_KEEP_REPO_PRIORITY,
g.modify_user = I_USER_NUMBER,
g.modify_time = sysdate
where g.inv_grp_seq = I_INV_GRP_SEQ;

delete from inventory_rule r where r.inv_grp_seq = I_INV_GRP_SEQ;
insert into inventory_rule(INV_GRP_SEQ, IVD, IVP, IVT_BO, IVT_CR_B, IVT_CR_S,
IVT_OR_B, IVT_OR_S, IVT_UW_B, IVT_UW_S, NEED_REVERSE, IVT_KR_B, IVT_KR_S)
values(I_INV_GRP_SEQ, 'IVD_BO_B', 1, 1, 0, 0, 0, 0, 0, 0, 'N', 0, 0);
insert into inventory_rule(INV_GRP_SEQ, IVD, IVP, IVT_BO, IVT_CR_B, IVT_CR_S,
IVT_OR_B, IVT_OR_S, IVT_UW_B, IVT_UW_S, NEED_REVERSE, IVT_KR_B, IVT_KR_S)
values(I_INV_GRP_SEQ, 'IVD_CR_S', 0, 0, 0, 0, 0, 0, 0, 0, 'Y', 0, 0);
insert into inventory_rule(INV_GRP_SEQ, IVD, IVP, IVT_BO, IVT_CR_B, IVT_CR_S,
IVT_OR_B, IVT_OR_S, IVT_UW_B, IVT_UW_S, NEED_REVERSE, IVT_KR_B, IVT_KR_S)
values(I_INV_GRP_SEQ, 'IVD_OR_S', 1, 0, 0, 0, 0, 1, 0, 0, 'N', 0, 0);
insert into inventory_rule(INV_GRP_SEQ, IVD, IVP, IVT_BO, IVT_CR_B, IVT_CR_S,
IVT_OR_B, IVT_OR_S, IVT_UW_B, IVT_UW_S, NEED_REVERSE, IVT_KR_B, IVT_KR_S)
values(I_INV_GRP_SEQ, 'IVD_KR_S', 1, 0, 0, 0, 0, 0, 0, 0, 'N', 0, 1);
insert into inventory_rule(INV_GRP_SEQ, IVD, IVP, IVT_BO, IVT_CR_B, IVT_CR_S,
IVT_OR_B, IVT_OR_S, IVT_UW_B, IVT_UW_S, NEED_REVERSE, IVT_KR_B, IVT_KR_S)
values(I_INV_GRP_SEQ, 'IVD_UW_B', 0, 0, 0, 0, 0, 0, 0, 0, 'Y', 0, 0);
insert into inventory_rule(INV_GRP_SEQ, IVD, IVP, IVT_BO, IVT_CR_B, IVT_CR_S,
IVT_OR_B, IVT_OR_S, IVT_UW_B, IVT_UW_S, NEED_REVERSE, IVT_KR_B, IVT_KR_S)
values(I_INV_GRP_SEQ, 'IVD_UW_S', 0, 0, 0, 0, 0, 0, 0, 0, 'Y', 0, 0);

cnt := ins_inv_rule(O_ERR_MESSAGE, I_INV_GRP_SEQ, I_SECURITY_PRIORITY, 'IVT_BO');
if I_OPEN_REPO_ENABLE = 'Y' then
if I_OPEN_REPO_PRIORITY between 1 and 9 then
cnt := ins_inv_rule(O_ERR_MESSAGE, I_INV_GRP_SEQ, I_OPEN_REPO_PRIORITY, 'IVT_OR_S');
else
O_ERR_MESSAGE := '未定义库存优先级.';
rollback;
return 6;
end if;
end if;
if I_KEEP_REPO_ENABLE = 'Y' then
if I_KEEP_REPO_PRIORITY between 1 and 9 then
cnt := ins_inv_rule(O_ERR_MESSAGE, I_INV_GRP_SEQ, I_KEEP_REPO_PRIORITY, 'IVT_KR_S');
else
O_ERR_MESSAGE := '未定义库存优先级.';
rollback;
return 6;
end if;
end if;

when 'D' then -- delete inventory group
select count(*) into cnt
from inventory_grp p
where p.cus_number = I_CUS_NUMBER
and p.inv_grp_seq = I_INV_GRP_SEQ;
if cnt = 0 then
O_ERR_MESSAGE := '找不到对应库存群組.';
rollback;
return 3;
end if;
select count(*) into cnt
from inventory_grp p
where p.cus_number = I_CUS_NUMBER
and p.inv_grp_seq = I_INV_GRP_SEQ
and p.status = 'U';
if cnt = 1 then
O_ERR_MESSAGE := '不可删除通用库存群組.';
rollback;
return 4;
end if;

delete from inventory_grp g
where g.inv_grp_seq = I_INV_GRP_SEQ;

delete from inventory_rule r where r.inv_grp_seq = I_INV_GRP_SEQ;
delete from inventory_contain c where c.inv_grp_seq = I_INV_GRP_SEQ;

else
rollback;
O_ERR_MESSAGE := '未定义操作类别.';
return 10;

end case;

O_ERR_MESSAGE := 'SUCCESS';
return 0;
exception
when others then
rollback;
O_ERR_MESSAGE := '异常错误！.';
return 9999;

return 0;
end MODIFY_INV_GRP;

[u010412956]

效率优化，还是代码优化？ 说明白是哪方面的优化

[linwaterbin]

代码看得头昏

[rabitsky]

看得头都晕了

[善若止水]

引用 2 楼 linwaterbin 的回复:

代码看得头昏

同感

[dyf0130]

纯力气活，有点头昏 先把不需要 select 就可以 return 的写在前面 比如 if trim(I_INV_GRP_NAME) is null then ----这个写的也有问题吧 I_INV_GRP_NAME is null O_ERR_MESSAGE := '库存群組名称不能为空'; return 5; end if;

[cyf108]

使用truncate 代替 delete等，但注意truncate 会让数据不能恢复

[gingerkang]

~~失业了没？