欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

记一次DB2存储过程的创建

程序员文章站 2022-07-13 08:04:12
...

虽然曾经学过Oracle的存储过程,但是一年来工作中从来没用到。

如今换了家对银行业务的公司,由于工作需要DB2的存储过程,所以采用自上而下有针对的学习方式进行补习,贴上学习笔记。

/**变量赋值*/
SET SCHEMA = 'BCAS';
/**专用寄存器*/
SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","DB2ADMIN","BCAS";
/**CREATE PROCEDURE <schema-name>.<procedure-name> (参数) [属性] <语句>*/
CREATE OR REPLACE PROCEDURE "BCAS"."P_B_BPLUS_CLAIM_CARD" (
    IN "IC_RQ"	VARCHAR(10),
    OUT "OI_ERRCODE"	INTEGER,
    OUT "OC_ERRTEXT"	VARCHAR(500) )
	/**赋给存储过程一个唯一名称,如果不指定,系统将生成一个惟一的名称*/
  SPECIFIC "SQL170419101918800"
  /**指定存储过程使用的语言。LANGUAGE SQL 是其默认值。还有其它的语言供选择,比如Java 或者C,可以将这一属性值分别设置为LANGUAGE JAVA 或者 LANGUAGE C*/
  LANGUAGE SQL
  /**DETERMINISTIC or NOT DETERMINISTIC:表示存储过程是动态或者非动态的。动态的返回的值是不确定的。非动态的存储过程每次执行返回的值是相同的*/
  NOT DETERMINISTIC
  /**EXTERNAL ACTION or NO EXTERNAL ACTION:表示存储过程是否执行一些改变理数据库状态的活动,而不通过数据库管理器管。
  默认是 EXTERNAL ACTION。如果指定为NO EXTERNAL ACTION ,则数据库会确定最最佳优化方案*/
  EXTERNAL ACTION
  /** MODIFIES SQL DATA: 表示存储过程可以执行任何 SQL 语句。可以对数据库中的数据进行增加、删除和修改*/
  MODIFIES SQL DATA
  /**OLD SAVEPOINT LEVEL or NEW SAVEPOINT LEVEL:建立存储点。OLD SAVEPOINT LEVEL是默认的存储点*/
  OLD SAVEPOINT LEVEL
BEGIN 
/**
描述:银行卡认领处理
参数:IC_RQ:当前日期
日期:2017-03-03
UPDATE:HUJX
*/
/**定义变量*/
  DECLARE SQLCODE INTEGER DEFAULT 0;--
  DECLARE SQLSTATE CHAR(5) DEFAULT '00000';--
  DECLARE C_BNOTE VARCHAR(200);--
  DECLARE C_SQL VARCHAR(5000);--
  DECLARE C_NOW_DATA VARCHAR(10);--
  DECLARE C_RLFS VARCHAR(2);--
  DECLARE C_TRAN_STARTDATE VARCHAR(10);--


  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION  --当发生SQL异常时的处理(CONTINUE 在处理器操作完成之后,会继续执行产生这个异常语句之后的下一条语句)
  SET OI_ERRCODE = SQLCODE;--
  SET OC_ERRTEXT = SQLSTATE;--

  SET C_BNOTE = '处理数据开始......';--
  SET C_BNOTE = '处理开卡认领关系开始......';--

  select VALUES into C_TRAN_STARTDATE from BCAS_SYSCODE where ID='71';--2017-2-20 全量装载开始日期
   --银行卡认领方式  1.系统认领;2.开卡自动认领;3.分户账认领;
  SELECT VALUES into C_RLFS FROM BCAS_SYSCODE WHERE CODE='CARD_CLAIM';--银行卡认领方式 1.系统认领;2.开卡自动认领;3.分户账认领;4.营销范围认领
  IF C_TRAN_STARTDATE = IC_RQ   --如果输入时间=2017-2-20
    THEN
       SET C_NOW_DATA = '1900-01-01';  --全量跑设置起始时间
    ELSE
       SET C_NOW_DATA = IC_RQ;   --非全量跑设置为当前时间
    END IF;--
  --系统认领(剔除社保卡之后)
  IF C_RLFS ='1' THEN 
    SET C_BNOTE = '......1、开始银行卡系统认领......';--
    INSERT INTO BPLUS_CLAIM_CARD (ID,CARD_NO,USER_ID,PERCENT,START_DT,END_DT,CREATE_ID,CREATE_DT,UPDATE_ID --银行卡认领表
    ,UPDATE_DT,CARD_FLG,AEA_DC_MARK,CARD_TYPE,CUST_NAME,BELONG_INSTN_COD,DEPT_ID,OPAC_DT,CLSD_DT,STATUS,FIRST_CL_FLG
    ,CHECK_INSTN_COD,CHECK_CL_FLG,TYPE)           --DISTINCT 去重
    SELECT F_GET_MAX_KEY('BCAS') ID,T.* FROM (SELECT DISTINCT CARD_NO, '00000000' USERID,100.00 PERCENT,IC_RQ START_DT,'2099-12-31' END_DT,'00000000' CREATE_ID,
      IC_RQ CREATE_DT,'00000000' UPDATE_ID,IC_RQ UPDATE_DT,CARD_FLG,AEA_DC_MARK,CARD_TYPE ,CUST_NAME
      ,BELONG_INSTN_COD,A.BELONG_INSTN_COD DEPT_ID,CARD_OPAC_DT OPAC_DT,CLSD_DT,'1' STATUS,'1' FIRST_CL_FLG,
      GRANT_INSTN_COD CHECK_INSTN_COD, '0' CHECK_CL_FLG,'10' TYPE FROM B_CARD_INFO A WHERE SUBSTR(A.CARD_NO,1,10) != (SELECT VALUES FROM bcas_syscode WHERE id='115')
	  AND CARD_OPAC_DT>=C_NOW_DATA )T;--B_CARD_INFO ATM信息表
    IF OI_ERRCODE <> 0  THEN 	
        GOTO ERROR_RETURN;--	
    END IF;   --
  END IF;--
  
  --开卡自动认领
  IF C_RLFS ='2' THEN 
      SET C_BNOTE = '......2、开始银行卡开卡自动认领......';--
     INSERT INTO BPLUS_CLAIM_CARD (ID,CARD_NO,USER_ID,PERCENT,START_DT,END_DT,CREATE_ID,CREATE_DT,UPDATE_ID
      ,UPDATE_DT,CARD_FLG,AEA_DC_MARK,CARD_TYPE,CUST_NAME,BELONG_INSTN_COD,DEPT_ID,OPAC_DT,CLSD_DT,STATUS,FIRST_CL_FLG
      ,CHECK_INSTN_COD,CHECK_CL_FLG,TYPE) 
      SELECT F_GET_MAX_KEY('BCAS') ID,T.* FROM (SELECT DISTINCT CARD_NO,nvl(A.MANAGE_ID,'00000000') USERID,100.00 PERCENT,IC_RQ START_DT,'2099-12-31' END_DT,'11111111' CREATE_ID,
        IC_RQ CREATE_DT,'00000000' UPDATE_ID,IC_RQ UPDATE_DT,CARD_FLG,AEA_DC_MARK,CARD_TYPE ,CUST_NAME
        ,BELONG_INSTN_COD,NVL(B.USERDEPT,A.BELONG_INSTN_COD) DEPT_ID,CARD_OPAC_DT OPAC_DT,CLSD_DT,'1' STATUS,'1' FIRST_CL_FLG,
        GRANT_INSTN_COD CHECK_INSTN_COD, '0' CHECK_CL_FLG,'10' TYPE FROM B_CARD_INFO A 
        LEFT JOIN SS_USER_BAK B ON A.MANAGE_ID = B.NAME AND B.SSSQ=IC_RQ WHERE SUBSTR(A.CARD_NO,1,10) != (SELECT VALUES FROM bcas_syscode WHERE id='115')
		AND  CARD_OPAC_DT>=C_NOW_DATA) T;--
    IF OI_ERRCODE <> 0  THEN 	
        GOTO ERROR_RETURN;--	
    END IF;   --
  END IF;--
  
  IF C_RLFS='3' THEN
    SET C_BNOTE = '......3、开始银行卡分户账认领......';--
  insert into A_TEST1 values('银行卡分户账认领');--
 INSERT INTO BPLUS_CLAIM_CARD (ID,CARD_NO,USER_ID,PERCENT,START_DT,END_DT,CREATE_ID,CREATE_DT,UPDATE_ID
  ,UPDATE_DT,CARD_FLG,AEA_DC_MARK,CARD_TYPE,CUST_NAME,BELONG_INSTN_COD,DEPT_ID,OPAC_DT,CLSD_DT,STATUS,FIRST_CL_FLG
  ,CHECK_INSTN_COD,CHECK_CL_FLG,TYPE) 
  SELECT F_GET_MAX_KEY('BCAS') ID,T.* FROM (SELECT DISTINCT CARD_NO,nvl(A.MANAGE_ID,'00000000') USERID,100.00 PERCENT,IC_RQ START_DT,'2099-12-31' END_DT,'22222222' CREATE_ID,
    IC_RQ CREATE_DT,'00000000' UPDATE_ID,IC_RQ UPDATE_DT,CARD_FLG,AEA_DC_MARK,CARD_TYPE ,CUST_NAME
    ,BELONG_INSTN_COD,NVL(B.USERDEPT,A.BELONG_INSTN_COD) DEPT_ID,CARD_OPAC_DT OPAC_DT,CLSD_DT,'1' STATUS,'1' FIRST_CL_FLG,
    GRANT_INSTN_COD CHECK_INSTN_COD, '0' CHECK_CL_FLG,'10' TYPE FROM B_CARD_INFO A 
    LEFT JOIN SS_USER_BAK B ON A.MANAGE_ID = B.NAME AND B.SSSQ=IC_RQ WHERE CARD_OPAC_DT>=C_NOW_DATA
	AND SUBSTR(A.CARD_NO,1,10) != (SELECT VALUES FROM bcas_syscode WHERE id='115') )T;--
    
    IF OI_ERRCODE <> 0  THEN 	
        GOTO ERROR_RETURN;--	
    END IF;  --
      insert into A_TEST1 values('2银行卡分户账认领');  --
    --将已存在的认领关系置为失效
    UPDATE BPLUS_CLAIM_CARD C SET END_DT = C_NOW_DATA,UPDATE_DT=C_NOW_DATA,STATUS = '0' WHERE C.STATUS = '1' AND 
    EXISTS (SELECT 1 FROM B_CARD_INFO B WHERE B.CARD_NO = C.CARD_NO AND B.MANAGE_ID <> C.USER_ID);--
    
    IF OI_ERRCODE <> 0  THEN 	
        GOTO ERROR_RETURN;--	
    END IF;  --
    --更新插入新的认领关系
  INSERT INTO BPLUS_CLAIM_CARD (ID,CARD_NO,USER_ID,PERCENT,START_DT,END_DT,CREATE_ID,CREATE_DT,UPDATE_ID
      ,UPDATE_DT,CARD_FLG,AEA_DC_MARK,CARD_TYPE,CUST_NAME,BELONG_INSTN_COD,DEPT_ID,OPAC_DT,CLSD_DT,STATUS,FIRST_CL_FLG
      ,CHECK_INSTN_COD,CHECK_CL_FLG,TYPE) 
      SELECT F_GET_MAX_KEY('BCAS') ID,T.* FROM (SELECT DISTINCT CARD_NO,nvl(A.MANAGE_ID,'00000000') USERID,100.00 PERCENT,IC_RQ START_DT,'2099-12-31' END_DT,'22222222' CREATE_ID,
        IC_RQ CREATE_DT,'00000000' UPDATE_ID,IC_RQ UPDATE_DT,CARD_FLG,AEA_DC_MARK,CARD_TYPE ,CUST_NAME
        ,BELONG_INSTN_COD,NVL(B.USERDEPT,A.BELONG_INSTN_COD) DEPT_ID,CARD_OPAC_DT OPAC_DT,CLSD_DT,'1' STATUS,'1' FIRST_CL_FLG,
        GRANT_INSTN_COD CHECK_INSTN_COD, '0' CHECK_CL_FLG,'10' TYPE FROM B_CARD_INFO A 
        LEFT JOIN (SELECT * FROM SS_USER_BAK WHERE SSSQ=IC_RQ) B ON A.MANAGE_ID = B.NAME  WHERE EXISTS (SELECT 1 FROM BPLUS_CLAIM_CARD B WHERE A.CARD_NO = B.CARD_NO AND A.MANAGE_ID <> B.USER_ID)
		AND SUBSTR(A.CARD_NO,1,10) != (SELECT VALUES FROM bcas_syscode WHERE id='115')) T;--
   
    IF OI_ERRCODE <> 0  THEN 	
        GOTO ERROR_RETURN;--	
    END IF;  --
  END IF;--

  SET C_BNOTE = '处理开卡认领关系结束。';--

   --正常	
  SET OI_ERRCODE =0;--	

  SET C_BNOTE = '处理数据完成。';--


  RETURN;--	                                                                 


  --出现异常,返回异常信息	

  ERROR_RETURN:	

  SET OC_ERRTEXT = 'SQL编号【' || C_BNOTE || '】,错误代码【' || CAST(OI_ERRCODE AS VARCHAR(20)) || '】,错误信息【' || F_GET_DBCODE(OI_ERRCODE) || '】';--	
  ROLLBACK;--
  RETURN;  --	
END;

COMMENT ON PROCEDURE "BCAS"."P_B_BPLUS_CLAIM_CARD"( VARCHAR(10), INTEGER, VARCHAR(500) ) IS '银行卡认领加工';--注释