oracle中的存储过程初试

CREATE OR REPLACE PROCEDURE p_sap_data_import
Authid Current_User is

   v_table_count   NUMBER;
   v_sql           VARCHAR2 (1000);
BEGIN
--第一步更新UE_ALLMATERIAL_TEMP表中QC1UNION，QC3UNION字段
      BEGIN
      UPDATE
      ue_allmaterial_temp_TEST b
         SET b.qc1union =
                (   b.中文长文本_原始描述
                 || b.旧物料号
                 || b.物料类型
                 || b.lot号
                 || b.机组
                 || b.质保分级
                 || b.安全分级
                 || b.系统号
                 || b.材质
                 || b.规格型号
                 || b.功能位置码
                );

      UPDATE ims.ue_allmaterial_temp_TEST b
         SET b.qc3union =
                (   b.中文长文本_原始描述
                 || b.物料类型
                 || b.lot号
                 || b.质保分级
                 || b.安全分级
                 || b.规格型号
                 || b.功能位置码
                );

--第二步按三步匹配存在的SAP物料号
      UPDATE ims.ue_allmaterial_temp_TEST a
         SET a.物料号 = (SELECT 物料号
                           FROM ims.ue_sapnozidian0803 b
                          WHERE a.qc1union = b.qc1union);

      UPDATE ims.ue_allmaterial_temp_TEST a
         SET a.物料号 =
                  (SELECT 物料号
                     FROM ims.ue_sapnozidian0803 b
                    WHERE a.旧物料号 || a.物料类型 = b.旧物料号 || b.物料类型)
       WHERE a.旧物料号 IS NOT NULL
         AND f_subnum_alpher (a.旧物料号) = 'True'
         AND a.物料类型 = 'C120 材料'
         AND 物料号 IS NULL;

      UPDATE ims.ue_allmaterial_temp_TEST a
         SET 物料号 = (SELECT 物料号
                         FROM ims.ue_sapnozidian0803 b
                        WHERE a.qc3union = b.qc3union)
       WHERE a.规格型号 IS NOT NULL
         AND LENGTH (a.功能位置码) > 8
         AND a.功能位置码 IS NOT NULL
         AND a.物料号 IS NULL;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         INSERT INTO ims.imscm_exception_msg
                     (exception_code, exception_msg
                     )
              VALUES (SYS_GUID (), '' || '第一步匹配存在的SAP物料号出错!'
                     );
      --TODO
      WHEN OTHERS
      THEN
         INSERT INTO ims.imscm_exception_msg
                     (exception_code, exception_msg
                     )
              VALUES (SYS_GUID (), '' || '第一步匹配存在的SAP物料号出错!'
                     );
   END;
      commit;
END p_sap_data_import;