我写的一个存储过程,在游标声明中用到over函数。
但PL/SQL developer老提示编译不能通过:
Compilation errors for PROCEDURE MD_KT.PR_SS_SETTLEMENT_MDKT_MError: PLS-00103: 出现符号 "("在需要下列之一时:
,from
Line: 20
Text: max(clb.scallback_record_id)over(partition by srv.sserv_form_num ) orderid但是,将其中is后面的select 语句单独执行确实可以的。
原代码如下:
Cursor cur_Service_Record
is
select * from
( SELECT distinct srv.sserv_form_num,
srv.dserv_date,
clb.scallback_record_id,
prod.sinverter_sign,
mch.*,
rank()over(partition by srv.sserv_form_num order by clb.scallback_record_id desc) orderid
FROM t_cs_callback_record clb, t_cs_service_record srv, t_cs_machine mch,t_cd_product prod
WHERE clb.sserv_record_id = srv.sserv_record_id
AND srv.sserv_unit_id = '123'
AND srv.smachine_id = mch.smachine_id
AND mch.sprod_id=prod.sprod_id
AND clb.sorg_id = 'orgid'
AND srv.sorg_id = 'orgid'
AND mch.sorg_id = 'orgid'
AND prod.sorg_id='orgid'
AND clb.CFLAG='1'
AND clb.CCNT_CHECK_STAT='Y'
AND Nvl(srv.CSATISFY_SETTLE_FLAG,'N') = 'N'
AND srv.srecord_type = 'B'
AND srv.dserv_date >= sysdate - 200
AND srv.dserv_date < trunc(sysdate+10,'mon')
----AND srv.dserv_date >to_date('2009-01-01','yyyy-mm-dd')
AND rownum<=5
AND exists
(select * from t_cs_callback_item_record rd,t_cd_callback_item itm
where rd.scallback_record_id=clb.scallback_record_id
and rd.scallback_item_id=itm.scallback_item_id
and itm.scallback_item_category_id='02'
and itm.ccallback_flaga='1'
and rd.sorg_id= 'orgid'
and itm.sorg_id='orgid'
)
)
where orderid=1
;
请问错在哪里?
但PL/SQL developer老提示编译不能通过:
Compilation errors for PROCEDURE MD_KT.PR_SS_SETTLEMENT_MDKT_MError: PLS-00103: 出现符号 "("在需要下列之一时:
,from
Line: 20
Text: max(clb.scallback_record_id)over(partition by srv.sserv_form_num ) orderid但是,将其中is后面的select 语句单独执行确实可以的。
原代码如下:
Cursor cur_Service_Record
is
select * from
( SELECT distinct srv.sserv_form_num,
srv.dserv_date,
clb.scallback_record_id,
prod.sinverter_sign,
mch.*,
rank()over(partition by srv.sserv_form_num order by clb.scallback_record_id desc) orderid
FROM t_cs_callback_record clb, t_cs_service_record srv, t_cs_machine mch,t_cd_product prod
WHERE clb.sserv_record_id = srv.sserv_record_id
AND srv.sserv_unit_id = '123'
AND srv.smachine_id = mch.smachine_id
AND mch.sprod_id=prod.sprod_id
AND clb.sorg_id = 'orgid'
AND srv.sorg_id = 'orgid'
AND mch.sorg_id = 'orgid'
AND prod.sorg_id='orgid'
AND clb.CFLAG='1'
AND clb.CCNT_CHECK_STAT='Y'
AND Nvl(srv.CSATISFY_SETTLE_FLAG,'N') = 'N'
AND srv.srecord_type = 'B'
AND srv.dserv_date >= sysdate - 200
AND srv.dserv_date < trunc(sysdate+10,'mon')
----AND srv.dserv_date >to_date('2009-01-01','yyyy-mm-dd')
AND rownum<=5
AND exists
(select * from t_cs_callback_item_record rd,t_cd_callback_item itm
where rd.scallback_record_id=clb.scallback_record_id
and rd.scallback_item_id=itm.scallback_item_id
and itm.scallback_item_category_id='02'
and itm.ccallback_flaga='1'
and rd.sorg_id= 'orgid'
and itm.sorg_id='orgid'
)
)
where orderid=1
;
请问错在哪里?
好象只看出rank()和over之间没有空格造成的错误
另外,楼主把这条带rank()over的select子句单独拿出来执行一下了吗,可以成功吗?
另外,楼主把这条带rank()over的select子句单独拿出来执行一下了吗,可以成功吗?
create table t
(
name varchar(10),
year int
)
insert into t values('张三',2001);
insert into t values('张三',2002);
insert into t values('张三',2003);
insert into t values('张三',2004);
insert into t values('张三',2006);
insert into t values('张三',2007);
insert into t values('李四',2002);
insert into t values('李四',2003);
select * from tdeclare
v_name varchar(20);
v_maxyear int;
cursor c is
select name, max(year) maxyear
from (select name, count(*) year
from (select name,year,
row_number() over(partition by name order by year) rn
from t
group by name,year
)
group by year-rn,name)
group by name;
begin
open c;
loop
fetch c into v_name,v_maxyear;
if c%found then
dbms_output.put_line('v_name='||v_name);
else
exit;
end if;
end loop;
close c;
end;
执行结果:
v_name李四
v_name张三PL/SQL 过程已成功完成。
declare
v_name varchar(20);
v_maxyear int;
cursor c is
select name, max(year) maxyear
from (select name, count(*) year
from (select name,year,
rank() over(partition by name order by year) rn
from t
group by name,year
)
group by year-rn,name)
group by name;
begin
open c;
loop
fetch c into v_name,v_maxyear;
if c%found then
dbms_output.put_line('v_name='||v_name);
else
exit;
end if;
end loop;
close c;
end;
执行结果:
v_name李四
v_name张三 PL/SQL 过程已成功完成。