Mysql儲存過程基礎（案例+程式碼）

StrayCat發表於2018-09-07
MySql儲存過程

  本人在最近的專案開發中經常使用到儲存過程（PROCEDURE），所以想通過本文借用一個簡單示例來分享一個基礎的儲存過程（本文中的面試題目僅僅只是起到一個引導作用，以方便理解示例中的儲存過程）。

題目：

  假設表card_info裡面有下列欄位：
  ID（表id） card_num（卡號） card_balance（卡餘額） card_jifen（卡積分）
  1，如需要將卡號的第3-6位為5432的會員卡，加200塊錢的SQL命令；
  2，如需要將同樣滿足上述條件的卡，先扣1000積分，之後再加200塊錢，且積分不足1000時不操作，最後還需返回執行命令的卡數量。

-- 建立表結構：
DROP TABLE IF EXISTS card_info;

CREATE TABLE card_info (
	id VARCHAR(32) NOT NULL COMMENT 'ID',
	card_num VARCHAR(10) NOT NULL COMMENT '卡號',
	card_balance DOUBLE(10,2) DEFAULT 0 COMMENT '卡餘額',
	card_jifen INT(10) DEFAULT 0 COMMENT '卡積分',
	PRIMARY KEY (id)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
複製程式碼

-- 插入示例資料：
INSERT INTO card_info(id, card_num, card_balance, card_jifen) VALUES(REPLACE(UUID(),'-',''), '0123456789', 100, 100);
INSERT INTO card_info(id, card_num, card_balance, card_jifen) VALUES(REPLACE(UUID(),'-',''), '0154326789', 0, 1000);
INSERT INTO card_info(id, card_num, card_balance, card_jifen) VALUES(REPLACE(UUID(),'-',''), '0154329876', 0, 2000);
複製程式碼

解答：

1、使用一般SQL語句：

1.UPDATE card_info SET card_balance = card_balance + 200 WHERE SUBSTR(card_num FROM 3 FOR 4) = '5432';
複製程式碼

2.UPDATE card_info SET card_balance = card_balance + 200, card_jifen = card_jifen - 1000 WHERE SUBSTR(card_num FROM 3 FOR 4) = '5432' AND card_jifen >= 1000;
複製程式碼

2、使用儲存過程：

第一步：建立儲存過程：

DROP PROCEDURE IF EXISTS test_proc;

DELIMITER // -- 定義結束符，Mysql預設結束符';'
CREATE PROCEDURE test_proc (IN param VARCHAR(32), OUT result INTEGER(10)) -- 輸入輸出引數（IN\OUT\INOUT）
BEGIN
-- DECLARE定義變數
DECLARE cardId, cardNum VARCHAR(32);
DECLARE cardBalance, cardJifen DOUBLE(10,2);
-- 定義迴圈結束標記
DECLARE flag INT DEFAULT TRUE;
-- 定義遊標
DECLARE cardInfo CURSOR FOR SELECT id, card_num, card_balance, card_jifen FROM card_info WHERE SUBSTR(card_num FROM 3 FOR 4) = param;
-- 將結束標記繫結到遊標（Sql Server使用@@FETCH_STATUS）
DECLARE CONTINUE HANDLER FOR NOT FOUND SET flag = FALSE;

SET result = 0;
-- 開啟遊標
OPEN cardInfo;
-- 從遊標中取值，交給變數
FETCH cardInfo INTO cardId, cardNum, cardBalance, cardJifen;
-- 開始迴圈
WHILE flag DO

	IF cardJifen >= 1000 THEN
		SET result = result +1;
		UPDATE card_info SET card_jifen = cardJifen - 1000, card_balance = cardBalance + 200 WHERE ID = cardId;
	END IF;

	-- 從遊標中取下一組值，交給變數
	FETCH cardInfo INTO cardId, cardNum, cardBalance, cardJifen;
END WHILE;
-- 關閉遊標，Sql server釋放遊標（DEALLOCATE）
CLOSE cardInfo;

END;
    // -- 整個過程結束
DELIMITER ; -- 將結束符改回預設
複製程式碼

第二步：呼叫儲存過程：

-- 定義引數和結果變數，呼叫儲存過程
SET @param = '5432';
CALL test_proc(@param, @result);
SELECT @result;
複製程式碼

  注意：Mysql和Sql Server中的儲存過程略微不同，例如：Sql Server中從遊標取值（FETCH NEXT FROM * INTO）時會有一個狀態@@TETCH_STATUS，不需要手動定義結束標記和繫結到遊標的操作；Sql Server中在關閉遊標後還需要手動釋放遊標（DEALLOCATE *）的操作。。。本文僅以Mysql為例。

相關文章