一、概念及形式

存儲(chǔ)過程就是作為可執(zhí)行對(duì)象存放在數(shù)據(jù)庫中的一個(gè)或多個(gè)SQL命令，通俗來講存儲(chǔ)過程其實(shí)就是能完成一定操作的一組SQL語句。

1、自定義語句結(jié)束符

DELIMITER $$

2、創(chuàng)建

使用CREATE動(dòng)作及PROCEDURE關(guān)鍵字進(jìn)行過程創(chuàng)建，一般格式為：

CREATE PROCEDURE 進(jìn)程名稱 ([IN/OUT/INOUT 參數(shù)名 類型])

3、開始/結(jié)束

整個(gè)存儲(chǔ)過程以BEGIN和END作為關(guān)鍵字，對(duì)其余進(jìn)行語句包裹，一般格式為：

BEGIN......END

3.1 會(huì)話變量（用戶變量）

可以在一個(gè)客戶端會(huì)話的任何地方聲明，作用域是整個(gè)會(huì)話，會(huì)話斷開后，會(huì)話變量也就消失。會(huì)話變量名以@開頭，使用SET直接賦值，在一個(gè)會(huì)話內(nèi)，會(huì)話變量只需初始化一次。

SET @變量名 = 初始值

3.2 存儲(chǔ)過程變量

只能在存儲(chǔ)過程中使用，以DECLARE為關(guān)鍵字聲明，定義格式如下

DECLARE 變量名 數(shù)據(jù)類型 [DEFAULT VALUE]

二、游標(biāo)

1、簡(jiǎn)述

游標(biāo)實(shí)際上是一種能從包括多條數(shù)據(jù)記錄的結(jié)果集中每次提取一條記錄的機(jī)制。游標(biāo)可以被看作是一個(gè)查詢結(jié)果集（可以是零條、一條或由相關(guān)的選擇語句檢索出的多條記錄）和結(jié)果集中指向特定記錄的游標(biāo)位置組成的一個(gè)臨時(shí)文件，提供了在查詢結(jié)果集中向前或向后瀏覽數(shù)據(jù)、處理結(jié)果集中數(shù)據(jù)的能力，是處理數(shù)據(jù)的一種方法。

2、聲明

使用之前必須先聲明，可用DECLARE CURSOR語句聲明游標(biāo)，其一般格式為：

DECLARE CURSOR 游標(biāo) FOR SELECT-查詢塊

3、打開

使用OPEN語句的功能打開游標(biāo)，實(shí)際上就是執(zhí)行相應(yīng)的SELECT查詢語句，將查詢結(jié)果放到緩沖區(qū)。這時(shí)游標(biāo)處于活動(dòng)狀態(tài)，游標(biāo)指針指向第一條記錄。其一般格式為：

OPEN 游標(biāo)

4、提取

FETCH語句是從游標(biāo)中提取特定的一行。其一般格式為：

FETCH [[NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n]FROM] <游標(biāo)名> [INTO：<主變量1>,<主變量2>......]

參數(shù)說明：

NEXT — 如果是在OPEN后第一次執(zhí)行FETCH命令，則返回結(jié)果集的第一行，否則使游標(biāo)的指針指向結(jié)果集的下一行，NEXT是默認(rèn)的選項(xiàng)。
PRIOR — 返回結(jié)果集當(dāng)前行的前一行。
FIRST — 返回結(jié)果集的第一行。
LAST — 返回結(jié)果集的最后一行。
ABSOLUTE n — 如果n是正數(shù)，返回結(jié)果集的第n行，如果n是負(fù)數(shù)，則返回結(jié)果集的倒數(shù)第n行。
RELATIVE n — 如果n是正數(shù)，返回當(dāng)前行后的第n行，如果n是負(fù)數(shù)，則返回當(dāng)前行開始倒數(shù)的第n行。
INTO — 該語句的功能是把游標(biāo)取出的當(dāng)前記錄送入到主變量，INTO后的主變量要與在DECLARE CURSOR中SELECT的字段相對(duì)應(yīng)（賦值的順序必須和取出的順序一致）。

5、遍歷

5.1 WHILE方式

OPEN 游標(biāo);
FETCH 游標(biāo) INTO 存儲(chǔ)過程變量;
WHILE 循環(huán)條件 DO
執(zhí)行操作
FETCH 游標(biāo) INTO 存儲(chǔ)過程變量 ;
END WHILE;
CLOSE 游標(biāo);

先判斷后執(zhí)行——使用WHILE循環(huán)時(shí)，需要在循環(huán)之前進(jìn)行一次FETCH動(dòng)作，游標(biāo)的屬性才會(huì)起作用，而且數(shù)據(jù)處理動(dòng)作必須放在循環(huán)體內(nèi)的FETCH方法之前，循環(huán)體內(nèi)的FETCH方法要放在最后，否則游標(biāo)的循環(huán)會(huì)多執(zhí)行一次。????????

5.2 REPEAT方式

OPEN 游標(biāo);
REPEAT
執(zhí)行操作;
UNTIL 退出條件 END REPEAT;
CLOSE 游標(biāo);

先執(zhí)行后判斷——在REPEAT語句中不管是否滿足給定條件，首先會(huì)執(zhí)行一次循環(huán)體中的操作，然后再在UNTIL中判斷給定的條件是否成立，如果條件不成立會(huì)繼續(xù)執(zhí)行，如果條件成立則退出REPEAT循環(huán)。

5.3 LOOP方式

-- label為循環(huán)別名
label: LOOP
執(zhí)行語句
IF 退出條件 THEN
LEAVE label;
END IF;
END LOOP label;

LOOP為死循環(huán)，需要手動(dòng)退出循環(huán)，其中可以把LEAVE看成JAVA中的BREAK，ITERATE看成JAVA的CONTINUE。

ps：在遍歷的過程中，當(dāng)循環(huán)的結(jié)束條件是定義的變量值發(fā)生變化（例如FLAG=0->FLAG=1），可能會(huì)導(dǎo)致遍歷的次數(shù)+1的情況，是由于當(dāng)遍歷到游標(biāo)的最后一條數(shù)據(jù)之后，此時(shí)的變量還是原值（FLAG=0），執(zhí)行第N+1次循環(huán)，但是在執(zhí)行FETCH語句時(shí)發(fā)現(xiàn)沒有數(shù)據(jù)可用，變量值發(fā)生改變（FLAG=1），但后續(xù)語句依舊執(zhí)行并跳出循環(huán)，因此導(dǎo)致了多執(zhí)行一次的情況，解決方式：在FETCH語句與執(zhí)行語句間增加判斷條件（IF FLAG = 0 THEN......）。

6、關(guān)閉

使用CLOSE 語句關(guān)閉游標(biāo)，其一般格式為：

CLOSE 游標(biāo)

三、動(dòng)態(tài)SQL及執(zhí)行

1、創(chuàng)建

建議使用concat字符串連接函數(shù)進(jìn)行字符串拼接，并賦值給定義完成的變量，其一般格式為：

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

2、執(zhí)行

動(dòng)態(tài)語句的執(zhí)行分為準(zhǔn)備、執(zhí)行和消除三個(gè)階段

PREPARE <別名> FROM <SQL語句>|<變量名>;
EXECUTE <別名>;
DEALLOCATE PREPARE <別名>;

以下示例展示動(dòng)態(tài)游標(biāo)（利用游標(biāo)和動(dòng)態(tài)SQL的組合）、多重游標(biāo)、避免存儲(chǔ)N+1次循環(huán)結(jié)果的示例，具體存儲(chǔ)過程語句如下：

-- 次自定義結(jié)束符
DELIMITER $$DROP PROCEDURE IF EXISTS MigrateReproducibl--eData $$
-- 聲明存儲(chǔ)過程
CREATE PROCEDURE `MigrateReproducibleData`(IN `queryType` VARCHAR ( 100 ),IN `queryValue` VARCHAR ( 100 ))BEGIN
-- 定義局部變量存儲(chǔ)main表中的數(shù)據(jù)
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 );-- 定義外層游標(biāo)循環(huán)標(biāo)記
DECLARE mainDone INT DEFAULT FALSE;
DECLARE mainSet CURSOR FOR SELECT * from mainView;
-- 定義游標(biāo)循環(huán)結(jié)束時(shí)的標(biāo)記操作	
DECLARE CONTINUE HANDLER FOR NOT FOUND SET mainDone = TRUE;DROP VIEW IF EXISTS mainView;
-- 定義外層游標(biāo)的數(shù)據(jù)集合
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 memberIDFROM reproducible_member_bill_main mainLEFT JOIN t_rse_subsidy_project project ON main.PROJECT_CODE = project.PROJECT_CODELEFT JOIN market_member member ON member.MEMBER_CODE = main.MEMBER_ID WHERE main.', `queryType`, ' = "', `queryValue`, '"' );-- 執(zhí)行動(dòng)態(tài)語句
PREPARE doMain FROM @mainSQL;
EXECUTE doMain;
DEALLOCATE PREPARE doMain;-- 外層游標(biāo)循環(huán)開始
OPEN mainSet;
WHILE NOT mainDone D-- 取出數(shù)據(jù)-并賦值
FETCH mainSet INTO billId,billCode,billType,totalFee,companyId,projectId,memberId;
-- 避免第N+1次操作		
IF NOT mainDone THEN -- 開始內(nèi)層游標(biāo)BEGIN-- 定義局部變量存儲(chǔ)detaiL中的數(shù)據(jù)DECLARE tradeDate VARCHAR ( 100 );DECLARE actualAmount VARCHAR ( 100 );DECLARE actualAmountNoTax VARCHAR ( 100 );-- 定義內(nèi)層游標(biāo)循環(huán)標(biāo)記DECLARE dateDone INT DEFAULT FALSE;-- 定義內(nèi)層游標(biāo)數(shù)據(jù)集合DECLARE dateSet CURSOR FOR SELECT * from dateView;-- 定義內(nèi)層游標(biāo)循環(huán)結(jié)束時(shí)的操作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;-- 開啟內(nèi)層游標(biāo)，對(duì)不同支付日期的數(shù)據(jù)進(jìn)行抽取OPEN dateSet;-- 內(nèi)層循環(huán)開始		WHILE NOT dateDone DOFETCH dateSet INTO tradeDate;-- 增加判斷，游標(biāo)循環(huán)結(jié)束后，不插入數(shù)據(jù)，減少多一次的循環(huán)IF NOT dateDone THEN -- 獲取“YES”狀態(tài)數(shù)據(jù)SELECT `VALUE` INTO actualAmount FROM reproducible_bill_detail WHERE BILL_ID = billid AND TRADE_DATE = tradeDate AND SETTLEMENT_ITEM_ID = 'YES';-- 獲取“NO”狀態(tài)數(shù)據(jù)	SELECT `VALUE` INTO actualAmountNoTax FROM reproducible_bill_detail WHERE BILL_ID = billid AND TRADE_DATE = tradeDate AND SETTLEMENT_ITEM_ID = 'No';-- 更新數(shù)據(jù)表	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 ;-- 結(jié)束內(nèi)循環(huán)END WHILE ; -- 關(guān)閉內(nèi)部游標(biāo)CLOSE dateSet;END;
END IF;
-- 結(jié)束外循環(huán)	
END WHILE ;
-- 關(guān)閉外部游標(biāo)
CLOSE mainSet;END $$DELIMITER;