mysql存储过程与存储函数实例分析

共计 6050 个字符，预计需要花费 16 分钟才能阅读完成。

这篇文章主要介绍了 mysql 存储过程与存储函数实例分析的相关知识，内容详细易懂，操作简单快捷，具有一定借鉴价值，相信大家阅读完这篇 mysql 存储过程与存储函数实例分析文章都会有所收获，下面我们一起来看看吧。

存储过程

简介

 是一组经过   预先编译   的  SQL  语句的封装
存储过程预先存储在  MySQL  服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列  SQL  语句全部执行 

分类

1、没有参数（无参数无返回） 
2、仅仅带  IN  类型（有参数无返回） 
3、仅仅带  OUT  类型（无参数有返回） 
4、既带  IN  又带  OUT（有参数有返回） 
5、带  INOUT（有参数有返回）

格式

DELIMITER $
CREATE PROCEDURE  存储过程名 (IN|OUT|INOUT  参数名   参数类型,...)
[characteristics ...]
BEGIN
 sql 语句 1;
 sql 语句 2;
END $
IN ：当前参数为输入参数，也就是表示入参；存储过程只是读取这个参数的值。如果没有定义参数种类，  默认就是  IN ，表示输入参数
OUT ：当前参数为输出参数，也就是表示出参；执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了
INOUT ：当前参数既可以为输入参数，也可以为输出参数
形参类型可以是  MySQL 数据库中的任意类型
characteristics  表示创建存储过程时指定的对存储过程的约束条件
1. BEGIN…END：BEGIN…END  中间包含了多个语句，每个语句都以（;）号为结束符
2. DECLARE：DECLARE  用来声明变量，使用的位置在于  BEGIN…END  语句中间，而且需要在其他语句使用之前进行变量的声明
3. SET：赋值语句，用于对变量进行赋值
4. SELECT… INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值
存储过程体中可以有多条  SQL  语句，如果仅仅一条 SQL  语句，则可以省略  BEGIN  和  END
DELIMITER  新的结束标记
DELIMITER //”语句的作用是将 MySQL 的结束符设置为 //，并以“END //”结束存储过程。存储过程定义完毕之后再使用“DELIMITER ;”恢复默认结束符
当使用 DELIMITER 命令时，应该避免使用反斜杠（‘\’）字符，因为反斜线是 MySQL 的转义字符 

代码案例

#  类型 1：无参数无返回值
#  举例 1：创建存储过程 select_all_data()，查看  employees  表的所有数据
DELIMITER $ #  开始
CREATE PROCEDURE select_all_data()
BEGIN
 SELECT * FROM employees;
END $
DELIMITER; #  结束
#  存储过程的调用
CALL select_all_data();
#  举例 2：创建存储过程 avg_employee_salary()，返回所有员工的平均工资
DELIMITER //
CREATE PROCEDURE avg_employee_salary()
BEGIN 
 SELECT AVG(salary) FROM employees;
END //
DELIMITER ;
#  调用
CALL avg_employee_salary();
#  举例 3：创建存储过程 show_max_salary()，用来查看“emps”表的最高薪资值
DELIMITER //
CREATE PROCEDURE show_max_salary()
BEGIN
 SELECT MAX(salary)
 FROM employees;
END //
DELIMITER ;
#  调用
CALL show_max_salary();
#  类型 2：带  OUT
#  举例 4：创建存储过程 show_min_salary()，查看“emps”表的最低薪资值。并将最低薪资通过 OUT 参数“ms”输出
DELIMITER //
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE) #  输出 ms，类型是 DOUBLE
BEGIN
 SELECT MIN(salary) INTO ms #  将 min 赋值给 ms
 FROM employees;
END //
DELIMITER 
#  调用
CALL show_min_salary(@ms);
#  查看变量值
SELECT @ms;
#  类型 3：带  IN
#  举例 5：创建存储过程 show_someone_salary()，查看“emps”表的某个员工的薪资，并用 IN 参数 empname 输入员工姓名
DELIMITER //
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20)) #  传入 empname，类型为 VARCHAR
BEGIN
 SELECT salary FROM employees
 WHERE last_name = empname; #  使用 empname
END //
DELIMITER;
#  调用方式 1
CALL show_someone_salary( Abel 
#  调用方式 2
SET @empname :=  Abel 
CALL show_someone_salary(@empname);
#  类型 4：带  IN  和  OUT
#  举例 6：创建存储过程 show_someone_salary2()，查看“emps”表的某个员工的薪资，并用 IN 参数 empname 输入员工姓名，用 OUT 参数 empsalary 输出员工薪资
DELIMITER //
CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20), OUT empsalary DECIMAL(10,2))
BEGIN
 SELECT salary INTO empsalary #  赋值
 FROM employees
 WHERE last_name = empname; #  使用参数
END //
DELIMITER;
#  调用
SET @empname =  Abel 
CALL show_someone_salary2(@empname, @empsalary);
#  查看
SELECT @empname
SELECT @empsalary;
#  类型 5：带  INOUT
#  举例 7：创建存储过程 show_mgr_name()，查询某个员工领导的姓名，并用 INOUT 参数“empname”输入员工姓名，输出领导的姓名
DELIMITER $
CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(25))
BEGIN
 SELECT last_name INTO empname
 FROM employees
 WHERE employee_id = (
 SELECT manager_id
 FROM employees
 WHERE last_name = empname
 );
END $
DELIMITER ;
SET @empname :=  Abel 
CALL show_mgr_name(@empname);
#  查看
SELECT @empname;

存储函数

格式

CREATE FUNCTION  函数名 (参数名   参数类型,...)
RETURNS  返回值类型
[characteristics ...]
BEGIN
函数体  # 函数体中肯定有  RETURN  语句
1、RETURNS type  语句表示函数返回数据的类型
2、RETURNS 子句只能对 FUNCTION 做指定，对函数而言这是   强制   的。它用来指定函数的返回类型，而且函数体必须包含一个  RETURN value  语句
3、characteristic  创建函数时指定的对函数的约束。取值与创建存储过程时相同
4、函数体也可以用 BEGIN…END 来表示 SQL 代码的开始和结束。如果函数体只有一条语句，也可以省略 BEGIN…END
5、调用存储函数
 SELECT  函数名 (实参列表)

characteristics

LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT  string
1、LANGUAGE SQL ：说明存储过程执行体是由 SQL 语句组成的，当前系统支持的语言为 SQL
2、[NOT] DETERMINISTIC ：指明存储过程执行的结果是否确定。DETERMINISTIC 表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC 表示结果是不确定
的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为 NOT DETERMINISTIC
3、{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } ：指明子程序使用 SQL 语句的限制
 CONTAINS SQL 表示当前存储过程的子程序包含 SQL 语句，但是并不包含读写数据的 SQL 语句
 NO SQL 表示当前存储过程的子程序中不包含任何 SQL 语句
 READS SQL DATA 表示当前存储过程的子程序中包含读数据的 SQL 语句
 MODIFIES SQL DATA 表示当前存储过程的子程序中包含写数据的 SQL 语句
  默认情况下，系统会指定为 CONTAINS SQL
4、SQL SECURITY { DEFINER | INVOKER } ：执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程
 DEFINER  表示只有当前存储过程的创建者或者定义者才能执行当前存储过程； INVOKER  表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。  如果没有设置相关的值，则 MySQL 默认指定值为 DEFINER
5、COMMENT  string  ：注释信息，可以用来描述存储过程 

代码案例

#  举例 1：创建存储函数，名称为 email_by_name()，参数定义为空，该函数查询 Abel 的 email，并返回，数据类型为字符串型
DELIMITER // #  开始
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25) #  返回值
 DETERMINISTIC #  表示结果确定
 CONTAINS SQL #  表示包含 sql 语句
 READS SQL DATA #  表示包含读数据的 sql
BEGIN
 RETURN (SELECT email FROM employees WHERE last_name =  Abel 
END //
DELIMITER; #  结束
#  调用
SELECT email_by_name();
#  举例 2：创建存储函数，名称为 email_by_id()，参数传入 emp_id，该函数查询 emp_id 的 email，并返回，数据类型为字符串型
#  创建函数前执行此语句，保证函数的创建会成功；则不需要写 characteristics
SET GLOBAL log_bin_trust_function_creators = 1;
#  声明函数
DELIMITER // #  开始
CREATE FUNCTION email_by_id(emp_id INT) #  传入参数
RETURNS VARCHAR(25) #  返回值
BEGIN
 RETURN (SELECT email FROM employees WHERE employee_id = emp_id); #  使用 emp_id
END //
DELIMITER;
#  调用
SELECT email_by_id(101);
#  调用方式 2
SET @emp_id := 102;
SELECT email_by_id(@emp_id);
#  举例 3：创建存储函数 count_by_id()，参数传入 dept_id，该函数查询 dept_id 部门的员工人数，并返回，数据类型为整型。DELIMITER //
CREATE FUNCTION count_by_id(dept_id INT)
RETURNS INT
BEGIN
 RETURN (SELECT COUNT(*) FROM employees WHERE department_id = dept_id);
END //
DELIMITER ;
#  调用
SET @dept_id := 50;
SELECT count_by_id(@dept_id);

存储过程、存储函数的查看

#  方式 1：使用 SHOW CREATE 语句查看存储过程和函数的创建信息
#  查看存储过程
SHOW CREATE PROCEDURE show_mgr_name;
#  查看存储函数
SHOW CREATE FUNCTION count_by_id;
#  方式 2：使用 SHOW STATUS 语句查看存储过程和函数的状态信息
#  查看存储过程
SHOW PROCEDURE STATUS;
#  查看指定存储过程
SHOW PROCEDURE STATUS LIKE  show_max_salary 
#  查看指定存储函数
SHOW FUNCTION STATUS LIKE  email_by_id 
#  方式 3：从 information_schema.Routines 表中查看存储过程和函数的信息
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME= email_by_id  AND ROUTINE_TYPE =  FUNCTION 
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME= show_min_salary  AND ROUTINE_TYPE =  PROCEDURE

存储过程、函数的修改

ALTER PROCEDURE show_max_salary
SQL SECURITY INVOKER
COMMENT  查询最高工资 

存储过程、函数的删除

DROP FUNCTION IF EXISTS count_by_id;
DROP PROCEDURE IF EXISTS show_min_salary;

关于“mysql 存储过程与存储函数实例分析”这篇文章的内容就介绍到这里，感谢各位的阅读！相信大家对“mysql 存储过程与存储函数实例分析”知识都有一定的了解，大家如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道。