mysql存储过程试题_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为例。