14、存储过程

14、存储过程

1、什么是存储过程？

如果实现用户的某些需求时，需要编写一组复杂的SQL语句才能实现，那么可以将这组复杂的SQL语句集编写在数据库中，由JDBC调用来执行这组SQL语句。把编写在数据库中的SQL语句集称为存储过程。
    存储过程（PROCEDURE）是事先经过编译并存储在数据库中的一段 SQL 语句的集合。调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
     存储过程类似于JAVA语言中的方法，需要先定义，使用时需要调用。存储过程可以定义参数，参数分为IN、OUT、INOUT 类型三种类型。IN类型的参数表示接收调用者传入的数据，OUT类型的参数表示向调用者返回数据，INOUT类型的参数既可以接收调用者传入的参数，也可以向调用者返回数据。

2、存储过程的语法格式

2.1 定义

DELIMITER$$
create procedure 存储过程名（in 变量名 类型，out 参数2，…）
begin
   declare   变量名 类型 [DEFAULT  值];
   set 变量名 = 值;
   存储过程语句块;
END$$

演示存储过程参数使用

DELIMITER$$                                       -- out 从存储过程向调用处输出
CREATE PROCEDURE test3(IN p_account VARCHAR(10),IN p_sex VARCHAR(1),OUT p_num INT)
BEGIN
   DECLARE v_num INT;
   SET v_num  = 10;
   SET p_num = v_num;
   INSERT INTO t_test(account,sex)VALUES(p_account,p_sex);
   SELECT p_num;
END$$ 

2.2 调用

CALL 存储过程名([形参]);

2.3 删除

DROP PROCEDURE 存储过程名

3、语法解析

1.存储过程的参数分为in，out，inout三种类型。

2.in代表输入参数（默认情况下为in参数），表示该参数的值必须由调用程序指定。

3.out代表输出参数，表示该参数的值经存储过程计算后，将out参数的计算结果返回给调用程序。

4.inout代表即是输入参数，又是输出参数，表示该参数的值即可以由调用程序指定，又可以将 inout参数的计算结果返回给调用程序。

5.存储过程中的语句必须包含在begin和end之间。

6.declare 中用来声明变量,变量默认赋值使用default,语句块中改变变量值,使用set 变量=值;

4、 流程控制

4.1 选择语句：

（1）if选择

IF  条件  THEN
       语句
   ELSEIF  条件   THEN
        语句
  ELSE 
        语句
  END IF;

在存储过程中判断账号是否存在,不存在保存,返回0 存在不保存返回1

DELIMITER$$
CREATE PROCEDURE test8(IN p_account VARCHAR(10),IN p_sex VARCHAR(1),OUT p_mark INT)
BEGIN
DECLARE v_count INT;
   SELECT COUNT(*) INTO v_count FROM t_test WHERE account = p_account;
    IF v_count=0 THEN 
      INSERT INTO t_test(account,sex)VALUES(p_account,p_sex);
      SET p_mark = 0;
    ELSE 
      SET p_mark = 1;
    END IF;
    SELECT p_mark;
END$$ 

CALL test8('admin2','男',@p)

（2）case选择

CASE WHEN   条件  THEN
      语句      
  ELSE 
      语句
  END CASE;

演示：

DELIMITER$$
CREATE PROCEDURE test9(IN p_day INT)
BEGIN
   CASE WHEN p_day=0 THEN 
          SELECT "星期天";
        WHEN p_day=1 THEN  
          SELECT "星期一";
        ELSE 
          SELECT "无效日期";
   END CASE;
END$$ 

CALL test9(2)

4.2 循环语句

loop 必须配合 leave 使用，loop没有停止语句，使用leave跳出被标注的流程控制语句

IF 条件 THEN LEAVE 标记;
END IF;
end loop 标记;

示例：

DELIMITER$$
CREATE PROCEDURE test10()
BEGIN
DECLARE v_num INT DEFAULT 0;
    addnum:LOOP
       SET v_num = v_num+1;
       IF v_num=10 THEN
          LEAVE addnum; 
       END IF;
      END LOOP addnum;
     SELECT v_num;
END$$ 

CALL test10();

5、在Java中调用存储过程

5.1 jdbc中调用

package com.ff.mybatisPro.test;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;

public class TestJdbcCall {
	
	public static void main(String[] args) throws SQLException {
		 Connection  connection = null;
		 CallableStatement  call = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");//创建Driver类的对象
			  connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/kj_db?characterEncoding=utf8", "root", "root");
			    //jdbc调用存储过程
			    call =   connection.prepareCall("{call test8(?,?,?)}");
			    //设置输入参数
		        call.setString(1,"admin3");
		        call.setString(2, "男");
		        //输出参数注册
		        call.registerOutParameter(3, Types.INTEGER);
		        call.executeUpdate();
		        //获得输出参数
		        System.out.println(call.getInt(3));
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("链接数据库异常");
		}catch (ClassNotFoundException e) {
			e.printStackTrace();
			System.out.println("类找不到异常");
		}finally{
			if(connection!=null){
				connection.close();
			}
			if(call!=null){
				call.close();
			}
			
		}
	}
}

5.2 MyBatis中调用

UserMapper.xml

<parameterMap type="map" id="usermap">
    <parameter property="account" jdbcType="VARCHAR" mode="IN"/>
    <parameter property="sex" jdbcType="VARCHAR" mode="IN"/>
    <parameter property="res" jdbcType="INTEGER" mode="OUT"/>
</parameterMap>

<insert id="save" parameterMap="usermap" 		statementType="CALLABLE">
	{call test8(?,?,?)}
</insert>

TestMybatisCall.java

package com.ff.mybatisPro.test;

import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import com.ff.mybatisPro.bean.User;
import com.ff.mybatisPro.dao.UserDao;
import com.ff.mybatisPro.util.MybatisUtil;

public class TestMybatisCall {
	
	@Test
	public void save(){
		SqlSession sqlSession1=MybatisUtil.getSqlSession();
		UserDao userdao1 = sqlSession1.getMapper(UserDao.class);
		Map<String, Object> parms = new HashMap<String, Object>();  
		parms.put("account","admin6");
		parms.put("sex","男");
		userdao1.save(parms);  
		System.out.println(parms.get("res"));//输出参数
		  
		sqlSession1.commit();
		sqlSession1.close();
	}	 
}