Oracle 函数 双重循环

img


首先说明一下业务场景，我需要在一个循环查询客户号的循环中，再加入一个循环

img


因为一个客户号可能对应多个保单号，我需要在加入一个循环并把它存入到一个表中，有哪些可行思路和技术？
我试用双重for item in 结果直接跳出，目前在尝试for item in 中循环游标，但是报错不知道怎么解决
下面是报错信息
FUNCTION CMS.ASSESSMIDDLECOUNT 编译错误

错误：PLS-00103: Encountered the symbol "FETCH" when expecting one of the following:

      constant exception 
      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;
不知道你这个问题是否已经解决, 如果还没有解决的话:

如果你已经解决了该问题, 非常希望你能够分享一下解决方案, 写成博客, 将相关链接放在评论区, 以帮助更多的人 ^-^