存储过程学习

/**
 数据清洗 第一个 存储过程，后面还有一个 调用另一个存储过程
 */
CREATE  PROCEDURE `bank_data_clear`()
BEGIN -- 语句开始
   -- 定义变量
   -- 相对方信息
   DECLARE opid int;
   DECLARE opname VARCHAR(255);
   DECLARE bankCount int DEFAULT 0;
   declare bankIds VARCHAR(500);
   DECLARE s int DEFAULT 0 ;
   -- 银行信息
   declare bankName VARCHAR(500);
   declare bankId int;
   DECLARE isHT int DEFAULT 0;
   -- 数据湖信息
   DECLARE isDataLake int DEFAULT 0;
   declare cnaps VARCHAR(500) DEFAULT '';
   declare dataLakeName VARCHAR(500) DEFAULT '';
   declare dataLakeId VARCHAR(20) DEFAULT '';
   -- 定义游标，并将sql结果集赋值到游标中
   DECLARE opposite CURSOR for (SELECT lco.ID AS opid,lco.FULL_NAME AS opname,    count( lcb.id ) AS bankCount,  GROUP_CONCAT( lcb.id ) AS bankIds FROM LAS_CONTRACT_OPPOSITE AS lco   LEFT JOIN las_contract_bank_info AS lcb ON lco.ID = lcb.OPPOSITE_ID WHERE  lco.SYSTEM_NAME = ''   OR lco.SYSTEM_NAME IS NULL GROUP BY    lco.ID HAVING count( lcb.id )>0 order by 1 desc);
   -- 声明当游标遍历完后将标志变量置成某个值
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
   -- 打开游标
   open opposite;
   -- 将游标中的值赋值给变量，注意：变量名不要和返回的列名同名，变量顺序要和sql结果列的顺序一致
      fetch opposite into opid,opname,bankCount,bankIds;
         -- 当s不等于1，也就是未遍历完时，会一直循环
      while s<>1 do
            -- 执行逻辑
            #INSERT INTO datalake_clear(`id`,`desc`) VALUES(opid,'我要更新');
            if bankCount=1 then
                     select lcbi.BANK_OF_DEPOSIT,lcbi.ID into bankName,bankId from las_contract_bank_info as lcbi where lcbi.OPPOSITE_ID = opid;
                     SET isHT= (select count(1) as isHT from LAS_CONTRACT_OPPOSITE_RELATED as lcor where lcor.BANK_ID = bankId);
                     if isHT<>0 then
                        #INSERT INTO datalake_clear(`id`,`desc`) VALUES(bankId,'签了');
                        set bankName = (TRIM(bankName));
                        set isDataLake =(select count(1) as isDataLake FROM data_lake_bank where `OPBANKNAME` = CONCAT(bankName));
                        #INSERT INTO datalake_clear(`id`,`desc`) VALUES(bankId,concat(bankName,isDataLake));
                        if  isDataLake>0 then
                           # 我要更新
                              (select dlb.ID,dlb.OPBANKNAME,dlb.`CNAPS`,count(1) as isDataLake into dataLakeId,dataLakeName,cnaps,isDataLake from data_lake_bank as dlb where dlb.`OPBANKNAME` = CONCAT(bankName) GROUP BY 1);
                              INSERT INTO datalake_clear(`id`, `desc`, `type`, `opId`, `cnaps`, `bankName`, `dataLakeId`) VALUES(bankId,concat('我要更新-',bankName,'-',opId),1,opId, cnaps, dataLakeName, dataLakeId);
                              set isDataLake =0;
                              set cnaps =null;
                              set dataLakeName=null;
                              set dataLakeId=0;
                        else
                              #没有找到联行号
                              INSERT INTO datalake_clear(`id`, `desc`, `type`, `opId`, `cnaps`, `bankName`, `dataLakeId`) VALUES(bankId,concat('没有找到联行号-只有一条银行信息-',bankName,'-',opId),2,opId, NULL, NULL, NULL);
                        end if;
                     else
                        # 未签合同
                        INSERT INTO datalake_clear(`id`, `desc`, `type`, `opId`, `cnaps`, `bankName`, `dataLakeId`) VALUES(bankId,concat('未签合同-只有一条银行信息-',bankName,'-',opId),3,opId, NULL, NULL, NULL);
                     end if;
               elseif bankCount>1 then
                  CALL bank_data_more(opid);
            end if;
            fetch opposite into opid,opname,bankCount,bankIds;
      end while;
      -- 关闭游标
      close opposite;
end
/* end 第一个结束 */

/*
    第二个存储过程 第一个调用
 */
CREATE PROCEDURE `bank_data_more`( IN `opId` BIGINT )
BEGIN
   -- 银行信息
   DECLARE    bankId_b INT;
   DECLARE bankName_b VARCHAR(500);
   DECLARE    isHT_b INT DEFAULT 0;
   DECLARE j INT DEFAULT 0;
   -- 数据湖信息
   DECLARE isDataLake_b int DEFAULT 0;
   declare cnaps_b VARCHAR(500);
   declare dataLakeName_b VARCHAR(500);
   declare dataLakeId_b int;
   declare datalekeCount int DEFAULT 0;
   -- 定义银行游标
   DECLARE bankInfo CURSOR FOR
      SELECT
         lcbi.ID AS bankId_b,
         lcbi.BANK_OF_DEPOSIT AS bankName_b,
         count( lcor.ID ) AS isHT_b,
         count(DISTINCT dlb.ID) as isDataLake_b,
         GROUP_CONCAT(DISTINCT dlb.CNAPS) as cnaps_b,GROUP_CONCAT(DISTINCT dlb.ID) as dataLakeId_b
      FROM
         las_contract_bank_info AS lcbi
         left join data_lake_bank as dlb on lcbi.BANK_OF_DEPOSIT = TRIM(dlb.OPBANKNAME)
         LEFT JOIN LAS_CONTRACT_OPPOSITE_RELATED AS lcor ON lcor.BANK_ID = lcbi.ID
      WHERE
         lcbi.OPPOSITE_ID = opId
      GROUP BY
         1 ;
   -- 用户循环判断银行
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET j = 1;
   OPEN bankInfo;
      FETCH bankInfo INTO bankId_b,bankName_b,isHT_b,isDataLake_b,cnaps_b,dataLakeId_b;
         WHILE j <> 1 DO
            if isHT_b>0 then
                  set bankName_b= TRIM(bankName_b);
                     if isDataLake_b<>0 then
                        set datalekeCount = (select count(1) as datalekeCount from datalake_clear as dd where dd.type=1 and dd.opId=opid);
                        if datalekeCount = 0 then
                           INSERT INTO datalake_clear(`id`, `desc`, `type`, `opId`,`cnaps`, `bankName`, `dataLakeId`) VALUES(bankId_b,concat('我要更新 多条-',bankName_b,'-',opId),1,opId, cnaps_b, bankName_b, dataLakeId_b);
                        else
                           INSERT INTO datalake_clear(`id`, `desc`, `type`, `opId`,`cnaps`, `bankName`, `dataLakeId`) VALUES(bankId_b,concat('存在多个联行已关联-',bankName_b,'-',opId),4,opId, cnaps_b, bankName_b, dataLakeId_b);
                           UPDATE datalake_clear SET `desc` = concat('存在多个联行已关联-',`bankName`,'-',opId),`type` = 4 WHERE `type` = 1 AND `opId` = opId;
                        end if;
                     else
                        #未找到关联银行
                        INSERT INTO datalake_clear(`id`, `desc`, `type`, `opId`,`cnaps`, `bankName`, `dataLakeId`) VALUES(bankId_b,concat('未找到关联联行号-',bankName_b,'-',opId),2,opId, NULL, NULL, NULL);
                     end if;
            else
               #未签合同
               INSERT INTO datalake_clear(`id`, `desc`, `type`, `opId`,`cnaps`, `bankName`, `dataLakeId`) VALUES(bankId_b,concat('未签合同-只有一条银行信息多条-',bankName_b,'-',opId),3,opId, NULL, NULL, NULL);
            end if;
            FETCH bankInfo INTO bankId_b,bankName_b,isHT_b,isDataLake_b,cnaps_b,dataLakeId_b;
   END WHILE; -- 关闭游标
CLOSE bankInfo;
END