什么存储过程和函数
存储过程和函数是在数据库中定义一些SQL语句的集合,方便直接调用,避免重复相同的SQL编写。
环境
MySQL:8.0.16;
创建存储过程
- 存储过程语法
CREATE [DEFINER = user] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body CREATE [DEFINER = user] FUNCTION sp_name ([func_parameter[,...]]) RETURNS type [characteristic ...] routine_body proc_parameter: [ IN | OUT | INOUT ] param_name type func_parameter: param_name type type: Any valid MySQL data type characteristic: COMMENT 'string' | LANGUAGE SQL | [NOT] DETERMINISTIC | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER } routine_body: Valid SQL routine statement
- 存储过程和函数参数
sp_name表示所要创建的储存过程和函数的名字。 proc_name表示储存过程的参数;每个参数由三部分组成,分别为输入/输出类型、参数名和参数类型;其中输入/输出类型有三种类型,分别为IN(输入类型)、OUT(输出类型)、INOUT(输入输出类型)。 func_parameter表示存储函数参数;每个参数由参数名和参数类型组成。 charateristic表示指定储存过程和函数的特性;LANGUAGE SQL:说明routine_body部分是由SQL语句组成的;[NOT]DETERMINISTIC:指明储存过程和函数执行的结果是否正确;DETERMINISTIC表示结果是确定的,每次执行储存过程时,相同的输入会得到相同的输出;NOT DETERMINISTIC表示结果时不确定的,相同的输入可能得到不同的输出;如果没有指定任何一个值,默认是NOT DETERMINISTIC。 {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}:指明子程序使用SQL语句限制;CONTAINS SQL表明子程序包含SQL语句,但是不包含读写数据的语句;NO SQL表明子程序不包含SQL语句;READS SQL DATA说明子程序包含读数据的语句;MODIFIES SQL DATA表明子程序包含写数据的语句;默认指定CONTAINS SQL。 SQL SECURITY { DEFINER | INVOKER }:指明谁有权限执行;DEFINER表示只有定义者才能执行;INVOKER表示拥有权限的调用者才可以执行;默认值DERINER。 COMMENT 'string':注释信息
- 创建储存过程
delimiter // CREATE PROCEDURE proc_count (OUT pram INT) BEGIN SELECT COUNT(*) INTO pram FROM `user`; END; // delimiter ; ------------------------------------------ # 调用该储存过程 (我user表中有5条数据) MySQL [test]> CALL proc_count(@a); Query OK, 1 row affected (0.11 sec) MySQL [test]> select @a; +------+ | @a | +------+ | 5 | +------+ 1 row in set (0.00 sec)
-
创建储存函数
DELIMITER // create function func_realname(uid INT(10)) returns varchar(30) comment '查询id对应姓名' READS SQL DATA begin return (select realname from `user` where `user`.id = uid); end; // DELIMITER ;
-
变量的使用
-
定义变量
DECLARE var_name[,...] type [DEFAULT value] DECLARE用来声明变量的;参数var_name是变量的名称,可以同时定义多个变量;参数type用来指定变量类型;DEFAULT value子句将变量默认值设置为value,没有使用DEFAULT子句时,默认值为NULL。 eg:DECLARE age INT DEFAULT 10;
-
为变量赋值
SET var_name=expr[,var_name=expr]... SET用来为变量赋值;参数var_name是变量的名称;参数expr是赋值表达式。一个SET语句可以同时为多个变量赋值,各个变量赋值语句之间用逗号隔开。 eg:SET age = 10; SELECT ... INTO 语句为变量赋值 SELECT col_name[,...] INTO var_name[,...] FROM table_name WHERE condition eg:SELECT age INTO temp_age FROM user WHERE id = 1;
-
-
流程控制语句
-
IF语句
IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list]... [ELSE statement_list] END IF 参数search_condition表示条件判断语句,参数statement_list表示不同的条件的执行语句。 eg: IF age > 20 THEN SET @count1=@count1+1; ElSEIF age=20 THEN @count2=@count2+1; ELSE @count3 = @count3+1; END IF;
-
CASE语句
CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list]... [ELSE statement_list] END CASE 参数case_value表示条件判断的变量;参数when_value表示变量的取值,参数statement_list表示不同when_value值的执行语句。 eg: CASE age WHEN 20 THEN SET @count1=@count1+1 ELSE SET @count2=@count2+1; END CASE;
-
LOOP语句
LOOP语句可以是某些特定的语句重复执行,实现一个简单的循环。LOOP语句本身没有停止循环,只是遇到LEAVE语句才能停止。 [begin_label:]LOOP statement_list END LOOP [end_label] 参数begin_label和参数end_label分别表示循环开始和结束标志,者两个标志必须相同,而且都可以省略;参数statement_list表示需要循环执行语句。 eg: add_num:LOOP SET @count=@count+1; END LOOP add_num;
-
LEVEL语句
LEVEL用于跳出循环 LEVEL label eg: add_num:LOOP SET @count=@count+1; IF @count=100 THEN LEAVE add_num; END LOOP add_num;
-
ITERATE语句
ITERAFE语句也是用来跳出循环,但只是跳转本次循环。 ITERATE label eg: add_num:LOOP SET @count=@count+1; if @count = 100 THEN LEAVE add_num; ELSE IF MOD (@count,3) = 0 THEN ITERATE add_num; SELECT * FROM user; END LOOP add_num;
-
REPEAT语句
有条件的循环语句 [begin_label:] REPEAT statement_list UNTIL search_condition END REPEAT [end_label] 参数statement_label表示循环的执行语句;参数search_condition表示结束循环的条件。 eg: REPEAT SET @count=@count+1 UNTIL @count=100 END REPEAT;
-
WHILE语句
WHILE语句也是有条件控制语句,但WHILE是满足条件是执行循环内的语句。 [begin_label:]WHILE search_condition DO statement_list END WHILE [end_label] 参数statement_condition表示循环执行条件,满足该条件时循环执行;参数statement_list表示循环执行的语句。 eg: WHILE @count<100 DO SET @count=@count+1; END WHILE;
-
调用储存过程和函数
- 调用储存过程
CALL proc_name([parameter[,...]]); 参数proc_name 存储过程名称,parameter储存过程参数。 eg: 前面创储存过程 MySQL [test]> CALL proc_count(@count); Query OK, 1 row affected (0.05 sec) MySQL [test]> select @count; +--------+ | @count | +--------+ | 5 | +--------+ 1 row in set (0.00 sec)
-
调用储存函数
前面创建了储存函数func_realname()。 MySQL [test]> select func_realname(1) AS realname; +----------+ | realname | +----------+ | 张三 | +----------+ 1 row in set (0.00 sec)
查看储存过程和函数
- 使用SHOW STATUS语句查看储存过程和函数状态
语法:SHOW {PROCEDURE|FUNCTION} STATUS {LIKE 'pattern'} MySQL [test]> SHOW PROCEDURE STATUS LIKE 'proc_count' \G; *************************** 1. row *************************** Db: test Name: proc_count Type: PROCEDURE Definer: root@% Modified: 2020-01-05 10:15:33 Created: 2020-01-05 10:15:33 Security_type: DEFINER Comment: character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8_general_ci 1 row in set (0.01 sec) MySQL [test]> SHOW FUNCTION STATUS LIKE 'func_realname' \G; *************************** 1. row *************************** Db: test Name: func_realname Type: FUNCTION Definer: root@% Modified: 2020-01-05 11:07:50 Created: 2020-01-05 11:07:50 Security_type: DEFINER Comment: 查询id对应姓名 character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8_general_ci 1 row in set (0.00 sec)
-
使用SHOW CREATE语句查看储存过程和函数的定义
语法:SHOW CREATE {PROCEDURE|FUNCTION} {proc_name|func_name} MySQL [test]> SHOW CREATE PROCEDURE proc_count \G; *************************** 1. row *************************** Procedure: proc_count 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`@`%` PROCEDURE `proc_count`(OUT pram INT) BEGIN SELECT COUNT(*) INTO pram FROM `user`; END character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8_general_ci 1 row in set (0.00 sec) MySQL [test]> SHOW CREATE FUNCTION func_realname \G; *************************** 1. row *************************** Function: func_realname 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`@`%` FUNCTION `func_realname`(uid INT(10)) RETURNS varchar(30) CHARSET utf8 READS SQL DATA COMMENT '查询id对应姓名' begin return (select realname from `user` where `user`.id = uid); end character_set_client: utf8mb4 collation_connection: utf8mb4_0900_ai_ci Database Collation: utf8_general_ci 1 row in set (0.00 sec)
-
从infomation_schema.Routine表中查看储存过程和函数的信息
语法:SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME = {proc_name|func_anme}; MySQL [test]> SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME = 'proc_count' \G; *************************** 1. row *************************** SPECIFIC_NAME: proc_count ROUTINE_CATALOG: def ROUTINE_SCHEMA: test ROUTINE_NAME: proc_count ROUTINE_TYPE: PROCEDURE DATA_TYPE: CHARACTER_MAXIMUM_LENGTH: NULL CHARACTER_OCTET_LENGTH: NULL NUMERIC_PRECISION: NULL NUMERIC_SCALE: NULL DATETIME_PRECISION: NULL CHARACTER_SET_NAME: NULL COLLATION_NAME: NULL DTD_IDENTIFIER: NULL ROUTINE_BODY: SQL ROUTINE_DEFINITION: BEGIN SELECT COUNT(*) INTO pram FROM `user`; 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: 2020-01-05 10:15:33 LAST_ALTERED: 2020-01-05 10:15:33 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@% CHARACTER_SET_CLIENT: utf8mb4 COLLATION_CONNECTION: utf8mb4_0900_ai_ci DATABASE_COLLATION: utf8_general_ci 1 row in set (0.00 sec) MySQL [test]> SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME = 'func_realname' \G; *************************** 1. row *************************** SPECIFIC_NAME: func_realname ROUTINE_CATALOG: def ROUTINE_SCHEMA: test ROUTINE_NAME: func_realname ROUTINE_TYPE: FUNCTION DATA_TYPE: varchar CHARACTER_MAXIMUM_LENGTH: 30 CHARACTER_OCTET_LENGTH: 90 NUMERIC_PRECISION: NULL NUMERIC_SCALE: NULL DATETIME_PRECISION: NULL CHARACTER_SET_NAME: utf8 COLLATION_NAME: utf8_general_ci DTD_IDENTIFIER: varchar(30) ROUTINE_BODY: SQL ROUTINE_DEFINITION: begin return (select realname from `user` where `user`.id = uid); end EXTERNAL_NAME: NULL EXTERNAL_LANGUAGE: SQL PARAMETER_STYLE: SQL IS_DETERMINISTIC: NO SQL_DATA_ACCESS: READS SQL DATA SQL_PATH: NULL SECURITY_TYPE: DEFINER CREATED: 2020-01-05 11:07:50 LAST_ALTERED: 2020-01-05 11:07:50 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: 查询id对应姓名 DEFINER: root@% CHARACTER_SET_CLIENT: utf8mb4 COLLATION_CONNECTION: utf8mb4_0900_ai_ci DATABASE_COLLATION: utf8_general_ci 1 row in set (0.00 sec)
修改储存过程和函数
- 修改储存过程和函数语法
ALTER PROCEDURE {proc_name|func_name} [characteristic ...] characteristic: COMMENT 'string' | LANGUAGE SQL | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA } | SQL SECURITY { DEFINER | INVOKER }
-
修改储存过程和函数
# 将SQL数据权限由READS SQL DATA 改成 MODIFIES SQL DATA,并指明调用者可以执行。 MySQL [test]> ALTER PROCEDURE proc_count MODIFIES SQL DATA SQL SECURITY INVOKER; Query OK, 0 rows affected (0.00 sec) # 指明调用者可以执行。 MySQL [test]> ALTER FUNCTION func_realname SQL SECURITY INVOKER; Query OK, 0 rows affected (0.01 sec)
删除储存过程和函数
- 删除储存过程和函数语法
DROP {PROCEDURE|FUNCTION} {proc_name|func_name};
- 删除存储过程
MySQL [test]> DROP PROCEDURE proc_count; Query OK, 0 rows affected (0.01 sec)
- 删除存储函数
MySQL [test]> DROP FUNCTION func_realname; Query OK, 0 rows affected (0.04 sec)
存储过程和函数对别
存储过程 | 存储函数 |
可以用过输出参数 | 不能拥有输出参数 |
需要CALL语句调用 | 可直接调用 |
不允许包含return语句 | 必须包含一个return语句 |