oracle package比较

动态SQL TABLE使用
判断数据

===========================================
2011-10-19
判断 函数

create or replace function F_CAN_APPNO_RECALL(app_no in varchar2) return boolean is
  app_count number;
  v_SQLStatement varchar2(512);
  SEND_TIME DATE;
  TYPE cursor_type IS REF CURSOR;
  C_CHKTIME cursor_type;
  i number;--控制查询语句只循环一次
begin
 select count(c_app_no) into app_count from web_app_base a where a.c_app_no=app_no and a.c_app_typ='A' and a.c_prod_no in('0326','0320')
        and a.c_app_status in ('2','O','4') and ROUND(TO_NUMBER(sysdate - a.t_insrnc_bgn_tm)) > -1;
 if(app_count=1)then
          i:=0;
          v_SQLStatement:='select T_SEND_TM from web_trd_app_send_packet where c_app_no ='''|| app_no ||''' order by t_send_tm desc';
          OPEN C_CHKTIME FOR v_SQLStatement;
            loop
               
                FETCH C_CHKTIME INTO  SEND_TIME;
                EXIT WHEN C_CHKTIME%notfound;
                i:=i+1;
                EXIT WHEN i>1;
                IF (FLOOR(SEND_TIME-sysdate) <= -10 ) THEN
                    return true;
                 END IF;
            END LOOP;
          CLOSE C_CHKTIME;
 end if;
  return false;
Exception
  when others then
       return false;
end F_CAN_APPNO_RECALL;

 

 

===========================================
不使用游标

create or replace function F_CAN_APPNO_RECALL(app_no in varchar2) return boolean is
  app_count number;
  SEND_TIME DATE;
begin
 select count(c_app_no) into app_count from web_app_base a where a.c_app_no=app_no and a.c_app_typ='A' and a.c_prod_no in('0326','0320')
        and a.c_app_status in ('2','O','4') and ROUND(TO_NUMBER(sysdate - a.t_insrnc_bgn_tm)) > -1;
 if(app_count=1)then
        select max(T_SEND_TM) into SEND_TIME from web_trd_app_send_packet where c_app_no =app_no;
        
        IF (FLOOR(SEND_TIME-sysdate) < -10 ) THEN
           return true;
        END IF;
 end if;
  return false;
Exception
  when others then
       return false;
end F_CAN_APPNO_RECALL;

 

 

============================================
包头

create or replace package pkg_finapp_validate_recall is

  -- Author  : CO-ZHANGBAIYU001
  -- Created : 2011-10-19 15:17:03
  -- Purpose : 

  --输入输出参数数组类型
  TYPE Type_Array IS TABLE OF VARCHAR2(50) INDEX BY BINARY_INTEGER;

  --function F_CAN_APPNO_RECALL(app_no in varchar2, Validate_Type number) return boolean;
  --校验
  procedure P_appno_validate(input_list in Type_Array, Validate_Type in number , output_list out Type_Array);
  
end pkg_finapp_validate_recall;

 

 

============================================
包体

create or replace package body pkg_finapp_validate_recall is


  -- 创建 : 2011-10-19 17:01:34
  -- 用途 : 审核校验
  function F_CAN_APPNO_RECALL(app_no in varchar2, Validate_Type number) return boolean is
    app_count number;
    SEND_TIME DATE;
  begin
    IF(Validate_Type = 0) THEN
        select count(c_app_no) into app_count from web_app_base a where a.c_app_no=app_no and a.c_app_status in ('2','O','4') and a.c_app_typ='A'
           and a.c_prod_no in('0326','0320') and ROUND(TO_NUMBER(sysdate - a.t_insrnc_bgn_tm)) > -1;
      ELSE
        select count(c_app_no) into app_count from web_app_base a where a.c_app_no=app_no and a.c_app_status in ('8') and a.c_app_typ='A'
           and a.c_prod_no in('0326','0320') and ROUND(TO_NUMBER(sysdate - a.t_insrnc_bgn_tm)) > -1;
    END IF;

    if(app_count=1) then
          select max(T_SEND_TM) into SEND_TIME from web_trd_app_send_packet where c_app_no =app_no;
         
          IF (FLOOR(SEND_TIME-sysdate) < -10 ) THEN
             RETURN TRUE;
          END IF;
    END IF;
    RETURN FALSE;
    EXCEPTION
      WHEN OTHERS THEN
           RETURN FALSE;
  end F_CAN_APPNO_RECALL;


   -- 创建 : 2011-10-19 17:01:34
  -- 用途 : 校验内容
  procedure P_appno_validate(input_list in Type_Array, Validate_Type in number , output_list out Type_Array) is
    j number:=1;
  begin
    FOR i IN 1..input_list.count LOOP
        IF F_CAN_APPNO_RECALL(input_list(i), Validate_Type) THEN
           output_list(j):=input_list(i);
           j:=j+1;
        END IF;
    END LOOP;
  end P_appno_validate;

end pkg_finapp_validate_recall;

 

 

============================================

 

CREATE OR REPLACE PACKAGE BODY PKG_EPCIS_AUTO_APP_VALIDATE IS

  /*******************************************************
  NAME         :P_EPCIS_AUTO_APP_VALIDATE
  SUMMARY      :
  PARAM        :VARCHAR2 -  App_No           
                NUMBER  -   Validate_Ret     
  DATE         :2011-10-19
  AUTHOR       :CO-ZHANGBAIYU001
  *******************************************************/

  PROCEDURE P_EPCIS_AUTO_APP_VALIDATE(App_No        IN VARCHAR2,
                                      Validate_Type IN NUMBER,
                                      Validate_Ret  OUT NUMBER) IS
    V_ERROR          VARCHAR2(400);
    V_SEND_TIME      NUMBER;
    V_DATA_COUNT     NUMBER;
    V_DAY_TO_INS_BGN NUMBER;
    V_PLY_START_TIME DATE;
    V_PLY_END_TIME   DATE;
    V_PROD_NO        VARCHAR2(50);
  BEGIN
    Validate_Ret := 0;
    IF Validate_Type NOT IN (0, 1) THEN
      RAISE_APPLICATION_ERROR(-20200, '校验类型应为0或1.');
    END IF;
    --判断
    SELECT A.C_PROD_NO
      INTO V_PROD_NO
      FROM WEB_APP_BASE A
     WHERE A.C_APP_NO = App_No;
    --判断数据正确性
    IF V_PROD_NO = '0325' THEN
      SELECT COUNT(A.C_APP_NO)
        INTO V_DATA_COUNT
        FROM WEB_APP_BASE A, WEB_APP_VHL B
       WHERE A.C_APP_NO = B.C_APP_NO
         AND A.C_APP_NO = App_No;
      V_ERROR := '找不到数据.';
    ELSIF V_PROD_NO = '0320' OR V_PROD_NO = '0326' THEN
      SELECT COUNT(A.C_APP_NO)
        INTO V_DATA_COUNT
        FROM WEB_TRD_APP_SEND_PACKET A, WEB_APP_BASE B, WEB_APP_VHL C
       WHERE A.C_QRY_CDE = C.C_QRY_CDE
         AND B.C_APP_NO = C.C_APP_NO
         AND B.C_APP_NO = App_No;
      V_ERROR := '找不到数据.';
    ELSE
      RETURN;
    END IF;
    IF V_DATA_COUNT = 0 THEN
      RAISE_APPLICATION_ERROR(-20201, V_ERROR);
    END IF;

    
    IF V_PROD_NO = '0325' THEN
      IF Validate_Type = 0 THEN
       
        SELECT DAY_TO_BGN
          INTO V_DAY_TO_INS_BGN
          FROM (SELECT TRUNC(SYSDATE) - TRUNC(A.T_INSRNC_BGN_TM) DAY_TO_BGN
                  FROM WEB_APP_BASE A, WEB_APP_VHL B
                 WHERE A.C_APP_NO = B.C_APP_NO
                   AND A.C_APP_NO = App_No
                   AND A.C_PROD_NO IN ('0325')
                   AND A.C_APP_STATUS IN ('2', 'O', '4')
                   AND A.C_APP_TYP = 'A'
                 ORDER BY DAY_TO_BGN DESC)
         WHERE ROWNUM < 2;
      ELSIF Validate_Type = 1 THEN
        
        SELECT DAY_TO_BGN
          INTO V_DAY_TO_INS_BGN
          FROM (SELECT TRUNC(SYSDATE) - TRUNC(A.T_INSRNC_BGN_TM) DAY_TO_BGN
                  FROM WEB_APP_BASE A, WEB_APP_VHL B
                 WHERE A.C_APP_NO = B.C_APP_NO
                   AND A.C_APP_NO = App_No
                   AND A.C_PROD_NO IN ('0325')
                   AND A.C_APP_STATUS IN ('8')
                   AND A.C_APP_TYP = 'A'
                 ORDER BY DAY_TO_BGN DESC)
         WHERE ROWNUM < 2;
      END IF;
    ELSIF V_PROD_NO = '0320' OR V_PROD_NO = '0326' THEN
      IF Validate_Type = 0 THEN
        
        SELECT T_SEND_TM, DAY_TO_BGN
          INTO V_SEND_TIME, V_DAY_TO_INS_BGN
          FROM (SELECT TRUNC(SYSDATE) - TRUNC(C.T_SEND_TM) T_SEND_TM,
                       TRUNC(SYSDATE) - TRUNC(A.T_INSRNC_BGN_TM) DAY_TO_BGN
                  FROM WEB_APP_BASE            A,
                       WEB_APP_VHL             B,
                       WEB_TRD_APP_SEND_PACKET C
                 WHERE A.C_APP_NO = B.C_APP_NO
                   AND B.C_QRY_CDE = C.C_QRY_CDE
                   AND A.C_APP_NO = App_No
                   AND A.C_PROD_NO IN ('0326', '0320')
                      AND A.C_APP_STATUS IN ('2', 'O', '4')
                   AND A.C_APP_TYP = 'A'
                 ORDER BY C.T_SEND_TM DESC)
         WHERE ROWNUM < 2;
      ELSIF Validate_Type = 1 THEN
        
        SELECT T_SEND_TM, DAY_TO_BGN
          INTO V_SEND_TIME, V_DAY_TO_INS_BGN
          FROM (SELECT TRUNC(SYSDATE) - TRUNC(C.T_SEND_TM) T_SEND_TM,
                       TRUNC(SYSDATE) - TRUNC(A.T_INSRNC_BGN_TM) DAY_TO_BGN
                  FROM WEB_APP_BASE            A,
                       WEB_APP_VHL             B,
                       WEB_TRD_APP_SEND_PACKET C
                 WHERE A.C_APP_NO = B.C_APP_NO
                   AND B.C_QRY_CDE = C.C_QRY_CDE
                   AND A.C_APP_NO = App_No
                   AND A.C_PROD_NO IN ('0326', '0320')
                   AND A.C_APP_STATUS IN ('8')
                   AND A.C_APP_TYP = 'A'
                 ORDER BY C.T_SEND_TM DESC)
         WHERE ROWNUM < 2;
      END IF;
      
      IF V_SEND_TIME >= 10 THEN
        
        Validate_Ret := 2;
        RETURN;
      END IF;
    END IF;
    
    IF V_DAY_TO_INS_BGN > 0 THEN
       Validate_Ret := 1;
      RETURN;
    END IF;

    --重复投保校验
    SELECT A.T_INSRNC_BGN_TM, A.T_INSRNC_END_TM
      INTO V_PLY_START_TIME, V_PLY_END_TIME
      FROM WEB_APP_BASE A
     WHERE A.C_APP_NO = App_No ; 
     FOR REC_DIFF_TIME IN (SELECT V_PLY_END_TIME -
                                 A.T_INSRNC_BGN_TM DIFF_TIME1,
                                 V_PLY_START_TIME -
                                 A.T_INSRNC_END_TM DIFF_TIME2
                            FROM WEB_PLY_BASE A, WEB_PLY_VHL B
                           WHERE A.C_APP_NO = B.C_APP_NO
                             AND A.C_PLY_STS = 'I'
                             AND A.C_PROD_NO = V_PROD_NO
                             AND B.C_FRM_NO IN
                                 (SELECT C_FRM_NO
                                    FROM WEB_APP_VHL A
                                   WHERE A.C_APP_NO = App_No)) LOOP
      IF (REC_DIFF_TIME.DIFF_TIME1 > 0 AND REC_DIFF_TIME.DIFF_TIME2 < 0) THEN
        Validate_Ret := 3;
        RETURN;
      END IF;
    END LOOP;

  END P_EPCIS_AUTO_APP_VALIDATE;

END PKG_EPCIS_AUTO_APP_VALIDATE;