存储过程与存储函数

一. 存储过程

1. 含义: 

存储过程的英文是 Stored Procedure 。它的思想很简单，就是一组经过 预先编译 的 SQL 语句的封装。

2.执行过程：

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

1.1 创建存储过程

语法:
 

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,...) 
[characteristics ...] 
BEGIN
    存储过程体 

END

#1.IN ：当前参数为输入参数，也就是表示入参；
存储过程只是读取这个参数的值。如果没有定义参数种类， 默认就是 IN ，表示输入参数。
#2.OUT ：当前参数为输出参数，也就是表示出参；
执行完成之后，调用这个存储过程的客户端或者应用程序就可以读取这个参数返回的值了。
# 3.INOUT ：当前参数既可以为输入参数，也可以为输出参数。
形参类型可以是 MySQL数据库中的任意类型

 characteristics 表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下：

具体见ppt

#第十五章 存储过程与存储函数
#0. 准备工作
CREATE DATABASE dbtest15;
USE dbtest15;
CREATE TABLE employees
AS SELECT *
FROM `atguigudb`.employees;
CREATE TABLE departments
AS SELECT * FROM `atguigudb`.departments;
SELECT * FROM employees;
SELECT * FROM departments;
#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()，用来查看“employees”表的最高薪资值。
DELIMITER $
CREATE PROCEDURE show_max_salary()
BEGIN
  SELECT MAX(salary) FROM employees;
END $
DELIMITER ;
#调用
CALL show_max_salary();
-------------------------------------------------------------
-- 类型二: 带out
#举例4：创建存储过程show_min_salary()，查看“emps”表的最低薪资值。并将最低薪资通过OUT参数“ms”输出
DELIMITER $
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN 
   SELECT MIN(salary) INTO ms
   FROM employees;
END $
DELIMITER ;
#把最低工资存在变量ms中
CALL show_min_salary(@ms);
# 查看变量值
SELECT @ms;
-----------------------------
-- 类型三: 带in
#举例5：创建存储过程show_someone_salary()，查看“employees”表的某个员工的薪资，并用IN参数empname输入员工姓名。
DELIMITER $
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
BEGIN 
   SELECT salary FROM employees WHERE last_name=empname;
END $
DELIMITER ;
调用方式一
CALL show_someone_salary('King');
调用方式二
SET @empname='King';
CALL show_someone_salary(@empname);
------------
-- 类型四 :带in 和 out
#举例6：创建存储过程show_someone_salary2()，查看“emps”表的某个员工的薪资，并用IN参数empname
#输入员工姓名，用OUT参数empsalary输出员工薪资。
DELIMITER $
CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20),OUT empsalary DOUBLE)
BEGIN 
  SELECT salary INTO empsalary FROM employees WHERE last_name=empname;
END $
DELIMITER ;
CALL show_someone_salary2('Abel',@empsalary); 
--
SET @empname='Abel';
CALL show_someone_salary2(@empname,@empsalary);
SELECT @empsalary;
--
-- 类型五: 带 inout
#举例7：创建存储过程show_mgr_name()，查询某个员工领导的姓名，并用INOUT参数“empname”输入员工姓名，输出领导的姓名。
DELIMITER $
CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(25) )
BEGIN 
   SELECT ename INTO empname FROM employees 
   WHERE eid = (SELECT MID FROM employees WHERE ename=empname);
END $
DELIMITER ;
#调用
SET @empname='Abel';
CALL show_mgr_name(@empname);
#缺点 不好调试,只能逐行调试

 二. 存储函数

2.1 创建存储函数与调用

CREATE FUNCTION 函数名(参数名 参数类型,...)
RETURNS 返回值类型 [characteristics ...] 
BEGIN函数体
 #函数体中肯定有 RETURN 语句
 END

1、参数列表：，FUNCTION中总是默认为IN参数。
2、RETURNS type 语句表示函数返回数据的类型；
RETURNS子句只能对FUNCTION做指定，对函数而言这是 强制 的。它用来指定函数的返回类型，而且函
数体必须包含一个 RETURN value 语句。
3、characteristic 创建函数时指定的对函数的约束。取值与创建存储过程时相同，这里不再赘述。
4、函数体也可以用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句，也可以省略
BEGIN…END。

#调用
SELECT 函数名(实参列表)

2.2 实操

#举例1：
创建存储函数，名称为email_by_name()，参数定义为空，该函数查询Abel的email，并返回，数据类型为
字符串型。
DELIMITER //
CREATE FUNCTION email_by_name()
RETURNS VARCHAR(25) 
        DETERMINISTIC
        CONTAINS SQL
        READS SQL DATA
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，并返回，数据类型
为字符串型。
#创建函数前执行此语句保证函数创建会成功
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);
END //
DELIMITER ;
#调用
SELECT email_by_id(100);
或者
SET @emp_id=100;
SELECT email_by_id(@emp_id);
举例3：
创建存储函数count_by_id()，参数传入dept_id，该函数查询dept_id部门的员工人数，并返回，数据类型
为整型。
SET GLOBAL log_bin_trust_function_creators = 1;
DELIMITER //
CREATE FUNCTION count_by_id(dept_id INT)
RETURNS INT
BEGIN
    RETURN (SELECT COUNT(*) FROM employees WHERE department_id = dept_id);
END //
DELIMITER ;
#调用
SELECT count_by_id(100);
或者
SET @dept_id := 100;
SELECT count_by_id(@dept_id);

三. 对比存储过程与存储函数

 

四. 存储过程和函数的查看、修改、删除 

4.1 查看(3种方式)

1. 使用SHOW CREATE语句查看存储过程和函数的创建信息
语法: SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名
SHOW CREATE PROCEDURE select_all_data;
SHOW CREATE FUNCTION count_by_id;

2. 使用SHOW STATUS语句查看存储过程和函数的状态信息
语法: SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
SHOW PROCEDURE STATUS LIKE 'select_all_data';

3. 从information_schema.Routines表中查看存储过程和函数的信息
语法:SELECT * FROM information_schema.Routines 
     WHERE ROUTINE_NAME='存储过程或函数的名' [AND ROUTINE_TYPE = {'PROCEDURE|FUNCTION'}];
说明：如果在MySQL数据库中存在存储过程和函数名称相同的情况，最好指定ROUTINE_TYPE查询条件来
指明查询的是存储过程还是函数。
举例：从Routines表中查询名称为CountProc的存储函数的信息
SELECT * FROM information_schema.Routines 
WHERE ROUTINE_NAME='count_by_id' AND ROUTINE_TYPE = 'FUNCTION' \G

4.2 修改

只能修改特性,不能修改功能
ALTER {PROCEDURE | FUNCTION} 存储过程或函数的名 [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' ，表示注释信息
例子:
ALTER PROCEDURE show_max_salary
SQL SECURITY INVOKER 
COMMENT '查询最高工资';

4.3 删除 

删除存储过程和函数，可以使用DROP语句，其语法结构如下：

DROP {PROCEDURE | FUNCTION} [IF EXISTS] 存储过程或函数的名

DROP PROCEDURE CountProc;
DROP FUNCTION CountProc;

 

五. 关于存储过程使用的争议 

优点:

缺点:

六. 章节练习

6.1 练习一(存储过程)

#0.准备工作
CREATE DATABASE test15_pro_func;
USE test15_pro_func;
#1. 创建存储过程insert_user(),实现传入用户名和密码，插入到admin表中
CREATE TABLE ADMIN( 
id INT PRIMARY KEY AUTO_INCREMENT, 
user_name VARCHAR(15) NOT NULL, 
pwd VARCHAR(25) NOT NULL );
DESC ADMIN;
DELIMITER $
CREATE PROCEDURE insert_user(IN `user_name` VARCHAR(15),IN `password` VARCHAR(25))
BEGIN 
   INSERT INTO `admin`(user_name,pwd) VALUES (`user_name`,`password`);
END $
DELIMITER ;
CALL insert_user('King','123456');
SELECT * FROM ADMIN;
#2. 创建存储过程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;
DROP PROCEDURE get_phone;
#3. 创建存储过程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 ;
CALL date_diff('1992-09-08','1992-10-30',@sum_date);
SELECT @sum_date;
#4. 创建存储过程format_date(),实现传入一个日期，格式化成xx年xx月xx日并返回
DELIMITER $
CREATE PROCEDURE format_date(IN date1 DATE,OUT date_gsh VARCHAR(25))
BEGIN
   SELECT DATE_FORMAT(date1,'%y年%m月%d日') INTO date_gsh;
END $
DELIMITER ;
CALL format_date('2022-05-05',@date_gsh);
SELECT @date_gsh;
#5. 创建存储过程beauty_limit()，根据传入的起始索引和条目数，查询女神表的记录
DELIMITER $
CREATE PROCEDURE beauty_limit(IN startindex INT,IN size INT)
BEGIN
   SELECT * FROM beauty LIMIT startindex,size;
END $
DELIMITER ;
CALL beauty_limit(2,3);
#6. 传入a和b两个值，最终a和b都翻倍并返回
DELIMITER $
CREATE PROCEDURE add_double(INOUT a INT,INOUT b INT)
BEGIN
  SELECT a*a,b*b INTO a,b; 
END $
DELIMITER ;
SET @a=1;
SET @b=2;
CALL add_double(@a,@b);
SELECT @a,@b;
或者
DELIMITER // CREATE PROCEDURE add_double(INOUT a INT ,INOUT b INT) 
BEGIN
  SET a = a * 2; 
  SET b = b * 2; 
END // 
DELIMITER ;
#7. 删除题目5的存储过程
DROP PROCEDURE beauty_limit;
#8. 查看题目6中存储过程的信息
SHOW CREATE PROCEDURE add_double;
SHOW PROCEDURE STATUS LIKE 'add_double';

 6.2 练习二(存储函数)

#0. 准备工作
USE test15_pro_func; 
CREATE TABLE employees 
AS SELECT * FROM atguigudb.`employees`;
CREATE TABLE departments 
AS SELECT * FROM atguigudb.`departments`;
#无参有返回
#1. 创建函数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();
#有参有返回 
#2. 创建函数ename_salary(),根据员工姓名，返回它的工资
SET GLOBAL log_bin_trust_function_creators = 1;
DELIMITER //
CREATE FUNCTION ename_salary(last_name VARCHAR(25))
RETURNS DOUBLE(8,2)
BEGIN
 RETURN(SELECT e.salary FROM employees e WHERE e.last_name=last_name);
END //
DELIMITER ;
SELECT ename_salary('Abel');
DESC employees;
#3. 创建函数dept_sal() ,根据部门名，返回该部门的平均工资
SET GLOBAL log_bin_trust_function_creators = 1;
DELIMITER //
CREATE FUNCTION dept_sal(department_name varchar(15))
RETURNS DOUBLE(8,2)
BEGIN 
  RETURN (SELECT AVG(salary) FROM employees e join departments d
  on e.department_id=d.department_id 
  where d.department_name=department_name);
END // 
DELIMITER ;
SELECT dept_sal('IT');
#4. 创建函数add_float()，实现传入两个float，返回二者之和
DELIMITER //
CREATE FUNCTION add_float(num1 FLOAT,num2 FLOAT)
RETURNS FLOAT
BEGIN
  RETURN (SELECT num1+num2);
END //
DELIMITER ;
SELECT add_float(1.1,2.1);