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;