存储过程与函数
MySQL从5.0版本开始支持存储过程和函数，存储过程和函数能够将复杂的SQL封装在一起，应用程序无须关注存储过程和函数内部复杂的SQL逻辑，而只需简单的调用存储过程和函数即可
存储过程
存储过程(Stored Procedure),是一组经过预先编译的SQL语句的封装。
存储过程预先存储在MySQL服务器上，需要执行的时候，客户端向服务器发出调用存储过程命令，服务器端把预先存储好的一系列SQL语句全部执行
优点：
- 简化操作，提高SQL语句的重用性，减少开发程序员的压力
- 减少操作过程中的失误，提高效率
- 减少网络传输量(客户端不需要把所有SQL语句通过网络发送给服务器)
- 减少SQL语句暴露在网上的风险，也提高了数据查询的安全性
分类：
注：IN、OUT、INTOUT都可以在一个存储过程中带多个
- 没有参数(无参数无返回)
- 仅带IN参数(有参数无返回)
- 仅带OUT参数(无参数有返回)
- 既带IN又带OUT参数(有参数有返回)
- 带INOUT(有参数有返回)
语法：
CREATE PROCEDURE 存储过程名(IN|OUT|INOUT 参数名 参数类型)
[characteristics...]
BEGIN
存储过程体
END
注：
1. 参数前面符号意思
- IN：当前参数为输入参数，表示入参，存储过程只读取这个参数值，如没定义参数种类，默认是IN，表示输入参数
- OUT：当前参数为输出参数，表示出参，执行完成后，调用这个存储过程的客户端或应用程序可以读取这个参数返回值
- INOUT：当前参数既可做输入参数，也可做输出参数
2.形参类型可以是MySQL数据库中任何类型
3.characteristics表示创建存储过程时指定的对存储过程的约束条件，其取值信息如下
LANGUAGE SQL
| [NOT] DETERMINISTIC
| {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY {DEFINER|INVOKER}
| COMMENT 'string'
- LANGUAGE SQL：说明存储过程执行体是由SQL语句组成的，当前系统支持的语言为SQL
- [NOT] DETERMINISTIC：指明存储过程执行的结果是否确定，DETERMINISTIC表示确定，每次执行存储过程时，相同的输入会得到相同输出，NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同输出，如没有指定任意一个值，默认为NOT DETERMINISTIC
- {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
+ CONTAINS SQL表示当前存储过程的子程序包含SQL语句，但并不包含读写数据的SQL
+ NO SQL表示当前存储过程的子程序中不包含任何SQL语句
+ READS SQL DATA表示当前存储过程的子程序中包含读数据的SQL语句
+ MODIFIES SQL DATA表示当前存储过程的子程序中包含写数据的SQL语句
- SQL SECURITY {DEFINER|INVOKER}执行存储过程的权限，即指明哪些用户能够执行当前存储过程
+ DEFINER表示只有当前存储过程的创建者或定义者才能执行当前存储过程
+ INVOKER表示拥有当前存储过程的访问权限的用户能够执行当前存储过程
4.存储过程可以有多条SQL语句，如仅一条SQL语句，则可以省略BEGIN和END
5.需要设置新的结束标记 DELIMITER $
创建存储过程
1.无参存储过程
mysql> delimiter $
mysql> create procedure select_all_data()
-> begin
-> select * from employees;
-> end $
Query OK, 0 rows affected (0.06 sec)
mysql> delimiter ;
mysql> show procedure status like 'select%';
+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+
| dbtest15 | select_all_data | PROCEDURE | root@localhost | 2022-09-22 02:18:52 | 2022-09-22 02:18:52 | DEFINER | | utf8mb4 | utf8mb4_0900_ai_ci | utf8mb4_0900_ai_ci |
+
1 row in set (0.00 sec)
mysql> call select_all_data();
2.带OUT存储过程
mysql> delimiter $
mysql> create procedure show_min_salary(OUT ms double)
-> begin
-> select min(salary) into ms from employees;
-> end $
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call show_min_salary(@ms);
Query OK, 1 row affected (0.00 sec)
mysql> select @ms;
+
| @ms |
+
| 2100 |
+
1 row in set (0.00 sec)
3.带IN参数，创建存储过程show_someone_salary(),查看employees表某个员工的工资
mysql> delimiter $
mysql> create procedure show_someone_salary(IN empname varchar(20))
-> begin
-> select salary from employees where last_name=empname;
-> end $
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call show_someone_salary('Fay');
+
| salary |
+
| 6000.00 |
+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
3.带IN和OUT
mysql> DELIMITER $
mysql> CREATE PROCEDURE show_someone_salary2(IN empname varchar(20),OUT empsalary double(10,2))
-> BEGIN
-> select salary into empsalary from employees where last_name=empname;
-> END $
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> DELIMITER ;
mysql> call show_someone_salary2('Fay',@empsalary);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @empsalary;
+
| @empsalary |
+
| 6000 |
+
1 row in set (0.00 sec)
4.带INOUT,创建存储过程show_mgr_name(),查询某个员工领导的姓名
mysql> DELIMITER $
mysql> CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(25))
-> BEGIN
-> SELECT last_name FROM employees WHERE employee_id=(SELECT manager_id FROM employees WHERE last_name=empname);
-> END $
Query OK, 0 rows affected (0.00 sec)
mysql> DELIMITER ;
mysql> set @empname='Fay';
Query OK, 0 rows affected (0.01 sec)
mysql> call show_mgr_name(@empname);
+
| last_name |
+
| Hartstein |
+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
存储函数
语法
CREATE FUNCTION 函数名(参数名 参数类型,...)
RETURNS 返回值类型
[characteristics]
BEGIN
函数体
END
1.参数列表：指定参数为IN,OUT或INOUT只对PROCEDURE是合法的，FUNCTION中总是默认为IN参数
2.RETURNS type语句表示函数返回数据的类型(RETURNS子句只能对FUNCTION做指定，对函数而言这是强制的，它用来指定函数的返回类型，而函数体必须包含一个RETURN value语句)
3.characteristic创建函数时指定对函数的约束，取值与创建存储过程相同。
4.以BEGIN开始，END结束
事例
1.创建存储函数，名称为email_by_name()，返回Fay的邮箱
mysql> DELIMITER $
mysql> CREATE FUNCTION email_by_name()
-> RETURNS VARCHAR(25)
-> BEGIN
-> RETURN (SELECT email from employees where last_name='Fay');
-> END $
ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
mysql> CREATE FUNCTION email_by_name()
-> RETURNS VARCHAR(25)
-> DETERMINISTIC
-> CONTAINS SQL
-> READS SQL DATA
-> BEGIN
-> RETURN (SELECT email from employees where last_name='Fay');
-> END $
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT email_by_name();
-> $
+
| email_by_name() |
+
| PFAY |
+
1 row in set (0.00 sec)
mysql> DELIMITER ;
mysql> SET GLOBAL log_bin_trust_function_creators=1;
Query OK, 0 rows affected (0.00 sec)
2.创建存储函数，名称为email_by_id(),参数传入emp_id
mysql> DELIMITER $
mysql> CREATE FUNCTION email_by_id(emp_id int) RETURNS VARCHAR(25) BEGIN RETTURN (SELECT email FROM employees where employee_id=emp_id); END$
mysql> DELIMITER ;
mysql> select email_by_id(110);
+
| email_by_id(110) |
+
| JCHEN |
+
1 row in set (0.01 sec)
对比存储函数和存储过程
<table>
<th><td></td><td>关键字</td><td>调用语法</td><td>返回值</td><td>应用场景</td></th>
<td><td>存储过程</td><td>PROCEDURE</td><td>CALL 存储过程()</td><td>0或者多个</td><td>一般用于更新</td></td>
<td><td>存储函数</td><td>FUNCTION</td><td>SELECT 函数名()</td><td>只能是一个</td><td>一般用于查询一个值并返回</td></td>
</table>
<h2>存储函数可以放在查询语句中使用，存储过程不行，反之，存储过程的功能更强大，包括能够执行对表的操作(创建表、删除表等)和事务操作，这些功能是存储函数不具备的</h2>
查看存储过程、存储函数
1.通过show create [procedure|function] 函数名或存储过程名称查看
mysql> SHOW CREATE PROCEDURE show_mgr_name\G;
*************************** 1. row ***************************
Procedure: show_mgr_name
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `show_mgr_name`(INOUT empname VARCHAR(25))
BEGIN
SELECT last_name from employees WHERE employee_id=(SELECT manager_id from employees WHERE last_name=empname);
END
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> SHOW CREATE FUNCTION email_by_id\G;
*************************** 1. row ***************************
Function: email_by_id
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `email_by_id`(emp_id int) RETURNS varchar(25) CHARSET utf8mb4
BEGIN RETURN (SELECT email FROM employees where employee_id=emp_id); END
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
ERROR:
No query specified
2.通过 SHOW STATUS 查看存储过程和函数的状态信息
mysql> SHOW PROCEDURE STATUS LIKE 'show_mgr_name'\G;
*************************** 1. row ***************************
Db: dbtest15
Name: show_mgr_name
Type: PROCEDURE
Definer: root@localhost
Modified: 2022-09-22 02:54:54
Created: 2022-09-22 02:54:54
Security_type: DEFINER
Comment:
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database Collation: utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
ERROR:
No query specified
3.通过information_schema.Routines表中查看
mysql> select * from information_schema.Routines where ROUTINE_NAME='email_by_id' and ROUTINE_TYPE='FUNCTION'\G;
*************************** 1. row ***************************
SPECIFIC_NAME: email_by_id
ROUTINE_CATALOG: def
ROUTINE_SCHEMA: dbtest15
ROUTINE_NAME: email_by_id
ROUTINE_TYPE: FUNCTION
DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 25
CHARACTER_OCTET_LENGTH: 100
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: utf8mb4
COLLATION_NAME: utf8mb4_0900_ai_ci
DTD_IDENTIFIER: varchar(25)
ROUTINE_BODY: SQL
ROUTINE_DEFINITION: BEGIN RETURN (SELECT email FROM employees where employee_id=emp_id); END
EXTERNAL_NAME: NULL
EXTERNAL_LANGUAGE: SQL
PARAMETER_STYLE: SQL
IS_DETERMINISTIC: NO
SQL_DATA_ACCESS: CONTAINS SQL
SQL_PATH: NULL
SECURITY_TYPE: DEFINER
CREATED: 2022-09-22 03:29:02
LAST_ALTERED: 2022-09-22 03:29:02
SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
ROUTINE_COMMENT:
DEFINER: root@localhost
CHARACTER_SET_CLIENT: utf8mb4
COLLATION_CONNECTION: utf8mb4_0900_ai_ci
DATABASE_COLLATION: utf8mb4_0900_ai_ci
1 row in set (0.01 sec)
ERROR:
No query specified
删除存储过程和函数
DROP {FUNCTION|PROCEDURE}[IF EXISTS] 存储过程或函数名
mysql> DROP FUNCTION IF EXISTS email_by_id;
Query OK, 0 rows affected (0.01 sec)
8706
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
