存储过程简介
是什么
SQL语句需要先编译然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。
存储过程是可编程的函数,在数据库中创建并保存,可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数,或者封装特定功能时,存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟,它允许控制数据的访问方式。
个人理解:有点类似于java的面向对象 可以将一些常用的sql语句封装成方法 我们可以通过sql语句来调用我们封装的这个方法 ,并且方法中可以也有参数,参数的类型划分更严格;
存储过程的优点
(1).增强SQL语言的功能和灵活性:存储过程可以用控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。
(2).标准组件式编程:存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改,对应用程序源代码毫无影响。
(3).较快的执行速度:如果某一操作包含大量的Transaction-SQL代码或分别被多次执行,那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询,优化器对其进行分析优化,并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化,速度相对要慢一些。
(4).减少网络流量:针对同一个数据库对象的操作(如查询、修改),如果这一操作所涉及的Transaction-SQL语句被组织进存储过程,那么当在客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而大大减少网络流量并降低了网络负载。
(5).作为一种安全机制来充分利用:通过对执行某一存储过程的权限进行限制,能够实现对相应的数据的访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全。
存储过程缺点
- 不易维护,一旦逻辑变了修改起来麻烦
- 如果写此存储过程的人离职了,对于接手她代码的人估计是一场灾难,因为别人还要去读懂你程序逻辑,还要读懂你存储逻辑。不利于扩展。
- 最大的缺点! 虽然存储过程可以减少代码量,提高开发效率。但是有一点非常致命的就是太耗性能。
存储过程的语法
显示所有的存储过程
SHOW PROCEDURE STATUS
1.创建一个存储过程
CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 …] 过程体
DELIMITER //
CREATE PROCEDURE test1()
BEGIN
select * FROM t_user;
END;
//
DELIMITER ;
call test1();
分隔符
MySQL默认以";"为分隔符,如果没有声明分割符,则编译器会把存储过程当成SQL语句进行处理,因此编译过程会报错,所以要事先用“DELIMITER //” 声明当前段分隔符,让编译器把两个 “//” 之间的内容当做存储过程的代码,不会执行这些代码;“DELIMITER ;”的意为把分隔符还原。
参数
存储过程根据需要可能会有 输入, 输出, 输入输出参数 ,如果有多个参数使用 " , " 分隔开,MySQL存储过程的参数用在存储过程的定义 ,共有三种参数类型 IN / OUT / INOUT
IN : 参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回,为默认值
OUT:该值可以在存储过程内部改变,并可以返回
INOUT:调用时指定,并且可被改变和返回
IN 参数例子
DELIMITER //
CREATE PROCEDURE test_in(IN p_in int)
BEGIN
SELECT p_in;
SET p_in=2;
SELECT p_in;
END;
//
DELIMITER ;
#调用1
CALL test_in(3);
#调用2
SET @p_in=1;
CALL test_in(@p_in);
SELECT @p_in;
OUT参数例子
#存储过程OUT参数
DELIMITER //
CREATE PROCEDURE test_out(OUT p_out int)
BEGIN
SELECT p_out;
SET p_out=2;
SELECT p_out;
END;
//
DELIMITER ;
#调用
SET @p_out=1;
CALL test_out(@p_out);
SELECT @p_out;
INOUT参数例子
#存储过程INOUT参数
DELIMITER //
CREATE PROCEDURE test_INOUT(INOUT p_inout int)
BEGIN
SELECT p_inout;
SET p_inout=2;
SELECT p_inout;
END;
//
DELIMITER ;
#调用
SET @p_inout=1;
CALL test_inout(@p_inout);
SELECT @p_inout;
条件语句
IF-THEN-ELSE语句
#CASE-WHEN-THEN-ELSE语句
DELIMITER //
CREATE PROCEDURE switch_test (IN parameter INT)
BEGIN
DECLARE var INT;#声明一个 int 的 var
SET var=parameter+1;
CASE var
WHEN 0 THEN
INSERT INTO t_test VALUES (17);
WHEN 1 THEN
INSERT INTO t_test VALUES (18);
ELSE
INSERT INTO t_test VALUES (19);
END CASE ;
END ;
//
DELIMITER ;
SET @paramenter = 3;
CALL switch_test(@paramenter);
select @paramenter;
循环语句
WHILE-DO…END-WHILE
DELIMITER //
CREATE PROCEDURE proc5()
BEGIN
DECLARE var INT;
SET var=0;
WHILE var<6 DO
INSERT INTO t VALUES (var);
SET var=var+1;
END WHILE ;
END;
//
DELIMITER ;
MySQL存储过程的删除
DROP PROCEDURE [过程1[,过程2…]] # 从MySQL的表格中删除一个或多个存储过程。
过程体
过程体的开始和结束使用的是 开始BEGIN 结束END 标识
在程序中测试
本地jar导入本地仓库
3.1 输入mvn install:install-file -Dfile=C:\Users\sinosoft\Desktop\lib\spring-cloud-sleuth-zipkin-1.2.0.RC1.jar -DgroupId=org.springframework.cloud -DartifactId=spring-cloud-sleuth-zipkin -Dversion=1.2.0.RC1 -Dpackaging=jar–>提示build Success即可。
命令解释:mvn install:install-file -Dfile=本地jar包路径 -DgroupId=可查看groupID -DartifactId=查看artifactId -Dversion=查看version -Dpackaging=jar
pom导入相关jar
<dependencies>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.35</version>
</dependency>
<dependency>
<groupId>com.oracle</groupId>
<artifactId>ojdbc6</artifactId>
<version>11.2.0.1.0</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
</dependency>
</dependencies>
mybatis 调用存储过程
数据库创建新增
--删除
DELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `delete_user`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `delete_user`(IN user_id INT)
BEGIN
DELETE FROM t_user WHERE id = (SELECT user_id);
END$$
DELIMITER ;
--新增
DELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `add_user`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `add_user`(IN user_name VARCHAR(255) , IN user_salary INT)
BEGIN
INSERT INTO t_user(NAME,salary) VALUES(user_name,user_salary);
END$$
DELIMITER ;
--修改
DELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `update_user`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `update_user`(IN user_name VARCHAR(255) , IN user_salary INT , IN user_id INT)
BEGIN
UPDATE t_user SET NAME= user_name , salary= user_salary WHERE id= user_id;
END$$
DELIMITER ;
--查询
DELIMITER $$
USE `test`$$
DROP PROCEDURE IF EXISTS `test1`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `test1`()
BEGIN
SELECT * FROM t_user;
END$$
DELIMITER ;
mapper.xml
<?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="com.sx.mapper.UserMapper">
<select id="queryUser" resultType="com.sx.pojo.User">
call test1()
</select>
<delete id="deleteUser" parameterType="java.lang.Integer">
call delete_user(#{value})
</delete>
<insert id="addUser" parameterType="com.sx.pojo.User">
CALL add_user(#{name},#{salary})
</insert>
<update id="updateUser" parameterType="com.sx.pojo.User">
call update_user(#{name},#{salary},#{id});
</update>
</mapper>
代码测试
@Test
public void test(){
Connection conn = null;
String sql;
// MySQL的JDBC URL编写方式:jdbc:mysql://主机名称:连接端口/数据库的名称?参数=值
// 避免中文乱码要指定useUnicode和characterEncoding
// 执行数据库操作之前要在数据库管理系统上创建一个数据库,名字自己定,
// 下面语句之前就要先创建javademo数据库
String url = "jdbc:mysql://localhost:3306/test?"
+ "user=root&password=root&useUnicode=true&characterEncoding=UTF8";
try {
// 之所以要使用下面这条语句,是因为要使用MySQL的驱动,所以我们要把它驱动起来,
// 可以通过Class.forName把它加载进去,也可以通过初始化来驱动起来,下面三种形式都可以
Class.forName("com.mysql.jdbc.Driver");// 动态加载mysql驱动
// or:
// com.mysql.jdbc.Driver driver = new com.mysql.jdbc.Driver();
// or:
// new com.mysql.jdbc.Driver();
System.out.println("成功加载MySQL驱动程序");
// 一个Connection代表一个数据库连接
conn = DriverManager.getConnection(url);
// Statement里面带有很多方法,比如executeUpdate可以实现插入,更新和删除等
Statement stmt = conn.createStatement();
sql = "CALL test1()";
int result = stmt.executeUpdate(sql);// executeUpdate语句会返回一个受影响的行数,如果返回-1就没有成功
if (result != -1) {
System.out.println("创建数据表成功");
sql = "CALL test1()";
ResultSet rs = stmt.executeQuery(sql);// executeQuery会返回结果的集合,否则返回空值
while (rs.next()) {
System.out
.println(rs.getString(1) + "\t" + rs.getString(2));// 入如果返回的是int类型可以用getInt()
}
}
} catch (SQLException e) {
System.out.println("MySQL操作错误");
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
} finally {
}
}