首先说明一下业务场景,我需要在一个循环查询客户号的循环中,再加入一个循环
因为一个客户号可能对应多个保单号,我需要在加入一个循环并把它存入到一个表中,有哪些可行思路和技术?
我试用双重for item in 结果直接跳出,目前在尝试for item in 中循环游标,但是报错不知道怎么解决
下面是报错信息
FUNCTION CMS.ASSESSMIDDLECOUNT 编译错误
错误:PLS-00103: Encountered the symbol "FETCH" when expecting one of the following:
constant exception <an identifier>
<a double-quoted delimited-identifier> table long double ref
char time timestamp interval date binary national character
nchar
行:363
文本:loop fetch C_MainTain into tp11, tcontno, ttsigndate, tyear, tbranchtype2, tAgentConferNo, ttransmoney;
错误:PLS-00103: Encountered the symbol "CLOSE" when expecting one of the following:
end not pragma final instantiable order overriding static
member constructor map
行:405
文本:close C_MainTain;
游标的写法如下:
tappntno := item.appntno;
declare
cursor C_MainTain is
select p11,
contno,
SignDate,
years,
branchtype2,
AgentConferNo,
transmoney
from lacommision
where agentcode = cAgentCode
and wageno between tStartDate and tEndDate
and branchtype = '2'
and appntno = tappntno
AND transtype = 'ZC'
AND paycount = '1';
open C_MainTain;
loop fetch C_MainTain into tp11, tcontno, ttsigndate, tyear, tbranchtype2, tAgentConferNo, ttransmoney;
insert into LATXNewDirectGroupCount(
select tManageCom,
b.branchattr,
cWageno,
'1',
cAgentCode,
trim(tappntno),
tp11,
trim(tcontno),
to_char(ttsigndate, 'yyyy-mm-dd'),
to_char(tyear, 'yyyy'),
(decode(tbranchtype2,
'01',
'直销',
'02',
'交叉',
'03',
'中介',
'04',
'综合')),
tAgentConferNo,
to_char(ttransmoney),
(case
when tAgentCount > 1 then
'是'
else
'否'
end),
ck08
from latree a, labranchgroup b
where a.agentcode = cAgentCode
and a.branchtype = '2'
and a.agentgroup = b.agentgroup
and a.agentgrade like 'V%'
and a.managecom like tManageCom || '%');
COMMIT;
END LOOP;
close C_MainTain;