oracle双层游标问题
CREATE OR REPLACE PROCEDURE q_MainPage_SpecialDrug AS
BEGIN
DECLARE
beginDate varchar(50);
endDate varchar(50);
CommonName varchar(50);
thisfdaid varchar(50);
thisfdaid2 varchar(50);
tempfdaids varchar2(8000);
set beginDare:=to_char(sysdate,'yyyy-mm-dd');
set beginDare:=to_char(sysdate,'yyyy-mm-dd')||'23:59:59';
--定义游标
CURSOR cr_cursor IS
select CommonName from PointRegulationSet;
BEGIN
open cr_cursor;
fetch cr_cursor into CommonName;
WHILE(cr_cursor%found) LOOP
begin
--获取绑定的企业串
EXECUTE IMMEDIATE 'SELECT Fdaids FROM PointRegulationSet WHERE CommonName='||to_char(CommonName)
INTO tempfdaids;
EXECUTE IMMEDIATE 'select * from table(split('||to_char(tempfdaids) ||','',''))'
INTO thisfdaid2;
CURSOR fda_cursor IS
thisfdaid2; BEGIN
OPEN fda_cursor;
fetch fda_cursor into thisfdaid;
WHILE(fda_cursor%FOUND) LOOP
BEGIN
insert into SpecialDrugStatistic(cEnterpriseCode,
cName,
fdaid,
commonName,
kc_sl,
xs_sl,
ys_sl,
yh_sl,
countDate)
select a.cEnterpriseCode,a.cName,a.FDAID, to_char(CommonName) commonName,a.kc,a.xs,a.ys,a.yh,beginDate countDate
from
(select a.cEnterpriseCode,cname,f.FDAID,isnull(kcsl,0) kc,isnull(xssl,0) xs,isnull(yssl,0) ys,isnull(yhsl,0) yh
from
(select a.cEnterpriseCode,kcsl,xssl,yssl,yhsl
from
(select f.cEnterpriseCode from typkc t join fdaenterprise f on t.cEnterpriseCode=f.cEnterpriseCode
where 1=1 AND sptym like '%' to_char(CommonName) '%' AND sp_datetime >= to_char(beginDate) AND sp_datetime <= to_char(endDate) AND f.fdaid = to_char(thisfdaid)
union
select f.cEnterpriseCode from txsjl t join fdaenterprise f on t.cEnterpriseCode=f.cEnterpriseCode
where 1=1 AND xs_tym like '%' to_char(CommonName) '%' AND Xs_datetime >= to_char(beginDate) AND Xs_datetime <= to_char(endDate) AND f.fdaid = to_char(thisfdaid)
union
select f.cEnterpriseCode from tysjl t join fdaenterprise f on t.cEnterpriseCode=f.cEnterpriseCode
where 1=1 AND ys_tym like '%' to_char(CommonName) '%' AND Ys_datetime >=to_char(beginDate) AND Ys_datetime <= to_char(endDate) AND f.fdaid = to_char(thisfdaid)
union
select f.cEnterpriseCode from tyhjl t join fdaenterprise f on t.cEnterpriseCode=f.cEnterpriseCode
where 1=1 AND yh_tym like '%' to_char(CommonName) '%' AND Yh_datetime >= to_char(beginDate) AND Yh_datetime <= to_char(endDate) AND f.fdaid = to_char(thisfdaid) ) a
left join
(select cEnterpriseCode,sum(spkcsl) kcsl from typkc
where 1=1 AND sptym like '%' to_char(CommonName) '%' AND sp_datetime >= to_char(beginDate) AND sp_datetime <= to_char(endDate)
group by cEnterpriseCode) b
on a.cEnterpriseCode=b.cEnterpriseCode
left join
(select cEnterpriseCode,sum(xs_sl) xssl from txsjl
where 1=1 AND xs_tym like '%' to_char(CommonName) '%' AND Xs_datetime >= to_char(beginDate) AND Xs_datetime <= to_char(endDate)
group by cEnterpriseCode) c
on a.cEnterpriseCode=c.cEnterpriseCode
left join
(select cEnterpriseCode,sum(ys_sl) yssl from tysjl
where 1=1 AND ys_tym like '%' to_char(CommonName) '%' AND Ys_datetime >= to_char(beginDate) AND Ys_datetime <= to_char(endDate)
group by cEnterpriseCode) d
on a.cEnterpriseCode=d.cEnterpriseCode
left join
(select cEnterpriseCode,sum(yh_yhsl) yhsl from tyhjl
where 1=1 AND yh_tym like '%' to_char(CommonName) '%' AND Yh_datetime >= to_char(beginDate) AND Yh_datetime <= to_char(endDate)
group by cEnterpriseCode) e
on a.cEnterpriseCode=e.cEnterpriseCode ) a
left join fdaenterprise f on a.cEnterpriseCode=f.cEnterpriseCode) a
order by a.FDAID asc;
FETCH fda_cursor into thisfdaid;
end;
end loop;
Close fda_cursor;
fetch cr_cursor into CommonName;
end;
end;
end loop;
--关闭游标
Close cr_cursor;
END;
END;
END;
红色部分报错：PROCEDURE DRUG_RR.Q_MAINPAGE_SPECIALDRUG 编译错误
错误：PLS-00103: 出现符号 "FDA_CURSOR"在需要下列之一时：
:= . ( @ % ;
行：31
文本：CURSOR fda_cursor IS
fda_cursor 是第二个游标，要根据第一个游标中获取的CommonName循环获取第二个游标中的值。
小菜鸟一枚，求高手指点
[解决办法]
是的，这是一个隐式游标，可以直接替代你的第一个游标。第二个游标因为是动态的无法使用这种方式简单化。