oracle存储过程问题，急急急！！！

[dragonerfish]

create or replace procedure calculate_agent_js_fee()
is
f_num number;
j number;
v_actfee agent_js_fee_table.actfee%type;
r_num number;
r number;
v_level_fee agent_js_rate.level_fee%type;
v_rate agent_js_rate.rate%type;
v_min_level_fee agent_js_rate.level_fee%type;

begin

create sequence table_num increment by 1 start with 1;
create table agent_js_fee_temp_table
as
select
table_num.nextval table_num,

agent_js_fee_table.s_agent,
agent_js_fee_table.agentname,
agent_js_fee_table.actfee,
agent_js_fee_table.fee_level,
agent_js_fee_table.js_rate,
agent_js_fee_table.js_fee,
agent_js_fee_table.p_mk,
agent_js_fee_table.cntperiodid

from (select s_agent,
agentname,
actfee,
fee_level,
js_rate,
js_fee,
p_mk,
cntperiodid
from agent_js_fee_table
)agent_js_fee_table
;

create index agent_js_fee_temp_table$s_agent
on agent_js_fee_temp_table(s_agent);

create index agent_js_fee_temp_table$actfee
on agent_js_fee_temp_table(actfee);

create index agent_js_fee_temp_table$cntperiodid
on agent_js_fee_temp_table(cntperiodid);

create index agent_js_fee_temp_table$fee_level
on agent_js_fee_temp_table(fee_level);

create index agent_js_fee_temp_table$js_rate
on agent_js_fee_temp_table(js_rate);

create index agent_js_fee_temp_table$js_fee
on agent_js_fee_temp_table(js_fee);

create index agent_js_fee_temp_table$p_mk
on agent_js_fee_temp_table(p_mk);

select max(table_num) into f_num
from agent_js_fee_temp_table;

create sequence rate_num increment by 1 start with 1;
create table agent_js_rate_temp
as
select
rate_num.nextval rate_num,
agent_js_rate.rate_id,
agent_js_rate.level_fee,
agent_js_rate.rate,
agent_js_rate.create_time

from (select rate_id,
level_fee,
rate,
create_time
from agent_js_rate
)agent_js_rate
;

select max(rate_num) into r_num
from agent_js_rate_temp;

<<js_fee>>
for j in 1..f_num loop
select agent_js_fee_table.actfee into v_actfee
from agent_js_fee_table,agent_js_fee_temp_table
where agent_js_fee_temp_table.table_num=j
and agent_js_fee_table.v_actfee=agent_js_fee_temp_table.v_actfee;

<<js_rate>>
for r in 1..r_num loop
select agent_js_rate.level_fee into v_level_fee
from agent_js_rate,agent_js_rate_temp
where agent_js_rate_temp.rate_num=r
and agent_js_rate.v_level_fee=agent_js_rate_temp.v_level_fee;

select agent_js_rate.rate into v_rate
from agent_js_rate,agent_js_rate_temp
where agent_js_rate_temp.rate_num=r
and agent_js_rate.v_level_fee=agent_js_rate_temp.v_level_fee;


select min(level_fee) into r_min_level_fee
from agent_js_rate;

if v_actfee<v_level_fee then
if v_actfee<v_min_level_fee then
update agent_js_fee_table
set js_rate=0
where agent_js_fee_temp_table.table_num=j
and agent_js_fee_table.v_actfee=agent_js_fee_temp_table.v_actfee;

update agent_js_fee_table
set js_fee=0
where agent_js_fee_temp_table.table_num=j
and agent_js_fee_table.v_actfee=agent_js_fee_temp_table.v_actfee;

update agent_js_fee_table
set fee_level=v_level_fee
where agent_js_fee_temp_table.table_num=j
and agent_js_fee_table.v_actfee=agent_js_fee_temp_table.v_actfee;
exit;
else
null;
end if;
else
update agent_js_fee_table
set js_rate=v_rate
where agent_js_fee_temp_table.table_num=j
and agent_js_fee_table.v_actfee=agent_js_fee_temp_table.v_actfee;

update agent_js_fee_table
set js_fee=actfee*v_rate
where agent_js_fee_temp_table.table_num=j
and agent_js_fee_table.v_actfee=agent_js_fee_temp_table.v_actfee;

update agent_js_fee_table
set fee_level=v_level_fee
where agent_js_fee_temp_table.table_num=j
and agent_js_fee_table.v_actfee=agent_js_fee_temp_table.v_actfee;

goto js_fee;
end if;
end loop;
end loop;

drop index agent_js_fee_table$s_agent;

drop index agent_js_fee_table$actfee;

drop index agent_js_fee_table$cntperiodid;

drop index agent_js_fee_table$fee_level;

drop index agent_js_fee_table$js_rate;

drop index agent_js_fee_table$js_fee;

drop index agent_js_fee_table$p_mk;

drop sequence table_num;

drop table agent_js_fee_temp_table;

drop sequence rate_num;

drop table agent_js_rate_temp;

commit;
end;


提示如下信息：MGR-00072: Warning: PROCEDURE CALCULATE_AGENT_JS_FEE created with compilation errors.

我用的是oracle8.0.6

不知是何原因？？？

[qianby]

up

[dragonerfish]

to KingSunSha(弱水三千):
对你的第二个update语句不是很明白，
select rate, rate * actfee, level_fee
from agent_js_rate y,

但agent_js_fee_table 中并没有actfee字段，希望再解释一下！！！

[KingSunSha]

是我写错了, 应该是:
select rate, rate * X.actfee, level_fee
from agent_js_rate y,

[dragonerfish]

to KingSunSha(弱水三千):
update的逻辑如下：
1.在agent_js_rate表中有三列rate_id,level_fee,rate,分别表示为费率编号，费率对应的最低费用，费率,此表按level_fee降序排列；
2.将agent_js_fee_table中的actfee与agent_js_rate表中的level_fee比较：
(1).actfee>或=level_fee,取level_fee对应的rate,agent_js_fee_table.js_rate=rate,agent_js_fee_table.fee_level=level_fee,agent_js_fee_table.js_fee=actfee*js_rate;
(2).actfee<level_fee,继续判断；
(3).actfee<min(level_fee),agent_js_fee_table.js_rate=0,agent_js_fee_table.fee_level=0,agent_js_fee_table.js_fee=0;

[KingSunSha]

如果我的理解不错的话,我想你的要求可以简化成这样吧:
1. 如果actfee<min(level_fee), 置0
2. 按照level_fee降序排列, 找到最后一个actfee>或=level_fee的纪录, 更新agent_js_fee_table表, 因为在这以前的所有更新会被覆盖, 根本没用

如果是这样的话, 一句SQL就行了(假定level_fee是agent_js_rate表的主键):
update agent_js_fee_table x
set (js_rate, js_fee, fee_level) =
(select decode(sign(x.actfee - minfee) -1, 0, rate),
decode(sign(x.actfee - minfee) -1, 0, rate * actfee),
decode(sign(x.actfee - minfee) -1, 0, level_fee)
from agent_js_rate y,
(select min(level_fee) minfee from agent_js_rate) z
where level_fee in (select min(level_fee)
from agent_js_rate
where x.actfee >= level_fee)
or (x.actfee < minfee
and level_fee in (select min(level_fee)
from agent_js_rate))
);

如果你嫌这句SQL太难看懂, 那么写成下面这样也可以:
create or replace procedure calculate_agent_js_fee
is
minfee agent_js_rate.level_fee%rowtype;
begin
--取出MINFEE
select min(level_fee) into minfee
from agent_js_rate;
--对actfee < minfee的纪录置0
update agent_js_fee_table
set js_rate = 0,
js_fee = 0,
fee_level = 0
where actfee < minfee;
--对其他的纪录取出最后一条actfee >= level_fee做对应的更新
update agent_js_fee_table x
set (js_rate, js_fee, fee_level) =
(select rate, rate * actfee, level_fee
from agent_js_rate y,
where level_fee in (select min(level_fee)
from agent_js_rate
where x.actfee >= level_fee)
);
end calculate_agent_js_fee;

[dragonerfish]

to KingSunSha(弱水三千):
对你的第二个update语句不是很明白，
select min(level_fee)
from agent_js_rate
where x.actfee >= level_fee)

但agent_js_rate中并没有actfee字段，另外min(level_fee)此句是否应改为max(level_fee)希望再解释一下！！！

[KingSunSha]

你的逻辑不是说agent_js_rate按照level_fee逆序排列吗? 那么必然是最小的一个level_fee对应的纪录才被真正用于更新(当然前提是x.actfee >= level_fee).
在第2句UPDATE中有个错误, 多了一个','号,所以你不能用吧. 现已修正如下:

update agent_js_fee_table x
set (js_rate, js_fee, fee_level) =
(select rate, rate * actfee, level_fee
from agent_js_rate y
where level_fee in (select min(level_fee)
from agent_js_rate
where x.actfee >= level_fee)
);

[dragonerfish]

to KingSunSha(弱水三千):
对你的第二个update语句不是很明白，
where level_fee in (select min(level_fee)
from agent_js_rate
where x.actfee >= level_fee)

但agent_js_rate中并没有actfee字段，希望再解释一下！！！

[KingSunSha]

我用的不是actfee而是x.actfee, 而x已经在update agent_js_fee_table x一句中指定为agent_js_fee_table. 在agent_js_fee_table表中有这个字段吧

[dragonerfish]

to KingSunSha(弱水三千):
用此过程报相同的错；即

提示如下信息：MGR-00072: Warning: PROCEDURE CALCULATE_AGENT_JS_FEE created with compilation errors.

[KingSunSha]

你的错误信息是指生成过程的时候产生编译错误，无法分析错误的具体原因。
你难道用sql plus来调试过程吗？也太难了一点吧？你找一个可以在线调试的工具看看具体的错误在哪里。说不定是哪个字段写错了

我这边实在看不出有什么错

[dragonerfish]

to KingSunSha(弱水三千):
用此过程报相同的错；即

提示如下信息：MGR-00072: Warning: PROCEDURE CALCULATE_AGENT_JS_FEE created with compilation errors.

show err命令显示错误如下：

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/10 PLS-00310: 使用 %ROWTYPE 属性时，'AGENT_JS_RATE.LEVEL_FEE'
必须命名表，游标或游标变量

3/10 PL/SQL: Item ignored
6/3 PL/SQL: SQL Statement ignored
6/30 PLS-00320: 此表达式的类型说明不完整或格式错误
9/3 PL/SQL: SQL Statement ignored
13/18 PLS-00320: 此表达式的类型说明不完整或格式错误

[KingSunSha]

是我太粗心了，把rowtype改成type就行了。对不起，再试一次吧。
create or replace procedure calculate_agent_js_fee
is
minfee agent_js_rate.level_fee%type;
begin
--取出MINFEE
select min(level_fee) into minfee
from agent_js_rate;
--对actfee < minfee的纪录置0
update agent_js_fee_table
set js_rate = 0,
js_fee = 0,
fee_level = 0
where actfee < minfee;
--对其他的纪录取出最后一条actfee >= level_fee做对应的更新
update agent_js_fee_table x
set (js_rate, js_fee, fee_level) =
(select rate, rate * actfee, level_fee
from agent_js_rate y
where level_fee in (select min(level_fee)
from agent_js_rate
where x.actfee >= level_fee)
);
end calculate_agent_js_fee;

说老实话，你的sql经验不是很多。

[wilddragon]

期待答案

[dragonerfish]

to KingSunSha(弱水三千):
1.在agent_js_rate表中有三列rate_id,level_fee,rate,分别表示为费率编号，费率对应的最低费用，费率,此表按level_fee升序排列；
2.程序修改如下：

create or replace procedure calculate_agent_js_test
is
minfee agent_js_rate.level_fee%type;
begin
--取出MINFEE
select min(level_fee) into minfee
from agent_js_rate;
--对actfee < minfee的纪录置0
update agent_js_fee_test
set js_rate=0,
js_fee=0,
fee_level=0
where actfee < minfee*100;
commit;
--对其他的纪录取出最后一条actfee >= level_fee做对应的更新
update agent_js_fee_test x
set (js_rate, js_fee, fee_level) =
(select rate, rate * actfee, level_fee*100
from agent_js_rate y
where level_fee in (select max(agent_js_rate.level_fee)
from agent_js_rate,agent_js_fee_table x
where x.actfee >= agent_js_rate.level_fee*100)
);
commit;
end calculate_agent_js_test;



但第一个update却不起作用，其相关字段的0值未写入库中。

[wilddragon]

期待答案

[KingSunSha]

COMMIT不需要。是第二个UPDATE把第一个UPDATE冲掉了
create or replace procedure calculate_agent_js_test
is
minfee agent_js_rate.level_fee%type;
begin
--取出MINFEE
select min(level_fee) into minfee
from agent_js_rate;
--对actfee < minfee的纪录置0
update agent_js_fee_test
set js_rate=0,
js_fee=0,
fee_level=0
where actfee < minfee*100;

--对其他的纪录取出最后一条actfee >= level_fee做对应的更新
update agent_js_fee_test x
set (js_rate, js_fee, fee_level) =
(select rate, rate * actfee, level_fee*100
from agent_js_rate y
where level_fee in (select max(agent_js_rate.level_fee)
from agent_js_rate,agent_js_fee_table x
where x.actfee >= agent_js_rate.level_fee*100)
)
where actfee >= minfee*100;
end calculate_agent_js_test;

另外，到底是actfee < minfee还是actfee < minfee*100？

[dragonerfish]

1.2个commit是分别对2个update的递交，如不写则库中没数据；
2.是actfee < minfee*100；
3.agent_js_rate表中有2个费率：
rate_id level_fee rate
1 5000 0.2
2 10000 0.3
但现在发现所有actfee>5000的记录都选用了0.3的费率；

[KingSunSha]

1. 执行完存储过程以后在用COMMIT或者结束这个会话,ORACLE会自动COMMIT
3. 看不到你对RATE有任何逻辑判断, 所以以LEVEL_FEE排序当然会选用LEVEL_FEE最大的一个RATE(0.3). 请再次说明怎样选用RATE和LEVEL_FEE

[dragonerfish]

to KingSunSha(弱水三千):
问题还没有解决，但还是要谢谢你！！！希望以后多指教！分数马上会结。