以下示例主要用于个人温习使用,因为长时间不谢存储过程或函数自己很容易忘记。
其他记录
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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
- 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