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为例。