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();
}
}