mysql存储过程详解以及在mybatis中执行存储过程

1.存储过程简介

Mysql储存过程是一组为了完成特定功能的SQL语句集，经过编译之后存储在数据库中，当需要使用该组SQL语句时用户只需要通过指定储存过程的名字并给定参数就可以调用执行它了，简而言之就是一组已经写好的命令，需要使用的时候拿出来用就可以了。

储存过程是一个可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。可用于数据库表格的一键生成，和表格的数据初始化

2.存储过程基本结构

DELIMITER //  声明语句结束符，用于区分;
create procedure 存储过程名(参数)
begin
... -- 此处写sql或者是控制语句
end//
DELIMITER ;

3.存储过程的参数

CREATE PROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])

• IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
• OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
• INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

在定义过程时，使用 DELIMITER // 命令将语句的结束符号从分号 ; 临时改为两个 //，使得过程体中使用的分号被直接传递到服务器，而不会被客户端（如mysql）解释。
IN 输入参数：表示调用者向过程传入值（传入值可以是字面量或变量）
OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）

4.存储过程的创建

实例：

-- 利用存储过程创建数据库表格，并填充初始化数据

-- 如果此存储过程已经存在，先将其删除
DROP PROCEDURE IF EXISTS initdata;

-- 声明语句结束符，用于区分;
DELIMITER //
-- 创建存储过程
CREATE PROCEDURE initdata()
BEGIN
-- 过程体开始

-- 创建一个sql异常处理器，error初始值为1，只要发生异常就将error的值置为0
DECLARE status INTEGER DEFAULT 1;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET status=0;

-- 判断t_student表是否已存在，存在则删除重建
DROP TABLE IF EXISTS t_student;
-- 创建t_student表的sql语句
CREATE TABLE t_student (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  stu_num varchar(50) DEFAULT NULL,
  stu_name varchar(100) DEFAULT NULL,
  stu_age int(11) DEFAULT NULL,
  stu_pwd varchar(50) DEFAULT '123456',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

-- 初始化填充数据
INSERT INTO t_student VALUES('1','001','张三','18','123456');
INSERT INTO t_student VALUES('2','002','张四','19','123456');
INSERT INTO t_student VALUES('3','003','张五','20','123456');
INSERT INTO t_student VALUES('4','004','张六','21','123456');
INSERT INTO t_student VALUES('5','005','张七','22','123456');
INSERT INTO t_student VALUES('6','006','张八','23','123456');

-- 如果status为1，则未发生异常可以提交事务，若status为0，则发生了异常，让事务回滚
IF status = 1 THEN
	COMMIT;
ELSE
  ROLLBACK;
END IF;

-- 返回状态码
SELECT status;

-- 过程体结束
END//
DELIMITER ;

-- 执行存储过程
CALL initdata();

5.存储过程的调用

在命令行下调用

-- 执行存储过程
CALL initdata();

在mybatis中执行存储过程

package cn.edu.zzti.cs.dao.init;

// mapper接口
public interface InitData {
    Integer initData();
}

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="cn.edu.zzti.cs.dao.init.InitData">
    <select id="initData" resultType="java.lang.Integer" statementType="CALLABLE">
        <![CDATA[
          call initData();
        ]]>
    </select>
</mapper>

6.测试

package cn.edu.zzti.cs.dao.init;


import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.transaction.annotation.Transactional;

@ContextConfiguration(locations = "classpath:/spring/applicationContext-dao.xml")
@RunWith(SpringJUnit4ClassRunner.class)
public class InitDataTest {
    @Autowired
    private InitDataMapper initDataMapper;

    /**
     * @description 测试数据库初始化
     **/
     @Test
    public void InitTest(){
        if(initDataMapper.initData()==1){
            System.out.println("初始化数据成功！");
        }else {
            System.out.println("初始化数据失败！");
        }
    }
}

测试结果

可以看到数据库中会多了一个t_student表，并填充了初始化数据。