记一次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 '银行卡认领加工';--注释

上一篇：记一次上线的曲折之路（存储过程执行失败）

下一篇：【CentOS 7】记一次迁移 Docker 容器存储目录的操作过程

记一次DB2存储过程的创建

ASP之简化创建关闭记录集对象并创建使用简单的MSSQL存储过程

探究DB2中RDF数据的特征并描述创建优化存储的流程

深入mysql创建自定义函数与存储过程的详解_MySQL

phpmyadmin创建mysql的存储过程

mysql-MySql命令行下创建存储过程的语法错误

存储过程中创建表时 ORA-01031: insufficient privileges的解决

MySQL创建递归型的存储过程

SQLServer存储过程创建和修改的实现代码

MySQL 存储过程的创建方案

记一次数据库调优过程（IIS发过来SQLSERVER 的FETCH API