DB2存储过程(Oracle对比)

DROP  PROCEDURE CDI.PRC_CIM_IDENTITY_FIND@
CREATE PROCEDURE CDI.PRC_CIM_IDENTITY_FIND(

  IN I_ID_NUM VARCHAR(21), --oracle I_ID_NUM in VARCHAR(21) DB2也有in out
  IN I_CERT_TYPE VARCHAR(5),
  IN I_NUM_TYPE INTEGER,
  OUT O_RESPNOSE_TYPE VARCHAR(1),
  OUT O_RESPNOSE_CODE VARCHAR(6)
 )
  DYNAMIC RESULT SETS 2
  LANGUAGE SQL
  NOT DETERMINISTIC
  CALLED ON NULL INPUT
  MODIFIES SQL DATA
  INHERIT SPECIAL REGISTERS

  BEGIN
      DECLARE V_ID_NUM    VARCHAR (21); --oracle:在is/as中对变量声明且没有DECLARE
   --查询号码（传入数据预处理）
      DECLARE V_CERT_TYPE    VARCHAR (8);                     --查询证件类型（传入数据预处理）
      DECLARE V_NUM_TYPE    INTEGER;                      --查询类型（传入数据预处理）1：查询号码是卡号；2：查询号码是证件号码；--3：查询号码是客户号；
      DECLARE V_CUST_UNION_ID VARCHAR(21);
      --声明出错处理
      DECLARE EXIT HANDLER FOR SQLEXCEPTIONBEGIN
            SET O_RESPNOSE_TYPE = 'E';--
            SET O_RESPNOSE_CODE = '000005';--
      END;--
      --声明结束
  
      --对传入参数空(值NULL)进行初始化
      SET V_ID_NUM = RTRIM(LTRIM(I_ID_NUM));--可代替函数trim() replace()
      SET V_CERT_TYPE = RTRIM(LTRIM(I_CERT_TYPE));--ora 赋值 :=
      SET V_NUM_TYPE = I_NUM_TYPE;
     --当V_NUM_TYPE查询类型为1时，查询结果为此卡所属客户的所有标识
     --当V_NUM_TYPE查询类型为2时，查询结果为此证件号码所属客户的所有标识；
  --当证件类型为空时，查询所有证件 --当V_NUM_TYPE查询类型为3时，查询结果为此客户号所属客户的所有标识
  IF V_CUST_UNION_ID is NULL THEN
    SET V_CUST_UNION_ID='';
  END IF;
  IF V_CERT_TYPE is NULL THEN
    SET V_CERT_TYPE='';
  END IF;
        --查询号码与查询类型不能为空
      IF V_ID_NUM IS NULL OR V_ID_NUM = '' OR V_NUM_TYPE < 1 THEN
         SET O_RESPNOSE_TYPE = 'E';
         SET O_RESPNOSE_CODE = '000003';
         RETURN;--终止存储过程继续执行
      ELSE
         --查询号码类型为1卡号并且查询号码不为空||测试数据--5218990187677975
         IF V_NUM_TYPE = 1 AND V_ID_NUM IS NOT NULL THEN
     BEGIN
   select DISTINCT CUST_UNION_ID INTO V_CUST_UNION_ID from
   CDI.CIM_CARD_INFO where CARD_NUM=V_ID_NUM WITH UR;--脏读
     END;
         --查询号码类型为2并且证件类型不为空
         ELSEIF V_NUM_TYPE = 2 AND V_CERT_TYPE <>'' THEN
     BEGIN
   select DISTINCT CUST_UNION_ID INTO V_CUST_UNION_ID from
   CDI.CIM_CUST_IDEN_INFO
   where CERT_NUM=V_ID_NUM AND CERT_TYPE_CD=V_CERT_TYPE FETCH FIRST 1 ROWS ONLY WITH UR;
     END;
         ELSEIF V_NUM_TYPE = 2 AND V_CERT_TYPE = '' THEN
     BEGIN
    SELECT CUST_UNION_ID INTO V_CUST_UNION_ID from
    CDI.CIM_CUST_IDEN_INFO
    where CERT_NUM=V_ID_NUM FETCH FIRST 1 ROW ONLY  WITH UR;--
     END;--
         ELSEIF  V_NUM_TYPE <> 1 AND V_NUM_TYPE <> 2 THEN
     BEGIN
     select DISTINCT CUST_UNION_ID INTO V_CUST_UNION_ID from
    CDI.CIM_CUST_BASE_INFO
     where CUST_REF_NUM=V_ID_NUM WITH UR;--
     END;--
         END IF;--
   IF V_CUST_UNION_ID = '' THEN
    SET O_RESPNOSE_TYPE = 'N';--
    SET O_RESPNOSE_CODE = '000004';--
   RETURN;--
   END IF;--
         IF V_NUM_TYPE = 1 THEN --查询卡号 /*卡标识*/
   BEGIN
   DECLARE RESULT_CARD CURSOR WITH RETURN FOR
     SELECT * from tableName WITH UR;--
    OPEN RESULT_CARD;--
    END;--
         ELSE --查询统一编号
   BEGIN
   DECLARE RESULT_CARD CURSOR WITH RETURN FOR
     SELECT * from tableName WITH UR;--
    OPEN RESULT_IDEN;--
    END;--
    SET O_RESPNOSE_TYPE = 'N';--
    SET O_RESPNOSE_CODE = '000000';--
         END IF;--
END@

GRANT EXECUTE ON PROCEDURE CDI.PRC_CIM_IDENTITY_FIND  TO PUBLIC;