存储过程
存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合,我们常用的操作数据库语言SQL语句在执行的时候需要要先编译,然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。
存储过程优点:
(1).存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。
(2).存储过程允许标准组件是编程。存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改,对应用程序源代码毫无影响
(3).存储过程是经过是是先编译成功存储在数据库的,调用存储过程或函数可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对于提高数据处理效率
- 创建存储过程语法:
由 [] 所包裹的可有,也可无
create procedre 存储过程名称 ([参数IN\OUT\OUTIN[...]])
begin
-- SQL语句,我们所执行的逻辑,我们的存储过程中的逻辑都放在begin和end中间
end
- 调用存储过程
call 存储过程名称([指定的参数,无参数则不传]) //call test()
- 查看数据库全部存储过程
select name from mysql.proc; --指定数据库查看存储过程 where db = '数据库名称'
- 查看指定存储过程详情
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 表
- 判断条件 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$
- 条件判断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$
- 循环语句 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
- 循环语句 loop
begin
LOOP_OK:look --给loop一个标记
--执行的循环逻辑
if 条件 then
leave LOOP_OK; --使用leave关键字加标记,停止loop循环,不在停止的话会是死循环
end if;
end loop;
end
- 游标和光标的使用 遍历
直接上示例:
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$
- 传参
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;
}