mysql数据库存储过程

最新推荐文章于 2024-05-20 13:15:00 发布

weixin_44224345

最新推荐文章于 2024-05-20 13:15:00 发布

阅读量131

点赞数

分类专栏： mysql数据库存储过程文章标签： mysql

本文链接：https://blog.csdn.net/weixin_44224345/article/details/86466861

版权

mysql数据库存储过程专栏收录该内容

1 篇文章 0 订阅

订阅专栏

存储过程

存储过程简介

是什么

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 标识

https://www.cnblogs.com/mark-chan/p/5384139.html

在程序中测试

本地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 {
        }

    }