登录 注册

MySQL 存储过程和函数（九）

极客说 记录精彩的程序人生 本文由博客端 http://qntop.com 主动推送

存储过程和函数

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

创建存储过程和函数

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

创建存储过程

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

    DELIMITER //   --将结束符号指定为//
CREATE PROCEDURE sp_name([proc_parameter])
[characteristics...] BEGIN routine_body;
END //

    CREATE PROCEDURE 为用来创建存储函数的关键字；sp_name 为存储过程的名称；routine_body 是 SQL 代码的内容，可以用 BEGIN...END 来表示 SQL 代码的开始和结束；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 | MODIFY SQL DATA }：指明子程序使用 SQL 语句的限制。CONTAINS SQL 表明子程序包含 SQL 语句，但是不包含读写数据的语句；NO SQL 表明子程序不包含 SQL 语句；READS SQL DATA 说明子程序包含读数据的语句；MODIFYS SQL DATA 表明子程序包含写数据的语句。默认情况下，系统会指定为 CONTAINS SQL。
    • SQL SECURITY { DEFINER | INVOKER }：指明谁有权限来执行。DEFINER 表示只有定义者才能执行。INVOKER 表示拥有权限的调用者可以执行。默认情况下，系统指定为 DEFINER。
    • COMMENT 'string'：注释信息，可以用来描述存储过程或函数。

  • 提示：“DELIMITER //” 语句的作用是将 MySQL 的结束符设置为//，并以“END //”结束存储过程。存储过程定义完毕之后再使用 “DELIMITER ;” 恢复默认结束符。DELIMITER 也可以指定其他符号作为结束符。当使用 DELIMITER 命令时，应该避免使用反斜杠（‘\’）字符，因为反斜杠是 MySQL 的转义字符。

创建存储函数

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

    CREATE FUNCTION func_name( [func_parameter] )
RETURNS type
[characteristics ...] routine_body; 
--rountine_body里必须包含RETURN VALUE语句,格式为RETURN 返回内容;

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

    [ IN | OUT | INOUT ] param_name type;

    func_parameter 指定存储函数的参数列表，含义与创建存储过程时相同。

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

  • 提示：

    • 如果再存储函数中的 RETURN 语句返回一个类型不同于函数的 RETURNS 子句指定类型的值，返回值将被强制为恰当的类型。
    • 指定参数为 IN、OUT 或 INOUT 只对 PROCEDURE 是合法的。（FUNCTION 中总是默认为 IN 参数。）RETURNS 子句只能对 FUNCTION 做指定，对函数而言是强制的。它用来指定函数的返回类型，而且函数体必须包含一个 RETURN value 语句。

变量的使用

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

  1. 定义变量

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

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

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

  2. 为变量赋值

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

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

    在存储过程中的 SET 语句是一般 SET 语句的扩展版本。被参考变量可能是子程序内声明的变量，或者是全局服务器变量，如系统变量或者用户变量。其中不同的变量类型（局域声明变量及全局变量）可以被混合起来。这也允许把局部变量和一些只对系统变量有意义的选项合并起来。

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

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

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

定义条件和处理程序

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

  1. 定义条件

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

    DECLARE condition_name CONDITION FOR [condition_type]

[condition_type]:
SQLSTATE [VALUE] sqlstate_value | mysql_error_code

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

  2. 定义处理程序

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

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

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

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

光标的使用

  • 查询语句可能返回多条记录，如果数据量非常大，需要在存储过程和存储函数中使用光标来逐条读取查询结果集中的记录。应用程序可以根据需要滚动或浏览其中的数据。
  • 光标的必须在声明处理程序之前被声明，并且变量和条件还必须在声明光标或处理程序之前被声明。

  1. 声明光标

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

    DECLARE cursor_name CURSOR FOR select_statement;

    其中，cursor_name 参数表示光标的名称；select_statement 参数表示 SELECT 语句的内容，其中一个用于创建光标的结果集。

  2. 打开光标

    打开光标的语法如下：

    OPEN cursor_name{光标名称};

  3. 使用光标

    使用光标的语法如下：

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

    其中，cursor_name 参数表示光标的名称；var_name 参数表示将光标中的 SELECT 语句查询出来的信息存入该参数中，var_name 必须在前面已经定义。

  4. 关闭光标

    关闭光标的语法如下：

    CLOSE cursor_name {光标名称};
  • 提示：MySQL 中光标只能在存储过程和函数中使用。

流程控制的使用

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

  1. IF 语句

    • IF 语句包含多个条件判断，根据判断的结果为 true 或 false 执行相应的语句，语法格式如下：

      IF expr_condition THEN statement_list
	[ELSEIF expr_condition THEN statement_list]...
	[ELSE statement_list]
END IF;

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

  2. CASE 语句

    • CASE 是另一个进行条件判断的语句，有两种语句格式，CASE 语句的第 1 种格式如下：

      CASE case_expr
	WHEN when_value THEN statement_list
	[WHEN when_value THEN statement_list] ...
	[ELSE statement_list]
END CASE;

    • CASE 语句的第 2 种格式如下：

      CASE
	WHEN expr_condition THEN statement_list
	[WHEN expr_condition THEN statement_list] ...
	[ELSE statement_list]
END CASE;

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

  3. LOOP 语句

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

      [lOOP_label:] LOOP
	statement_list
END LOOP [lOOP_label];

      loop_label 表示 LOOP 语句的标注名称，该参数可以省略；statement_list 参数表示需要循环执行的语句。

  4. LEAVE 语句

    LEAVE 语句用来退出任何被标注的流程控制构造，LEAVE 语句的基本格式如下：

    LEAVE label;

    其中，label 参数表示循环的标志。LEAVE 和 BEGIN...END 或循环一起被使用。

  5. ITERATE 语句

    ITERATE 语句将执行顺序转到语句段开头处，语句基本格式如下：

    ITERATE label;

    ITERATE 只可以出现在 LOOP、REPEAT 和 WHILE 语句内。ITERATE 的意思为“再次循环”，label 参数表示循环的标志。ITERATE 语句必须跟在循环标志的前面。

  6. REPEAT 语句

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

    [repeat_label:] REPEAT
	statement_list
UNTIL expr_condition
END REPEAT [repeat_label];

    REPEAT 语句内的语句或语句群被重复，直到 expr_condition 为真。

  7. WHILE 语句

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

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

调用存储过程

  • 存储过程和函数有多种调用方法。存储过程必须使用 CALL 语句调用，并且存储过程和数据库相关，如果要执行其他数据库中的存储过程，需要指定数据库名称。存储函数的调用与 MySQL 中预定义的函数的调用方式相同。

调用存储过程

  • 存储过程就是通过 CALL 语句进行调用的，语法如下：

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

    CALL 语句调用一个先前用 CREATE PROCEDURE 创建的存储过程，其中 sp_name 为存储过程名称，parameter 为存储过程的参数。

调用存储函数

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

查看存储过程和函数

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

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

  • SHOW STATUS 语句可以查看存储过程和函数的状态，其基本语法结构如下：

    SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'patten'];

    这个语句是一个 MySQL 的扩展，它返回子程序的特征，如数据库、名字、类型、创建者及创建和修改日期。

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

  • MySQL 可以使用 SHOW CREATE 语句查看存储过程和函数的状态。

    SHOW CREATE {PROCEDURE | FUNCTION} sp_name;

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

  • MySQL 中存储过程和函数的信息存储在 information_schema 数据库下的 Routines 表中。可以通过查询该表的记录来查询存储过程和函数的信息。其基本语法格式如下：

    SELECT * FROM information_schema.Routines
WHERE ROUTINES_NAME = 'sp_name';

    其中，ROUTINES_NAME 字段中存储的是存储过程和函数的名称；sp_name 参数表示存储过程或函数的名称。

修改存储过程和函数

  • 使用 ALTER 语句可以修改存储过程或函数的特性。其基本语法格式如下：

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

    characteristic 参数指定存储函数的特性，可能的取值有：

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

删除存储过程和函数

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

    DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name;

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

注意事项

  • MySQL 存储过程和函数有什么区别？

    在本质上它们都是存储程序。函数只能通过 return 语句返回单个值或者表对象；而存储过程不允许执行 return，但是可以通过 out 参数返回多个值。函数限制比较多，不能使用临时表，只能用表变量，还有一些函数都不可用等，而存储过程的限制相对较少。函数可以嵌入 SQL 语句中使用，可以在 SELECT 语句中作为查询语句的一个部分调用；而存储过程一般是作为一个独立的部分来执行。

  • 存储过程中的代码可以改变吗？

    目前，MySQL 没有提供对已存在的存储过程代码的修改。如果必须要修改存储过程，就只能删除后重建或者新建。

  • 存储过程中可以调用其他存储过程吗？

    可以。但不能使用 DROP 语句删除其他存储过程。

  • 存储过程的参数可以使用中文吗？

    当传入的参数值可能是中文时，需要在定义存储过程的时候，在后面加上 character set gbk，不然调用存储过程中使用中文参数会出错。基本语法格式如下：

    CREATE PROCEDURE sp_name (IN|OUT|INOUT param_name param_type character set gbk [...])
  • MySQL

    MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    685 引用 • 535 回帖
  • 存储过程
    8 引用 • 20 回帖
  • 函数
    8 引用 • 27 回帖
  • 光标
    2 引用 • 1 回帖
佛山 位置
1 1

相关帖子

回帖
MySQL 存储过程和函数（九）

欢迎来到这里！

我们正在构建一个小众社区，大家在这里相互信任，以平等 • 自由 • 奔放的价值观进行分享交流。最终，希望大家能够找到与自己志同道合的伙伴，共同成长。

注册 关于
请输入回帖内容 ...
wangdj
佛山
回帖
1
 帖子
25
 积分
274