代码之家 › 专栏 › 技术社区 › hud

在oracle中,游标的更新出现错误

cursor stored-procedures oracle

hud · 技术社区 · 5 年前

我创造了一个 stored procedure

以下是程序。

CREATE OR REPLACE PROCEDURE FIBER_SIGNOFF_UGAR_UPD AS 
BEGIN

for cur_r in (
                   select inv.CIRCLE, 
                          
      
   regexp_substr(MP,'[^/]+',1,1)MPNAME,regexp_substr(MP,'[^/]+',1,2)MPCODE, 
                          inv.ROUTE_APPROVED_BY_CMM as CMM_APPROVED_LENGTH, 
                          inv.SPAN_TYPE,  ROUND(SUM(NVL(CALCULATED_LENGTH,0)/1000),4) AS NE_LENGTH, 
                          ROUND(SUM(CASE WHEN RJ_CONSTRUCTION_METHODOLOGY NOT LIKE '%AERIAL%' OR RJ_CONSTRUCTION_METHODOLOGY IS  NULL THEN NVL(CALCULATED_LENGTH,0) ELSE 0 END)/1000,4) AS UG_LENGTH
                         ,ROUND(SUM(CASE WHEN RJ_CONSTRUCTION_METHODOLOGY LIKE '%AERIAL%' THEN NVL(CALCULATED_LENGTH,0) ELSE 0 END)/1000,4) AS AR_LENGTH,
                          ROUND(SUM(CASE WHEN RJ_CONSTRUCTION_METHODOLOGY NOT LIKE '%AERIAL%' OR RJ_CONSTRUCTION_METHODOLOGY IS  NULL THEN NVL(CALCULATED_LENGTH,0) ELSE 0 END)/1000,4)/ ROUND(SUM(NVL(CALCULATED_LENGTH,0)/1000),4)*100 as ug_percentage,      
                    mv.rj_intracity_link_id  FROM NE.MV_SPAN@DB_LINK_NE_VIEWER mv
                   join TBL_FIBER_INV_CMP_REPORT_MV inv
              on   ((inv.SPAN_TYPE = 'INTERCITY' AND inv.SPAN_LINK_ID = mv.rj_span_id)
                 or (inv.SPAN_TYPE = 'INTRACITY' AND inv.SPAN_LINK_ID = mv.rj_intracity_link_id)
                 or (inv.SPAN_TYPE = 'ENTERPRISE' AND inv.SPAN_LINK_ID = mv.rj_intracity_link_id))
              GROUP BY mv.rj_intracity_link_id, inv.ROUTE_APPROVED_BY_CMM, inv.SPAN_TYPE,inv.CIRCLE, inv.MP
              
              )
              
loop


          begin
          
            update TBL_FIBER_INV_SIGN_OFF_SHEET set
              FSA_UG = cur_r.CMM_APPROVED_LENGTH * cur_r.ug_percentage / 100,
              FSA_AERIAL = cur_r.CMM_APPROVED_LENGTH - (cur_r.CMM_APPROVED_LENGTH * cur_r.ug_percentage / 100)
            WHERE  SPAN_LINK_ID = 
              CASE cur_r.SPAN_TYPE
                         WHEN 'INTERCITY' THEN cur_r.rj_span_id
                         WHEN 'INTRACITY' THEN cur_r.rj_intracity_link_id
                         WHEN 'ENTERPRISE' THEN cur_r.rj_intracity_link_id
                         END;          
          
          end


end loop;


END FIBER_SIGNOFF_UGAR_UPD;

错误(33,29):PL/SQL:ORA-00920:关系运算符无效错误(39,1):PLS-00103:在预期以下情况之一时遇到符号“END”:;符号“;”被替换为“结束”继续。

请帮忙

1 回复 | 直到 5 年前

user12766509 user12766509 5 年前

  CREATE OR REPLACE PROCEDURE FIBER_SIGNOFF_UGAR_UPD AS 
BEGIN

for cur_r in (
                   select inv.CIRCLE, 
                          inv.MP, 
                          inv.ROUTE_APPROVED_BY_CMM as CMM_APPROVED_LENGTH, 
                          inv.SPAN_TYPE,  ROUND(SUM(NVL(CALCULATED_LENGTH,0)/1000),4) AS NE_LENGTH, 
                          ROUND(SUM(CASE WHEN RJ_CONSTRUCTION_METHODOLOGY NOT LIKE '%AERIAL%' OR RJ_CONSTRUCTION_METHODOLOGY IS  NULL THEN NVL(CALCULATED_LENGTH,0) ELSE 0 END)/1000,4) AS UG_LENGTH
                         ,ROUND(SUM(CASE WHEN RJ_CONSTRUCTION_METHODOLOGY LIKE '%AERIAL%' THEN NVL(CALCULATED_LENGTH,0) ELSE 0 END)/1000,4) AS AR_LENGTH,
                          ROUND(SUM(CASE WHEN RJ_CONSTRUCTION_METHODOLOGY NOT LIKE '%AERIAL%' OR RJ_CONSTRUCTION_METHODOLOGY IS  NULL THEN NVL(CALCULATED_LENGTH,0) ELSE 0 END)/1000,4)/ ROUND(SUM(NVL(CALCULATED_LENGTH,0)/1000),4)*100 as ug_percentage,      
                    mv.rj_intracity_link_id  FROM NE.MV_SPAN@DB_LINK_NE_VIEWER mv
                   join TBL_FIBER_INV_CMP_REPORT_MV inv
              on   ((inv.SPAN_TYPE = 'INTERCITY' AND inv.SPAN_LINK_ID = mv.rj_span_id)
                 or (inv.SPAN_TYPE = 'INTRACITY' AND inv.SPAN_LINK_ID = mv.rj_intracity_link_id)
                 or (inv.SPAN_TYPE = 'ENTERPRISE' AND inv.SPAN_LINK_ID = mv.rj_intracity_link_id))
              GROUP BY mv.rj_intracity_link_id, inv.ROUTE_APPROVED_BY_CMM, inv.SPAN_TYPE,inv.CIRCLE, inv.MP
              
              )
              
loop


          begin
          
            update TBL_FIBER_INV_SIGN_OFF_SHEET set
              FSA_UG = cur_r.CMM_APPROVED_LENGTH * cur_r.ug_percentage / 100,
              FSA_AERIAL = cur_r.CMM_APPROVED_LENGTH - (cur_r.CMM_APPROVED_LENGTH * cur_r.ug_percentage / 100)
           WHERE  SPAN_LINK_ID = 
                  CASE  WHEN cur_r.SPAN_TYPE ='INTERCITY' THEN cur_r.rj_span_id
                        WHEN cur_r.SPAN_TYPE IN('INTRACITY','ENTERPRISE') THEN cur_r.rj_intracity_link_id
                  END;     
          
          end;


end loop;


END FIBER_SIGNOFF_UGAR_UPD;

Littlefoot 5 年前

这个 END 缺少分号:

      END;                    --> here
   END LOOP;
END FIBER_SIGNOFF_UGAR_UPD;

由于关系运算符无效:此案例缺少 关系运算符 :

WHERE CASE cur_r.span_type
           WHEN 'INTERCITY' THEN cur_r.rj_span_id
           WHEN 'INTRACITY' THEN cur_r.rj_intracity_link_id
           WHEN 'ENTERPRISE' THEN cur_r.rj_intracity_link_id
      END

WHERE CASE cur_r.span_type
           WHEN 'INTERCITY' THEN cur_r.rj_span_id
           WHEN 'INTRACITY' THEN cur_r.rj_intracity_link_id
           WHEN 'ENTERPRISE' THEN cur_r.rj_intracity_link_id
      END = 1
          ---
          This