MySQL存储过程和函数

最新推荐文章于 2022-08-25 22:45:42 发布

寻找09之夏

最新推荐文章于 2022-08-25 22:45:42 发布

阅读量190

点赞数

分类专栏： MySQL 文章标签： MySQL 存储过程存储函数 PROCEDURE FUNCTION

本文链接：https://blog.csdn.net/qq_34272964/article/details/103837505

版权

MySQL 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

什么存储过程和函数

存储过程和函数是在数据库中定义一些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语句

寻找09之夏

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL存储过程和函数

什么存储过程和函数存储过程和函数是在数据库中定义一些SQL语句的集合，方便直接调用，避免重复相同的SQL编写。环境MySQL：8.0.16；创建存储过程存储过程语法 CREATE [DEFINER = user] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] rout...
复制链接

扫一扫

专栏目录