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

    其他记录

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
BEGINDECLARE 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') THENset vefityStr = '不存在error';ELSEIF (isLimit = '1') THEN##历史订单的商品数据获取select sum(OG.COUNT) into usedBuyCount from PM_OTO_ORDERS_GOODS OG ,PM_OTO_ORDERS_MASTER OMwhere 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) THENset vefityStr = '校验通过,状态OK';ELSEset vefityStr = '超过限购,请调整购物车error';END IF;ELSEset 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 THENLEAVE 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 THENLEAVE read_loop;END IF;#		IF  (batchId IS NOT NULL) THENIF 	 (batchId IS NOT NULL) THENLEAVE 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 THENLEAVE 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 THENLEAVE read_loop;END IF;IF batchId IS NOT NULL THENLEAVE read_loop;END IF;-- 结束循环END LOOP read_loop;-- 关闭游标CLOSE batchIds;IF batchId IS NULL THENINSERT 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...-- 打开游标cur3OPEN cur3;-- 开始循环1read_loop1: LOOP-- 提取数据FETCH cur3 INTO orderId, orderTotal, goodsAmount, orderFreight;-- 声明结束的时候IF done1 THENLEAVE read_loop1;END IF;-- select orderId;-- 打开游标1OPEN cur1;-- 开始循环2read_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 THENSET done1 = 0;LEAVE read_loop2;END IF;-- ================具体工作  循环判断：IF orderId = orderId1 THENSET flag = 0;LEAVE read_loop2;ELSEIF orderId != orderId1 THENSET flag = 1;END IF;-- 关闭循环2END LOOP read_loop2;-- 关闭游标1CLOSE cur1;-- 具体工作2IF 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;-- 重置标记flagSET flag = 0;-- 关闭循环1END LOOP read_loop1;-- 关闭游标cur3CLOSE 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 THENLEAVE 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	# 如果有序列值，那么序列值+1SET 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' THENset 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' THENset 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' THENset 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' THENset 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位数字UUIDreturn nowSeq;
end