争议？MySQL存储过程与函数，封装，体，完整详细可收藏(2)

示例：

DELIMITER $

CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型,…)

[characteristics …]

BEGIN

sql语句1;

sql语句2;

END $

2.2 代码举例

举例1：创建存储过程select_all_data()，查看 emps 表的所有数据

DELIMITER $

CREATE PROCEDURE select_all_data()

BEGIN

SELECT * FROM emps;

END $

DELIMITER ;

举例2：创建存储过程avg_employee_salary()，返回所有员工的平均工资

DELIMITER //

CREATE PROCEDURE avg_employee_salary ()

BEGIN

SELECT AVG(salary) AS avg_salary FROM emps;

END //

DELIMITER ;

举例3：创建存储过程show_max_salary()，用来查看“emps”表的最高薪资值

CREATE PROCEDURE show_max_salary()

LANGUAGE SQL

NOT DETERMINISTIC

CONTAINS SQL

SQL SECURITY DEFINER

COMMENT ‘查看最高薪资’

BEGIN

SELECT MAX(salary) FROM emps;

END //

DELIMITER ;

举例4：创建存储过程show_min_salary()，查看“emps”表的最低薪资值。并将最低薪资通过OUT参数“ms”输出

DELIMITER //

CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)

BEGIN

SELECT MIN(salary) INTO ms FROM emps;

END //

DELIMITER ;

举例5：创建存储过程show_someone_salary()，查看“emps”表的某个员工的薪资，并用IN参数empname输入员工姓名

DELIMITER //

CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))

BEGIN

SELECT salary FROM emps WHERE ename = empname;

END //

DELIMITER ;

---

3. 调用存储过程

---

3.1 调用格式

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

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

格式：

调用in模式的参数：

CALL sp1(‘值’);

调用out模式的参数：

SET @name;

CALL sp1(@name);

SELECT @name;

调用inout模式的参数：

SET @name=值;

CALL sp1(@name);

SELECT @name;

3.2 代码举例

举例1：

DELIMITER //

CREATE PROCEDURE CountProc(IN sid INT,OUT num INT)

BEGIN

SELECT COUNT(*) INTO num FROM fruits

WHERE s_id = sid;

END //

DELIMITER ;

调用存储过程：

mysql> CALL CountProc (101, @num);

Query OK, 1 row affected (0.00 sec)

查看返回结果：

mysql> SELECT @num;

举例2：创建存储过程，实现累加运算，计算 1+2+…+n 等于多少

DELIMITER //

CREATE PROCEDURE add_num(IN n INT)

BEGIN

DECLARE i INT;

DECLARE sum INT;

SET i = 1;

SET sum = 0;

WHILE i <= n DO

SET sum = sum + i;

SET i = i +1;

END WHILE;

SELECT sum;

END //

DELIMITER ;

3.3 如何调试

在 MySQL 中，存储过程不像普通的编程语言（比如 VC++、Java 等）那样有专门的集成开发环境。因此，你可以通过 SELECT 语句，把程序执行的中间结果查询出来，来调试一个 SQL 语句的正确性。调试成功之后，把 SELECT 语句后移到下一个 SQL 语句之后，再调试下一个 SQL 语句。这样 逐步推进 ，就可以完成对存储过程中所有操作的调试了。当然，你也可以把存储过程中的 SQL 语句复制出来，逐段单独调试。

---

4. 存储函数的使用

---

前面学习了很多函数，使用这些函数可以对数据进行的各种处理操作，极大地提高用户对数据库的管理效率。MySQL支持自定义函数，定义好之后，调用方式与调用MySQL预定义的系统函数一样。

4.1 语法分析

学过的函数：LENGTH、SUBSTR、CONCAT等

语法格式：

CREATE FUNCTION 函数名(参数名 参数类型,…)

RETURNS 返回值类型

[characteristics …]

BEGIN

函数体 #函数体中肯定有 RETURN 语句

END

说明：

①参数列表：指定参数为IN、OUT或INOUT只对PROCEDURE是合法的，FUNCTION中总是默认为IN参数。

②RETURNS type 语句表示函数返回数据的类型；RETURNS子句只能对FUNCTION做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个 RETURN value 语句。

③characteristic 创建函数时指定的对函数的约束。取值与创建存储过程时相同。

④函数体也可以用BEGIN…END来表示SQL代码的开始和结束。如果函数体只有一条语句，也可以省略BEGIN…END。

4.2 调用存储函数

在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法是一样的。换言之，用户自己定义的存储函数与MySQL内部函数是一个性质的。区别在于，存储函数是用户自己定义的，而内部函数是MySQL的开发者定义的。

SELECT 函数名(实参列表)

4.3 代码举例

举例1：创建存储函数，名称为email_by_name()，参数定义为空，该函数查询Abel的email，并返回，数据类型为字符串型。

DELIMITER //

CREATE FUNCTION email_by_name()

RETURNS VARCHAR(25)

DETERMINISTIC

CONTAINS 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，并返回，数据类型为字符串型。

DELIMITER //

CREATE FUNCTION email_by_id(emp_id INT)

RETURNS VARCHAR(25)

DETERMINISTIC

CONTAINS SQL

BEGIN

RETURN (SELECT email FROM employees WHERE employee_id = emp_id);

END //

DELIMITER ;

调用：

SET @emp_id = 102;

SELECT email_by_id(102);

举例3：创建存储函数count_by_id()，参数传入dept_id，该函数查询dept_id部门的员工人数，并返回，数据类型为整型。

DELIMITER //

CREATE FUNCTION count_by_id(dept_id INT)

RETURNS INT

LANGUAGE SQL

NOT DETERMINISTIC

READS SQL DATA

SQL SECURITY DEFINER

COMMENT ‘查询部门平均工资’

BEGIN

RETURN (SELECT COUNT(*) FROM employees WHERE department_id = dept_id);

END //

DELIMITER ;

调用：

SET @dept_id = 50;

SELECT count_by_id(@dept_id);

注意：若在创建存储函数中报错“ 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：mysql> SET GLOBAL log_bin_trust_function_creators = 1;

4.4 对比存储函数和存储过程

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

---

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

---

5.1 查看

创建完之后，怎么知道我们创建的存储过程、存储函数是否成功了呢？

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

①SHOW CREATE语句查看存储过程和函数的创建信息，基本语法结构如下：

SHOW CREATE {PROCEDURE | FUNCTION} 存储过程名或函数名

举例：

SHOW CREATE FUNCTION test_db.CountProc \G

②SHOW STATUS语句查看存储过程和函数的状态信息，基本语法结构如下：

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE ‘pattern’]

这个语句返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。

[LIKE ‘pattern’]：匹配存储过程或函数的名称，可以省略。当省略不写时，会列出MySQL数据库中存在的所有存储过程或函数的信息。

③从information_schema.Routines表中查看存储过程和函数的信息

MySQL中存储过程和函数的信息存储在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

5.2 修改

修改存储过程或函数，不影响存储过程或函数功能，只是修改相关特性。使用ALTER语句实现。

ALTER {PROCEDURE | 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 ，表示子程序中包含读数据的语句。

自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！

如果你觉得这些内容对你有帮助，可以扫码获取！！（备注Java获取）

最后

各位读者，由于本篇幅度过长，为了避免影响阅读体验，下面我就大概概括了整理了

《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！
-9t61vSom-1713463166511)]

[外链图片转存中…(img-jPW0sdWU-1713463166512)]

[外链图片转存中…(img-DFrznPCy-1713463166512)]

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！

如果你觉得这些内容对你有帮助，可以扫码获取！！（备注Java获取）

最后

各位读者，由于本篇幅度过长，为了避免影响阅读体验，下面我就大概概括了整理了

[外链图片转存中…(img-G8jhAeSo-1713463166512)]

[外链图片转存中…(img-sDg9F35R-1713463166513)]

[外链图片转存中…(img-H5M9obF5-1713463166513)]

[外链图片转存中…(img-f84crn5n-1713463166514)]

《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！