彻底搞懂MySQL存储过程和函数

目录
  • 1.0  创建存储过程和函数
    • 1. 创建存储过程
    • 2. 创建存储函数
  • 2|0变量
    • 1. 定义变量
    • 2. 变量赋值
  • 3|0定义条件和处理程序
    • 1. 定义条件
    • 2. 定义处理程序
  • 4|0光标
    • 1. 声明光标
    • 2. 打开光标
    • 3. 使用光标
    • 4. 关闭光标
  • 5|0流程控制
    • 1. IF 语句
    • 2. CASE 语句
    • 3. LOOP 语句
    • 4. LEAVE 语句
    • 5. ITERATE 语句
    • 6. REPEAT 语句
    • 7. WHILE 语句
  • 6|0操作存储过程和函数
    • 1. 调用存储过程
    • 2. 调用存储函数
    • 3. 使用 SHOW STATUS 语句查看存储过程和函数的状态
    • 4. 使用 SHOW CREATE 语句查看存储过程和函数的定义
    • 5. 从 information_schema.Routine 表中查看存储过程和函数的信息
    • 6. 修改存储过程和函数
    • 7. 删除存储过程和函数
  • 总结

1.0  创建存储过程和函数

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

1. 创建存储过程

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 ; 将结束符恢复为分号

2. 创建存储函数

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 ;

2|0变量

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

1. 定义变量

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;

2. 变量赋值

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

示例:将变量 sql_test 赋值为 30

SET test_sql = 30;

3|0定义条件和处理程序

定义条件和处理程序是事先定义程序执行过程中可能遇到的问题,并且可以在处理程序中定义解决这些问题的能力

1. 定义条件

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;

2. 定义处理程序

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 匹配数值类型错误代码

下面是定义处理程序的几种示例方式:

// 方法一:捕获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';

4|0光标

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

1. 声明光标

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;

2. 打开光标

OPEN cursor_name;
  • 参数 cursor_name 表示光标的名称

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

OPEN cur_employee;

3. 使用光标

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;

4. 关闭光标

CLOSE cursor_name
  • cursor_name 表示光标的名称

5|0流程控制

1. IF 语句

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;

2. 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 的执行语句

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

CASE age
	WHEN 20 THEN SET @count1=@count1+1;
	ELSE SET @count2=@count2+1;
END CASE;

3. LOOP 语句

[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

4. LEAVE 语句

主要用于跳出循环

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

下面是一个 LEAVE 语句的示例

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

5. ITERATE 语句

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

ITERATE label

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

6. REPEAT 语句

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

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

下面是一个 REPEAT 语句的示例

add_num:REPEAT
	SELECT @count1=@count1+1;
	UNTIL @count1=100
END REPEAT add_num

7. WHILE 语句

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

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

下面是一个 WHILE 语句的示例

WHILE @count<100 DO
	SET @count=@count+1;
END WHILE;

6|0操作存储过程和函数

1. 调用存储过程

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

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

2. 调用存储函数

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

3. 使用 SHOW STATUS 语句查看存储过程和函数的状态

SHOW {PROCEDURE|FUNCTION} STATUS{LIKE 'pattern'}
  • 参数 PROCEDURE 表示查询存储过程,FUNCTION 表示存储函数
  • 参数 LIKE 'pattern' 用来匹配存储过程或函数的名称

4. 使用 SHOW CREATE 语句查看存储过程和函数的定义

SHOW CREATE {PROCEDURE|FUNCTION} proc_name

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

SELECT * FROM information_schema.Routine
WHERE ROUTINE_NAME='proc_name'

6. 修改存储过程和函数

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' 是注释信息

7. 删除存储过程和函数

通过 DROP 语句删除存储过程

DROP PROCEDURE proc_name;

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

DROP FUNCTION func_name;

总结

到此这篇关于MySQL存储过程和函数的文章就介绍到这了,更多相关MySQL存储过程和函数内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

(0)

相关推荐

  • Mysql存储过程和函数区别介绍

    存储过程是用户定义的一系列sql语句的集合,涉及特定表或其它对象的任务,用户可以调用存储过程,而函数通常是数据库已定义的方法,它接收参数并返回某种类型的值并且不涉及特定用户表. 存储过程和函数存在以下几个区别: 1)一般来说,存储过程实现的功能要复杂一点,而函数的实现的功能针对性比较强.存储过程,功能强大,可以执行包括修改表等一系列数据库操作:用户定义函数不能用于执行一组修改全局数据库状态的操作. 2)对于存储过程来说可以返回参数,如记录集,而函数只能返回值或者表对象.函数只能返回一个变量:而存

  • mysql 查询数据库中的存储过程与函数的语句

    方法一: select `name` from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE' //存储过程 select `name` from mysql.proc where db = 'your_db_name' and `type` = 'FUNCTION' //函数 方法二: show procedure status; //存储过程 SHOW PROCEDURE STATUS WHERE db='serva

  • mysql 导入导出数据库以及函数、存储过程的介绍

    mysql常用导出数据命令:1.mysql导出整个数据库  mysqldump -hhostname -uusername -ppassword databasename > backupfile.sql   mysqldump -hlocalhost -uroot hqgr> hqgr.sql     (如果root用户没用密码可以不写-p,当然导出的sql文件你可以制定一个路径,未指定则存放在mysql的bin目录下) 2.mysql导出数据库一个表 mysqldump -hhostnam

  • mysql中存储过程、函数的一些问题

    最近写一些mysql的存储过程和函数,发现网上比较有价值的文档很少,大都是照着手册上抄来的,有些实际问题解决不了,比如用变量作表名. 经过反复调试,总算找到解决办法,一下是一些简单的记录,比较零碎.部分内容转自http://my.opera.com/Dereky/blog/show.dml/322997 1.用变量做表名: 简单的用set或者declare语句定义变量,然后直接作为sql的表名是不行的,mysql会把变量名当作表名.在其他的sql数据库中也是如此,mssql的解决方法是将整条sq

  • MYSQL的存储过程和函数简单写法

    什么是存储过程 简单的说,就是一组SQL语句集,功能强大,可以实现一些比较复杂的逻辑功能,类似于JAVA语言中的方法: ps:存储过程跟触发器有点类似,都是一组SQL集,但是存储过程是主动调用的,且功能比触发器更加强大,触发器是某件事触发后自动调用: 有哪些特性 有输入输出参数,可以声明变量,有if/else, case,while等控制语句,通过编写存储过程,可以实现复杂的逻辑功能: 函数的普遍特性:模块化,封装,代码复用: 速度快,只有首次执行需经过编译和优化步骤,后续被调用可以直接执行,省

  • MySQL自定义函数和存储过程示例详解

    前言 本文主要给大家介绍的是关于MySQL自定义函数和存储过程的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧 1.前置条件 MySQL数据库中存在表user_info,其结构和数据如下: mysql> desc user_info; +-----------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------+--

  • 理解MySQL存储过程和函数

    一.概述  一提到存储过程可能就会引出另一个话题就是存储过程的优缺点,这里也不做讨论,一般别人问我我就这样回答你觉得它好你就用它.因为mysql中存储过程和函数的语法非常接近所以就放在一起,主要区别就是函数必须有返回值(return),并且函数的参数只有IN类型而存储过程有IN.OUT.INOUT这三种类型. 二.语法   创建存储过程和函数语法 CREATE PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] rout

  • 深入mysql创建自定义函数与存储过程的详解

    一 创建自定义函数在使用mysql的过程中,mysql自带的函数可能不能完成我们的业务需求,这时就需要自定义函数,例如笔者在开发过程中遇到下面这个问题:mysql表结构如下 复制代码 代码如下: DROP TABLE IF EXISTS `test`;CREATE TABLE `test` (  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,  `pic` varchar(50) NOT NULL,  `hashcode` varchar(16) N

  • MySql存储过程与函数详解

    存储过程和函数是在数据库中定义一些SQL语句的集合,然后直接调用这些存储过程和函数来执行已经定义好的SQL语句.存储过程和函数可以避免开发人员重复的编写相同的SQL语句.而且,存储过程和函数是在MySQL服务器中存储和执行的,可以减少客户端和服务器端的数据传输. 一.存储过程 1.1.基本语法 CREATE PROCEDURE sp_name ([proc_parameter[,...]])    [characteristic ...] routine_body Sp_name:存储过程的名称

  • 细谈Mysql的存储过程和存储函数

    1 存储过程 1.1 什么是存储过程 存储过程是一组为了完成某项特定功能的sql语句集,其实质上就是一段存储在数据库中的代码,他可以由声明式的sql语句(如CREATE,UPDATE,SELECT等语句)和过程式sql语句(如IF...THEN...ELSE控制结构语句)组成.存储过程思想上很简单,就是数据库 SQL 语言层面的代码封装与重用. 1.2 存储过程的优缺点 优点: 1.可增强sql语言的功能和灵活性 存储过程可以用流程控制语言编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算.

随机推荐