MySQL 存储过程和存储函数示例

    以下示例主要用于个人温习使用，因为长时间不谢存储过程或函数自己很容易忘记。

    其他记录

CREATE DEFINER=`wpms_test`@`%` PROCEDURE `BookBusinessSerialNumber`(get_business_id varchar(40))
BEGIN
	#Routine body goes here...
	# 需求：传入服务商ID，返回当前日期的流水号，从1开始依次递增
	
	# 获取当天日期
	DECLARE curentDate date; 
	# 获取当前服务商的日期
	DECLARE businessDate date;
	# 获取当前服务商的流水号
	DECLARE serialOrder int(12);
	# 返回当前服务商的流水号
	DECLARE nowSerialOrder int(12);

	SELECT curdate() INTO curentDate;
	SELECT a.ORDER_DATE, a.ORDER_COUNT INTO businessDate, serialOrder from PM_BOOK_BUSINESS_SERIALORDER a where a.BUSINESS_ID = `get_business_id`;

#	SELECT curentDate, businessDate from dual;
	# 判断商户服务日期和当前日期是否相等
	IF(curentDate = businessDate) THEN		
		#SELECT 1 from dual;
		set nowSerialOrder = serialOrder + 1;		# 得到后一个流水号
		UPDATE PM_BOOK_BUSINESS_SERIALORDER set ORDER_COUNT = `nowSerialOrder` WHERE BUSINESS_ID = `get_business_id`;
	ELSEIF(serialOrder is NULL) THEN   # 在表PM_BOOK_BUSINESS_SERIALORDER没有这个服务商信息
		#SELECT 2 from dual;
		set nowSerialOrder = 1;
		INSERT INTO PM_BOOK_BUSINESS_SERIALORDER(BUSINESS_ID, ORDER_DATE, ORDER_COUNT) VALUES(`get_business_id`, `curentDate`, `nowSerialOrder`);
	ELSE																# 当天对应服务商生成一个新的流水号，从1开始
		#SELECT 3 from dual;
		set nowSerialOrder = 1;
		UPDATE PM_BOOK_BUSINESS_SERIALORDER set ORDER_COUNT = 1, ORDER_DATE = curentDate WHERE BUSINESS_ID = `get_business_id`;
	END IF;  

	SELECT nowSerialOrder;
#	SELECT curentDate, businessDate, serialOrder, nowSerialOrder, get_business_id;
END

    存储函数

    1、ZhangFun1

CREATE DEFINER=`wpms_test`@`%` FUNCTION `ZhangFun1`(`orderId` varchar(50)) RETURNS int(11)
BEGIN
	#Routine body goes here...
	INSERT INTO Zhang_Test(ORDER_ID,PARTNER_ID,PUBLISH_ID,COUNT,PRICE,FREIGHT)
		VALUES(orderId, 'C1001','A1001',10,5000,500);

	RETURN 0;
END

    2、vefityGoodsLimit

CREATE DEFINER=`wpms_test`@`%` FUNCTION `vefityGoodsLimit`(`publishId` varchar(50),`buyCount` int(11),`userId` varchar(50)) RETURNS varchar(200) CHARSET utf8
BEGIN


       DECLARE isLimit char(1);
       DECLARE buyLimit int(11);
       DECLARE usedBuyCount int(11);
       
       DECLARE vefityStr varchar(200);
    
       
        ##获取商品的限购标识
        ##0不限购1限购

       select P.IS_LIMIT,P.BUY_LIMIT INTO isLimit,buyLimit from PM_OTO_GOODS_PUBLISH P where P.PUBLISH_ID = publishId;
       #空值判断 
       set isLimit = IFNULL(isLimit,'N');
        ##限购
        IF (isLimit = 'N') THEN
        set vefityStr = '不存在error';
        ELSEIF (isLimit = '1') THEN
         ##历史订单的商品数据获取
       select sum(OG.COUNT) into usedBuyCount from PM_OTO_ORDERS_GOODS OG ,PM_OTO_ORDERS_MASTER OM
        where OG.PUBLISH_ID = publishId and OM.ORDER_ID = OG.ORDER_ID 
        and (OM.DEAL_STATE ='1' or OM.DEAL_STATE = '3') and OM.USER_ID = userId;
          
          ##进行空值判断     
          ##进行购买数量判断
          set usedBuyCount = IFNULL(usedBuyCount,0);

          ##进行购买数量判断
          IF (buyLimit-usedBuyCount >= buyCount) THEN
             set vefityStr = '校验通过,状态OK';
          ELSE
           set vefityStr = '超过限购,请调整购物车error';
          END IF;
         
        ELSE
        set vefityStr = '校验通过,状态OK';
        END IF;
 
       RETURN vefityStr;

END

    存储过程

    1、ZhangT1

CREATE DEFINER=`wpms_test`@`%` PROCEDURE `ZhangT1`()
BEGIN
	#Routine body goes here...
	#需求：查询表PM_PREPARE_BATCHID中IS_USED为0的BATCH_ID。如果不满足条件则创建一条新数据
	-- 定义结束标记
	DECLARE done INT DEFAULT 0;
	-- 定义变量
	DECLARE getBatchId VARCHAR(40);
	-- 定义游标
	DECLARE batchId CURSOR FOR SELECT BATCH_ID FROM PM_PREPARE_BATCHID WHERE IS_USED = '0';
	
	-- 将结束标志绑定到游标
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;



	-- 打开游标
	OPEN batchId;
		-- 开始循环
		read_loop:LOOP
			-- 提取游标中的数据
			FETCH batchId INTO getBatchId;
#SELECT batchId;
			-- 退出循环条件
			IF done THEN
				LEAVE read_loop;
			END IF;

		-- 结束循环
		END LOOP read_loop;
	-- 关闭游标
	CLOSE batchId;
END

    2、ZhangT2

CREATE DEFINER=`wpms_test`@`%` PROCEDURE `ZhangT2`(IN `orderId` varchar(50))
BEGIN
	#Routine body goes here...
	INSERT INTO Zhang_Test(ORDER_ID,PARTNER_ID,PUBLISH_ID,COUNT,PRICE,FREIGHT)
		VALUES(orderId, 'C1001','A1001',10,5000,500);
END

    3、ZhangT3

CREATE DEFINER=`wpms_test`@`%` PROCEDURE `ZhangT3`(IN partner varchar(40), OUT batch varchar(40))
BEGIN
	#Routine body goes here...
#DECLARE id VARCHAR(40) partnerId;
#Routine body goes here...
	#需求：查询PM_PREPARE_BATCHID 中店铺ID和IS_USED为0的BATCH_ID信息。【如果没有则新建一条数据】
	#返回批次号
	-- 定义结束标记  
	DECLARE done INT DEFAULT 0;
	# 定义变量
	DECLARE batchId VARCHAR(40);
	#定义店铺ID
#	DECLARE partnerId VARCHAR(40);
	# 定义光标
	DECLARE batchIds CURSOR FOR SELECT BATCH_ID  FROM PM_PREPARE_BATCHID WHERE PARTNER_ID = partner AND IS_USED = '0';
	-- 将结束标志绑定到游标
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
	
	-- 打开游标
	OPEN batchIds;
		-- 开始循环
		read_loop:LOOP
			-- 提取游标中的数据
			FETCH batchIds INTO batchId;
			
			-- 退出循环条件
			IF done THEN
				LEAVE read_loop;
			END IF;
	#		IF  (batchId IS NOT NULL) THEN
			IF 	 (batchId IS NOT NULL) THEN
					LEAVE read_loop;
			END IF;

		-- 结束循环
		END LOOP read_loop;
	-- 关闭游标
	CLOSE batchIds;

#	IF batchId IS NULL THEN
#		INSERT INTO PM_PREPARE_BATCHID(BATCH_ID, PARTNER_ID, IS_USED, UPDATE_TIME, REMARK) 
#				VALUES(date_format(NOW(),'%Y%m%d%H%i0%s'),partner,'0',NOW(),null);
#		SELECT BATCH_ID INTO batchId FROM PM_PREPARE_BATCHID WHERE PARTNER_ID = partner AND IS_USED = '0';
#	END IF;
#SELECT batchId;
SET batch  = batchId;
#SELECT partnerId;
END

    4、ZhangT4

CREATE DEFINER=`wpms_test`@`%` PROCEDURE `ZhangT4`()
BEGIN
	-- 定义数据
	DECLARE done INT DEFAULT 0;
	-- 定义数据
	DECLARE orderId VARCHAR(50);
	DECLARE orderTotal int;
	DECLARE goodsAmount int;
	DECLARE orderFreight int;
	-- 游标
	DECLARE cur CURSOR FOR SELECT ORDER_ID,ORDER_TOTAL,GOODS_AMOUNT,ORDER_FREIGHTINT from PM_OTO_ORDERS_MASTER ;
		/*	

		WHERE ORDER_ID in (
		'2014090121223423632275694420286',
		'2014090121255423632275694420287',
		'2014090121261723632275694420288',
		'2014090121322123632275694420289',
		'2014090121324323632275694420290'
		);
		*/
	-- 将结束标志绑定到游标
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
#Routine body goes here...
	
	-- 打开游标
	OPEN cur;
		-- 开始循环
		read_loop:LOOP
			-- 提取游标中的数据
			FETCH cur into orderId, orderTotal, goodsAmount, orderFreight;
			-- 什么时候退出该循环
			IF done THEN
				LEAVE read_loop;
			END IF;

			-- 具体做什么事
			INSERT INTO Zhang_Test2(ORDER_ID, ORDER_TOTAL, GOODS_AMOUNT, ORDER_FREIGHTINT)
				VALUES(orderId, orderTotal, goodsAmount, orderFreight);



		-- 结束循环
		END LOOP read_loop;
	-- 关闭游标
	CLOSE cur;
	
END

    5、ZhangT5

CREATE DEFINER=`wpms_test`@`%` PROCEDURE `ZhangT5`()
BEGIN
	#Routine body goes here... 
	#返回批次号
	-- 定义结束标记
	DECLARE done INT DEFAULT 0;
	DECLARE batchId VARCHAR(40);
	DECLARE batchIds CURSOR FOR SELECT BATCH_ID FROM PM_PREPARE_BATCHID WHERE IS_USED = '0';
	-- 将结束标志绑定到游标
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
	
	-- 打开游标
	OPEN batchIds;
		-- 开始循环
		read_loop:LOOP
			-- 提取游标中的数据
			FETCH batchIds INTO batchId;
#SELECT batchId;
			-- 退出循环条件
			IF done THEN
				LEAVE read_loop;
			END IF;
			IF batchId IS NOT NULL THEN
				LEAVE read_loop;
			END IF;

		-- 结束循环
		END LOOP read_loop;
	-- 关闭游标
	CLOSE batchIds;

	IF batchId IS NULL THEN
		INSERT INTO PM_PREPARE_BATCHID(BATCH_ID, IS_USED, UPDATE_TIME, REMARK) VALUES(date_format(NOW(),'%Y%m%d%H%i0%s'),'0',NOW(),'说明');
		SELECT BATCH_ID INTO batchId FROM PM_PREPARE_BATCHID WHERE IS_USED = '0';
		SELECT batchId;
	END IF;
SELECT batchId;
END

    6、ZhangT6

CREATE DEFINER=`wpms_test`@`%` PROCEDURE `ZhangT6`()
BEGIN
	/*
	每两秒插入一条数据[不要重复插入已有数据]
	*/
	-- 定义结束标记
	DECLARE done1 INT DEFAULT 0;
	-- 定义标记位，判断量表中的主键是否相等
	DECLARE flag INT DEFAULT 0;
	-- 定义变量
	DECLARE orderId VARCHAR(50);
	DECLARE orderTotal int;
	DECLARE goodsAmount int;
	DECLARE orderFreight int;

	DECLARE orderId1 VARCHAR(50);
-- DECLARE orderId2 VARCHAR(50);
	-- 定义游标
	DECLARE cur1 CURSOR FOR SELECT ORDER_ID from Zhang_Test2;
-- DECLARE cur2 CURSOR FOR SELECT ORDER_ID from PM_OTO_ORDERS_MASTER;
	DECLARE cur3 CURSOR FOR SELECT ORDER_ID, ORDER_TOTAL, GOODS_AMOUNT, ORDER_FREIGHTINT from PM_OTO_ORDERS_MASTER ;
	-- 将结束标志绑定到游标
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = 1;
	#Routine body goes here...
	-- 打开游标cur3
	OPEN cur3;
		-- 开始循环1
		read_loop1: LOOP
			-- 提取数据
			FETCH cur3 INTO orderId, orderTotal, goodsAmount, orderFreight;
			-- 声明结束的时候
			IF done1 THEN
				LEAVE read_loop1;
			END IF;
			
-- select orderId;
			-- 打开游标1
			OPEN cur1;
				-- 开始循环2
				read_loop2: LOOP
					-- 提取数据
					FETCH cur1 INTO orderId1;
					-- 判断null情况
					IF orderId1 is null THEN
						-- SET orderId1 = 0;
						-- 插入一条数据
						INSERT INTO Zhang_Test2(ORDER_ID, ORDER_TOTAL, GOODS_AMOUNT, ORDER_FREIGHTINT)
							VALUES(orderId, orderTotal, goodsAmount, orderFreight);
						-- 退出循环
						LEAVE read_loop2;
						LEAVE read_loop1;
					END IF;
-- select orderId;
					-- 声明结束的时候
					IF done1 THEN
						SET done1 = 0;
						LEAVE read_loop2;
					END IF;

					-- ================具体工作  循环判断：
					IF orderId = orderId1 THEN
						SET flag = 0;

						LEAVE read_loop2;
					ELSEIF orderId != orderId1 THEN
						SET flag = 1;

					END IF;


				-- 关闭循环2
				END LOOP read_loop2;
			-- 关闭游标1
			CLOSE cur1;

			-- 具体工作2
			IF flag = 1 THEN
				-- 插入一条数据
				INSERT INTO Zhang_Test2(ORDER_ID, ORDER_TOTAL, GOODS_AMOUNT, ORDER_FREIGHTINT)
					VALUES(orderId, orderTotal, goodsAmount, orderFreight);
				-- 因为是插一条，退出循环
				LEAVE read_loop1;  -- 每插入一条数据则推出该循环。如果注释改行，则向表Zhang_Test2插入所有数据
			END IF;


			-- 重置标记flag
			SET flag = 0;
		-- 关闭循环1
		END LOOP read_loop1;
	-- 关闭游标cur3
	CLOSE cur3;
	
END

    7、ZhangT7

CREATE DEFINER=`wpms_test`@`%` PROCEDURE `ZhangT7`()
BEGIN

		-- 需要定义接收游标数据的变量 
		DECLARE a CHAR(16);
		-- 遍历数据结束标志
		DECLARE done INT DEFAULT FALSE;
		-- 游标
		DECLARE cur CURSOR FOR SELECT i FROM test.t;
		-- 将结束标志绑定到游标
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

		-- 打开游标
		OPEN cur;
		-- 开始循环
		read_loop: LOOP
			-- 提取游标里的数据，这里只有一个，多个的话也一样；
			FETCH cur INTO a;
			-- 声明结束的时候
			IF done THEN
				LEAVE read_loop;
			END IF;

			-- 这里做你想做的循环的事件
			INSERT INTO test.t VALUES (a);

		END LOOP;
		-- 关闭游标
		CLOSE cur;
END

    8、ZhangT8

CREATE DEFINER=`wpms_test`@`%` PROCEDURE `ZhangT8`(IN `getSeqNum` varchar(1))
BEGIN
	#Routine body goes here...
	#需求获取序列号，格式为:8位日期 + 8位序列号  如2014082100000001
	#查询时根据当前日期值【如：20140821】

	# 定义最大序号值
	DECLARE maxSeq VARCHAR(20);
	# 当前日期值
	DECLARE nowDate VARCHAR(20);
	
	# 获取当前日期值
	set nowDate = (SELECT date_format(NOW(),'%Y%m%d') from dual);
	# 获取最大序列值
	SET maxSeq = (SELECT MAX(a.ORDER_ID) from Zhang_Test a where 1=1 and a.order_id LIKE CONCAT('%',nowDate,'%') );	#'%20150623%'
	#SET maxSeq = 2015062399999888;
	#SET maxSeq = 2015062399999888+1;		# 99999888
	#SET maxSeq = maxSeq+1;
	#SET maxSeq = FORMAT(maxSeq+1,0);
	# CAST('123' AS SIGNED)
	#SET maxSeq = CAST(maxSeq as SIGNED)+6;
	SET maxSeq = maxSeq+0+6;
	#SET maxSeq = (SELECT MAX(a.TableField) from TableName a where 1=1 and a.TableField LIKE CONCAT('%',nowDate,'%') );	#'%20150623%'
	#INSERT INTO Zhang_Test (order_id) VALUES(maxSeq);
	# 获取序列信息
	SELECT maxSeq ;
	#SELECT nowDate;
	


END

    9、ZhangT9

CREATE DEFINER=`wpms_test`@`%` PROCEDURE `ZhangT9`(IN `seqNum` varchar(2))
BEGIN
	#Routine body goes here...
	#需求获取序列号，格式为:8位日期 + 8位序列号  如2014082100000001
	#根据编号获取对应表的序列值 
	#1 PM_OTO_ORDERS_ACCOUNT  店铺账本表
	#2 PM_OTO_ORDERS_ACCTLOG  店铺账本记录表
	#3 PM_OTO_WITHDRAW_PARTNER  店铺提现申请表
	#4 PM_OTO_WITHDRAW_LOG  店铺提现申请轨迹表
	#5 PM_OTO_PARTNERS_BANK	店铺银行卡表

	# 定义最大序号值
	DECLARE maxSeq VARCHAR(20);
	# 定义当前日期值
	DECLARE nowDate VARCHAR(20);

	# 获取当前日期值
	set nowDate = (SELECT date_format(NOW(),'%Y%m%d') from dual);

	if seqNum = '1' THEN
		#获取今天最大序列值
		#set maxSeq = (SELECT FORMAT(MAX(a.ACCT_BALANCE_ID)+1,0) from PM_OTO_ORDERS_ACCOUNT a where 1=1 and a.ACCT_BALANCE_ID like CONCAT('%',nowDate,'%'));
		set maxSeq = (SELECT MAX(a.ACCT_BALANCE_ID) from PM_OTO_ORDERS_ACCOUNT a where 1=1 and a.ACCT_BALANCE_ID like CONCAT('%',nowDate,'%'));
		if maxSeq IS NOT NULL THEN	# 如果有序列值，那么序列值+1
			SET maxSeq = CONVERT(maxSeq,SIGNED)+1;
		end if;
		if maxSeq IS NULL then	# 如果今天没有序列值，那么设定序列值，从1开始
			set maxSeq = CONCAT('',nowDate,'00000001');
		end if;
		INSERT INTO PM_OTO_ORDERS_ACCOUNT (ACCT_BALANCE_ID) VALUES(CONCAT('',maxSeq,''));		#插入一条数据，以当前序列值为主键，防止主键冲突
	end if;

	if seqNum = '2' THEN
		set maxSeq = (SELECT MAX(a.CHARGE_ID) from PM_OTO_ORDERS_ACCTLOG a where 1=1 and a.CHARGE_ID like CONCAT('%',nowDate,'%'));
		if maxSeq IS NOT NULL THEN	
			SET maxSeq = CONVERT(maxSeq,SIGNED)+1;
		end if;
		if maxSeq IS NULL then	
			set maxSeq = CONCAT('',nowDate,'00000001');
		end if;
		INSERT INTO PM_OTO_ORDERS_ACCTLOG (CHARGE_ID) VALUES(maxSeq);		
	end if;

	if seqNum = '3' THEN
		set maxSeq = (SELECT MAX(a.WITHDRAW_ID) from PM_OTO_WITHDRAW_PARTNER  a where 1=1 and a.WITHDRAW_ID like CONCAT('%',nowDate,'%'));
		if maxSeq IS NOT NULL THEN	
			SET maxSeq = CONVERT(maxSeq,SIGNED)+1;
		end if;
		if maxSeq IS NULL then	
			set maxSeq = CONCAT('',nowDate,'00000001');
		end if;
		INSERT INTO PM_OTO_WITHDRAW_PARTNER (WITHDRAW_ID) VALUES(maxSeq);
	end if;

	if seqNum = '4' THEN
		set maxSeq = (SELECT MAX(a.ID) from PM_OTO_WITHDRAW_LOG  a where 1=1 and a.ID like CONCAT('%',nowDate,'%'));
		if maxSeq IS NOT NULL THEN	
			SET maxSeq = CONVERT(maxSeq,SIGNED)+1;
		end if;
		if maxSeq IS NULL then	
			set maxSeq = CONCAT('',nowDate,'00000001');
		end if;
		INSERT INTO PM_OTO_WITHDRAW_LOG (ID) VALUES(maxSeq);
	end if;

	if seqNum = '5' THEN
		set maxSeq = (SELECT MAX(a.ID) from PM_OTO_PARTNERS_BANK  a where 1=1 and a.ID like CONCAT('%',nowDate,'%'));
		if maxSeq IS NOT NULL THEN	
			SET maxSeq = CONVERT(maxSeq,SIGNED)+1;
		end if;
		if maxSeq IS NULL then	
			set maxSeq = CONCAT('',nowDate,'00000001');
		end if;
		INSERT INTO PM_OTO_PARTNERS_BANK (ID) VALUES(maxSeq);
	end if;

	SELECT maxSeq;

END

    10、返回主键信息

CREATE DEFINER=`wpms_test`@`%` FUNCTION `_nextval2`(n varchar(50)) RETURNS varchar(20) CHARSET utf8
begin  
	## 需求：返回信息格式：%Y%m%d%H%i%s + 六位数字UUID 
	## 确保整个库生成数字不冲突

	# 定义当前要返回的序列变量
	DECLARE nowSeq VARCHAR(20);
	# 定义当前日期时间信息
	DECLARE nowDateTime VARCHAR(14); 
	# 定义9位数字随机数
	DECLARE curNum VARCHAR(6);
	
	set nowDateTime = (SELECT date_format(NOW(),'%Y%m%d%H%i%s') from dual); 
	set curNum  = (SELECT RIGHT(UUID_SHORT(),6) from dual);

	set nowSeq = (SELECT CONCAT(nowDateTime, curNum) seq from dual);	# 当前时间+ 6位数字UUID
	return nowSeq;
end