MySql 存储过程和函数

书香水墨

已于 2022-12-15 22:48:50 修改

阅读量1.9k

点赞数 1

分类专栏：数据库文章标签： mysql

于 2019-10-13 23:22:16 首次发布

本文链接：https://blog.csdn.net/qq_27870421/article/details/102496544

版权

数据库专栏收录该内容

118 篇文章 3 订阅

订阅专栏

`一、存储过程`

简単地说，存储过程就是一条或者多条SQL语句的集合，可视为批文件，但是其作用不仅限于批处理。
存储程序可以分为存储过程和函数，MySQL中创建存储过程和函数使用的语句分别是： CREATE PROCEDURE和CREATE FUNCTION。使用CALL语句来调用存储过程，只能用输出变量返回值。函数可以从语句外调用(即通过引用函数名)，也能返回标量值。存储过程也可以调用其他存储过程。

1.1 创建存储过程

1.1.1 语法

CREATE PROCEDURE sp_name ([proc_parameter])
[characteristics...] routine body

1.1.2 参数说明

CREATE PROCEDURE为用来创建存储函数的关键字;
sp_name为存储过程的名称;
proc_parameter为指定存储过程的参数列表，列表形式：[IN | OUT | INOUT ] param name type
其中，
IN表示输入参数,
OUT表示输出参数，
INOUT表示既可以输入也可以输岀；
param name 表示参数名称；
type表示参数的类型，该类型可以是MySQL数据库中的任意类型。
characteristics指定存储过程的特性，有以下取值：
LANGUAGE SQL：
说明routine_body部分是由SQL语句组成的，当前系统支持的语言为 SQL, SQL是LANGUAGE特性的唯一值
[NOT] DETERMINISTIC：
指明存储过程执行的结果是否正确.
DETERMINISTIC表示结果是确定的每次执行存储过程时，相同的输入会得到相同的输i出。
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表示拥有权限的调用者可以执行。
默认情况下，系统指定为 DEFINER。
COMMENT ‘string’：
注释信息，可以用来描述存储过程或函数。
routine_body是SQL代码的内容，可以用BEGIN…END来表示SQL代码的开始和结束。

1.1.3 示列

-- DELIMITER //用来将mysql的结束符设置为//，存储过程定义完成在使用`DELIMITER ;`来回复
DELIMITER //
CREATE PROCEDURE AvgFruitPrice()
BEGIN
	SELECT AVG(f_price) AS avgprice
	FROM fruits;
END//

1.1.4 创建带参数的存储过程

CREATE PROCEDURE CountProc (OUT paraml INT)
BEGIN
	SELECT COUNT(*) INTO paraml FROM fruits;
END;

上述代码的作用是创建一个获取fruits表记录条数的存储过程，名称是CountProc, COUNT(*) 计算后把结果放入参数paramI中。

1.2 创建存储函数

1.2.1 语法

CREATE FUNCTION func_name ([func_parameter])
	RETURNS type
[characteristic...] routine body

1.2.2 参数说明

CREATE FUNCTION 为用来创建存储函数的关键字；
func_name 表示存储函数的名称；
func_parameter 为存储过程的参数列表，参数列表形式：[IN | OUT | INOUT] param_name type
其中，IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；param_name 表示参数名称：type表示参数的类型，该类型可以是MySQL数据库中的任意类型。
RETURNS type语句表示函数返冋数据的类型;
characteristic 指定存储函数的特性，取值与创建存储过程时相同;
routine_body 是SQL代码的内容。

1.2.3 示列：创建存储函数

        创建存储函数，名称为NameByZip,该函数返回SELECT语句的査询结果，返回类型为字符串型。
        指定参数为IN、OUT或INOUT只对PROCEDURE是合法的。（FUNCTION中总是默认为IN参数）。
        RETURNS子句只能对FUNCTION做指定，对函数而言这是强制提示的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。

CREATE FUNCTION NameByZip()
RETURNS CHAR(50)
RETURN (SELECT s_name FROM suppliers WHERE s_call= '48075');

`二、变量的使用`

2.1 定义变量

变量可以在子程序中声明并使用，这些变量的作用范围是在BEGIN…END程序中。
在存储过程中使用DECLARE语句定义变量。

2.1.1 语法格式

DECLARE var_name1[, var_mame2]... date_type [DEFAULT value];

2.1.2 参数说明

var_name1为局部变量的名称；
date_type为一个mysql支持的数据类型；
DEFAULT value子句给变量提供一个默认值。值除了可以被声明为一个常数之外，还可以被指定为一个表达式。如果没有DEFAULT子句，初始值为NULL。

2.1.3 示列

DECLARE myparam INT DEFAULT 100;

2.2 为变量赋值

2.2.1 语法

SET var name = expr [, var_name = expr]

2.2.2 示列

DECLARE varl, var2, var3 INT;
SET varl = 10, var2 = 20;
SET var3 = varl + var2;

2.2.3 为多个变量赋值

这个SELECT语法把选定的列直接存储到对应位置的变量Ocol_name表示字段名称；var_name 表示定义的变量名称；table_expr表示查询条件表达式，包括表名称和WHERE子句。

SELECT col_name[,...] INTO var_name[,...] table_expr;

DECLARE fruitname CHAR(50);
DECLARE fruitprice DECIMAL(8,2);

SELECT f_name, f_price INTO fruitname, fruitprice
FROM fruits WHERE f_id ='al';

`三、定义条件和处理程序`

特定条件需要特定处理。这些条件可以联系到错误，以及子程序中的一般流程控制。定义条件是事先定义程序执行过程中遇到的问题，处理程序定义了在遇到这些问题时应当釆取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力，避免程序异常停止运行。

3.1 定义条件

3.1.1 语法

DECLARE condition_name CONDITION FOR [condition_type]

[condition_type]:
	SQLSTATE [VALUE] sqlstete_value | mysql_error_code

3.1.2 参数说明

condition_name参数表示条件的名称；
condition_type参数表示条件的类型；
sqlstate_value 和 mysql_error_code都可以表示MySQL的错误，sqlstate_value为长度为5的字符串类型错误代码， mysql_error_code 为数值类型错误代码。例如：ERROR 1142(42000)4*, sqlstate_value 的值是 42000, mysql_error_code 的值是 1142。

3.1.3 示列

--方法一：使用 sqlstete_value
DECLARE command_not_allowed CONDITION FOR SQLSTATE 42000;

-- 方法二：使用 mysql_error_code
DECLARE command not allowed CONDITION FOR 1148;

3.2 定义处理程序

3.2.1 语法

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement
handlertype:
	CONTINUE | EXIT | UNDO
	
conditionvalue:
	SQLSTATE [VALUE] sql_state_value
	|condition_name
	| SQLWARNING
	| NOT FOUND
	| SQLEXCEPTION
	| mysql_error_code

3.2.2 参数说明

handler_type 为 错误处理方式，参数取3个值：CONTINUE、EXIT和UNDO。
CONTINUE 表示遇到错误不处理，继续执行；
EXIT遇到错误马上退出；
UNDO表示遇到错误后撤回之前的操作，MySQL中暂时不支持这样的操作。
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匹配数值类型错误代码。
sp_statement 参数为程序语句段，表示在遇到定义的错误时，需要执行的存储过程或函数。

3.2.3 示列

-- 方法一：捕获 sql_state_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42802' SET @info='NO_SUCH_TABLE';

-- 方法二：捕获 mysql_error_code
DECLARE CONTINUE HANDLER FOR 1146 SET @info='NO_SUCH_TABLE';

-- 方法三：先定义条件，然后调用
DECLARE no_such_table CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR NO_SUCH_TABLE SET @info='NO_SUCH_TABLE';

-- 方法四：使用SQLWARNING
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';

-- 方法五：使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='NO_SUCH_TABLE';

-- 方法六：使用SQL EXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

上述代码是6种定义处理程序的方法。
第一种方法是捕获sqlstate_value值。如果遇到sqlstate_value值为“42S02”，执行CONTINUE 操作，并且输出“NO_SUCH_TABLE”信息。
第二种方法是捕获mysqlerrorcode值。如果遇到mysqlerrorcode值为1146,执行CONTINUE 操作，并且输出“NQ_SUCH_TABLE”信息。
第三种方法是先定义条件，然后再调用条件。这里先定义no_such_table条件，遇到1146错误就执CONTINUE操作。
第四种方法是使用SQLWARNING。SQLWARNING捕获所有以01开头的sqlstate_value值，然后执行EXIT操作，并且输出“ERROR”信息。
第五种方法是使用NOT FOUND。 NOT FOUND捕获所有以02开头的sqlstate_value值，然后执行EXIT操作，并且输出“NO_SUCH_TABLE”信息。
第六种方法是使用SQLEXCEPTION。SQLEXCEPTION捕获所有没有被SQLWARNING或 NOT FOUND捕获的sqlstate_value值，然后执行EXIT操作，并且输出“ERROR”信息。

`四、光标的使用`

査询语句可能返回多条记录，如果数据量非常大，需要在存储过程和储存函数中使用光标来逐条读取査询结果集中的记录。应用程序可以根据需要滚动或浏览其中的数据。

4.1 声明光标

-- cursor_name参数表示光标的名称；
-- select_slatement参数表示SELECT语句的内容，返回一个用于创建光标的结果集。
DECLARE cursor_name CURSOR FOR select_statement

DECLARE cursor_fruit CURSOR FOR SELECT f_name, f_price FROM fruit;

4.2 打开光标

OPEN cursor_name;

4.3 使用光标

4.3.1 语法

FETCH cursor_name INTO var_name1［, var_name2］...{参数名称}

4.3.2 参数描述

cursor_name参数表示光标的名称；
var_name参数表示将光标中的SELECT语句査询岀来的信息存入该参数中，var_name必须在声明光标之前就定义好。

4.3.3 示列

将光标cursor_fruit中SELECT语句査询出来的信息存入fruit_name 和 fruit_price 中。fruit_name和fruit_price必须在前面已经定义

FETCH cursor_fruit INTO fruit_name, fruit_price;

4.4 关闭光标

CLOSE cursor_name;

`五、流程控制的使用`

5.1 IF语句

5.1.1 语法

IF实现了一个基本的条件构造。如果expr_condition求值为真(TRUE),相应的SQL语句列表被执行：如果没有expr_condition匹配，则ELSE子句里的语句列表被执行。statement list可以包括一个或多个语句。

IF expr_condition THEN statement list
	[ELSEIF expr_condition THEN statement_list]...
	[ELSE statement_list]
END IF

5.1.2 示列

IF val IS NULL THEN SELECT 'val is NULL1';
	ELSE SELECT 'val is not NULL';
END IF;

== 5.2 CASE语句==

5.2.1 语法1

-- case_expr参数表示条件判断的表达式，决定了那一个WHEN子句会被执行;
-- when_value 参数表示表达式可能的值，如果某个when_value表达式与case_expr表达式结果相同，则执行对应THEN关键字后的statement_list中的语句;
-- statement_list参数表示不同when_value值的执行语句。
CASE case_expr
	WHEN when_value THEN statement_list
	[WHEN when_value THEN statement_list]...
	[ELSE statement_list]
END CASE

5.2.2 语法2

-- expr_condition参数表示条件判断语句；
-- statement_ist参数表示不同条件的执行语句; 该语句中，WHEN语句将被逐个执行，直到某个expr_condilion表达式为真，则执行对应THEN关键字后面的statement_ist语句。如果没有条件匹配，ELSE子句里的语句被执行。
CASE
	WHEN expr condition THEN statement list
	[WHEN expr condition THEN statement list]...
	[ELSE statementjist]
END CASE

5.2.2 示列

CASE val 
	WHEN 1 THEN SELECT 'val is 1';
	WHEN 2 THEN SELECT 'val is 2';
	ELSE SELECT 'val is not 1 or 2';
END CASE;

CASE
	WHEN val IS NULL THEN SELECT 'Pal is NULL1';
	WHEN val < 0 THEN SELECT 'val is less than O';
	WHEN val > 0 THEN SELECT 'val is greater than 0';
	ELSE SELECT 'val is 0';
END CASE;

5.3 LOOP语句

LOOP循环语句用来重复执行某些语句，与IF和CASE语句相比，LOOP只是创建一个循环操作的过程，并不进行条件判断。LOOP内的语句一直重复执行直到循环被退出，跳出循环过程，使用LEAVE子句。

5.3.1 语法

[loop_label:] LOOP
	statement_list
END LOOP [loop_label]

5.3.2 示列

DECLARE id INT DEFAULT 0;
add_loop: LOOP
SET id = id + 1;
	IF id >= 10 THEN LEAVE add_loop;
	END IF;
END LOOP add_loop;

5.4 LEAVE语句

LEAVE语句用来退岀任何被标注的流程控制构造。

5.4.1 语法

LEAVE label;

5.4.2 示列

add_num: LOOP
	SET @count = @count+l;
	IF @count = 50 THEN LEAVE add_num;
END LOOP add num;

5.5 ITERATE 语句

ITERATE语句将执行顺序转到语句段开头处。
ITERATE只可以出现在LOOP、REPEAT和WHILE语句内„ ITERATE的意思为"再次循环"， label参数表示循环的标志。ITERATE语句必须跟在循环标志前面。

5.5.1 语法

ITERATE label;

5.5.2 示列

pl=0,如果pl的值小于10时，重复执行pl加1操作；当pl大于等于10并且小于20时，打印消息"pl is between 10 and 20";.当pl大于20时，退出循环。

CREATE PROCEDURE doiterate()
BEGIN
	DECLARE pl INT DEFAULT 0;
	my_loop: LOOP
		SET pl = pl + 1;
		IF pl < 10 THEN ITERATE my_loop;
		ELSEIF pl > 20 THEN LEAVE my_loop;
		END IF;
		SELECT 'pl is between 10 and 20';
	END LOOP my_loop;
END

5.6 REPEAT 语句

REPEAT语句创建-个带条件判断的循环过程，每次语句执行完毕之后，会对条件表达式进行判断，如果表达式为真，则循环结束；否则重复执行循环中的语句。

5.6.1 语法

repeat_label为REPEAT语句的标注名称，该参数可以省略；
REPEAT语句内的语句或语句群被重复，直至expr_condition为真。

[repeat label:] REPEAT
	statement_list
UNTIL expr_condition
END REPEAT [repeat_label]

5.6.2 示列

该示例循环执行id加1的操作。当id值小于10时，循环重复执行：当id值大于或者等于10 时，使用LEAVE语句退出循环。REPEAT循环都以END REPEAT结束。

DECLARE id INT DEFAULT 0;
REPEAT
	SET id = id + 1;
	UNTIL id >= 10
END REPEAT;

5.7 WHILE 语句

WHILE语句创建一个带条件判断的循环过程，与REPEAT不同，WHILE在执行语句执行时，先对指定的表达式进行判断，如果为真，则执行循环内的语句，否则退出循环。

5.7.1 语法

while_label为WHILE语句的标注名称；
expr_condition为进行判断的表达式，如果表达式结果为真，WHILE语句内的语句或语句群被执行，直至expr_condition为假，退出循环。

[while_label:] WHILE expr_condition DO
	statement_list
END WHILE [while_label]

5.7.2 示列

DECLARE iINT DEFAULT 0;
WHILE i< 10 DO
	SET i = i + 1;
END WHILE;

`六、调用存储过程和函数`

6.1 调用存储过程

CALL sp_name([parameter[,...]])

DELIMITER//

CREATE PROCEDURE CountProc (IN sid INT, OUT num INT)
BEGIN
	SELECT COUNT(*) INTO num FROM fruits WHERE s_id = sid;
END //
DELIMITER;

CALL CountProc (101, @num);
select @num;

6.2 调用存储函数

在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法是一样的。换言之,用户自己定义的存储函数与MySQL内部函数是一个性质的。区别在于，存储函数是用户自己定义的，而内部函数是MySQL的开发者定义的。

DELIMITER //

CREATE FUNCTION CountProc (sid INT)
RETURNS INT
BEGIN
	RETURN (SELECT COUNT(*) FROM fruits WHERE s_id = sid);
END//

DELIMITER;

 SELECT CountProc(101);

`七、查看存储过程和函数`

7.1 SHOW STATUS语句查看存储过程和函数的状态

7.1.1 语法

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

这个语句是一个MySQL的扩展。它返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。如果没有指定样式，根据使用的语句，所有存储程序或存储函数的信息都被列出。 PROCEDURE和FUNCTION分别表示查看存储过程和函数；LIKE语句表示匹配存储过程或函数的名称。

7.1.2 示列：查看数据库中所有名称以字母’C’开头的存储过程信息

SHOW PROCEDURE STATUS LIKE 'C%'\G;

7.2 SHOW CREATE语句查看存储过程和函数的定义

7.2.1 语法

这个语句是一个MySQL的扩展。类似于SHOW CREATE TABLE,它返回一个可用来重新创建巳命名子程序的确切字符串。PROCEDURE和FUNCTION分别表示查看存储过程和函数：LIKE 语句表示匹配存储过程或函数的名称。

SHOW CREATE {PROCEDURE | FUNCTION} sp_name;

7.2.2 示列：查看test数据库中名称为CountProc 的存储过程

SHOW CREATE FUNCTION test.CountProc \G;

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

从Routines表中查询名称为CountProc的存储函数的信息。

SELECT * FROM information_schema.Routines
WHERE ROUTINE_NAME='CountProc' AND ROUTINE_TYPE = 'FUNCTION' \G

`八、修改存储过程和函数`

8.1 语法

ALTER (PROCEDURE | FUNCTION} sp_name [characteristic ...]

8.2 参数解释

sp_name 参数表示存储过程或函数的名称;
characteristic 参数指定存储函数的特性，可能的取值有：
• CONTAINS SQL表示子程序包含SQL语句，但不包含读或写数据的语句.
• NO SQL表示子程序中不包含SQL语句.
• READS SQL DATA表示子程序中包含读数据的语句.
• MODIFIES SQL DATA表示子程序中包含写数据的语句。
• SQL SECURITY { DEFINER | INVOKER }指明谁有权限来执行.
• DEFINER表示只有定义者自己才能够执行.
• INVOKER表示调用者可以执行.
• COMMENT 'string’表示注释信息。

修改存储过程使用ALTER PROCEDURE语句，修改存储函数使用ALTER FUNCTION 语句。但是，这两个语句的结构是一样的，语句中的所有参数也是一样的。而且，它们与创建存储过程或函数中的参数也是基本一样的。

8.3 示列

修改存储过程CountProc的定义。将读写权限改为MODIFIES SQL DATA,并指明调用者可以执行。

ALTER PROCEDURE CountProc 
MODIFIES SQL DATA
SQL SECURITY INVOKER;

`九、删除存储过程`

9.1 语法

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

9.2 示列

DROP PROCEDURE CountProc;
DROP FUNCTION CountProc;

书香水墨

关注

1
点赞
踩
10

收藏

觉得还不错? 一键收藏
打赏
1
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录