一、概念及形式

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

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

DELIMITER $$

2、創(chuàng)建

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

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

3、開始/結(jié)束

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

BEGIN......END

3.1 會話變量（用戶變量）

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

SET @變量名 = 初始值

3.2 存儲過程變量

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

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

二、游標(biāo)

1、簡述

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

2、聲明

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

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

3、打開

使用OPEN語句的功能打開游標(biāo)，實際上就是執(zhí)行相應(yīng)的SELECT查詢語句，將查詢結(jié)果放到緩沖區(qū)。這時游標(biāo)處于活動狀態(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)的選項。
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的字段相對應(yīng)（賦值的順序必須和取出的順序一致）。

5、遍歷

5.1 WHILE方式

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

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

5.2 REPEAT方式

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

先執(zhí)行后判斷——在REPEAT語句中不管是否滿足給定條件，首先會執(zhí)行一次循環(huán)體中的操作，然后再在UNTIL中判斷給定的條件是否成立，如果條件不成立會繼續(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)，需要手動退出循環(huán)，其中可以把LEAVE看成JAVA中的BREAK，ITERATE看成JAVA的CONTINUE。

ps：在遍歷的過程中，當(dāng)循環(huán)的結(jié)束條件是定義的變量值發(fā)生變化（例如FLAG=0->FLAG=1），可能會導(dǎo)致遍歷的次數(shù)+1的情況，是由于當(dāng)遍歷到游標(biāo)的最后一條數(shù)據(jù)之后，此時的變量還是原值（FLAG=0），執(zhí)行第N+1次循環(huán)，但是在執(zhí)行FETCH語句時發(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)

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

1、創(chuàng)建

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

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

2、執(zhí)行

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

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

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

-- 次自定義結(jié)束符
DELIMITER $$DROP PROCEDURE IF EXISTS MigrateReproducibl--eData $$
-- 聲明存儲過程
CREATE PROCEDURE `MigrateReproducibleData`(IN `queryType` VARCHAR ( 100 ),IN `queryValue` VARCHAR ( 100 ))BEGIN
-- 定義局部變量存儲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é)束時的標(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í)行動態(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-- 定義局部變量存儲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é)束時的操作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)，對不同支付日期的數(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;