MySQL-存储过程
是什么？简单说，是一组SQL集，功能强大，可以实现比较复杂的逻辑功能，类似于JAVA语言中的方法。
一：分隔符
MySQL默认以";"为分隔符，如果没有声明分割符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错，所以要事先用“DELIMITER //”声明当前段分隔符，让编译器把两个"//"之间的内容当做存储过程的代码，不会执行这些代码；“DELIMITER ;”意为把分隔符还原。
二：语法
DELIMITER //
CREATE PROCEDURE myproc()
BEGIN
SELECT 'lixiong';
END
//
DELIMITER ;
三：参数
存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用","分割开。MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT:
IN：参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
DELIMITER //
CREATE PROCEDURE in_param(IN p_in int)
BEGIN
SELECT p_in;
SET p_in=2;
SELECT p_in;
END;
//
DELIMITER ;
#调用
SET @p_in=1;
CALL in_param(@p_in);
SELECT @p_in;
OUT:该值可在存储过程内部被改变，并可返回
DELIMITER //
CREATE PROCEDURE out_param(OUT p_out int)
BEGIN
SELECT p_out;
SET p_out=2;
SELECT p_out;
END;
//
DELIMITER ;
#调用
SET @p_out=1;
CALL out_param(@p_out);
SELECT @p_out;
INOUT:调用时指定，并且可被改变和返回
DELIMITER //
CREATE PROCEDURE inout_param(INOUT p_inout int)
BEGIN
SELECT p_inout;
SET p_inout=2;
SELECT p_inout;
END;
//
DELIMITER ;
#调用
SET @p_inout=1;
CALL inout_param(@p_inout) ;
SELECT @p_inout;
四：过程体
过程体的开始与结束使用BEGIN与END进行标识。
五：变量
定义：DECLARE 变量名1[,变量名2...] 数据类型 [默认值];
DECLARE a INT DEFAULT 0;
赋值：SET 变量名 = 变量值 [,变量名= 变量值 ...];
SET a=0;
六：调用-删除
调用：call 存储过程名（参数）；
删除：DROP PROCEDURE IF EXISTS 存储过程名；
七：控制语句
创建表 t
CREATE TABLE `t` ( `s1` int(1) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8
1）IF-THEN-ELSE语句
DROP PROCEDURE IF EXISTS proc1; DELIMITER // CREATE PROCEDURE proc1(IN parameter INT) BEGIN DECLARE var INT; SET var=parameter; IF var=1 THEN INSERT INTO t VALUES (1); ELSE UPDATE t SET s1=s1+1; END IF ; END ; // DELIMITER ; #调用 值 SET @parameter=2; CALL proc1(@parameter) ; SELECT * FROM t; TRUNCATE TABLE `t`;
2）CASE-WHEN-THEN-ELSE语句
DROP PROCEDURE IF EXISTS proc2; DELIMITER // CREATE PROCEDURE proc2 (IN parameter INT) BEGIN DECLARE var INT; SET var=parameter; CASE var WHEN 1 THEN INSERT INTO t VALUES (1); WHEN 2 THEN INSERT INTO t VALUES (2); ELSE INSERT INTO t VALUES (100); END CASE ; END ; // DELIMITER ; #调用 SET @parameter=3; CALL proc2(@parameter); SELECT * FROM t; TRUNCATE TABLE `t`;
3）WHILE-DO…END-WHILE
DROP PROCEDURE IF EXISTS proc3; DELIMITER // CREATE PROCEDURE proc3() BEGIN DECLARE var INT; SET var=0; WHILE var<6 DO INSERT INTO t VALUES (var); SET var=var+1; END WHILE ; END; // DELIMITER ; #调用 CALL proc3(); SELECT * FROM t; TRUNCATE TABLE `t`;
4）REPEAT...END REPEAT
DROP PROCEDURE IF EXISTS proc4; DELIMITER // CREATE PROCEDURE proc4 () BEGIN DECLARE v INT; SET v=0; REPEAT INSERT INTO t VALUES(v); SET v=v+1; UNTIL v>=5 END REPEAT; END; // DELIMITER ; #调用 CALL proc4(); SELECT * FROM t; TRUNCATE TABLE `t`;
5）LOOP...END LOOP ：标号可以用在begin repeat while 或者loop 语句前
DROP PROCEDURE IF EXISTS proc5; DELIMITER // CREATE PROCEDURE proc5 () BEGIN DECLARE v INT; SET v=0; LOOP_LABLE:LOOP INSERT INTO t VALUES(v); SET v=v+1; IF v >=5 THEN # 判断大于等于停止计数 LEAVE LOOP_LABLE; END IF; END LOOP; END; // DELIMITER ; #调用 CALL proc5(); SELECT * FROM t; TRUNCATE TABLE `t`;
6）ITERATE迭代 ：通过引用复合语句的标号,来从新开始复合语句
DROP PROCEDURE IF EXISTS proc6; DELIMITER // CREATE PROCEDURE proc6() BEGIN DECLARE v INT; SET v=0; LOOP_LABLE:LOOP IF v=3 THEN SET v=v+1; ITERATE LOOP_LABLE; END IF; INSERT INTO t VALUES(v); SET v=v+1; IF v>=5 THEN LEAVE LOOP_LABLE; END IF; END LOOP; END; // DELIMITER ; #调用 CALL proc6(); SELECT * FROM t; TRUNCATE TABLE `t`;
七：加盟签约案列
DELIMITER $$
USE `haochacang`$$
DROP PROCEDURE IF EXISTS `addJoinSigningByTime`$$
CREATE DEFINER=`root`@`%` PROCEDURE `addJoinSigningByTime`(IN time1 VARCHAR(10),IN time2 VARCHAR(10))
BEGIN
DECLARE chanFollower,referee,greadeId,count1,id2,reviewPaymentDate,agrade,bgrade,cgrade INT;
DECLARE confirmDate,orderTime VARCHAR(20);
DECLARE money,Amoney,Bmoney,Cmoney DOUBLE;
DECLARE a INT DEFAULT 0;
#定义采购审核游标
DECLARE datas CURSOR FOR
SELECT
chan_follower,
IF((SELECT phone FROM `company_user` WHERE `id`=c.chan_follower)=(SELECT mobile FROM `customer_info` WHERE `id`= r.`customer_parent_id`),1,0) referee,
confirm_date,
`grade`
FROM `customer_info` c
LEFT JOIN customer_relation r ON c.id = r.customer_id
WHERE
c.customer_type = 2 #采购商
AND c.confirm_status = 4 #复核通过
AND c.chan_follower IS NOT NULL #跟进人不为空的
AND c.confirm_date IS NOT NULL #审核时间不为空的
AND `grade` IN (2,3,4)
#AND confirm_date>= DATE_ADD(CURDATE(),INTERVAL -1 DAY)
AND confirm_date< CURDATE();
#定义订单金额游标
DECLARE orders CURSOR FOR
SELECT
c.`grade`,
c.chan_follower,
IF((SELECT phone FROM `company_user` WHERE `id`=c.chan_follower)=(SELECT mobile FROM `customer_info` WHERE `id`= r.`customer_parent_id`),1,0) referee,
(IFNULL(SUBDATE(o.`first_saleafter_time`,-7),SUBDATE(o.`end_time`,-7))) tjTime,
(op.`sell_price`*op.`amount`-IFNULL(op.`single_hd_discounts_price`,0)-IFNULL(op.`multi_hd_discounts_price`,0)-
IFNULL(op.`coupon_discounts_price`,0)-IFNULL(op.`pt_price`,0)-IFNULL(op.`supplier_price`,0) )AS money
FROM `order_product` op
LEFT JOIN `order_item` o ON o.`order_id`=op.`order_id`
LEFT JOIN `product` p ON op.`product_id`=p.`product_id`
LEFT JOIN `customer_info` c ON c.`id` =op.`customer_id`
LEFT JOIN customer_relation r ON c.id = r.customer_id
WHERE o.`order_status`=4 #已完成
AND op.is_gift=0 #非赠品
#AND p.`is_extract` =1 #提成商品
AND c.`grade` IN (2,3,4) #A,B,C等级
AND c.`customer_type`=2
AND (o.`first_saleafter_time` IS NOT NULL || o.`end_time` IS NOT NULL)
AND (DATE_ADD(o.`end_time`,INTERVAL 7 DAY)<NOW() || DATE_ADD(o.`first_saleafter_time`,INTERVAL 7 DAY)<NOW())#售后超过7天
AND (SELECT COUNT(`saleafter_id`) FROM `order_saleafter` WHERE order_id=o.`order_id` AND `order_status` IN(0,1,2,3,4,5))=0; #售后均已完成
DECLARE CONTINUE HANDLER FOR NOT FOUND SET a = 1;
TRUNCATE TABLE `statistic_join_signing`;
/************************************************************客户等级添加更新*********************************************************************/
OPEN datas;
FETCH datas INTO chanFollower,referee,confirmDate,greadeId;
WHILE a<>1 DO
#推荐人是自己
IF(referee=1) THEN
SELECT COUNT(id),id,review_date,`a_grade`,`b_grade`,`c_grade` INTO count1,id2,reviewPaymentDate,agrade,bgrade,cgrade
FROM `statistic_join_signing` s WHERE s.`user_id`=chanFollower AND s.`review_date`=(DATE_FORMAT(confirmDate,'%Y%m')) AND s.`referee`=1;
#推荐人是别人
ELSE
SELECT COUNT(id),id,review_date,`a_grade`,`b_grade`,`c_grade` INTO count1,id2,reviewPaymentDate,Agrade,Bgrade,Cgrade
FROM `statistic_join_signing` s WHERE s.`user_id`=chanFollower AND s.`review_date`=(DATE_FORMAT(confirmDate,'%Y%m')) AND s.`referee`=0;
END IF;
#如果是插入置0
IF(count1=0) THEN SET Agrade=0,Bgrade=0,Cgrade=0; END IF;
#根据等级Id加1
CASE greadeId
WHEN 2 THEN SET agrade =agrade+1;
WHEN 3 THEN SET bgrade =bgrade+1;
ELSE SET cgrade =cgrade+1;
END CASE;
#推荐人是自己
IF(referee=1) THEN
CASE count1
WHEN 1 THEN
#更新推荐人是自己的操作
UPDATE`statistic_join_signing`SET`update_time`=NOW(),`a_grade`=agrade,`b_grade`=bgrade,`c_grade`=cgrade WHERE `id`=id2;
ELSE
#插入推荐人是自己的操作
INSERT INTO `statistic_join_signing` (`user_id`,`referee`,`review_date`,`create_time`,`update_time`,`a_grade`,`b_grade`,`c_grade`,`a_money`,`b_money`,`c_money`)
VALUES(chanFollower,1,(DATE_FORMAT(confirmDate,'%Y%m')),NOW(),NOW(),agrade,bgrade,cgrade,0,0,0);
#无推荐人是其他的默认零
SELECT COUNT(id) INTO count1 FROM `statistic_join_signing` WHERE `user_id`=chanFollower AND `review_date`=(DATE_FORMAT(confirmDate,'%Y%m')) AND`referee`=0;
IF(count1=0) THEN
INSERT INTO `statistic_join_signing` (`user_id`,`referee`,`review_date`,`create_time`,`update_time`,remark,`a_grade`,`b_grade`,`c_grade`,`a_money`,`b_money`,`c_money`)
VALUES(chanFollower,0,(DATE_FORMAT(confirmDate,'%Y%m')),NOW(),NOW(),NULL,0,0,0,0,0,0);
END IF;
END CASE;
#推荐人是别人
ELSE
CASE count1
WHEN 1 THEN
#更新推荐人不是自己的操作
UPDATE`statistic_join_signing`SET`update_time`=NOW(),`a_grade`=agrade,`b_grade`=bgrade,`c_grade`=cgrade WHERE `id`=id2;
ELSE
#插入推荐人不是自己的操作
INSERT INTO `statistic_join_signing` (`user_id`,`referee`,`review_date`,`create_time`,`update_time`,`a_grade`,`b_grade`,`c_grade`,`a_money`,`b_money`,`c_money`)
VALUES(chanFollower,0,(DATE_FORMAT(confirmDate,'%Y%m')),NOW(),NOW(),agrade,bgrade,cgrade,0,0,0);
#无推荐人是自己的默认为零
SELECT COUNT(id) INTO count1 FROM `statistic_join_signing` WHERE `user_id`=chanFollower AND `review_date`=(DATE_FORMAT(confirmDate,'%Y%m')) AND`referee`=1;
IF(count1=0) THEN
INSERT INTO `statistic_join_signing` (`user_id`,`referee`,`review_date`,`create_time`,`update_time`,remark,`a_grade`,`b_grade`,`c_grade`,`a_money`,`b_money`,`c_money`)
VALUES(chanFollower,1,(DATE_FORMAT(confirmDate,'%Y%m')),NOW(),NOW(),NULL,0,0,0,0,0,0);
END IF;
END CASE;
END IF;
FETCH datas INTO chanFollower, referee,confirmDate,greadeId;
END WHILE;
CLOSE datas;
/************************************************************订单金额添加更新*********************************************************************/
UPDATE `statistic_join_signing` SET `a_money`=0,`b_money`=0,`c_money`=0;
SET a=0;
OPEN orders;
FETCH orders INTO greadeId,chanFollower,referee,orderTime,money;
WHILE a<>1 DO
#推荐人是自己
IF(referee=1) THEN
SELECT COUNT(id),id,review_date,`a_money`,`b_money`,`c_money` INTO count1,id2,reviewPaymentDate,Amoney,Bmoney,Cmoney
FROM `statistic_join_signing` s WHERE s.`user_id`=chanFollower AND s.`review_date`=(DATE_FORMAT(orderTime,'%Y%m')) AND s.`referee`=1;
#推荐人是别人
ELSE
SELECT COUNT(id),id,review_date,`a_money`,`b_money`,`c_money` INTO count1,id2,reviewPaymentDate,Amoney,Bmoney,Cmoney
FROM `statistic_join_signing` s WHERE s.`user_id`=chanFollower AND s.`review_date`=(DATE_FORMAT(orderTime,'%Y%m')) AND s.`referee`=0;
END IF;
#如果是插入置0
IF(count1=0) THEN SET Amoney=0,Bmoney=0,Cmoney=0; END IF;
CASE greadeId
WHEN 2 THEN SET Amoney =Amoney+money;
WHEN 3 THEN SET Bmoney =Bmoney+money;
ELSE SET Cmoney =Cmoney+money;
END CASE;
IF(id2>0) THEN
UPDATE `statistic_join_signing` SET`update_time`=NOW(),`a_money`=Amoney,`b_money`=Bmoney,`c_money`=Cmoney WHERE `id`=id2;
ELSE
#推荐人是自己
IF(referee=1) THEN
#插入推荐人是自己的操作
INSERT INTO `statistic_join_signing` (`user_id`,`referee`,`review_date`,`create_time`,`update_time`,`a_grade`,`b_grade`,`c_grade`,`a_money`,`b_money`,`c_money`)
VALUES(chanFollower,1,(DATE_FORMAT(orderTime,'%Y%m')),NOW(),NOW(),0,0,0,Amoney,Bmoney,Cmoney);
#无推荐人是其他的默认零
SELECT COUNT(id) INTO count1 FROM `statistic_join_signing` WHERE `user_id`=chanFollower AND `review_date`=(DATE_FORMAT(orderTime,'%Y%m')) AND`referee`=0;
IF(count1=0) THEN
INSERT INTO `statistic_join_signing` (`user_id`,`referee`,`review_date`,`create_time`,`update_time`,remark,`a_grade`,`b_grade`,`c_grade`,`a_money`,`b_money`,`c_money`)
VALUES(chanFollower,0,(DATE_FORMAT(orderTime,'%Y%m')),NOW(),NOW(),NULL,0,0,0,0,0,0);
END IF;
#推荐人是别人
ELSE
#插入推荐人不是自己的操作
INSERT INTO `statistic_join_signing` (`user_id`,`referee`,`review_date`,`create_time`,`update_time`,`a_grade`,`b_grade`,`c_grade`,`a_money`,`b_money`,`c_money`)
VALUES(chanFollower,0,(DATE_FORMAT(orderTime,'%Y%m')),NOW(),NOW(),0,0,0,Amoney,Bmoney,Cmoney);
#无推荐人是自己的默认为零
SELECT COUNT(id) INTO count1 FROM `statistic_join_signing` WHERE `user_id`=chanFollower AND `review_date`=(DATE_FORMAT(orderTime,'%Y%m')) AND`referee`=1;
IF(count1=0) THEN
INSERT INTO `statistic_join_signing` (`user_id`,`referee`,`review_date`,`create_time`,`update_time`,remark,`a_grade`,`b_grade`,`c_grade`,`a_money`,`b_money`,`c_money`)
VALUES(chanFollower,1,(DATE_FORMAT(orderTime,'%Y%m')),NOW(),NOW(),NULL,0,0,0,0,0,0);
END IF;
END IF;
END IF;
FETCH orders INTO greadeId,chanFollower,referee,orderTime,money;
END WHILE;
CLOSE orders;
END$$
DELIMITER ;
其它：https://blog.csdn.net/weixin_34029680/article/details/92578513
浙公网安备 33010602011771号