13. 存储过程与存储函数

2023-10-04 14:22:58

一、存储过程

1.1、存储过程概述

存储过程的英文是 Stored Procedure。它的思想很简单，就是一组经过预先编译的 SQL 语句的封装。
存储过程预先存储在 MySQL 服务器上，需要执行的时候，客户端只需要向服务器端发出调用存储过程的命令，服务器端就可以把预先存储好的这一系列 SQL 语句全部执行。
好处：
- 简化操作，提高了sql语句的重用性，减少了开发程序员的压力
- 减少操作过程中的失误，提高效率
- 减少网络传输量（客户端不需要把所有的 SQL 语句通过网络发给服务器）
- 减少了 SQL 语句暴露在网上的风险，也提高了数据查询的安全性
和视图、函数的对比：
- 它和视图有着同样的优点，清晰、安全，还可以减少网络传输量。
- 不过它和视图不同，视图是虚拟表，通常不对底层数据表直接操作，而存储过程是程序化的 SQL，可以直接操作底层数据表，相比于面向集合的操作方式，能够实现一些更复杂的数据处理。
一旦存储过程被创建出来，使用它就像使用函数一样简单，我们直接通过调用存储过程名即可。相较于函数，存储过程是没有返回值的。

1.2、分类

存储过程的参数类型可以是IN、OUT和INOUT。根据这点分类如下：

没有参数（无参数无返回）
仅仅带 IN 类型（有参数无返回）
仅仅带 OUT 类型（无参数有返回）
既带 IN 又带 OUT（有参数有返回）
带 INOUT（有参数有返回）

注意：IN、OUT、INOUT 都可以在一个存储过程中带多个。

1.3、创建存储过程

DELIMITER 新的结束标记

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...)
[characteristics ...]
BEGIN
	存储过程体
END 新的结束标记

DELIMITER ;

LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

参数前面的符号的意思
- IN：当前参数为输入参数，也就是表示入参；
  - 存储过程只是读取这个参数的值。如果没有定义参数种类，默认就是 IN，表示输入参数。
- OUT：当前参数为输出参数，也就是表示出参；
  - 执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。
- INOUT：当前参数既可以为输入参数，也可以为输出参数。
characteristics 表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下：
- LANGUAGE SQL：说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。
- [NOT] DETERMINISTIC：指明存储过程执行的结果是否确定。
  - DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。
  - NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。
  - 如果没有指定任意一个值，默认为NOT DETERMINISTIC。
- { 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。
- SQL SECURITY { DEFINER | INVOKER }：执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程。
  - DEFINER表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；
  - INVOKER表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。
  - 如果没有设置相关的值，则MySQL默认指定值为DEFINER。
- COMMENT 'string'：注释信息，可以用来描述存储过程。
存储过程体中间包含了多条 SQL 语句，每个语句都以（;）号为结束符。
- 如果仅仅一条SQL 语句，则可以省略 BEGIN 和 END
DECLARE：用来声明变量，使用的位置在于 BEGIN…END 语句中间，而且需要在其他语句使用之前进行变量的声明。
SET：赋值语句，用于对变量进行赋值。
SELECT… INTO：把从数据表中查询的结果存放到变量中，也就是为变量赋值。
需要设置新的结束标记：DELIMITER 新的结束标记
- 因为MySQL默认的语句结束符号为分号‘;’。为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符。
- 存储过程定义完毕之后再使用“DELIMITER ;”恢复默认结束符。
- 当使用DELIMITER命令时，应该避免使用反斜杠（‘\’）字符，因为反斜线是MySQL的转义字符。

example:

DROP DATABASE IF EXISTS template;
CREATE DATABASE IF NOT EXISTS template;
USE template;

CREATE TABLE IN NOT EXISTS employees
AS 
SELECT * FROM atguigudb.employees;

CREATE TABLE IF NOT EXISTS departments
AS
SELECT * FROM atguigudb.departments;

-- 创建存储过程：无参数
DELIMITER $

CREATE PROCEDURE select_all_data()
BEGIN
	SELECT * FROM employees;
END $

DELIMITER ;

-- 创建存储过程：带OUT
DELIMITER //

CREATE PROCEDURE show_min_salary(OUT min_salary DOUBLE)
BEGIN 
	SELECT MIN(salary) INTO min_salary 
    FROM employees;
END //

DELIMITER ;

-- 创建存储过程：带IN
DELIMITER $

CREATE PROCEDURE show_someone_salary(IN employee_name VARCHAR(25))
BEGIN
	SELECT salary 
    FROM employees 
    WHERE last_name = employee_name;
END $

DELIMITER ;

-- 创建存储过程：带IN和OUT
DELIMITER //

CREATE PROCEDURE show_somebody_salary(IN employee_name VARCHAR(25),OUT employee_salary DOUBLE)
BEGIN 
	SELECT salary INTO employee_salary 
	FROM employees 
	WHERE last_name = employee_name;
END //

DELIMITER ;

-- 创建存储过程：带INOUT
DELIMITER $

CREATE PROCEDURE show_manager_name(INOUT employee_name VARCHAR(25))
BEGIN
	SELECT last_name INTO employee_name
	FROM employees
	WHERE employee_id = (
		SELECT manager_id 
		FROM employees 
		WHERE last_name = employee_name
	);
END $

DELIMITER ;

1.4、调用存储过程

存储过程有多种调用方法。存储过程必须使用CALL语句调用，并且存储过程和数据库相关，如果要执行其他数据库中的存储过程，需要指定数据库名称。

CALL 存储过程名(实参列表)

-- 1. 调用in模式的参数：
CALL 存储过程名('值');

-- 2. 调用out模式的参数：
SET @name;
CALL 存储过程名(@name);
SELECT @name;

-- 3. 调用inout模式的参数：
SET @name=值;
CALL 存储过程名(@name);
SELECT @name;

example:

-- 调用无参数的存储过程
call select_all_date();

-- 调用OUT模式的存储过程
CALL show_min_salary(@min_salary);
SELECT @min_salary;

-- 调用IN模式的存储过程
-- 调用方式1
CALL show_someone_salary('Abel');
-- 调用方式2
SET @employee_name := 'Abel';
CALL show_someone_salary(@employee_name);

-- 调用IN和OUT模式的存储过程
SET @emp_name = 'ABel';
CALL show_somebody_salary(@emp_name,@emp_salary);
SELECT @emp_salary;

-- 调用INOUT模式的存储过程
SET @emp_name = 'ABel';
CALL show_manager_name(@emp_name);
SELECT @emp_name;

1.5、存储过程的查看

MySQL存储了存储过程和函数的状态信息，用户可以使用SHOW STATUS语句或SHOW CREATE语句来查看，也可直接从系统的information_schema数据库中查询。这里介绍3种方法。

-- 1.使用SHOW CREATE语句查看存储过程的创建信息
SHOW CREATE PROCEDURE 存储过程名;
-- 2. 使用SHOW STATUS语句查看存储过程的状态信息
-- 这个语句返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。
-- [LIKE 'pattern']：匹配存储过程或函数的名称，可以省略。当省略不写时，会列出MySQL数据库中存在的所有存储过程的信息。
SHOW PROCEDURE STATUS [LIKE 'pattern'];
-- 3. 从information_schema.Routines表中查看存储过程的信息
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='存储过程名' [AND ROUTINE_TYPE = 'PROCEDURE'];

example:

-- 1.使用SHOW CREATE语句查看存储过程的创建信息
SHOW CREATE PROCEDURE show_manager_name;
-- 2. 使用SHOW STATUS语句查看存储过程的状态信息
SHOW PROCEDURE STATUS LIKE 'show_min_salary';
-- 3. 从information_schema.Routines表中查看存储过程的信息
-- 如果在MySQL数据库中存在存储过程和函数名称相同的情况，最好指定ROUTINE_TYPE查询条件来指明查询的是存储过程还是函数。
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='show_some_salary' AND ROUTINE_TYPE = 'PROCEDURE';

1.6、存储过程的修改

-- 修改存储过程或函数，不影响存储过程或函数功能，只是修改相关特性
ALTER PROCEDURE 存储过程名 [characteristic ...];

其中，characteristic指定存储过程或函数的特性，其取值信息与创建存储过程、函数时的取值信息略有不同。

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

CONTAINS SQL，表示子程序包含SQL语句，但不包含读或写数据的语句。
NO SQL，表示子程序中不包含SQL语句。
READS SQL DATA，表示子程序中包含读数据的语句。
MODIFIES SQL DATA，表示子程序中包含写数据的语句。
SQL SECURITY { DEFINER | INVOKER }，指明谁有权限来执行。
- DEFINER，表示只有定义者自己才能够执行。
- INVOKER，表示调用者可以执行。
COMMENT 'string'，表示注释信息。

example:

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='show_min_salary';

ALTER PROCEDURE show_min_salary
SQL SECURITY INVOKER
COMMENT '查询最低工资';

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='show_min_salary';

1.7、存储过程的删除

DROP PROCEDURE [IF EXISTS] 存储过程的名;

example:

DROP PROCEDURE IF EXISTS select_all_data;

二、存储函数的使用

2.1、创建存储函数

DELIMITER 新的结束标记

CREATE FUNCTION 函数名(参数名 参数类型,...) 
RETURNS 返回值类型
[characteristics ...]
BEGIN
	函数体   
	RETURN 语句
END 新的结束标记

DELIMITER ;

参数列表：指定参数为IN、OUT或INOUT只对PROCEDURE是合法的，FUNCTION中总是默认为IN参数。
RETURNS type 语句表示函数返回数据的类型；
- RETURNS子句只能对FUNCTION做指定，对函数而言这是强制的。
- 它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。
characteristic 创建函数时指定的对函数的约束。
- LANGUAGE SQL：说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL。
- [NOT] DETERMINISTIC：指明存储过程执行的结果是否确定。
  - DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。
  - NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。
  - 如果没有指定任意一个值，默认为NOT DETERMINISTIC。
- { 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。
- SQL SECURITY { DEFINER | INVOKER }：执行当前存储过程的权限，即指明哪些用户能够执行当前存储过程。
  - DEFINER表示只有当前存储过程的创建者或者定义者才能执行当前存储过程；
  - INVOKER表示拥有当前存储过程的访问权限的用户能够执行当前存储过程。
  - 如果没有设置相关的值，则MySQL默认指定值为DEFINER。
- COMMENT 'string'：注释信息，可以用来描述存储过程。
函数体也可以用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句，也可以省略BEGIN…END。

example:

USE template;

DELIMITER $

CREATE FUNCTION email_by_name(employee_name VARCHAR(25))
RETURNS VARCHAR(25)
	DETERMINISTIC 
	CONTAINS SQL
	READS SQL DATA
BEGIN 
	RETURN (SELECT email FROM employees WHERE last_name = employee_name);
END $

DELIMITER ;

注意：若在创建存储函数中报错“you might want to use the less safe log_bin_trust_function_creators variable”，有两种处理方法：

方式1：加上必要的函数特性“[NOT] DETERMINISTIC”和“{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}”
方式2：SET GLOBAL log_bin_trust_function_creators = 1;

2.2、调用存储函数

SELECT 函数名(实参列表)

example:

SELECT email_by_name('Abel');

2.3、存储函数的查看

-- 1.使用SHOW CREATE语句查看存储函数的创建信息
SHOW CREATE FUNCTION 存储函数名;
-- 2. 使用SHOW STATUS语句查看存储函数的状态信息
-- 这个语句返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。
-- [LIKE 'pattern']：匹配存储过程或函数的名称，可以省略。当省略不写时，会列出MySQL数据库中存在的所有存储函数的信息。
SHOW FUNCTION STATUS [LIKE 'pattern']
-- 3. 从information_schema.Routines表中查看存储函数的信息
-- 如果在MySQL数据库中存在存储过程和函数名称相同的情况，最好指定ROUTINE_TYPE查询条件来指明查询的是存储过程还是函数。
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='存储函数的名' [AND ROUTINE_TYPE = 'FUNCTION'];

example:

-- 1.使用SHOW CREATE语句查看存储函数的创建信息
SHOW CREATE FUNCTION email_by_name;
-- 2. 使用SHOW STATUS语句查看存储过程和函数的状态信息
SHOW FUNCTION STATUS LIKE 'email_by_name';
-- 3. 从information_schema.Routines表中查看存储函数的信息
SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='email_by_name' AND ROUTINE_TYPE = 'FUNCTION';

2.4、存储函数的修改

-- 修改存储过程或函数，不影响存储过程或函数功能，只是修改相关特性
ALTER FUNCTION 存储函数的名 [characteristic ...];

其中，characteristic指定存储过程或函数的特性，其取值信息与创建存储过程、函数时的取值信息略有不同。

{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

CONTAINS SQL，表示子程序包含SQL语句，但不包含读或写数据的语句。
NO SQL，表示子程序中不包含SQL语句。
READS SQL DATA，表示子程序中包含读数据的语句。
MODIFIES SQL DATA，表示子程序中包含写数据的语句。
SQL SECURITY { DEFINER | INVOKER }，指明谁有权限来执行。
- DEFINER，表示只有定义者自己才能够执行。
- INVOKER，表示调用者可以执行。
COMMENT 'string'，表示注释信息。

example:

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='email_by_name';

ALTER FUNCTION email_by_name
SQL SECURITY INVOKER
COMMENT '某职员的邮箱';

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='email_by_name';

2.5、存储函数的删除

DROP FUNCTION [IF EXISTS] 存储函数的名;

example:

DROP FUNCTION IF EXISTS email_by_name;

三、对比存储函数和存储过程

	关键字	调用语法	返回值	应用场景
存储过程	PROCEDURE	CALL 存储过程()	理解为有0个或多个	一般用于更新
存储函数	FUNCTION	SELECT 函数()	只能是一个	一般用于查询结果为一个值并返回时

此外，存储函数可以放在查询语句中使用，存储过程不行。反之，存储过程的功能更加强大，包括能够执行对表的操作（比如创建表，删除表等）和事务操作，这些功能是存储函数不具备的。

四、练习题

-- 1.准备工作 
CREATE DATABASE test15_pro_func; 
USE test15_pro_func; 

-- 2. 创建存储过程insert_user(),实现传入用户名和密码，插入到admin表中 
CREATE TABLE admin( 
	id INT PRIMARY KEY AUTO_INCREMENT, 
	user_name VARCHAR(15) NOT NULL, 
	pwd VARCHAR(25) NOT NULL 
);

DELIMITER $

CREATE PROCEDURE insert_user(IN user_name VARCHAR(15),IN pwd VARCHAR(25))
BEGIN
	INSERT INTO admin(user_name,pwd) VALUES(user_name,pwd);
END $

DELIMITER ;

CALL insert_user('Sakura','sakura27185');
SELECT * FROM admin;

-- 3. 创建存储过程get_phone(),实现传入女神编号，返回女神姓名和女神电话 
CREATE TABLE beauty( 
	id INT PRIMARY KEY AUTO_INCREMENT, 
	NAME VARCHAR(15) NOT NULL, 
	phone VARCHAR(15) UNIQUE, 
	birth DATE 
);

INSERT INTO beauty(NAME,phone,birth) 
VALUES ('朱茵','13201233453','1982-02-12'), 
       ('孙燕姿','13501233653','1980-12-09'), 
       ('田馥甄','13651238755','1983-08-21'), 
       ('邓紫棋','17843283452','1991-11-12'), 
       ('刘若英','18635575464','1989-05-18'), 
       ('杨超越','13761238755','1994-05-11'); 
       
SELECT * FROM beauty; 

DELIMITER //

CREATE PROCEDURE get_phone(IN id INT,OUT NAME VARCHAR(15),OUT phone VARCHAR(15))
BEGIN
	SELECT b.name,b.phone INTO NAME,phone FROM beauty b WHERE b.id = id;
END //

DELIMITER ;

CALL get_phone(1,@name,@phone);
SELECT @name,@phone;

-- 4. 创建存储过程date_diff()，实现传入两个女神生日，返回日期间隔大小 
DELIMITER //

CREATE PROCEDURE date_diff(IN birth1 DATE,IN birth2 DATE,OUT sum_date INT)
BEGIN 
	SELECT DATEDIFF(birth1,birth2) INTO sum_date;
END //

DELIMITER ;

SET @birth1 = '1982-02-12';
SET @birth2 = '1980-12-09';
CALL date_diff(@birth1,@birth2,@sum_date);
SELECT @sum_date;

-- 5. 创建存储过程format_date(),实现传入一个日期，格式化成xx年xx月xx日并返回 
DELIMITER $

CREATE PROCEDURE format_date(IN my_date DATE,OUT str_date VARCHAR(25))
BEGIN 
	SELECT DATE_FORMAT(my_date,'%y年%m月%d日') INTO str_date;
END $

DELIMITER ;

CALL format_date(CURDATE(),@str);
SELECT @str;

-- 6. 创建存储过程beauty_limit()，根据传入的起始索引和条目数，查询女神表的记录 
DELIMITER $

CREATE PROCEDURE beauty_limit(IN start_index INT,IN size INT)
BEGIN 
	SELECT * FROM beauty LIMIT start_index,size;
END $

DELIMITER ;

CALL beauty_limit(1,3);

-- 7.  创建带inout模式参数的存储过程,传入a和b两个值，最终a和b都翻倍并返回 
DELIMITER //

CREATE PROCEDURE add_double(INOUT a INT,INOUT b INT)
BEGIN 
	SET a = a*2;
	SET b = b*2;
END //

DELIMITER ;

SET @a = 3;
SET @b = 5;
CALL add_double(@a,@b);
SELECT @a,@b;

-- 8. 删除题目5的存储过程 
DROP PROCEDURE IF EXISTS beauty_limit;

-- 9. 查看题目6中存储过程的信息
SHOW CREATE PROCEDURE add_double;
SHOW PROCEDURE STATUS LIKE 'add_double';

-- 10. 准备工作 
USE test15_pro_func; 
CREATE TABLE employees AS SELECT * FROM atguigudb.employees; 
CREATE TABLE departments AS SELECT * FROM atguigudb.departments; 

-- 11. 无参有返回 ,创建函数get_count(),返回公司的员工个数 
SET GLOBAL log_bin_trust_function_creators = 1;

DELIMITER $

CREATE FUNCTION get_count()
RETURNS INT
BEGIN
	RETURN (SELECT COUNT(*) FROM employees);
END $

DELIMITER ;

SELECT get_count();

-- 12. 有参有返回 ,创建函数ename_salary(),根据员工姓名，返回它的工资 
DELIMITER $

CREATE FUNCTION ename_salary(emp_name VARCHAR(25))
RETURNS DOUBLE
BEGIN 
	RETURN (SELECT salary FROM employees WHERE last_name = emp_name);
END $

DELIMITER ;

SELECT ename_salary('Abel');

-- 13. 创建函数dept_sal() ,根据部门名，返回该部门的平均工资 
DELIMITER //

CREATE FUNCTION dept_sal(dept_name VARCHAR(15))
RETURNS DOUBLE
BEGIN 
	RETURN (
		SELECT AVG(salary)
		FROM employees e JOIN departments d 
		ON e.department_id = d.department_id
		WHERE d.department_name = dept_name
	);
END //

DELIMITER ;

SELECT dept_sal('Marketing');

-- 14. 创建函数add_float()，实现传入两个float，返回二者之和
DELIMITER $

CREATE FUNCTION add_float(value1 FLOAT,value2 FLOAT)
RETURNS FLOAT
BEGIN 
	RETURN (SELECT value1+value2);
END $

DELIMITER ;

SET @value1 = 12.3;
SET @value2 = 12.4;
SELECT add_float(@value1,@value2);

码农公寓

一、存储过程

1.1、存储过程概述

1.2、分类

1.3、创建存储过程

1.4、调用存储过程

1.5、存储过程的查看

1.6、存储过程的修改

1.7、存储过程的删除

二、存储函数的使用

2.1、创建存储函数

2.2、调用存储函数

2.3、存储函数的查看

2.4、存储函数的修改

2.5、存储函数的删除

三、对比存储函数和存储过程

四、练习题

相关文章