MySQL | 存储过程和函数

目录

一、创建存储过程和函数

1.1 创建存储过程

1.2 创建存储函数

1.3 变量的使用

1.3.1 定义变量

1.3.2 为变量赋值

1.4 定义条件和处理程序

1.4.1 定义条件

1.4.2 定义处理程序

1.5 光标的使用

1.5.1 声明光标

1.5.2 打开光标

1.5.3 使用光标

1.5.4 关闭光标

1.6 流程控制的使用

1.6.1 IF语句

1.6.2 CASE语句

1.6.3 LOOP语句

1.6.4 LEAVE语句

1.6.5 ITERATE语句

1.6.6 REPEAT语句

1.6.7 WHILE语句

二、调用存储过程和函数

2.1 调用存储过程

2.2 调用存储函数

三、查看存储过程和函数

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

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

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

四、修改存储过程和函数

五、删除存储过程和函数

---

简单地说，存储过程就是一条或者多条SQL语句的集合，可视为批文件，但是其作用不仅限于批文件。

一、创建存储过程和函数

存储程序可以分为存储过程和函数。在MySQL中，创建存储过程和函数使用的语句分别是CREATE PROCEDURE和CREATE FUNCTION。使用CALL语句来调用存储过程，只能用输出变量返回值。函数可以从语句外调用（引用函数名），也能返回标量值。存储过程也可以调用其他存储过程。

1.1 创建存储过程

创建存储过程，需要使用CREATE PROCEDURE语句，基本语法格式如下：

CREATE PROCEDURE sp_name ([proc_parameter]) [characteristics ...] routine_body

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表示结果是是确定的。每次执行存储过程时，相同的输入会得到相同的输出。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代码的开始和结束。

例：创建一个查看furits表的存储过程，每次调用这个存储过程的时候都会执行SELECT语句查看表的内容，代码的执行过程如下：

DELIMITER //
CREATE PROCEDURE Proc()
BEGIN
SELECT * FROM fruits
END //

DELIMITER;

提示：
“DELIMITER //”语句的作用是将MySQL的结束符设置为//，因为MySQL默认的语句结束符符号为分号‘；’。为了避免与存储过程中SQL语句结束符相冲突，需要使用DELIMITER改变存储过程的结束符，并以“END //”结束存储过程。存储过程定义完毕之后再使用“DELIMITER;”恢复默认结束符。DELIMITER也可以指定其他符号作为结束符。

例：创建名称为CountProc的存储过程，代码如下：

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

DELIMITER;

1.2 创建存储函数

创建存储函数，需要使用CREATE FUNCTION语句，基本语法格式如下：

CREATE FUNCTION func_name ([func_parameter])
RETURNS type
[characteristics ...] routine_body

CREATE FUNCTION为用来创建存储函数的关键字；func_name表示存储函数的名称；func_parameter为存储过程的参数列表，参数列表形式如下：

[ IN | OUT | INOUT ] param_name type

其中，IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出；param_name表示参数名称；type表示参数的类型，该类型可以是MySQL数据库中的任意类型。

RETURNS type语句表示函数返回数据的类型；characteristic指定存储函数的特性，取值与创建存储过程时相同。

例：创建一个存储函数NameByZip,参数定义为空，返回一个INT类型的结果。代码如下：

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

DELIMITER;

如果在存储函数中的RETURN语句返回一个类型不同于函数RETURNS子句中指定类型的值，返回值将被强制为恰当的类型。比如，如果一个函数返回一个ENUM或SET值，但是RETURN语句返回一个整数，对于SET成员集相应的ENUM成员，从函数返回的值是字符串。

提示：

指定参数为IN、OUT或INOUT只对PROCEDURE是合法的。（FUNCTION中总是默认为IN参数）。RETURNS子句只能对FUNCTION做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。

1.3 变量的使用

变量可以在子程序中声明并使用，这些变量的作用范围是在BEGIN...END程序中。

1.3.1 定义变量

在存储过程中使用DECLARE语句定义变量，语法格式如下：

DECLARE var_name [,var_name]... date_type [DEFAULT value];

如果没有DEFAULE子句，初始值为NULL。

例：定义名称为myparam的变量，类型为INT类型，默认值为100，代码如下：

DECLARE myparam INT DEFAULT 100;

1.3.2 为变量赋值

定义变量之后，为变量赋值可以改变变量的默认值。在MySQL中，使用SET语句为变量赋值，语法格式如下：

SET var_name = expr [,var_name=expr] ...;

在存储程序中的SET语句是一般SET语句的扩展版本。

例：声明3个变量，分别为var1、var2和var3，数据类型为INT，使用SET为变量赋值，代码如下：

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

在MySQL中，还可以通过SELECT...INTO为一个或多个变量赋值，语法如下：

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

 例：声明变量fruitname和fruitprice，通过SELECT...INTO语句查询指定记录并为变量赋值，代码如下：

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

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

1.4 定义条件和处理程序

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

1.4.1 定义条件

定义条件使用DECLARE语句，语法格式如下：

DECLARE condition_name CONDITION FOR [condition_type]

[condition_type]:
SQLSTATE [VALUE] sqlstate_value | mysql_error_code

sqlstate_value和mysql_error_code都可以表示MySQL的错误，sqlstate_value为长度为5的字符串类型错误代码，MySQL_err_code为数值类型错误代码。例如，在ERROR 1142(42000)中，sqlstate_value的值是42000，MySQL_error_code的值是1142。

这个语句指定需要特殊处理的函数。它将一个名字和指定的错误条件关联起来。这个名字可以随后被用在定义处理程序的DECLARE HANDLER语句中。

例：定义“ERROR 1148(42000)”错误，名称为command_not_allowed。可以用另种不同的方法来定义，代码如下：

//方法一：使用sqlstate_value
DECLARE command_bot_allowed CONDITION FOR SQLSTATE '42000';
//方法二：使用mysql_error_code
DECLARE command_not_allowed CONDITION FOR 1148

1.4.2 定义处理程序

定义处理程序时，使用DECLARE语句的语法如下：

DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement
handler_type:
    CONTINUE | EXIT | UNDO

condition_value:
    SQLSTATE [VALUE] sqlstate_value
| condition_name
| SQLWARNING
| NOT FOUND
| SQLEXCEPTION
| mysql_error_code

其中，handler_type为错误处理方式，参数取2个值：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参数为程序语句段，表示在遇到定义的错误时需要执行的存储过程或函数。

例：定义处理程序的几种方式，代码如下：

//方法一：捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02' 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';

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

1.5 光标的使用

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

光标必须在声明处理程序之前被声明，并且变量和条件还必须在声明光标或处理程序之前被声明。

1.5.1 声明光标

在MySQL中，使用DECLARE关键字来声明光标，其语法的基本格式如下：

DECLARE cursor_name CURSOR FOR select_statement

例：声明名称为cursor_fruit的光标，代码如下：

DECLARE cursor_fruit CURSOR FOR SELECT f_name,f_price FROM fruits;

1.5.2 打开光标

打开光标的语法如下：

OPEN cursor_name{光标名称}

例：打开名称为cursor_fruit的光标，代码如下：

OPEN cursor_fruit;

1.5.3 使用光标

使用光标的语法如下：

FETCH cursor_name INTO var_name [,var_name] ... {参数名称}

例：使用名称为cursor_fruit的光标将查询出来的数据存入fruit_name和fruit_price这两个变量中，代码如下：

FETCH cursor_fruit INTO fruit_name,fruit_price;

1.5.4 关闭光标

关闭光标的语法如下：

CLOSE cursor_name{光标名称}

这个语句关闭先前打开的光标。

如果未被明确地关闭，光标在它被声明的复合语句的末尾关闭。

提示：
MySQL中光标只能在存储过程和函数中使用。

1.6 流程控制的使用

流程控制语句用来根据条件控制语句的执行。MySQL中用来构造流程控制的语句有IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE语句。

每个流程中可能包含一个单独语句，或者是使用BEGIN...END构造的复合句，构造可以被嵌套。

1.6.1 IF语句

IF语句包含多个判断条件，根据判断的结果为TRUE或FAUSE执行相应的语句。

提示：

MySQL中还有一个IF()函数，不同于这里描述的IF语句。

例：

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

该示例判断val值是否为空，如果val值为空，输出字符串“val is NULL”；否则输出字符串“val is not NULL”。IF语句都需要使用END IF来结束。

1.6.2 CASE语句

CASE语句是另一个进行条件判断的语句。

例1：

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;

例2：

CASE
    WHEN val is NULL THEN SELECT 'val is null';
    WHEN val < 0 THEN SELECT 'val is less than 0';
    WHEN val > 0 THEN SELECT 'val is greater then 0';
    ELSE SELECT 'val is 0';
END CASE;

提示：
这里介绍的用在存储程序里的CASE语句与“控制流程函数”里描述的SQL CASE表达式的CASE语句有轻微不同。这里的CASE语句不能有ELSE NULL子句，并且用END CASE替换END来终止。

1.6.3 LOOP语句

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

例：使用LOOP语句进行循环操作，id值小于10时将重复执行循环过程，代码如下：

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;

1.6.4 LEAVE语句

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

LEAVE和BEGIN...END或循环一起被使用。

例：

add_num:LOOP
SET @count=count+1;
IF @count=50 THEN LEAVE add_num;
END LOOP add_num;

1.6.5 ITERATE语句

ITERATE语句将执行顺序转到语句开头段开头处。

ITERATE只可以出现在LOOP、REPEAT和WHILE语句内。ITERATE语句必须跟在循环标志前面。

例：

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

1.6.6 REPEAT语句

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

例：

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

1.6.7 WHILE语句

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

例：

DECALRE i INT DEFAULT 0;
WHILE i < 10  DO
SET i=i+1;
END WHILE;

二、调用存储过程和函数

2.1 调用存储过程

存储过程是通过CALL语句进行调用的。

例：定义名为CountProc1的存储过程，然后调用这个存储过程。

定义存储过程：

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

DELIMITER;

调用存储过程：

CALL CountProc1 (101,@num);

查看返回结果：

SELECT @num;

2.2 调用存储函数

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

例：定义存储函数CountProc2，然后调用这个函数，代码如下：

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

DELIMITER;

注意：如果在创建存储函数中报错“you *might* want to use the less safe log_bin_trust_function_creators variable"，需要执行以下代码：

SET GLOBAL log_bin_trust_function_creators=1;

调用存储函数：

SELECT CountProc2(101);

三、查看存储过程和函数

MySQL存储了存储过程和函数的状态信息，用户可以使用SHOW STATUS语句或者SHOW CREATE语句来查看，也可直接从系统的information_schema数据库中查询。

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

例：

SHOW PROCEDURE STATUS LIKE 'C%' \G

这个代码语句获取数据库中所有名称以字母'C'开头的存储过程的信息。

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

例：

SHOW CREATE FUNCTION test_db.CountProc2 \G

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

MySQL中存储过程和函数的信息存储在information_schema数据库下的Routines表中，可以通过查询该表的记录来查询存储过程和函数的信息。

例：从Routines表中查询名称为CountProc2的存储函数的信息，代码如下：

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

如果有存储过程和存储函数名称相同，就需要同时制定ROUTINE_TYPE字段表明查询的是哪种类型的存储程序。

四、修改存储过程和函数

使用ALTER语句可以修改存储过程或函数的特性。

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

其中，sp_name参数表示存储过程或函数的名称；characteristic参数指定存储函数的特性，可能的取值有：

• CONTAINS SQL，表示子程序包含SQL语句，但不包含读或写数据的语句。
• NOT SQL，表示子程序中不包含SQL语句。
• READS SQL DATA，表示子程序中包含读数据的语句。
• MODIFIES SQL DATA，表示子程序包含写数据的语句。
• SQL SECURITY {DEFINER|INVOKER}，知名谁有权限来执行。
• DEFINER，表明只有定义者自己才能够执行。
• INVOKER，表明调用者可以执行。
• COMMENT 'string'，表明注释信息。

注意：
修改存储过程使用ALTER PROCEDURE语句，修改存储函数使用ALTER FUNCTION语句。

例1：修改存储过程CountProc的定义。将读写权限改为MODIFIES SQL DATA，并指明调用者可以执行，代码如下：

ALTER PROCEDURE CountProc
MODIFIES SQL DATA
SQL SECURITY INVOKER;

例2：修改存储函数CountProc的定义。将读写权限改为READS SQL DATA，并加上注释信息“FIND NAME”，代码如下：

ALTER FUNCTION CountProc
READS SQL DATA
COMMENT 'FIND NAME';

五、删除存储过程和函数

删除存储过程和函数，可以使用DROP语句，其语法结构如下：

DROP {PROCEDURE|FUNCTION} [IF EXISTS] sp_name

IF ENIXTS子句是一个MySQL的扩展。如果程序或函数不存储，它可以防止发生错误，产生一个用SHOW WARNING查看的警告。

例：

DROP PROCEDURE CountProc;
DROP FUNCTION CountProc;

上面语句的作用就是删除存储过程CountProc和存储函数CountProc。