一、概念及形式

存储过程就是作为可执行对象存放在数据库中的一个或多个SQL命令通俗来讲存储过程其实就是能完成一定操作的一组SQL语句。

1、自定义语句结束符

DELIMITER $$

2、创建

使用CREATE动作及PROCEDURE关键字进行过程创建一般格式为

CREATE PROCEDURE 进程名称 ([IN/OUT/INOUT 参数名 类型])

3、开始/结束

整个存储过程以BEGIN和END作为关键字对其余进行语句包裹一般格式为

BEGIN......END

3.1 会话变量用户变量

可以在一个客户端会话的任何地方声明作用域是整个会话会话断开后会话变量也就消失。会话变量名以@开头使用SET直接赋值在一个会话内会话变量只需初始化一次。

SET @变量名 = 初始值

3.2 存储过程变量

只能在存储过程中使用以DECLARE为关键字声明定义格式如下

DECLARE 变量名 数据类型 [DEFAULT VALUE]

二、游标

1、简述

游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标可以被看作是一个查询结果集可以是零条、一条或由相关的选择语句检索出的多条记录和结果集中指向特定记录的游标位置组成的一个临时文件提供了在查询结果集中向前或向后浏览数据、处理结果集中数据的能力是处理数据的一种方法。

2、声明

使用之前必须先声明可用DECLARE CURSOR语句声明游标其一般格式为

DECLARE CURSOR 游标 FOR SELECT-查询块

3、打开

使用OPEN语句的功能打开游标实际上就是执行相应的SELECT查询语句将查询结果放到缓冲区。这时游标处于活动状态游标指针指向第一条记录。其一般格式为

OPEN 游标

4、提取

FETCH语句是从游标中提取特定的一行。其一般格式为

FETCH [[NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n]FROM] <游标名> [INTO<主变量1>,<主变量2>......]

参数说明

NEXT — 如果是在OPEN后第一次执行FETCH命令则返回结果集的第一行否则使游标的指针指向结果集的下一行NEXT是默认的选项。
PRIOR — 返回结果集当前行的前一行。
FIRST — 返回结果集的第一行。
LAST — 返回结果集的最后一行。
ABSOLUTE n — 如果n是正数返回结果集的第n行如果n是负数则返回结果集的倒数第n行。
RELATIVE n — 如果n是正数返回当前行后的第n行如果n是负数则返回当前行开始倒数的第n行。
INTO — 该语句的功能是把游标取出的当前记录送入到主变量INTO后的主变量要与在DECLARE CURSOR中SELECT的字段相对应赋值的顺序必须和取出的顺序一致。

5、遍历

5.1 WHILE方式

OPEN 游标;
FETCH 游标 INTO 存储过程变量;
WHILE 循环条件 DO
执行操作
FETCH 游标 INTO 存储过程变量 ;
END WHILE;
CLOSE 游标;

先判断后执行——使用WHILE循环时需要在循环之前进行一次FETCH动作游标的属性才会起作用而且数据处理动作必须放在循环体内的FETCH方法之前循环体内的FETCH方法要放在最后否则游标的循环会多执行一次。        

5.2 REPEAT方式

OPEN 游标;
REPEAT
执行操作;
UNTIL 退出条件 END REPEAT;
CLOSE 游标;

先执行后判断——在REPEAT语句中不管是否满足给定条件首先会执行一次循环体中的操作然后再在UNTIL中判断给定的条件是否成立如果条件不成立会继续执行如果条件成立则退出REPEAT循环。

5.3 LOOP方式

-- label为循环别名
label: LOOP
执行语句
IF 退出条件 THEN
LEAVE label;
END IF;
END LOOP label;

LOOP为死循环需要手动退出循环其中可以把LEAVE看成JAVA中的BREAKITERATE看成JAVA的CONTINUE。

ps在遍历的过程中当循环的结束条件是定义的变量值发生变化例如FLAG=0->FLAG=1可能会导致遍历的次数+1的情况是由于当遍历到游标的最后一条数据之后此时的变量还是原值FLAG=0执行第N+1次循环但是在执行FETCH语句时发现没有数据可用变量值发生改变FLAG=1但后续语句依旧执行并跳出循环因此导致了多执行一次的情况解决方式在FETCH语句与执行语句间增加判断条件IF FLAG = 0 THEN......。

6、关闭

使用CLOSE 语句关闭游标其一般格式为

CLOSE 游标

三、动态SQL及执行

1、创建

建议使用concat字符串连接函数进行字符串拼接并赋值给定义完成的变量其一般格式为

SET <变量名> = CONCAT(STR1,STR2,...)

2、执行

动态语句的执行分为准备、执行和消除三个阶段

PREPARE <别名> FROM <SQL语句>|<变量名>;
EXECUTE <别名>;
DEALLOCATE PREPARE <别名>;

以下示例展示动态游标利用游标和动态SQL的组合、多重游标、避免存储N+1次循环结果的示例具体存储过程语句如下

-- 次自定义结束符
DELIMITER $$

DROP PROCEDURE IF EXISTS MigrateReproducibl--eData $$
-- 声明存储过程
CREATE PROCEDURE `MigrateReproducibleData`(IN `queryType` VARCHAR ( 100 ),IN `queryValue` VARCHAR ( 100 ))

BEGIN
-- 定义局部变量存储main表中的数据
DECLARE billId VARCHAR ( 100 );
DECLARE billCode VARCHAR ( 100 );
DECLARE billType VARCHAR ( 100 );
DECLARE totalFee VARCHAR ( 100 );
DECLARE companyId VARCHAR ( 100 );
DECLARE projectId VARCHAR ( 100 );
DECLARE memberId VARCHAR ( 100 );
	
-- 定义外层游标循环标记
DECLARE mainDone INT DEFAULT FALSE;
DECLARE mainSet CURSOR FOR SELECT * from mainView;
-- 定义游标循环结束时的标记操作	
DECLARE CONTINUE HANDLER FOR NOT FOUND SET mainDone = TRUE;
	
DROP VIEW IF EXISTS mainView;
-- 定义外层游标的数据集合
set @mainSQL = CONCAT('CREATE VIEW mainView AS',
'SELECT main.BILL_ID,main.BILL_CODE,main.BILL_TYPE,main.TOTAL_FEE,main.COMPANY_ID,project.ID AS projectID,member.ID AS memberID
    FROM reproducible_member_bill_main main
    LEFT JOIN t_rse_subsidy_project project ON main.PROJECT_CODE = project.PROJECT_CODE
    LEFT JOIN market_member member ON member.MEMBER_CODE = main.MEMBER_ID 
    WHERE main.', `queryType`, ' = "', `queryValue`, '"' );
	
-- 执行动态语句
PREPARE doMain FROM @mainSQL;
EXECUTE doMain;
DEALLOCATE PREPARE doMain;
	
-- 外层游标循环开始
OPEN mainSet;
WHILE NOT mainDone D
		
-- 取出数据-并赋值
FETCH mainSet INTO billId,billCode,billType,totalFee,companyId,projectId,memberId;
-- 避免第N+1次操作		
IF NOT mainDone THEN 
    -- 开始内层游标
    BEGIN
    -- 定义局部变量存储detaiL中的数据
    DECLARE tradeDate VARCHAR ( 100 );
    DECLARE actualAmount VARCHAR ( 100 );
    DECLARE actualAmountNoTax VARCHAR ( 100 );
    -- 定义内层游标循环标记
    DECLARE dateDone INT DEFAULT FALSE;
    -- 定义内层游标数据集合
    DECLARE dateSet CURSOR FOR SELECT * from dateView;
    -- 定义内层游标循环结束时的操作
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET dateDone = TRUE;
			
    set @dateSQL = CONCAT('CREATE VIEW dateView AS ',
        'SELECT DISTINCT TRADE_DATE FROM reproducible_bill_detail WHERE BILL_ID = "', billId, '"' );
    DROP VIEW IF EXISTS dateView;
			
    PREPARE doMain FROM @dateSQL;
    EXECUTE doMain;
    DEALLOCATE PREPARE doMain;
			
    -- 开启内层游标对不同支付日期的数据进行抽取
    OPEN dateSet;
	-- 内层循环开始		
        WHILE NOT dateDone DO

        FETCH dateSet INTO tradeDate;
        -- 增加判断游标循环结束后不插入数据减少多一次的循环
        IF NOT dateDone THEN 

        -- 获取“YES”状态数据
	SELECT `VALUE` INTO actualAmount FROM reproducible_bill_detail 
	WHERE BILL_ID = billid AND TRADE_DATE = tradeDate AND SETTLEMENT_ITEM_ID = 'YES';
					
	-- 获取“NO”状态数据	
	SELECT `VALUE` INTO actualAmountNoTax FROM reproducible_bill_detail 
	WHERE BILL_ID = billid AND TRADE_DATE = tradeDate AND SETTLEMENT_ITEM_ID = 'No';
					
	-- 更新数据表	
	INSERT INTO `bill_payment_result` (`ID`,`BILL_CODE`,`MEMBER_ID`,`BILL_TYPE`,`PROJECT_CODE`,
					`TRADE_DATE`,`COST_TYPE`,`ACTUAL_AMOUNT`,`HAVA_PAID_AMOUNT`,`TOTAL_AMOUNT`,`PAYMENT_TIME`,
					`FINANCIAL_STATUS`,`DATA_STATUS`,`CREATE_TIME`,`COMPANY_ID`,`PAY_INFO_ID`,`PAY_MODE`,`DOCU_NUM`,
					`ACTUAL_AMOUNT_NO_TAX`,`PAY_STATUS`,`FAIL_MEMO`,`CASH_AMOUNT`,`BILL_AMOUNT`,`PAY_PROGRESS`)
	VALUES(REPLACE(UUID(),'-',''),billCode,memberId,billType,projectId,tradeDate,'01',actualAmount,
		actualAmount,totalFee,NULL,'16','01',DATE_FORMAT(SYSDATE(),'%Y%m%d%H%i%s'),companyId,NULL,
		NULL,NULL,actualAmountNoTax,'01',NULL,NULL,NULL,'02');

        END IF ;
        -- 结束内循环
        END WHILE ; 
    -- 关闭内部游标
    CLOSE dateSet;
			
    END;
END IF;
-- 结束外循环	
END WHILE ;
-- 关闭外部游标
CLOSE mainSet;

END $$

DELIMITER;