工作中遇到的一个值得学习的存储过程//

CREATE OR REPLACE PACKAGE BODY soa_jc_vendor IS
   v_in_data_set          t_pojcvendor_tab;
   --写错误日志的PROC
   PROCEDURE error_log(    p_in_data_set        IN  varchar2,
                           p_pri_key            IN  varchar2,
                           p_error_message      IN  varchar2,
                           p_index              IN OUT number,
                           p_error_reason       IN OUT t_error_message
                           )IS
            v_count                             number := 0;
            BEGIN
               
                 IF p_error_reason.count = 0 THEN
                    p_error_reason.extend;
                    p_error_reason(p_index) := t_error(null,null,null);
                    p_error_reason(p_index).in_data_set := p_in_data_set;
                    p_error_reason(p_index).pri_key := p_pri_key;
                    p_error_reason(p_index).error_message := p_error_message;
                    p_index := p_index + 1;
                 ELSE
                   FOR i IN 1..p_error_reason.COUNT LOOP
                     
                       IF p_error_reason(i).pri_key = p_pri_key THEN
                          p_error_reason(i).error_message := p_error_reason(i).error_message
                                                             ||';'||p_error_message;
                          v_count := v_count + 1;
                       END IF;
                   END LOOP;
                 
                   IF v_count = 0 THEN
                      p_error_reason.extend;
                      p_error_reason(p_index) := t_error(null,null,null);
                      p_error_reason(p_index).in_data_set := p_in_data_set;
                      p_error_reason(p_index).pri_key := p_pri_key;
                      p_error_reason(p_index).error_message := p_error_message;
                      p_index := p_index + 1;
                   END IF;
                 END IF;
                
            END error_log;
   --非空字段校验
   PROCEDURE validate_nullable(p_recode            IN OUT t_pojcvendor,
                               v_index             IN OUT number,
                               v_errorCollection   IN OUT t_error_message,
                               v_result            IN OUT boolean
                               ) IS    
   BEGIN
     v_result := true;
     --必要字段不空
     IF p_recode.PRI_KEY is null THEN
        v_result := false;
         error_log('t_pojcvendor_tab',
                   p_recode.PRI_KEY,
                   'PRI_KEY不能为空',
                   v_index,
                   v_errorCollection);
     END IF;  
     IF p_recode.JC_VENDOR_NUM is null THEN
        v_result := false;
         error_log('t_pojcvendor_tab',
                   p_recode.PRI_KEY,
                   '供应商编号不能为空',
                   v_index,
                   v_errorCollection);
     END IF;
     IF p_recode.JC_VENDOR_NAME is null THEN
        v_result := false;
         error_log('t_pojcvendor_tab',
                   p_recode.PRI_KEY,
                   '供应商名称不能为空',
                   v_index,
                   v_errorCollection);
     END IF;
     IF p_recode.ISSUE_DATE is null THEN
        v_result := false;
         error_log('t_pojcvendor_tab',
                   p_recode.PRI_KEY,
                   '发布日期不能为空',
                   v_index,
                   v_errorCollection);
     END IF;
   EXCEPTION
     WHEN others THEN
         v_result := false;
         error_log('t_pojcvendor_tab',
                   p_recode.PRI_KEY,
                   '非空校验意外错误',
                   v_index,
                   v_errorCollection); 
   END validate_nullable;
  
   --供应商名称和编码校验
   PROCEDURE validate_vendor(  p_recode            IN OUT t_pojcvendor,
                               v_index             IN OUT number,
                               v_errorCollection   IN OUT t_error_message,
                               v_result            IN OUT boolean
                               ) IS
     v_count                   number := 0;                         
   BEGIN
     v_result := true;
     SELECT count(1)
       INTO v_count
       FROM QPO_JC_VENDOR_tmp
      WHERE jc_vendor_name = p_recode.JC_VENDOR_NAME;
     IF v_count > 0 THEN
       v_result := false;
       error_log('t_pojcvendor_tab',
                   p_recode.PRI_KEY,
                   '供应商名称在临时表已存在',
                   v_index,
                   v_errorCollection);
     END IF; 
     SELECT count(1)
       INTO v_count
       FROM QPO_JC_VENDORS
      WHERE jc_vendor_name = p_recode.JC_VENDOR_NAME;
     IF v_count > 0 THEN
       v_result := false;
       error_log('t_pojcvendor_tab',
                   p_recode.PRI_KEY,
                   '供应商名称在映射表已存在',
                   v_index,
                   v_errorCollection);
     END IF;  
     SELECT count(1)
       INTO v_count
       FROM QPO_JC_VENDOR_tmp
      WHERE jc_vendor_num = p_recode.JC_VENDOR_NUM;
     IF v_count > 0 THEN
       v_result := false;
       error_log('t_pojcvendor_tab',
                   p_recode.PRI_KEY,
                   '供应商编码在临时表已存在',
                   v_index,
                   v_errorCollection);
     END IF;  
     SELECT count(1)
       INTO v_count
       FROM QPO_JC_VENDORS
      WHERE jc_vendor_num = p_recode.JC_VENDOR_NUM;
     IF v_count > 0 THEN
       v_result := false;
       error_log('t_pojcvendor_tab',
                   p_recode.PRI_KEY,
                   '供应商编码在映射表已存在',
                   v_index,
                   v_errorCollection);
     END IF; 
     --系统中是否存在
     /*SELECT count(1)
       INTO v_count
       FROM po_vendors
      WHERE segment1 = p_recode.JC_VENDOR_NUM;
     IF v_count > 0 THEN
       v_result := false;
       error_log('t_pojcvendor_tab',
                   p_recode.PRI_KEY,
                   '供应商'||p_recode.JC_VENDOR_NUM||'已经存在，检查是否重复发送',
                   v_index,
                   v_errorCollection);
     END IF; */
   EXCEPTION
     WHEN others THEN
       v_result := false;
       error_log('t_pojcvendor_tab',
                   p_recode.PRI_KEY,
                   '校验供应商时发生意外错误',
                   v_index,
                   v_errorCollection); 
   END validate_vendor;                                                  
                                  
   --数据处理
   PROCEDURE data_handle(p_recode            IN OUT t_pojcvendor,
                         v_index             IN OUT number,
                         v_errorCollection   IN OUT t_error_message,
                         v_result            IN OUT boolean
                         ) IS
     v_error               number := 0;                   
     v_count               number := 0;
     v_user_id             number;                  
   BEGIN
     v_result := true;
     --校验PK是否唯一
     FOR i IN 1..v_in_data_set.count LOOP
         IF v_in_data_set(i).pri_key = p_recode.PRI_KEY THEN
           v_count := v_count + 1;
         END IF;
     END LOOP;
     IF v_count > 1 THEN
       v_error := v_error + 1;
       error_log('t_pojcvendor_tab',
                   p_recode.PRI_KEY,
                   '数据集中主键不能重复',
                   v_index,
                   v_errorCollection);
     END IF;
     --校验供应商编号是否唯一
     v_count := 0;
     FOR i IN 1..v_in_data_set.count LOOP
         IF v_in_data_set(i).JC_VENDOR_NUM = p_recode.JC_VENDOR_NUM THEN
           v_count := v_count + 1;
         END IF;
     END LOOP;
     IF v_count > 1 THEN
       v_error := v_error + 1;
       error_log('t_pojcvendor_tab',
                   p_recode.PRI_KEY,
                   --'供应商编号不能重复',
                   '供应商编号在同一批导入数据集中有重复',
                   v_index,
                   v_errorCollection);
     END IF;
     --校验供应商编号是否唯一
     v_count := 0;
     FOR i IN 1..v_in_data_set.count LOOP
         IF v_in_data_set(i).JC_VENDOR_name = p_recode.JC_VENDOR_NAME THEN
           v_count := v_count + 1;
         END IF;
     END LOOP;
     IF v_count > 1 THEN
       v_error := v_error + 1;
       error_log('t_pojcvendor_tab',
                   p_recode.PRI_KEY,
                   --'供应商名称不能重复',
                   '供应商名称在同一批导入数据集中有重复',
                   v_index,
                   v_errorCollection);
     END IF;
     --校验必要字段非空
     validate_nullable(p_recode,
                       v_index,
                       v_errorCollection,
                       v_result);
     IF v_result = false THEN
       v_error := v_error + 1;
     END IF;                 
                      
     --校验供应商名称和编码
     validate_vendor(p_recode,
                     v_index,
                     v_errorCollection,
                     v_result);
     IF v_result = false THEN
       v_error := v_error + 1;
     END IF;            
    
     IF v_error > 0 THEN
       v_result := false;
     END IF;    
     --校验制单人ID和员工号
     /*IF p_recode.CREATED_BY IS NOT NULL THEN
       --制单人ID
       SELECT count(1)
         INTO v_count
         FROM fnd_user
        WHERE user_id = p_recode.CREATED_BY;
       
        IF v_count = 0 THEN
           v_result := false;
           error_log('t_po_jcvendor',
                     p_recode.PRI_KEY,
                     '制单人在系统中不存在',
                     v_index,
                     v_errorCollection);
        END IF;
     ELSE
       --制单人员工号(正确时为created_by赋值)
       IF p_recode.EMPLOYEE_NUMBER IS NOT NULL THEN
         BEGIN
          SELECT fu.user_id
            INTO v_user_id
            FROM FND_USER FU,
                 PER_PEOPLE_F PPF
           WHERE FU.EMPLOYEE_ID = PPF.PERSON_ID
             AND TRUNC(SYSDATE) BETWEEN nvl(TRUNC(FU.START_DATE),TRUNC(SYSDATE)-1)
                                AND nvl(TRUNC(FU.END_DATE),TRUNC(SYSDATE)+1)
             AND ppf.employee_number = p_recode.EMPLOYEE_NUMBER;
         EXCEPTION
           WHEN no_data_found THEN
            v_result := false;
            error_log('t_po_jcvendor',
                     p_recode.PRI_KEY,
                     '员工未与用户关联或员工不存在',
                     v_index,
                     v_errorCollection);
         END;
         p_recode.CREATED_BY := v_user_id;
       ELSE
         v_result := false;
         error_log('t_po_jcvendor',
                   p_recode.PRI_KEY,
                   '制单人ID和员工号不能同时为空',
                   v_index,
                   v_errorCollection);   
       END IF;  
     END IF;   */
   END data_handle;                     
   --程序入口        
   PROCEDURE create_jcvendor(  p_ERRBUF             OUT NOCOPY VARCHAR2,
                               p_RETCODE            OUT NOCOPY NUMBER,
                               p_servername           IN varchar2,
                               p_version              IN varchar2,
                               p_User_id            IN NUMBER,
                               p_Resp_id            IN NUMBER,
                               p_App_id             IN NUMBER,
                               p_in_data_set        IN t_pojcvendor_tab,
                               p_result             OUT number,
                               p_reason             OUT t_error_message,
                               p_request_id         OUT number,
                               p_time_stamp         OUT date
                            ) IS
     v_result               boolean;
     v_index                number := 1;
     v_error                number := 0;
    
     v_org_id               number;
     v_batch_id             number;                  
   BEGIN
     p_result := 0;
     p_time_stamp := sysdate;
     p_request_id := 0;
     --初始化用户
     BEGIN
          fnd_global.apps_initialize(user_id           => p_user_id,
                                     resp_id           => p_resp_id,
                                     resp_appl_id      => p_app_id
                                     );
     EXCEPTION
       WHEN others THEN
         v_error := v_error + 1;
         error_log('t_pojcvendor_tab',
                   '0',
                   '初始化用户错误',
                   v_index,
                   p_reason);                             
     END;
     dbms_output.enable(100000);
     COMMIT;
     v_org_id := fnd_global.ORG_ID;
     dbms_output.put_line('org_id: '||v_org_id);
     --准备数据和错误集合
     p_reason := t_error_message();
     v_in_data_set := t_pojcvendor_tab();
     v_in_data_set := p_in_data_set;
    
     --循环处理
     FOR i IN 1..v_in_data_set.count LOOP
        --调用处理方法
        data_handle(v_in_data_set(i),
                    v_index,
                    p_reason,
                    v_result);
        IF v_result = false THEN
           v_error := v_error + 1;
        END IF;   
     END LOOP;
    
     --数据集无误循环插表
    
     IF v_error = 0 THEN
     SELECT Fnd_Concurrent_Requests_S.NEXTVAL
       INTO v_batch_id
    FROM Dual;
       FOR i IN 1..v_in_data_set.count LOOP
         BEGIN
          INSERT INTO qpo_jc_vendor_tmp
             (      request_id,
                    file_name,
                    JC_VENDOR_NUM     ,
                    JC_VENDOR_NAME    ,
                    --VENDOR_ID         ,
                    ISSUE_DATE
                   )
          VALUES
             (     v_batch_id,
                   v_in_data_set(i).pri_key,
                   v_in_data_set(i).JC_VENDOR_NUM,
                   v_in_data_set(i).JC_VENDOR_NAME,
                   v_in_data_set(i).ISSUE_DATE
                   );        
         EXCEPTION
           WHEN others THEN
             v_error := v_error + 1;
             error_log('t_pojcvendor_tab',
                       v_in_data_set(i).pri_key,
                       '插入到临时表错误',
                       v_index,
                       p_reason);           
         END;         
       END LOOP;
     END IF;
     --校验无误且插表无误，跑请求。
     IF v_error = 0 THEN
       COMMIT;
      -- 提交关联请求
      p_request_id := fnd_request.submit_request(application => 'IF',
                                                      program     => 'QPOJCVNDMAP',
                                                      description => NULL,
                                                      start_time  => NULL,
                                                      sub_request => FALSE,
                                                      argument1   => v_batch_id);

      IF p_request_id = 0 THEN
         ROLLBACK;
         v_error := v_error + 1;
         error_log('t_pojcvendor_tab',
                   '0',
                   '提交请求时出错',
                   v_index,
                   p_reason);
      ELSE
        COMMIT;            
      END IF;
     ELSE
       ROLLBACK;
     END IF;
    
     IF v_error = 0 THEN
        p_result := 1;
        p_time_stamp := sysdate;
     END IF;
     /*不管成功与否都不处理导入的数据？*/
   EXCEPTION
     WHEN others THEN
       p_result := 0;
       p_time_stamp := sysdate;
       error_log('t_pojcvendor_tab',
                 '0',
                 '程序执行错误'||sqlerrm,
                 v_index,
                 p_reason);
   END create_jcvendor;
END soa_jc_vendor;