MySql存储过程介绍

布满荆棘的人生 2024-03-23 14:11 65阅读 0赞

存储过程（Stored Procedure）是一种在关系型数据库中存储复杂程序代码和SQL语句的技术。 它们可以用来完成一些多表查询、数据处理或逻辑判断。下面是一个基本的存储过程应该包含的内容：

1.  存储过程名称：为了让程序员和数据库管理员更容易地找到存储过程，需要给存储过程起一个有意义的名称。
2.  参数列表：存储过程可以接收多个参数，这些参数可以是输入参数或输出参数。输入参数是从应用程序传递给存储过程的值，输出参数是存储过程返回的值。
3.  SQL语句：存储过程通常包含一些SQL语句，这些SQL语句可以用来进行数据操作、底层的事物处理、条件控制等等。
4.  返回值：存储过程可以返回一个或多个值，这些值可以是标量值、表格变量、游标等等。

下面两个简单的存储过程的demo来加深理解。

#### demo1 ####

首先我们可以用一个简单的存储过程来实现简单的插入操作。代码如下：

CREATE PROCEDURE insert_data ( IN NAME VARCHAR ( 50 ), IN age INT ) BEGIN
    	INSERT INTO students ( NAME, age )
    	VALUES
    	( NAME, age );
    END;

这个存储过程的作用是往名为students的表中插入一条数据，其中有name和age两个字段。用这个存储过程，我们只需要将name和age两个参数传递进去，就可以完成插入数据的操作。调用这个存储过程的代码如下：

CALL insert_data('Tom',18);

接下来，我们可以通过一个带有循环的存储过程来查询名为students的表。代码如下：

CREATE PROCEDURE get_all_students () BEGIN
    	DECLARE
    		i INT DEFAULT 0;
    	SELECT
    		COUNT(*) 
    	FROM
    		students INTO i;
    	WHILE
    			i > 0 DO
    		SELECT NAME
    			,
    			age 
    		FROM
    			students 
    		WHERE
    			id = i;
    		
    		SET i = i - 1;
    		
    	END WHILE;
    END;

这个存储过程的作用是查询名为students的表中的所有数据。存储过程首先获取students表中行数，然后通过一个循环逐个输出数据。我们调用这个存储过程的代码如下：

CALL get_all_students();

下面我们举个实际的应用场景。例如，在一个电商网站中，我们需要统计用户的消费总额并将总额更新到用户的账户余额上。这时候我们可以通过存储过程来实现这个功能。代码如下：

CREATE PROCEDURE update_user_balance ( IN user_id INT ) BEGIN
    	DECLARE
    		total_amount FLOAT DEFAULT 0;
    	SELECT
    		SUM( amount ) INTO total_amount 
    	FROM
    		orders 
    	WHERE
    		user_id = user_id;
    	UPDATE users 
    	SET balance = balance + total_amount 
    	WHERE
    	id = user_id;
    END;

这个存储过程的作用是根据用户id从订单表中计算出该用户的消费总额，并将总额更新到该用户的账户余额上。我们可以通过以下代码来调用这个存储过程：

CALL update_user_balance(100);

以上仅是MySQL存储过程的一些简单demo，但可以看出，利用存储过程，我们可以有效地提高代码的可读性和执行效率，以及保证数据的安全性。

#### demo2 ####

在应用程序中，可以使用mybatis来调用它。下面是代码示例：

1.  创建表  
    在MySQL中创建一个名为user的表，该表包含4个字段：id、name、age和gender，其中id是主键。

CREATE TABLE `user` (
    	`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
    	`name` VARCHAR ( 20 ) DEFAULT NULL,
    	`age` INT ( 11 ) DEFAULT NULL,
    	`gender` VARCHAR ( 4 ) DEFAULT NULL,
    PRIMARY KEY ( `id` ) 
    ) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4;

1.  创建实体类  
    创建一个User实体类，它对应数据库中的user表。

@Entity
    @Table(name = "user")
    public class User {
        
    
        @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        private Integer id;
    
        @Column(name = "name")
        private String name;
    
        @Column(name = "age")
        private Integer age;
    
    	@Column(name = "gender")
    	private String gender;
    
    // 省略 getter 和 setter 方法
    }

1.  创建mapper  
    创建一个mapper接口UserMapper，定义与user表相关的操作方法。

public interface UserMapper {
        
            
        void addUser(User user);
        
        User getUserById(Integer id);
        
        void updateUser(User user);
        
        void deleteUser(Integer id);
        
    }

1.  创建mapper映射文件  
    在classpath下创建一个名为UserMapper.xml的文件，该文件包含了UserMapper接口中相关方法的SQL语句。

<?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.example.mapper.UserMapper">
    
        <insert id="addUser" parameterType="com.example.entity.User">
            insert into user(name, age, gender)
            values (#{name}, #{age}, #{gender})
        </insert>
        
        <select id="getUserById" parameterType="java.lang.Integer"
                resultType="com.example.entity.User">
            select *
            from user
            where id = #{id}
        </select>
        
        <update id="updateUser" parameterType="com.example.entity.User">
            update user
            set name=#{name},
                age=#{age},
                gender=#{gender}
            where id = #{id}
        </update>
        
        <delete id="deleteUser" parameterType="java.lang.Integer">
            delete
            from user
            where id = #{id}
        </delete>
        
    </mapper>

1.  创建service层  
    创建一个UserService接口，定义一些业务方法。

public interface UserService {
        
    
        void addUser(User user);
        
        User getUserById(Integer id);
        
        void updateUser(User user);
        
        void deleteUser(Integer id);
        
    }

创建UserService接口的实现类UserServiceImpl，使用@Autowired将UserMapper注入到UserServiceImpl中。

@Service
    public class UserServiceImpl implements UserService {
        
            
        @Autowired
        private UserMapper userMapper;
        
        @Override
        public void addUser(User user) {
        
            userMapper.addUser(user);
        }
        
        @Override
        public User getUserById(Integer id) {
        
            return userMapper.getUserById(id);
        }
        
        @Override
        public void updateUser(User user) {
        
            userMapper.updateUser(user);
        }
        
        @Override
        public void deleteUser(Integer id) {
        
            userMapper.deleteUser(id);
        }
        
    }

以上就是使用MyBatis调用MySQL存错的全部步骤，其实就是将一些通用的SQL数据封装到SQL Map 中，外部再调用。因为MyBatis是单独将sql语句封装到SQL Map中进行管理和维护的，这为重构提供了方便，业务操作方法的SQL语句维护简单明了，易于维护，也提高了代码的复用性和可移植性。