A - mysql 分支结构、函数、过程

1 if

1. 分支结构
   if（condition1）then (执行)
   eseif（condition2）then（执行）
   else（结果集）
   end if;
2. 函数
   if（condition，value if true，value if false）

   SELECT *,IF(sex = 1,'男','女') AS '性别' FROM stu_info WHERE id = 3;

2.case
1）分支结构，类似于switch
case(column)
when(condition1) then(执行)
when(condition2) then(执行)
when(condition3) then(执行)
else(执行)
end;

SELECT
	*,
CASE sex 
	    WHEN 1 THEN
		'男' 
		WHEN 0 THEN
		'女' ELSE '嘿嘿' 
	    END AS a 
FROM
	stu_info;

3.while
1)分支结构
while(condition) do{
…
}end while;

4 函数
1）函数与java中的方法一样，有入参，有且只有一个返回值
2）函数的返回值，只能是一个值，不能是结果集
3）变量：成员变量和会话变量
成员变量：需要先声明，再赋值。赋值关键字declare
会话变量：格式为@变量名，不需要单独声明，直接赋值即可；
4)用set对变量赋值
5）调用函数：select f_01(a,b);
注意视图调用：select * from v_stu_info

-- 调用函数f_01
SELECT f_01(1,2);

  CREATE DEFINER=`root`@`localhost` FUNCTION `f_01`(`a` int,`b` int) RETURNS int
    DETERMINISTIC #DETERMINISTIC表示在函数输入不变的情况下，输出是否确定。
                  #如果函数输入一样时，会返回相同的结果。这样数据库就会调用前一个计算的值，而不需要再重新计算一次。这对于使用   
                   #函数索引，有很大好处，会提高函数执行效率。
    
BEGIN
-- 成员变量（需要先声明再赋值）和会话变量（不需声明，可直接赋值）的区别
-- 声明成员变量，DECLARE
DECLARE res INT DEFAULT 0;
-- 赋值，关键字 SET
SET res = a+b;

-- 会话变量，变量名前加一个@符号
-- @变量名，无需单独声明，直接赋值就可以(如result)
 SET @result = a*b;
 
 SET @tar = true;
 while @tar DO
     SET res = res +1;
     IF res>10 THEN SET @tar = FALSE;
     END IF;
END WHILE;

	RETURN res;
END

5.过程
1）调用存储过程
call p_01(null,null);
2）参数：
IN：输入参数类型
表示过程向调用者传入的值（传入值可以是true、false、变量）
OUT：输出参数
表示调用者向过程传出值（可以返回多个值，传出值只能是变量）
INOUT：输入输出参数
既表示过程向调用者传入的值，又表示调用者向过程传出值（值只能是变量）
3）存储过程的优缺点
优点：存储过程可封装，并且隐藏复杂的业务逻辑
存储过程指只创建时进行编译（预编译，执行速度快），以后每次执行存储过程都不需要再重新编译，而一般sql
语句每执行一次就编译一次，所以使用存储过程可以提高数据库速度
缺点：存储过程性能调试与撰写，受限于各种数据库。简单来说，存储过程，难以调试

例1：使用过程完成：查询宋丹丹老师的学生中成绩最高的学生信息

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_01`(IN `a` int,IN `b` int)
BEGIN
-- 	存储过程
-- 班级id
DECLARE p_c_id int;
-- 最高成绩
DECLARE p_max_grade int;

-- 查询所有的学生信息
-- 1.查询宋丹丹老师的班级
SET p_c_id = (
SELECT cla_id FROM cla_info WHERE cla_teacher = '宋丹丹');

-- 2.查找该班最高的成绩
SET p_max_grade = (
SELECT MAX(grade) FROM stu_info where cla_id = p_c_id);

-- 3.根据匹配的成绩找到匹配的学生信息
SELECT * FROM stu_info WHERE cla_id = p_c_id and grade  = p_max_grade;
END

例2：

– 订单号:雪花算法
– 正常一般 用年月日时分+一些字段生成订单号
– 如何避免订单号重复：拼接一些不会重复的东西，比如用户id（用户单点登录，随机数也有可能重复）
– 单点登录：同一账号在同一时空只能在一台设备上登录
– 避免一个用户疯狂点击提交，产生多个订单：提交按钮置灰设置、mq消息队列
– mq：rocket mq、rabbit mq、active mq等
注意，mysql中if-else语句不能嵌套begin-end一起使用

#要求：
#商品表:商品id、商品名称、单价、库存数量
#用户表:用户id、用户名、手机号、账户余额四个字段
#订单表有字段:订单号、用户id、商品id、购买数量、支付金额、交易时间
#交易流水表:流水id(null)、用户id(not null)、订单号(null)、交易类型（1支付-扣钱、0充值-加钱)、交易时间(要求与订单表时间一致)
#开发存储过程模拟电商下单
#参数:用户id、商品id、购买数量
#要求用事务控制(注意，对数据库数据的修改需要用事务处理，以便发生错误时数据回滚)

#业务逻辑
#1计算支付金额，验证用户账户余额是否充足，不够返回下单失败:账户余额不足
#2验证商品库存是否充足，yes-扣减商品库存no-返回下单失败:库存不足
#3扣减用户账户余额
#4生成订单
#5生成支付流水

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_test`(IN `u_id` int,IN `g_id` int,IN `pay_num` int)
BEGIN
-- 问题：mysql中可以用begin-end吗？？
-- 为什么不能返回@tar(字符串)
-- 生成订单号，流水号是指随机序列还是插入一条记录？
  DECLARE t_error INT;
	DECLARE p_money INT;
	DECLARE p_price INT;
	DECLARE p_num INT;
	DECLARE account INT;
  DECLARE tar INT;
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error = 1;
-- 	商品库存
SET p_num = (
SELECT stock_num from good_info WHERE good_id = g_id);
-- 	商品单价
	SET p_price = (
	SELECT price from good_info where good_id = g_id);
	
-- 	支付金额
	SET p_money = p_price * pay_num;
-- 	SET p_money  = (SELECT pay_num*price FROM good_info WHERE good_id = g_id);

-- 	用户账户余额
  SET account = (
	SELECT money FROM user_info WHERE user_id = u_id);
	
-- 	交易时间(要求与订单表时间一致)
SET @time = NOW();

SET @order_id = (
	SELECT CONCAT(
    	     u_id,
          DATE_FORMAT(@time, '%Y%m%d%H%i%s'),
           lpad(round(round(rand(),4)*1000),4,'0')
        ));

SET @trade_id = (
	SELECT CONCAT(
    	     g_id,
          DATE_FORMAT(@time, '%Y%m%d%H%i%s'),
           lpad(round(round(rand(),4)*1000),4,'0')
        ));
	IF(account<p_money) THEN
	  SELECT "下单失败，账户余额不足";
		ELSEIF p_num<pay_num THEN
		 SELECT "下单失败，库存不足";
		  ELSE #开启事务,一旦有失败，就回滚
			 START TRANSACTION;
			 
			  UPDATE good_info SET stock_num = stock_num - pay_num WHERE good_id = g_id;
			  UPDATE user_info SET money = money - p_money WHERE user_id = u_id;
			  INSERT INTO order_info VALUES(@order_id,u_id,g_id,pay_num,p_num,NOW());
              INSERT INTO trade_info VALUES(@trade_id,u_id,@order_id,1,NOW());
				
				IF t_error = 1 THEN 
				ROLLBACK;
				SELECT "事务执行失败" as remark;
				ELSE 
				COMMIT;
				SELECT "事务执行成功" AS remark;
				END IF;
				
	END IF;
END

6.游标（目前不懂，用到再学吧）
1）游标是一组类似于数组的具有排序的结果集
2）mysql游标只能向前读，不能跳到中间记录都或者从后向前读
3）mysql中游标只能用于存储过程和函数
4）DECLARE变量必须在声明游标之前，声明游标必须在所有执行算法（add,delete,update,select）之前
5）关键字：cursor

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_02`(IN `id` int)
BEGIN
	# 游标
	# 1)游标是一组类似于数组的具有排序的结果集。
	# 2)在mysql中游标只能用于存储过程和函数。	
	# 3)mysql的游标是向前只读的，也就是说，你只能顺序地从开始往后读取结果集，不能从后往前，也不能直接跳到中间的记录。

	DECLARE p_id INT;
	DECLARE p_name varchar(55);
	
	# 声明变量，控制遍历游标的循环终止
	DECLARE curStatus INT DEFAULT 1;

	# 声明游标
	#声明游标，必须在所有执行sql（select/insert/update/delete）之前声明游标
	DECLARE list CURSOR FOR SELECT id,stu_name FROM stu_info WHERE cla_id = id;
	
	# CONTINUE HANDLER 声明handler(处理器)，handler会自动地向下读取游标的一行数据
	# 当handler读不到数据时(not found)表明已经读完了游标的数据
	# 此时设置curStatus = 0
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET curStatus = 0;
	
	# 开启游标
	OPEN list;
	
		# 向下读取一行数据，并将这行数据赋值给与其字段对应的变量
		FETCH list INTO p_id,p_name;
		
		#遍历游标
		WHILE curStatus = 1 DO
			# 执行sql语句
			# select/insert/update/delete...
			INSERT INTO person_info SET name = p_name;
	
			FETCH list INTO p_id,p_name;
	
		END WHILE;
	
	# 关闭游标
	CLOSE list;
	

END;