Mysql存储过程以及使用Mybatis调用存储过程

存储过程

存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，我们常用的操作数据库语言SQL语句在执行的时候需要要先编译，然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。
存储过程优点：
(1).存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

(2).存储过程允许标准组件是编程。存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响

(3).存储过程是经过是是先编译成功存储在数据库的，调用存储过程或函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输,对于提高数据处理效率

1. 创建存储过程语法：

由 [] 所包裹的可有，也可无

create procedre 存储过程名称 ([参数IN\OUT\OUTIN[...]])
begin
    -- SQL语句,我们所执行的逻辑，我们的存储过程中的逻辑都放在begin和end中间
end

2. 调用存储过程

call 存储过程名称([指定的参数，无参数则不传])       //call test()

3. 查看数据库全部存储过程

select name from mysql.proc;   --指定数据库查看存储过程  where db = '数据库名称'

4. 查看指定存储过程详情

show create procedure 数据库名称.存储过程名称 \G;   //一定要加上\g

示例：
我们创建一个简单的数据库存储过程

delimiter $   --代表接下来的语句用$来结尾
create procedure test()
begin
	declare a int default 0;  --定义一个int类型的变量并设置默认值为0，declare是定义变量的关键字
	select count(1) into a from table01;  --将table表的总记录条数查出并使用into赋值为给a
	select a;  --打印输出a的值
end $ 

5.定义变量 （局部变量只可以作用在begin…end中）

declare 变量名称 变量类型 default 默认值  --default可以不写，指定默认值

变量赋值：
直接赋值：

set 变量名称 = 值；

查询赋值：

select 查询字段 into 变量名称 from 表

6. 判断条件 if

begin
	if  判断条件  then  执行的逻辑
	elseif	判断条件	then	执行的逻辑
	else	执行的逻辑
	end if;  --结束if（必须有）
end$

示例：

create procedure test08(in a int)
begin
	declare desci varchar(20) default '';
	if a>=175 then
		set desci = '高挑';
	elseif a>=170 and a<175 then
		set desci = '标准';
	else 
		set desci = '一般';
	end if;
	select desci;  --输出
end$

7. 条件判断case

begin
	case when 判断条件 then 执行的逻辑
	when 判断条件 then 执行的逻辑 --记住从最开始只有一个case，之后的都是when开头
	...
	else 执行的逻辑
	end case; -- 结束case（必须有）
end

示例：

create procedure test09(in a int)
begin
	declare desci varchar(20);
		case when a>=175 then 
			set desci = '高挑身材';
		when a>=170 and a <175 then
			set desci = '标准身材';
		else 
			set desci = '一般';
		end case;
	select desci;  --输出
end$

8. 循环语句 while

begin
	while 条件 do
		--执行的逻辑
	end while; --结束while循环
end

示例：

create procedure test10(in a int)
begin
	declare b int;
	while a<=5 do  --当a满足小于等于5的时候执行循环
		set b = a+b;
		set a = a+1;
		select a;  --输出
	end while;
end$

9.循环语句 repeat

begin
	repeat
	 -- 循环逻辑
	until 条件  --关键字until 后是条件，当满足条件的时候，停止循环
	end repeat;
end

10. 循环语句 loop

begin
	LOOP_OK：look  --给loop一个标记
	 --执行的循环逻辑
	 if 条件 then    
	 leave LOOP_OK;  --使用leave关键字加标记,停止loop循环，不在停止的话会是死循环
	 end if;
	end loop;
end

1. 游标和光标的使用 遍历
   直接上示例：

DELIMITER $
CREATE PROCEDURE test14()
BEGIN 

	DECLARE done INT DEFAULT 1;  --定义一个变量用来控制循环的停止
	DECLARE id VARCHAR(10);  --定义变量用来接收光标
	DECLARE NAME VARCHAR(10);
	DECLARE lis CURSOR FOR SELECT * FROM test;  --**创建游标** 关键字 cursor for
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 0;  --当若没有数据返回,程序继续，将done变为0，用来停止循环
	
	OPEN LIST;  --**打开游标**
	
		fo:LOOP  --开始循环
		FETCH lis INTO id,NAME; --通过fetch ,,, into ,,, **获得光标** 关键字 fetch...into
		SELECT id,NAME;
		
			IF done <= 0 THEN
				LEAVE fo;  --退出循环
			END IF;
			
		END LOOP;
		
	CLOSE LIST; --**关闭游标**
	
END$

12. 传参

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：
CREATE PROCEDURE( [[IN|OUT|INOUT] 参数名 数据类型...] )

IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

OUT 输出参数:该值可在存储过程内部被改变，并可返回

INOUT 输入输出参数:调用时指定，并且可被改变和返回

**Ⅰ. IN参数例子**
创建:
mysql > DELIMITER //  
mysql > CREATE PROCEDURE demo_in_parameter(IN p_in int)  
-> BEGIN   
-> SELECT p_in;   --打印输出
-> SET p_in=2;   
-> SELECT p_in;   --打印输出
-> END;   
-> //  
mysql > DELIMITER ; 

执行结果:

mysql > SET @p_in=1;  
mysql > CALL demo_in_parameter(@p_in);  
+------+  
| p_in |  
+------+  
|   1  |   
+------+  
 
+------+  
| p_in |  
+------+  
|   2  |   
+------+  
 
mysql> SELECT @p_in;  
+-------+  
| @p_in |  
+-------+  
|  1    |  
+-------+  

以上可以看出，p_in虽然在存储过程中被修改，但并不影响@p_id的值

 

**Ⅱ.OUT参数例子**

创建:

mysql > DELIMITER //  
mysql > CREATE PROCEDURE demo_out_parameter(OUT p_out int)  
-> BEGIN 
-> SELECT p_out;  
-> SET p_out=2;  
-> SELECT p_out;  
-> END;  
-> //  
mysql > DELIMITER ; 

执行结果:

mysql > SET @p_out=1;  
mysql > CALL sp_demo_out_parameter(@p_out);  
+-------+  
| p_out |   
+-------+  
| NULL  |   
+-------+  
 
+-------+  
| p_out |  
+-------+  
|   2   |   
+-------+  
 
mysql> SELECT @p_out;  
+-------+  
| p_out |  
+-------+  
|   2   |  
+-------+  

**Ⅲ. INOUT参数例子**

创建:

mysql > DELIMITER //   
mysql > CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int)   
-> BEGIN 
-> SELECT p_inout;  
-> SET p_inout=2;  
-> SELECT p_inout;   
-> END;  
-> //   
mysql > DELIMITER ; 
 

 

执行结果:
mysql > SET @p_inout=1;  
mysql > CALL demo_inout_parameter(@p_inout) ;  
+---------+  
| p_inout |  
+---------+  
|    1    |  
+---------+  
 
+---------+  
| p_inout |   
+---------+  
|    2    |  
+---------+  
 
mysql > SELECT @p_inout;  
+----------+  
| @p_inout |   
+----------+  
|    2     |  
+----------+ 

Mybatis 调用存储过程

mapper.xml映射文件方式
我们的存储过程为：

CREATE PROCEDURE getTest()
BEGIN
	SELECT * FROM test;
END

mapper.xml statementType的值必须显示指定为CALLABLE（这种是调用存储过程）

<!--调用数据库存储过程-->
    <select id="getTest" resultType="com.sjh.pojo.Test" statementType="CALLABLE">
      {call getTest()}
    </select>

mapper.class 与平常调用即可

//    mapper.xml方式
    List<Test> getTest();

注解方式
个人比较推荐使用注解方式

//    注解方式
    @Select("call getTest()")
    @Options(statementType = StatementType.CALLABLE)
    List<Test> getTest();

传参数：

//    注解传参方式
    @Select("call getTest02(#{id,mode=IN,jdbcType=VARCHAR},#{b,mode=OUT,jdbcType=VARCHAR})")
    @Options(statementType = StatementType.CALLABLE)
    //Test getTestThree(@Param("id") String id,@Param("te")String te);
    Test getTestThree(Map<String,Object> map);

mode指定IN、OUT、INOUT jdbcType 指定我们传参数的类型
id为IN类型参数，b为OUT类型参数，查看我们的service层

public Test getTestThree(){
        Map<String,Object> map = new HashMap<>();
        map.put("id","12345");
        map.put("b","haha");
        Test testThree = testMapper.getTestThree(map);  --调用mapper方法接收结果集
        if(testThree != null){
            System.err.println(map.get("b").toString()); -- 这里接收b的OUT类型的参数，在存储过程中我们将b从新赋值为“zzzzzz”，在控制台已成功打印
        }
        return testThree;
    }