退出块的PLSQL中的异常块

我使用以下代码块来更新我的前端段 . 但是当我编译这个时

DECLARE
   p_person_id                   NUMBER          := NULL;
   p_business_group_id           NUMBER;
   p_id_flex_num                 NUMBER;
------------------
   p_analysis_criteria_id        NUMBER          := NULL;
   p_person_analysis_id          NUMBER          := NULL;
   p_per_object_version_number   NUMBER          := NULL;
   v_err                         VARCHAR2 (1000) := NULL;
   p_medical_id                  VARCHAR2 (100)  := NULL;
BEGIN
   FOR i IN (SELECT *
               FROM xx_hr_upload_master_data_new
              WHERE person_id IS NOT NULL
                AND business_group_id IS NOT NULL
                AND medical_id IS NOT NULL)
   LOOP
      p_id_flex_num := 50530;
      BEGIN
         SELECT   sv.segment1, NVL (sit.object_version_number, 1),
                  sit.analysis_criteria_id, MAX (sit.person_analysis_id)
             INTO p_medical_id, p_per_object_version_number,
                  p_analysis_criteria_id, p_person_analysis_id
             FROM fnd_id_flex_structures_tl sttl,
                  fnd_id_flex_structures st,
                  per_person_analyses sit,
                  per_analysis_criteria sv
            WHERE sttl.id_flex_structure_name = 'ISPs Medical Data'
              AND sttl.LANGUAGE = USERENV ('LANG')
              AND st.id_flex_code = sttl.id_flex_code
              AND st.id_flex_num = sttl.id_flex_num
              AND st.id_flex_num = sit.id_flex_num
              AND st.id_flex_num = sv.id_flex_num
              AND sit.analysis_criteria_id = sv.analysis_criteria_id
              AND sit.person_id = i.person_id
         --and sv.SEGMENT1 = '4602001140'
         GROUP BY sv.segment1,
                  NVL (sit.object_version_number, 1),
                  sit.analysis_criteria_id;
      EXCEPTION
         WHEN OTHERS
         THEN
            p_medical_id := NULL;
            p_per_object_version_number := 1;
            p_person_analysis_id := NULL;
            p_analysis_criteria_id := NULL;
            p_person_analysis_id := NULL;
            p_per_object_version_number := NULL;
            p_person_id := NULL;
      END;
      BEGIN
------------------------------------------------
         IF (p_medical_id IS NULL AND p_analysis_criteria_id IS NULL)
         THEN
            -- create a new record in the SIT (Special Information Type)table .
            p_person_id := i.person_id;
            p_medical_id := TO_CHAR (i.medical_id);
-----------------------------
            hr_sit_api.create_sit
                  (p_validate                       => FALSE,
                   p_person_id                      => p_person_id,
                   p_business_group_id              => i.business_group_id,
                   p_id_flex_num                    => p_id_flex_num,
                   p_effective_date                 => TRUNC (SYSDATE),
                   p_date_from                      => TRUNC (SYSDATE),
                   p_segment1                       => p_medical_id,
                   p_analysis_criteria_id           => p_analysis_criteria_id,
                   p_person_analysis_id             => p_person_analysis_id,
                   p_pea_object_version_number      => p_per_object_version_number
                  );
         ELSE
            -- employee has previous Billing_Acc_Num then update that number in  the SIT table .
            hr_sit_api.update_sit
                 (p_validate                       => FALSE,
                  p_person_analysis_id             => p_person_analysis_id,
                  p_pea_object_version_number      => p_per_object_version_number,
                  p_date_from                      => TRUNC (SYSDATE),
                  p_segment1                       => p_medical_id,
                  p_analysis_criteria_id           => p_analysis_criteria_id
                 );
         END IF;
         UPDATE xx_hr_upload_master_data_new xx
            SET xx.error_msg = 'Done',
                xx.emp_data = 'Done'
          WHERE xx.business_group_id = i.business_group_id
            AND xx.employee_number = i.employee_number;
      EXCEPTION
         WHEN OTHERS
         THEN
            p_analysis_criteria_id := NULL;
            p_person_analysis_id := NULL;
            p_per_object_version_number := NULL;
            p_person_id := NULL;
            p_medical_id := NULL;
            v_err := NULL;
            v_err := SQLERRM;
            UPDATE xx_hr_upload_master_data_new xx
               SET xx.error_msg = v_err
             WHERE xx.business_group_id = i.business_group_id
               AND xx.employee_number = i.employee_number;
      END;
   END LOOP;
   COMMIT;
END;

现在,当select查询没有提取任何内容时,将输入异常块,其中所有变量都被启动为null .

我希望在此之后,应该输入在条件IF(p_medical_id IS NULL和p_analysis_criteria_id IS NULL)内调用create api的开始 . 但是这没有发生,程序在进入异常块后退出此异常块后退出 .

回答(1)

3 years ago

提示可能是:如果没有结果,第一个选择不会引发任何异常 . 如果没有结果,则不会输入for循环,因此唯一会发生的是提交 . (如果for循环中的select会得到0行或者超过1行,那么它会引发异常 . 原因是'select ... into ...':当使用'into'时,select必须返回精确的1行 . )