sql-总结-学习mysql存储过程 存储函数

学习mysql存储过程 存储函数

存储过程 存储函数 章节学习https://www.iqiyi.com/v_19rtyehzs0.html#curid=7768805900_2677562b8e34058d3628ac00209f32bb

mybatis 调用存储函数

mybatis 版本

<dependency>
	<groupId>org.mybatis.spring.boot</groupId>
	<artifactId>mybatis-spring-boot-starter</artifactId>
	<version>1.3.2</version>
</dependency>

service

 Integer affect =  forumForumMapper.mySort(record.getFid(), record.getAnotherFid());

mapper 接口

/**
 * @Description 存储函数 对两个板块的排序字段兑换位置
 * @Date 17:36 2020/1/2
 * @Param [fid, anotherFid]
 * @return java.lang.Integer
 */
 Integer mySort(@Param("fid") Integer fid, @Param("anotherFid") Integer anotherFid);

mapper XML

 <!-- 定义存储函数 对板块位置对换 -->
 <select id="mySort" parameterType="java.util.Map" resultType="java.lang.Integer">
     SELECT fun_mySort(#{fid, jdbcType=INTEGER}, #{anotherFid, jdbcType=INTEGER}) AS effect;
 </select>

返回结果

简单 存储过程

DELIMITER $
CREATE PROCEDURE pro_test1()
BEGIN
SELECT 'hell word';
END$

简单调用

CALL pro_test1();

将查询出来的某个值赋值给某个变量

CREATE PROCEDURE pro_test2()
BEGIN
DECLARE num int DEFAULT 0 ;
SELECT COUNT(*) INTO num FROM Course ;
END;
CALL pro_test2();

删除存储过程

DROP PROCEDURE pro_test2;

CREATE PROCEDURE pro_test3()
BEGIN
DECLARE num int DEFAULT 0;
SELECT COUNT(*) INTO num FROM Course ;
SELECT num;
END;

-- 查询所有的存储过程
SHOW PROCEDURE status;

-- 显示 pro_test1 创建时的详细信息
SHOW CREATE PROCEDURE pro_test1;
-- 调用存储过程
CALL pro_test3();


-- if 条件
-- 根据定义的身高变量， 判定当前身高的所属的身材类型
-- 180 及以上 -------------------------》身材高挑
-- 170 - 180 --------------------------》标准身材
-- 170 一下  --------------------------》 一般身材

CREATE PROCEDURE pro_test4()
BEGIN
DECLARE hight int DEFAULT 175;
DECLARE descprition VARCHAR(50) DEFAULT '';

if hight >= 180 THEN
	SET descprition = '身材高挑';
ELSEIF hight >=170 AND hight < 180 THEN
	SET descprition = '标准身材';
else
	SET descprition = '一般身材';
END IF;

SELECT CONCAT('shen gao ', hight, 'shen cai ', descprition);
END;

CALL pro_test4();


-- 存储过程带参数 注意 in 标识入参， 默认就是入参，out 标识返回结果参数， inout 表示即是入参，又表示出参

CREATE PROCEDURE pro_test5(in hight int)
BEGIN
DECLARE descprition VARCHAR(50) DEFAULT '';

if hight >= 180 THEN
	SET descprition = '身材高挑';
ELSEIF hight >=170 AND hight < 180 THEN
	SET descprition = '标准身材';
else
	SET descprition = '一般身材';
END IF;

SELECT CONCAT('shen gao ', hight, 'shen cai ', descprition);
END;

CALL pro_test5(65);
CALL pro_test5(190);


-- 应该使用变量 来接收输出参数 @descprition 标识用户会话变量
-- 用户会话变量标识， 当前连接，即当前会话结束时候，会话变量就会释放。
-- @@ 如果变量前面有两个@@  标识系统变量。
--  输出参数，相当于返回值。
CREATE PROCEDURE pro_test6(in hight int, out descprition VARCHAR(50))
BEGIN
if hight >= 180 THEN
	SET descprition = 'shen cai gao tiao';
ELSEIF hight >=170 AND hight < 180 THEN
	SET descprition = 'biao zhun shen cai';
else
	SET descprition = 'yi ban shen cai';
END IF;
END;

 CALL pro_test6(190, @descprition);
 -- 输出变量
 SELECT  @descprition;
 
  CALL pro_test6(158, @descprition);
 -- 输出变量
 SELECT  @descprition;
 
 -- 定义一个会话变量 
-- 输出会话变量
 SET @name ='itcast';
 SELECT @name;
 
 -- case 结构 
 -- 一种case 后面是个变量 
 --  一种后面是表达式 
 -- 给定一个月份， 然后计算该月份的季度
 -- 输入参数，可以省略in 
 CREATE PROCEDURE pro_test7(mounth int)
 BEGIN 
 declare result varchar(19);
 
	CASE
	WHEN mounth >= 1 and mounth <= 3 THEN
		set result = 'yi ji du';
	WHEN mounth >= 4 and mounth <= 6 THEN
		set result = 'er ji du';
	WHEN mounth >= 7 and mounth <= 9 THEN
		set result = 'san ji du';
	WHEN mounth >= 10 and mounth <= 12 THEN
		set result = 'si ji du';
	ELSE
		set result = 'input incorrect';
  END CASE;
	
	SELECT CONCAT('current mounth ', mounth, 'current ji du', result) as content;
END;

-- 删除存储过程
DROP PROCEDURE pro_test7;

-- 调用存储过程
CALL pro_test7(4);

-- 循环 while 是满足条件，就循环
--  从1 加到n

CREATE PROCEDURE pro_test8(n int)
BEGIN
	DECLARE total int DEFAULT 0;
	DECLARE num int DEFAULT 1;
	
	WHILE num <= n DO
		SET total = total + num;
		SET num = num + 1;
  END WHILE;
	SELECT total;
END;

CALL pro_test8(4);

-- repeat 满足某个条件，就退出循环
-- repeat 注意 until 后面不能用分号
-- 使用repeat 完成 1 加到n
CREATE PROCEDURE pro_test9(n int)
BEGIN
  declare total int default 0;
	
	REPEAT
	    SET total = total + n;
      SET n = n - 1;	
  UNTIL n = 0 
  END REPEAT;
  SELECT total;
END;

CALL pro_test9(50);

-- 使用loop 循环
-- 注意要给循环起个名字， 退出时候，使用leave 循环名；
-- 使用loop 完成 1 加到n
-- 注意结束循环条件 leave  
CREATE PROCEDURE pro_test10(n int)
BEGIN
	declare total int default 0 ;
	
	c: LOOP 
	   set total = total + n;
		 set n = n - 1;

	IF n <= 0 THEN
		LEAVE c; 
	END IF; 
END LOOP c;

SELECT total;
END;

CALL pro_test10(3);

-- 游标 使用来存储查询结果集的数据类型
-- 查询表中的数据， 展示

CREATE PROCEDURE pro_test11()
BEGIN
  
	DECLARE s_sex varchar(20);
  DECLARE s_birth varchar(20);
  DECLARE s_name varchar(20);
  DECLARE s_id int(11);
	DECLARE stu_result CURSOR FOR SELECT * FROM Student;
	
	OPEN stu_result;
	
	FETCH stu_result INTO s_id, s_name, s_birth, s_sex;
	SELECT CONCAT('s_id: ', s_id, ' ,s_name: ', s_name, ' , s_birth: ', s_birth, ' ,s_sex: ', s_sex);
	
	FETCH stu_result INTO s_id, s_name, s_birth, s_sex;
  SELECT CONCAT('s_id: ', s_id, ' ,s_name: ', s_name, ' , s_birth: ', s_birth, ' ,s_sex: ', s_sex);
	
	CLOSE stu_result;
	
END;

DROP PROCEDURE pro_test11;
CALL pro_test11();

-- 使用循环 使用游标输出
-- mysql 提供的一个光标退出机制


CREATE PROCEDURE pro_test12()
BEGIN
  
	DECLARE s_sex varchar(20);
  DECLARE s_birth varchar(20);
  DECLARE s_name varchar(20);
  DECLARE s_id int(11);
	DECLARE has_data int default 1;	
	
	DECLARE stu_result CURSOR FOR SELECT * FROM Student;
	DECLARE EXIT HANDLER FOR NOT FOUND SET has_data = 0;
	
	OPEN stu_result;
	
	REPEAT
		FETCH stu_result INTO s_id, s_name, s_birth, s_sex;
	  SELECT CONCAT('s_id: ', s_id, ' ,s_name: ', s_name, ' , s_birth: ', s_birth, ' ,s_sex: ', s_sex);
  UNTIL has_data= 0
	END REPEAT;
	
	CLOSE stu_result;
END;

CALL pro_test12();


 -- 存储函数
 -- 存储函数有返回值
 -- 创建存储函数，返回一个表中的id= 01的 出生年月
 CREATE FUNCTION returnCount (sid VARCHAR(20))
 RETURNS VARCHAR(20)
 BEGIN
 
 DECLARE birth VARCHAR(20) DEFAULT '' ;
 
 SELECT s_birth INTO birth FROM Student WHERE s_id = sid;
 return birth;

 END;
 
 -- 删除函数
 DROP FUNCTION returnCount;
 -- 调用存储函数
 -- 是跟调用其他函数一样的。 SELECT 函数名
 SELECT returnCount('01');