【MySQL】探索MySQL存储过程的魔力,初学者的数据库编程秘笈(内含实战SQL脚本)
作者:mmseoamin日期:2023-12-21

🧑‍💻作者名称:DaenCode

🎤作者简介:啥技术都喜欢捣鼓捣鼓,喜欢分享技术、经验、生活。

😎人生感悟:尝尽人生百味,方知世间冷暖。

📖所属专栏:重温MySQL


【MySQL】探索MySQL存储过程的魔力,初学者的数据库编程秘笈(内含实战SQL脚本),在这里插入图片描述,第1张


文章目录

  • 🌟前言
  • 🌟存储过程介绍
  • 🌟存储过程基本语法
  • 🌟存储过程实战
    • 无参数列表存储过程
    • 带参数列表存储过程
    • 带流程控制语句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:可选项,用于传递给存储过程的参数列表。参数与存储过程定义时指定的参数列表对应,多个参数之间使用逗号分隔。如果存储过程没有参数,可以省略该部分。

      🌟存储过程实战

      以下例子,均按下表进行操作

      【MySQL】探索MySQL存储过程的魔力,初学者的数据库编程秘笈(内含实战SQL脚本),在这里插入图片描述,第2张

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

      检验结果:

      【MySQL】探索MySQL存储过程的魔力,初学者的数据库编程秘笈(内含实战SQL脚本),在这里插入图片描述,第3张

      带参数列表存储过程

      此存储过程名为getEmployeeById,它接受一个输入参数empId,通过这个参数来查询与给定id相匹配的员工记录。

      CREATE PROCEDURE getEmployeeById(IN empId INT)
      BEGIN
          SELECT * FROM employees WHERE id = empId;
      END;
      

      调用存储过程进行检验

      CALL getEmployeeById(2);
      

      检验结果

      【MySQL】探索MySQL存储过程的魔力,初学者的数据库编程秘笈(内含实战SQL脚本),在这里插入图片描述,第4张

      带流程控制语句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)
      

      检验结果

      【MySQL】探索MySQL存储过程的魔力,初学者的数据库编程秘笈(内含实战SQL脚本),在这里插入图片描述,第5张

      带条件控制语句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()
      

      检验结果

      【MySQL】探索MySQL存储过程的魔力,初学者的数据库编程秘笈(内含实战SQL脚本),在这里插入图片描述,第6张

      带循环语句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();
      

      检验结果

      原来薪水

      【MySQL】探索MySQL存储过程的魔力,初学者的数据库编程秘笈(内含实战SQL脚本),在这里插入图片描述,第7张

      当前薪水

      【MySQL】探索MySQL存储过程的魔力,初学者的数据库编程秘笈(内含实战SQL脚本),在这里插入图片描述,第8张

      带循环语句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();
      

      检验结果

      【MySQL】探索MySQL存储过程的魔力,初学者的数据库编程秘笈(内含实战SQL脚本),在这里插入图片描述,第9张

      带循环语句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();
      

      检验结果

      【MySQL】探索MySQL存储过程的魔力,初学者的数据库编程秘笈(内含实战SQL脚本),在这里插入图片描述,第10张

      查询存储过程

      --查询所有数据库
      SHOW PROCEDURE STATUS;
      --查询指定数据库test。以上所有例子都基于test数据库
      SHOW PROCEDURE STATUS WHERE db='test'
      

      结果

      【MySQL】探索MySQL存储过程的魔力,初学者的数据库编程秘笈(内含实战SQL脚本),在这里插入图片描述,第11张

      查询存储过程定义源码

      SHOW CREATE PROCEDURE 存储过程名;
      SHOW CREATE PROCEDURE categorizeEmployee;
      

      结果

      【MySQL】探索MySQL存储过程的魔力,初学者的数据库编程秘笈(内含实战SQL脚本),在这里插入图片描述,第12张

      删除存储过程

      DROP PROCEDURE 存储过程名;
      

      🌟写在最后

      最后感谢大家对于此博文的阅读,如对存储过程有补充或者疑问之处,请大家在评论区留言。感谢大家的指正与点评。


      【MySQL】探索MySQL存储过程的魔力,初学者的数据库编程秘笈(内含实战SQL脚本),请添加图片描述,第13张