MySQL 存储过程和函数

创建存储过程和函数

创建存储过程和函数就是将经常使用的一组 SQL 语句组合在一起,并将这些 SQL 语句当作一个整体存储在 MySQL 服务器

CREATE PROCEDURE procedure_name ([proc_param[,...]])
    [characteristic ...] routine_body
  • procedure_name 表示所要创建的存储过程的名字
  • proc_param 表示存储过程的参数
  • characteristic 表示存储过程的特性
  • routine_body 表示存储过程的 SQL 语句

procedure_name 不能出现重名

proc_param 中每个参数的语法形式如下,每个参数由三部分组成,分别为输入/输出类型、参数名和参数类型。输入/输出类型有三种类型,分别是 IN(输入类型)、OUT(输出类型)、INOUT(输入/输出类型)。param_name 表示参数名,type 表示参数类型

[ IN | OUT | INOUT ] param_name type

characteristic 指定存储过程的特性,有以下取值:

  • LANGUAGE SQL:说明存储过程中使用的是 SQL 语言编写的
  • [NOT] DETERMINISTIC:说明存储过程执行的结果是否正确,DETERMINISTIC 表示结果是确定的,即每次执行存储过程,相同的输入会得到相同的输出;NOT DETERMINISTIC 表示结果是不确定的,相同的输入可能得到不同的输出,默认为 NOT DETERMINISTIC
  • {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}:指明子程序使用 SQL 语句的限制,CONTAINS SQL 表示子程序不包含读或者写数据的语句,NO SQL 表示子程序不包含 SQL,READS SQL DATA 表示子程序包含读数据的语句,但是不包含写数据的语句,MODIFIES SQL DATA 表示子程序包含写数据的语句
  • SQL SECURITY {DEFINER | INVOKER}:指明谁有权限执行,DEFINER 表示只有定义者才能执行,INVOKER 表示拥有权限的调用者可以执行,默认为 DEFINER
  • COMMENT ‘string’:注释信息,可以用来描述存储过程或函数

示例:创建存储过程,查询表 t_employee 中所有员工的薪水的存储过程

DELIMITER $$
CREATE PROCEDURE proc_employee()
COMMENT '查询员工薪水'
BEGIN
    SELECT salary FROM t_employee
END;
$$
DELIMITER ;

MySQL 中默认以分号 ; 作为语句结束符,存储过程中的 SQL 语句需要分号来结束,为了避免冲突,先用 DELIMITER $$ 将 MySQL 的结束符设置为 $$,再用 DELIMITER ; 将结束符恢复为分号

CREATE FUNCTION fun_name([func_param,[,...]])
    [characteristic ...] routine_body
  • fun_name 表示函数名字
  • func_param 表示函数从参数
  • characteristic 表示函数特性,取值与存储过程的取值相同
  • routine_body 表示函数的 SQL 语句

func_param 中每个参数的语法形式如下,分别为参数名和参数类型

param_type type

还是之前的示例

DELIMITER $$
    CREATE FUNCTION func_employee(id INT(4))
        RETURNS INT(6)
    COMMENT '查询员工薪水'
    BEGIN
        SELECT salary FROM t_employee
    END;
    $$
DELIMITER ;

在 MySQL 中,可以定义和使用变量,这些变量的作用范围在 BEGIN … END 程序段中

DECLARE var_name[,...] type [DEFAULT value]
  • 关键字 DECLARE 用来声明变量
  • 参数 var_name 是变量的名称,可以同时定义多个变量
  • 参数 type 用来指定变量的类型
  • DEFAULT value 子句将变量默认值设置为 value,没有使用 DEFAULT 子句时,默认值为 NULL

示例:定义变量 sql_test,数据类型为 INT 型,默认值为 10

DECLARE test_sql INT DEFAULT 10;
SET var_name=expr[,var_name=expr]...

  • 关键字 SET 用来为变量赋值
  • 参数 var_name 是变量的名称
  • 参数 expr 是赋值表达式

示例:将变量 sql_test 赋值为 30

SET test_sql = 30;

定义条件和处理程序

定义条件和处理程序是在程序执行期间可能遇到的预定义问题,解决这些问题的能力可以在处理程序中定义。

[En]

Defining conditions and handlers is a pre-defined problem that may be encountered during program execution, and the ability to solve these problems can be defined in the handler.

DECLARE condition_name CONDITION FOR condition_value
condition_value:
SQLSTATE[VALUE] sqlstate_value|mysql_error_code
  • 参数 condition_name 表示条件名称
  • 参数 condition_value 表示条件类型
  • 参数 sqlstate_value 和 参数 mysql_error_code 都可以表示 MySQL 的错误

示例:定义 ERROR 1146(42S02) 错误,名称为 can_not_find,可以用两种不同的方法来定义

// 方法一:使用sqlstate_value
DECLARE can_not_find CONDITION FOR SQLSTATE '42S02';
// 方法二:使用mysql_error_code
DECLARE can_not_find CONDITION FOR 1146;
DECLARE handler_type HANDLER FOR condition_value[,...] proc_statement
handler_type:
CONTINUE|EXIT|UNDO
condition_value:
SQLSTATE[VALUE]sqlstate_value|condition_name|SQLWARNING|NOT FOUND|SQLEXCEPTION|mysql_error_code
  • handler_type 指明错误的处理方式:该参数有三个取值:
  • CONTINUE:表示遇到错误不处理,继续向下执行
  • EXIT:表示遇到错误马上退出
  • UNDO:表示遇到错误后撤回之前的操作
  • condition_value 表示错误类型,可以有以下取值:
  • SQLSTATE[VALUE]sqlstate_value 包含 5 个字符的字符串错误值
  • condition_name 表示 DECLARE CONDITION 定义的错误条件名称
  • SQLWARNING 匹配所有 01 开头的 SQLSTATE 错误代码
  • NOT FOUND 匹配所有 02 开头的 SQLSTATE 错误代码
  • SQLEXCEPTION 匹配所有没有被 SQLWARNING 或 NOT FOUND 捕获的 SQLSTATE 错误代码
  • mysql_error_code 匹配数值类型错误代码
  • proc_statement 匹配数值类型错误代码

以下是几个如何定义处理程序的示例:

[En]

Here are several examples of how to define handlers:

// 方法一:捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
SET @info='NOT FOUND';
// 方法二:使用mysql_error_code
DECLARE CONTINUE HANDLER FOR 1146
SET @info='NOT FOUND';
// 方法三:先定义条件,然后调用
ECLARE can_not_find CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR can_not_find
SET @info='NOT FOUND';
// 方法四:使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING
SET @info='ERROR';
// 方法五:使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND
SET @info='NOT FOUND';
// 方法六:使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR 使用SQLEXCEPTION
SET @info='ERROR';

查询语句可以查询多条记录,使用存储过程和函数中的游标逐条读取查询结果集中的记录。游标必须在处理程序之前以及变量和条件之后声明

[En]

Query statements may query multiple records, using cursors in stored procedures and functions to read records in the query result set one by one. The cursor must be declared before the handler and after variables and conditions

DECLARE cursor_name CURSOR
FOR select_statement;
  • 参数 cursor_name 表示光标名称
  • 参数 select_statement 表示 SELECT 语句

下面声明一个名为 cur_employee 的光标

DECLARE cur_employee CURSOR
FOR SELECT name,age FROM t_employee;
OPEN cursor_name;
  • 参数 cursor_name 表示光标的名称

下面打开一个名为 cur_employee 的光标

OPEN cur_employee;
FETCH cursor_name
INTO var_name[,var_name...]
  • cursor_name 表示光标的名称
  • var_name 表示将光标中的 SELECT 语句查询出来的信息存入该参数,该参数必须在声明光标之前就定义好

下面打开一个名为 cur_employee 的光标,将查询的数据存入 emp_name 和 emp_age 这两个变量中

FETCH cur_employee INTO emp_name,emp_age;
CLOSE cursor_name
  • cursor_name 表示光标的名称

流程控制

IF search_condition THEN statement_list
 [ELSEIF search_condition THEN statement_list]...

 [ELSE statement_list]
END IF
  • 参数 search_condition 表示条件判断语句
  • 参数 statement_list 表示不同条件的执行语句

下面是一个 IF 语句的示例

IF age>20 THEN SET @count1=@conut1+1;
    ELSEIF age=20 THEN @count2=@conut2+1;
    ELSE @count3=@count3+1;
END IF;
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 的执行语句

下面的是一个 CASE 语句的示例

CASE age
    WHEN 20 THEN SET @count1=@count1+1;
    ELSE SET @count2=@count2+1;
END CASE;
[begin_label:]LOOP
    statement_list
END LOOP [end_label]
  • 参数 begin_label 和参数 end_label 分别表示循环开始和结束的标志,必须相同,而且都可以省略
  • 参数 statement_list 表示需要循环执行的语句

下面是一个 LOOP 语句的示例

add_num:LOOP
    SELECT @count1=@count1+1;
END LOOP add_num

主要用于跳出循环

LEAVE label
  • 参数 label 表示循环的标志

下面是一个 LEAVE 语句的示例

add_num:LOOP
    SELECT @count1=@count1+1;
    IF @count1=100 THEN
        LEAVE add_num;
END LOOP add_num

也是用于跳出循环,不过是跳出本次循环,直接进入下次循环,类似 Java 的 continue

ITERATE label
  • 参数 label 表示循环的标志

下面是一个 ITERATE 语句的示例

add_num:LOOP
    SELECT @count1=@count1+1;
    IF @count1=100 THEN
        LEAVE add_num;
    ELSE IF MOD(@count1,3)=0 THEN
        ITERATE add_num;
    SELECT * FROM employee;
END LOOP add_num

REPEAT 语句是由条件的控制循环语句,当满足特定条件就会退出循环语句

[begin_label:]REPEAT
    statement_list
    UNTIL search_condition
END REPEAT [end_label]
  • 参数 statement_list 表示需要循环执行的语句
  • 参数 search_condition 表示结束循环的条件,满足该条件即结束循环

下面是一个 REPEAT 语句的示例

WHILE 语句也是有条件控制的循环语句,与 REPEAT 语句不同的是,WHILE 语句是当满足条件时执行循环内的语句

[begin_label:]WHILE search_condition DO
    statement_list
END WHILE [end_label]
  • 参数 statement_list 表示需要循环执行的语句
  • 参数 search_condition 表示结束循环的条件,满足该条件即结束循环

下面是一个 WHILE 语句的示例

WHILE @count

操作存储过程和函数

在 MySQL 中,使用 CALL 语句来调用存储过程

CALL proc_name([parameter[,...]])
  • proc_name 是存储过程的名称
  • paramter 是指存储过程的参数

在 MySQL 中,存储函数的使用方法和 MySQL 内部函数是一样的

SHOW {PROCEDURE|FUNCTION} STATUS{LIKE 'pattern'}
  • 参数 PROCEDURE 表示查询存储过程,FUNCTION 表示存储函数
  • 参数 LIKE ‘pattern’ 用来匹配存储过程或函数的名称
SHOW CREATE {PROCEDURE|FUNCTION} proc_name
SELECT * FROM information_schema.Routine
WHERE ROUTINE_NAME='proc_name'
ALTER {PROCEDURE|FUNCTION} proc_name[characteristic...];
characteristic:
    {CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY{DEFINER|INVOKER}
|COMMENT 'string'
  • 参数 proc_name 表示存储过程或函数的名称
  • 参数 characteristic 指定存储过程的特性:
  • CONTAINS SQL 表示子程序包含 SQL 语句,但不包含读或写数据的语句
  • NO SQL 表示子程序不包含 SQL 语句
  • READS SQL DATA 表示子程序中包含读数据的语句
  • MODIFIES SQL DATA 表示子程序中包含写数据的语句
  • SQL SECURITY{DEFINER|INVOKER} 指明谁有权限执行:
  • DEFINER 表示只有定义者才能执行
  • INVOKER 表示调用者可以执行
  • COMMENT ‘string’ 是注释信息

通过 DROP 语句删除存储过程

DROP PROCEDURE proc_name;

通过 DROP FUNCTION 语句删除存储函数

DROP FUNCTION func_name;

Original: https://www.cnblogs.com/Yee-Q/p/16183517.html
Author: 低吟不作语
Title: MySQL 存储过程和函数

原创文章受到原创版权保护。转载请注明出处:https://www.johngo689.com/505460/

转载文章受原作者版权保护。转载请注明原作者出处!

(0)

大家都在看

  • visual studio 2015 IOS开发连接mac时提示错误couldn’t connect to xxxx, please try again的一个方法

    本人使用虚拟机MAC。原本使用虚拟机中的VS2015连接正常没有问题。 但是当把MAC的虚拟机文件COPY到另一个机器上,提示”couldn’t conne…

    数据库 2023年6月14日
    0131
  • MySQL锁:01.总览

    锁总览 锁的作用 加锁流程 锁对数据库的影响 锁等待 死锁 锁类型 锁范围 锁方式 全局锁 全局读锁 全局QC锁 QC锁存在的问题: 备份锁 backup lock MDL锁 MD…

    数据库 2023年6月16日
    0173
  • JWT+SpringSecurity登录和权限管理

    一、什么是JWT 说起JWT,我们应该来谈一谈基于token的认证和传统的session认证的区别。说起JWT,我们应该来谈一谈基于token的认证和传统的session认证的区别…

    数据库 2023年6月6日
    0130
  • [LeetCode]28. 实现 strStr()

    实现 strStr() 函数。 给定一个 haystack 字符串和一个 needle 字符串,在 haystack 字符串中找出 needle 字符串出现的第一个位置 (从0开始…

    数据库 2023年6月9日
    0126
  • Java 多线程共享模型之管程(下)

    共享模型之管程 wait、notify wait、notify 原理 Owner 线程发现条件不满足,调用 wait 方法,即可进入 WaitSet 变为 WAITING 状态 B…

    数据库 2023年6月16日
    0171
  • MIT 6.824 Lab2D Raft之日志压缩

    书接上文Raft Part C | MIT 6.824 Lab2C Persistence。 实验准备 实验代码: git://g.csail.mit.edu/6.824-gola…

    数据库 2023年6月14日
    0210
  • MySQL事务ACID原理深度解析

    什么是MySQL事务? 事务是指对数据库的一组操作的集合,集合中的SQL语句要么全部执行成功,要么就全部失败,如果集合中任一操作出错,则此集合所有对数据库的操作全部回滚。 以常见的…

    数据库 2023年5月24日
    0129
  • 主从复制直接转换MGR_5.7验证试验

    IP port role info 192.168.188.51 4000 node1 master 192.168.188.52 4000 node2 slave1 192.16…

    数据库 2023年6月16日
    0270
  • Mysql 的Innodb引擎和Myisam数据结构和区别

    先大体看一下MySQL的SQL layer层的一个架构流程: 简要介绍一些关键模块: [En] Give a brief description of some key modul…

    数据库 2023年5月24日
    0134
  • gauss正则找汉字

    select substring( ‘沙特阿拉伯6.20’ from ‘[\u4E00-\u9FA5]{2,5}’ )返回 ‘沙特阿拉伯’ Original: https://ww…

    数据库 2023年6月16日
    0114
  • GROUP BY 后获取每一组最新的一条记录

    最近有一种需求,一张订单可能有多个支付单,这就要求我们拿到每一张订单的最新支付单。具体思路如下: [En] Recently, there is a demand that the…

    数据库 2023年5月24日
    0111
  • 剑指 Offer II 091. 粉刷房子

    剑指 Offer II 091. 粉刷房子 动态规划当前粉刷房子的花费可以由上一家粉刷房子的花费推导出来,所以可以使用动态规划求解这道题。首先确定dp数组的含义,每个房子都可以被粉…

    数据库 2023年6月16日
    0161
  • 1. SQL

    404. 抱歉,您访问的资源不存在。 可能是网址有误,或者对应的内容被删除,或者处于私有状态。 代码改变世界,联系邮箱 contact@cnblogs.com 园子的商业化努力-困…

    数据库 2023年6月16日
    0123
  • 今日🎸吉他练到这儿

    本文来自博客园,作者:ukyo–BlackJesus,转载请注明原文链接:https://www.cnblogs.com/ukzq/p/16750421.html Or…

    数据库 2023年6月11日
    0134
  • 从SQL Server到MySQL,携程核心系统无感迁移实战

    前言 携程酒店订单系统的存储设计从1999年收录第一单以来,已经完成了从单一SQLServer数据库到多IDC容灾、完成分库分表等多个阶段,在见证了大量业务奇迹的同时,也开始逐渐暴…

    数据库 2023年5月24日
    0107
  • MySQL45讲之备库并行复制策略

    前言 本文主要介绍 MySQL 备库的并行复制策略。 为什么备库需要并行复制 如果主数据库有大量的更新操作,因为主数据库可以并发写入,而从数据库只能在单个线程中执行,那么从数据库的…

    数据库 2023年5月24日
    0141
亲爱的 Coder【最近整理,可免费获取】👉 最新必读书单  | 👏 面试题下载  | 🌎 免费的AI知识星球