存储过程如下:
CREATE OR REPLACE PROCEDURE p_inherit
begin
insert into t_provision_user (pro_id,
service_id,
feepseudocode,
destpseudocode,
status,
provisiontime,
fee_flag)
selectseq_pro_user.nextval,
b.service_id,
b.feepseudocode,
b.destpseudocode,
b.status,
sysdate,
b.fee_flag
from
(select a.*,
ROW_NUMBER() OVER (PARTITION BY a.feepseudocode,a.service_id ORDER BY a.provisiontime desc) AS ranks
from t_provision_user a
where to_char(a.provisiontime,'MM')=to_char(add_months(sysdate,-1),'MM')
) b
where b.ranks=1 and b.status=1;
commit;
end p_inherit;
在8i中编译如下存储过程报错:
Compilation errors for PROCEDURE WAPLAT.P_INHERIT
Error: PLS-00103: 出现符号 "("在需要下列之一时:
,from
Line: 11
Text: ROW_NUMBER() OVER (PARTITION BY a.feepseudocode,a.service_id ORDER BY a.provisiontime desc) AS ranks
但是我在9iR2的环境下编译却没有问题,在8i中单独执行那个insert语句也没有问题
在8i中是不是不支持在存储过程中使用分析函数?
|