🧑💻作者名称:DaenCode
🎤作者简介:啥技术都喜欢捣鼓捣鼓,喜欢分享技术、经验、生活。
😎人生感悟:尝尽人生百味,方知世间冷暖。
📖所属专栏:重温MySQL
文章目录
🌟前言
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 ...]BEGINDECLARE 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语句:
--创建表CREATETABLE employees (
id INTPRIMARYKEY,
name VARCHAR(255),
age INT,
salary DECIMAL(10,2));--表中插入数据INSERTINTO employees (id, name, age, salary)VALUES(1,'张三',25,5000),(2,'李四',30,6000),(3,'王五',35,7000),(4,'赵六',40,8000),(5,'钱七',45,9000);
无参数列表存储过程
此存储过程名为getAllEmployees。它将从employees表中检索所有的员工记录并返回结果集。
CREATEPROCEDURE getAllEmployees()BEGINSELECT*FROM employees;END;
调用存储过程进行检验
CALL getAllEmployees();
检验结果:
带参数列表存储过程
此存储过程名为getEmployeeById,它接受一个输入参数empId,通过这个参数来查询与给定id相匹配的员工记录。
CREATEPROCEDURE getEmployeeById(IN empId INT)BEGINSELECT*FROM employees WHERE id = empId;END;
调用存储过程进行检验
CALL getEmployeeById(2);
检验结果
带流程控制语句IF ELSE存储过程
此存储过程名为getEmployeeSalaryGrade,它接受一个输入参数empId,根据员工的薪水确定员工的薪资等级。根据不同的薪水范围,将员工的薪资等级分为"低级"、“中级"和"高级”。
CREATEPROCEDURE getEmployeeSalaryGrade(IN empId INT)BEGINDECLARE empSalary DECIMAL(10,2);DECLARE empGrade VARCHAR(10);SELECT salary INTO empSalary FROM employees WHERE id = empId;IF empSalary <5000THENSET empGrade ='低级';ELSEIF empSalary >=5000AND empSalary <8000THENSET empGrade ='中级';ELSESET empGrade ='高级';ENDIF;SELECT empGrade AS grade;END;
调用存储过程进行检验
call getEmployeeSalaryGrade(3)
检验结果
带条件控制语句CASE存储过程
此存储过程名为categorizeEmployee,它根据员工的姓名确定员工的职位。
CREATEPROCEDURE categorizeEmployee()BEGINSELECT id, name,CASEWHEN name ='张三'THEN'管理人员'WHEN name ='李四'THEN'管理人员'ELSE'普通员工'ENDAS category
FROM employees;END;
调用存储过程进行检验
call categorizeEmployee()
检验结果
带循环语句WHILE存储过程
此存储过程名为 IncreaseSalary,它会遍历 employees 表中的每个员工,并将他们的薪水增加 10%。
CREATEPROCEDURE IncreaseSalary()BEGINDECLARE emp_count INT;DECLARE i INTDEFAULT0;DECLARE emp_id INT;DECLARE emp_salary DECIMAL(10,2);-- 获取员工数量SELECTCOUNT(*)INTO emp_count FROM employees;-- 循环遍历并更新薪水WHILE i < emp_count DOSET i = i +1;-- 获取当前员工的id和薪水SELECT id, salary INTO emp_id, emp_salary FROM employees WHERE id = i;-- 对薪水进行增加UPDATE employees SET salary = emp_salary *1.1WHERE id = emp_id;ENDWHILE;END;
调用存储过程进行检验
CALL IncreaseSalary();
检验结果
原来薪水
当前薪水
带循环语句REPEAT UNTIL存储过程
此存储过程名为 DisplayEmployeesWithRepeatUntil,它会使用 REPEAT…UNTIL 循环遍历 employees 表中的每个员工,并输出他们的信息。
DELIMITER $$
CREATEPROCEDURE DisplayEmployeesWithRepeatUntil()BEGIN--声明变量DECLARE emp_count INT;--员工数量DECLARE i INTDEFAULT1;DECLARE emp_id INT;DECLARE emp_name VARCHAR(50);DECLARE emp_age INT;DECLARE emp_salary DECIMAL(10,2);-- 获取员工数量SELECTCOUNT(*)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 ENDREPEAT;END$$
DELIMITER;
调用存储过程进行检验
CALL DisplayEmployeesWithRepeatUntil();
检验结果
带循环语句LOOP存储过程
此存储过程名为 DisplayEmployeesWithLoop,它会使用LOOP循环遍历 employees 表中的每个员工,并输出他们的信息。
CREATEPROCEDURE DisplayEmployeesWithLoop()BEGINDECLARE emp_count INT;DECLARE i INTDEFAULT1;DECLARE emp_id INT;DECLARE emp_name VARCHAR(50);DECLARE emp_age INT;DECLARE emp_salary DECIMAL(10,2);-- 获取员工数量SELECTCOUNT(*)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 THENLEAVE emp_loop;ENDIF;ENDLOOP;END;
调用存储过程检验
CALL DisplayEmployeesWithLoop();
检验结果
查询存储过程
--查询所有数据库SHOWPROCEDURESTATUS;--查询指定数据库test。以上所有例子都基于test数据库SHOWPROCEDURESTATUSWHERE db='test'
结果
查询存储过程定义源码
SHOWCREATEPROCEDURE 存储过程名;SHOWCREATEPROCEDURE categorizeEmployee;
结果
删除存储过程
DROPPROCEDURE 存储过程名;
🌟写在最后
最后感谢大家对于此博文的阅读,如对存储过程有补充或者疑问之处,请大家在评论区留言。感谢大家的指正与点评。
版权归原作者 DaenCode 所有, 如有侵权,请联系我们删除。