mysql存储过程和使用场景

一、什么是存储过程

简单的说，就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，有点类似于应用程序的一个功能函数。

存储过程跟触发器有点类似，都是一组SQL集，但是存储过程是主动调用的，且功能比触发器更加强大，触发器是某件事触发后自动调用；

二、存储过程特性

有输入输出参数，可以声明变量，有if/else, case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能；

函数的普遍特性：模块化，封装，代码复用；

速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤；

基于以上特性，所以对于一些性能要求高，业务复杂的模块可以写到存储过程中，应用层直接调用即可。

三、创建简单的存储过程

3.1基本的语法

CREATE PROCEDURE pro_now() -- 存储过程名称，自定义
BEGIN -- 开始存储过程
       # 需要执行操作的sql语句集,可对数据表 进行CRUD 操作
       -- insert some sql here

end; -- 结束存储过程

3.2、创建一个查询当前时间的存储过程：

CREATE  PROCEDURE pro_now() 
BEGIN
    SELECT now();
END;

3.3、调用存储过程： call pro_now(); – call 关键字, “pro_now()” 存储过程名称

3.4、查看已创建的存储过程： show PROCEDURE STATUS [ where name=‘pro_now’] ;

3.5、删除存储过程：DROP PROCEDURE pro_now; – “pro_now” 存储过程名称

四、创建带参数的存储过程

4.1、 带输入参数的存储过程， 使用关键字： in

a、示例如下：

CREATE PROCEDURE pro_now_in(in time  VARCHAR(20) CHARACTER set "utf8")	
        -- CHARACTER set "utf8",设定字符集，解决中文乱码
BEGIN
	 SELECT now(),time;
end;

b、调用存储过程：

  set @time='当前时间';
  call pro_now_in(@time); --  call pro_now_in('当前时间'); 这样也可以

c、结果如下：

4.2 带输出参数的存储过程，使用关键字： out

a、示例如下：

CREATE PROCEDURE pro_now_out(out time  VARCHAR(20),out title VARCHAR(20) CHARACTER set utf8)
BEGIN
	 SELECT now(),'当前时间' into time , title;
end;

b、调用存储过程：

call pro_now_out(@times,@title);
SELECT @title AS "标题",@times AS "时间";

c、结果：

4.3、带输入、输出参数的存储过程，使用关键字： inout

a、示例如下：

CREATE PROCEDURE pro_now_inout(inout name VARCHAR(20),in title VARCHAR(10), out time VARCHAR(10))
BEGIN
	 SELECT CONCAT(name,'<--->',title) AS name,now() into name,time;
end;

b、调用存储过程：

set @name='jack';
set @title='toady';
call pro_now_inout(@name,@title,@time);
select @name as 'name and title',@time as 'time';

c、结果：

d、理解：

in （输入）： name 、title
out （输出）： name 、 time
CONCAT(name,’<—>’,title) 字符串拼接，对应 name 输出、 now() 对应 time 输出。

五、创建带控制流程的存储过程

5.1、if 语句
a、示例如下：

 CREATE PROCEDURE pro_if(in num INT)
     BEGIN
	    DECLARE result VARCHAR(20) CHARACTER set  utf8 DEFAULT null;
	    IF num = 0 THEN  -- 开始if判断，注意用一个等号"="
	        set result='num 为0啦'; -- 满足条件
	    ELSEIF num > 0 THEN -- 下一个if判断
		set result='num 大于 0';
  	    ELSEIF num < 0 THEN
		set result='num 小于 0';
	    ELSE -- 所有条件不满足的情况下
		set result='num is null or other status';
	    end if; -- 结束if 判断 
	    SELECT result;
 end;

b、调用存储过程：

 call pro_if('33');

c、结果如下：
、
5.2 case 语句

a、示例如下：

     CREATE PROCEDURE pro_case(in num INT)
BEGIN
	DECLARE result VARCHAR(20) CHARACTER set  utf8 DEFAULT null;
	case num  -- 开始case 判断
	when  2 THEN  -- 满足条件执行
		set result='num 值是2';
 	 when -2 THEN  
		set result='num 值是-2';
	else  -- 所有条件不满足，执行
		set result='num 不等于2和-2';
	end case ; -- 结束case语句
	SELECT result;
end;

b、调用存储过程：

call pro_case(-2);

c、结果如下：

5.3、while 循环语句

a、示例如下：

CREATE PROCEDURE pro_while(in num INT)
BEGIN
	DECLARE i int;
	DECLARE result int;
	set i=0;
	set result=0;
	while i < num DO -- 开始while 循环
		set result=result+i;
		set i=i+1;
	end while; -- 结束while 循环 
	SELECT result,i;
end;

b、调用存储过程：

call pro_while(100);

c、结果如下：

六、创建带游标循环的存储过程

1、示例如下：

CREATE PROCEDURE pro_cursor(out count int)
BEGIN
	declare  paper_id  VARCHAR(1000) ; -- 论文主键id
	declare doctroName VARCHAR(1000) character set gbk; -- 医生名称
	DECLARE paper_hos VARCHAR(1000); -- 医院id
	DECLARE paper_room      VARCHAR(100); -- 医生专业
	declare done int DEFAULT false ; -- 自定义控制游标循环变量,默认false
	DECLARE  my_cursor CURSOR for (SELECT id,authorName,hospitalId,room
							from yee_article_paper_authorid ); -- 定义游标并输入结果集  
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=TRUE; -- 绑定控制变量到游标,游标循环结束自动转true 
	OPEN my_cursor; -- 打开游标
	myLoop:LOOP -- 开始循环体,myLoop为自定义循环名,结束循环时用到  
	FETCH my_cursor into paper_id,doctroName,paper_hos,paper_room ;  -- 将游标当前读取行的数据顺序赋予自定义变量12  
	if done THEN -- 判断是否继续循环  
		LEAVE myLoop;-- 结束循环
	END IF;
	 -- 自己要做的事情,在 sql 中直接使用自定义变量即可  
	insert into temp(str_id,name,hospitalId,room) 	
VALUES(paper_id,doctroName,paper_hos,paper_room);
	COMMIT; -- 提交事务
  END  LOOP myLoop; -- 结束 自定义循环体
	CLOSE my_cursor; -- 关闭游标
	# 循环结束后，统计导入个数
	SELECT count(id)  count from temp into count; -- 计算个数
end

游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次一行或者多行前进或向后浏览数据的能力。

七、Spring mvc 调取存储过程

SimpleJdbcCall 类可以被用于调用一个包含 IN 和 OUT 参数的存储过程。你可以在处理任何一个 RDBMS 时使用这个方法，就像 Apache Derby， DB2， MySQL， Microsoft SQL Server， Oracle，和 Sybase。

7、1创建表：

CREATE TABLE ssers(
   ID   INT NOT NULL AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE  INT NOT NULL,
   PRIMARY KEY (ID)
);

7.2 创建存储过程：

DELIMITER $$
DROP PROCEDURE IF EXISTS `TEST`.`getRecord` $$
CREATE PROCEDURE `TEST`.`getRecord` (
IN in_id     INTEGER,
OUT out_name VARCHAR(20)  CHARACTER set "utf8",
OUT out_age  INTEGER
)
BEGIN
   SELECT name, age
   INTO out_name,out_age
   FROM users where id = in_id;
END $$
DELIMITER ;

delimiter是MySQL中的命令，这个命令与存储过程没什么关系。
其实就是告诉mysql解释器，该段命令是否已经结束了，mysql是否可以执行了。
即改变输入结束符。
默认情况下，delimiter是分号“;”。

7.3 dao 层调用存储过程

Public class UserDaoImpl implements UserDao {

    @Autowired
    private JdbcTemplate myJdbc;

    @Autowired
    private DataSource dataSource;


    private UserModel  userModel;

    @Override
    public UserModel getUser(int id){

        //创建jdbccall对象
        SimpleJdbcCall jdbcCall = new SimpleJdbcCall(dataSource).withProcedureName("getRecord");

        //调用存储过程
        SqlParameterSource in   = new MapSqlParameterSource().addValue("in_id", id);
        Map<String, Object> out = jdbcCall.execute(in);

        UserModel user = new UserModel(0,null,0);

        user.setId(id);
        user.setName((String) out.get("out_name"));
        user.setAge((Integer) out.get("out_age"));
        return user;

        //普通的sql查询
        //String SQL     = "select * from users id = ?";
        //UserModel user = myJdbc.queryForObject(SQL,new Object[]{id}, new UserMapper());
        //return user;

    }

}

八、存储过程弊端

不同数据库，语法差别很大，移植困难，换了数据库，需要重新编写；

不好管理，把过多业务逻辑写在存储过程不好维护，不利于分层管理，容易混乱，一般存储过程适用于个别对性能要求较高的业务，其它的必要性不是很大；

参考链接：

https://blog.csdn.net/HaHa_Sir/article/details/79728854

https://www.cnblogs.com/chenpi/p/5136483.html

https://www.w3cschool.cn/wkspring/3yh61mmc.html