【MySQL】探索MySQL存储过程的魔力，初学者的数据库编程秘笈（内含实战SQL脚本）_百度小程序

您的位置：上海毫米网络优化公司 > 小程序 > 百度小程序 >

文章目录

🌟前言
🌟存储过程介绍
🌟存储过程基本语法
🌟存储过程实战
- 无参数列表存储过程
- 带参数列表存储过程
- 带流程控制语句IF ELSE存储过程
- 带条件控制语句CASE存储过程
- 带循环语句WHILE存储过程
- 带循环语句REPEAT UNTIL存储过程
- 带循环语句LOOP存储过程
- 查询存储过程
- 查询存储过程定义源码
- 删除存储过程
- 🌟写在最后
  🌟前言
  
  MySQL存储过程作为一种服务器端的数据库编程方式，提供了高效、可重用的方法来执行相对复杂的数据库操作。本篇文章主要针对初学者，展开对MySQL存储过程的讲解，帮助MySQL初学者能够更好的学习。
  
  🌟存储过程介绍
  
  定义：
  
  存储过程是一组预定义的SQL语句集合，被存储在数据库中，以便复用和调用。它们可以接收参数、执行逻辑判断、进行循环和异常处理，并返回结果。存储过程在数据库服务器上执行，减少了网络传输的开销，提升了性能。
  MySQL存储过程支持常见的编程结构，如条件语句（IF-ELSE、CASE）、循环（WHILE、LOOP）和异常处理（TRY-CATCH）。除了基本的SQL语句，还可以通过使用变量、游标和临时表等特性，实现更复杂的操作和逻辑。
  
  优势：
  
  提高性能：存储过程在数据库服务器上执行，减少了与客户端的数据传输，降低了网络开销，从而提高了性能。此外，存储过程可以被编译和优化，减少了重复代码的冗余，进一步提升了查询执行效率。
  简化复杂的查询：对于复杂的查询或需要按特定顺序执行的多个查询，存储过程提供了一种简洁、可重用的解决方案。通过将逻辑封装在存储过程中，可以减少应用程序中的SQL代码量，使数据库逻辑更清晰、易于维护。
  增强安全性：存储过程可以设置为只能由特定用户或角色调用，从而增强了对数据库的安全性。
  减轻客户端负担：将复杂的业务逻辑放在存储过程中，可以减轻客户端的负担。客户端只需调用相应的存储过程，而无需编写冗长的SQL语句和处理复杂的结果集，简化了应用程序的开发和维护。
  
  劣势：
  
  可移植性较差：存储过程在不同的数据库系统之间的语法和特性可能存在差异，这导致了存储过程的可移植性较差。如果需要将应用程序迁移到另一个数据库系统，可能需要对存储过程进行调整和修改。
  难以调试和维护：存储过程的调试和维护相对复杂。由于存储过程在数据库服务器上执行，无法像客户端应用程序那样方便地进行调试。此外，存储过程的逻辑可能分散在多个存储过程中，增加了维护的难度。
  
  🌟存储过程基本语法
  
  创建：相当于Java中定义方法
```
CREATE [DEFINER = {user | CURRENT_USER}] PROCEDURE procedure_name ([parameter_list])
    [characteristic ...]
    BEGIN
        DECLARE variable_name data_type; -- 声明变量
        -- 存储过程的逻辑代码
    END;
```
  DEFINER：可选项，创建存储过程的用户。可以指定具体的用户名，或使用CURRENT_USER表示当前用户。
  procedure_name：指定存储过程的名称，遵循标识符命名规则。
  parameter_list：可选项，用于指定存储过程的参数列表。参数由参数类型和参数名称组成，多个参数之间使用逗号分隔。参数类型可以是IN、OUT 或 INOUT，分别表示输入参数、输出参数和既是输入参数又是输出参数(赋值)。
  characteristic：可选项，用于指定存储过程的特性。常用特性包括：
  - LANGUAGE SQL：表示存储过程使用 SQL 语言编写。
  - [NOT] DETERMINISTIC：指示存储过程是否是确定性的，即相同输入是否总是产生相同的结果。
  - CONTAINS SQL：表示存储过程包含 SQL 语句。
  - NO SQL：表示存储过程不包含 SQL 语句。
  - READS SQL DATA：表示存储过程只读取数据，不修改数据。
  - MODIFIES SQL DATA：表示存储过程修改数据。
    BEGIN和END：定义存储过程的逻辑代码块的开始和结束位置。
    DECLARE：用于声明变量。在存储过程的逻辑代码块中，可以使用DECLARE语句来声明变量，指定变量名称和数据类型。
  调用：相当于Java中调用方法
```
CALL procedure_name([argument_list]);
```
  CALL：关键字用于调用存储过程。
  procedure_name：要调用的存储过程的名称。
  argument_list：可选项，用于传递给存储过程的参数列表。参数与存储过程定义时指定的参数列表对应，多个参数之间使用逗号分隔。如果存储过程没有参数，可以省略该部分。
  
  🌟存储过程实战
  
  以下例子，均按下表进行操作
  SQL语句：
```
--创建表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    age INT,
    salary DECIMAL(10, 2)
);
--表中插入数据
INSERT INTO employees (id, name, age, salary)
VALUES
    (1, '张三', 25, 5000),
    (2, '李四', 30, 6000),
    (3, '王五', 35, 7000),
    (4, '赵六', 40, 8000),
    (5, '钱七', 45, 9000);
```
  无参数列表存储过程
  
  此存储过程名为getAllEmployees。它将从employees表中检索所有的员工记录并返回结果集。
```
CREATE PROCEDURE getAllEmployees()
BEGIN
    SELECT * FROM employees;
END;
```
  调用存储过程进行检验
```
CALL getAllEmployees();
```
  检验结果：
  
  带参数列表存储过程
  
  此存储过程名为getEmployeeById，它接受一个输入参数empId，通过这个参数来查询与给定id相匹配的员工记录。
```
CREATE PROCEDURE getEmployeeById(IN empId INT)
BEGIN
    SELECT * FROM employees WHERE id = empId;
END;
```
  调用存储过程进行检验
```
CALL getEmployeeById(2);
```
  检验结果
  
  带流程控制语句IF ELSE存储过程
  
  此存储过程名为getEmployeeSalaryGrade，它接受一个输入参数empId，根据员工的薪水确定员工的薪资等级。根据不同的薪水范围，将员工的薪资等级分为"低级"、“中级"和"高级”。
```
CREATE PROCEDURE getEmployeeSalaryGrade(IN empId INT)
BEGIN
    DECLARE empSalary DECIMAL(10, 2);
    DECLARE empGrade VARCHAR(10);
    SELECT salary INTO empSalary FROM employees WHERE id = empId;
    IF empSalary < 5000 THEN
        SET empGrade = '低级';
    ELSEIF empSalary >= 5000 AND empSalary < 8000 THEN
        SET empGrade = '中级';
    ELSE
        SET empGrade = '高级';
    END IF;
    SELECT empGrade AS grade;
END;
```
  调用存储过程进行检验
```
call getEmployeeSalaryGrade(3)
```
  检验结果
  
  带条件控制语句CASE存储过程
  
  此存储过程名为categorizeEmployee，它根据员工的姓名确定员工的职位。
```
CREATE PROCEDURE categorizeEmployee()
BEGIN
    SELECT id, name,
        CASE
            WHEN name = '张三' THEN '管理人员'
            WHEN name = '李四' THEN '管理人员'
            ELSE '普通员工'
        END AS category
    FROM employees;
END;
```
  调用存储过程进行检验
```
call categorizeEmployee()
```
  检验结果
  
  带循环语句WHILE存储过程
  
  此存储过程名为 IncreaseSalary，它会遍历 employees 表中的每个员工，并将他们的薪水增加 10%。
```
CREATE PROCEDURE IncreaseSalary()
BEGIN
    DECLARE emp_count INT;
    DECLARE i INT DEFAULT 0;
    DECLARE emp_id INT;
    DECLARE emp_salary DECIMAL(10, 2);
    -- 获取员工数量
    SELECT COUNT(*) INTO emp_count FROM employees;
    -- 循环遍历并更新薪水
    WHILE i < emp_count DO
        SET i = i + 1;
        -- 获取当前员工的id和薪水
        SELECT id, salary INTO emp_id, emp_salary FROM employees WHERE id = i;
        -- 对薪水进行增加
        UPDATE employees SET salary = emp_salary * 1.1 WHERE id = emp_id;
    END WHILE;
    
END;
```
  调用存储过程进行检验
```
CALL IncreaseSalary();
```
  检验结果
  原来薪水
  当前薪水
  
  带循环语句REPEAT UNTIL存储过程
  
  此存储过程名为 DisplayEmployeesWithRepeatUntil，它会使用 REPEAT…UNTIL 循环遍历 employees 表中的每个员工，并输出他们的信息。
```
DELIMITER $$
CREATE PROCEDURE DisplayEmployeesWithRepeatUntil()
BEGIN
--声明变量
    DECLARE emp_count INT;--员工数量
    DECLARE i INT DEFAULT 1;
    DECLARE emp_id INT;
    DECLARE emp_name VARCHAR(50);
    DECLARE emp_age INT;
    DECLARE emp_salary DECIMAL(10, 2);
    -- 获取员工数量
    SELECT COUNT(*) INTO emp_count FROM employees;
    -- 循环遍历并输出员工信息
    REPEAT
        -- 获取当前员工信息
        SELECT id, name, age, salary INTO emp_id, emp_name, emp_age, emp_salary FROM employees WHERE id = i;
        -- 输出员工信息
        SELECT CONCAT('ID: ', emp_id, ', Name: ', emp_name, ', Age: ', emp_age, ', Salary: ', emp_salary) AS employee_info;
        SET i = i + 1;
    UNTIL i > emp_count END REPEAT;
END$$
DELIMITER ;
```
  调用存储过程进行检验
```
CALL DisplayEmployeesWithRepeatUntil();
```
  检验结果
  
  带循环语句LOOP存储过程
  
  此存储过程名为 DisplayEmployeesWithLoop，它会使用LOOP循环遍历 employees 表中的每个员工，并输出他们的信息。
```
CREATE PROCEDURE DisplayEmployeesWithLoop()
BEGIN
    DECLARE emp_count INT;
    DECLARE i INT DEFAULT 1;
    DECLARE emp_id INT;
    DECLARE emp_name VARCHAR(50);
    DECLARE emp_age INT;
    DECLARE emp_salary DECIMAL(10, 2);
    -- 获取员工数量
    SELECT COUNT(*) INTO emp_count FROM employees;
    -- 循环遍历并输出员工信息
    emp_loop: LOOP
        -- 获取当前员工信息
        SELECT id, name, age, salary INTO emp_id, emp_name, emp_age, emp_salary FROM employees WHERE id = i;
        -- 输出员工信息
        SELECT CONCAT('ID: ', emp_id, ', Name: ', emp_name, ', Age: ', emp_age, ', Salary: ', emp_salary) AS employee_info;
        SET i = i + 1;
        IF i > emp_count THEN
            LEAVE emp_loop;
        END IF;
    END LOOP;
END;
```
  调用存储过程检验
```
CALL DisplayEmployeesWithLoop();
```
  检验结果
  
  查询存储过程
```
--查询所有数据库
SHOW PROCEDURE STATUS;
--查询指定数据库test。以上所有例子都基于test数据库
SHOW PROCEDURE STATUS WHERE db='test'
```
  结果
  
  查询存储过程定义源码
```
SHOW CREATE PROCEDURE 存储过程名;
SHOW CREATE PROCEDURE categorizeEmployee;
```
  结果
  
  删除存储过程
```
DROP PROCEDURE 存储过程名;
```
  🌟写在最后
  
  最后感谢大家对于此博文的阅读，如对存储过程有补充或者疑问之处，请大家在评论区留言。感谢大家的指正与点评。