存储过程和函数

表示执行结果不确定。 {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}：指明子程序使用SQL语句的限制。 SQL SECURITY { DEFINER | INVOKER }：指定可执行存储过程的用户，DEFINER表示只有创建者才能执行，
➢ 2.3 调用存储过程和函数
2
调用存储函数
通常使用关键字SELECT调用存储函数，其语法形式如下：
SELECT function_name([parameter[,……]]);
【实例4】
调用实例13-2创建的存储函数func()，查看其返回值。
首先登录MySQL，并选择数据库db_shop。SQL语句及其执行结果如下：
下面以名为“not_found_database”的条件“ERROR 1049(42000)”的定义为例，来看看这两 种形式的区别。
DECLARE not_found_database CONDITION FOR 1049;
步骤1 使用Navicat for MySQL连接MySQL后，双击需要操作的数据库“test_db”，然后单击“函数”按 钮。 步骤2 单击“新建函数”按钮，选择需要创建的类型，此处选择创建存储过程，如图13-1所示。
➢ 2.4 使用图形化工具创建存储过程和函数
步骤3 在编辑区填写存储过程需要的参数， 单击编辑区左下方的“+”按钮可以添加参数，单 击“－”按钮可以删除参数，如果存储过程没有参 数，直接单击“完成”按钮即可，如图13-2所示。
存储过程和函数
存储过程和函数概述
1
针对表的一个完整操作往往不是单条SQL语句就能实现的，而是需要一组SQL语句来实现。 例如，要完成一个购买商品的订单的处理，一般需要考虑以下几步：
（1）在生成订单之前，首先需要查看商品库存中是否有相应商品；
（2）如果商品库存中不存在相应商品，需要向供应商订货；
（3）如果商品库存中存在相应商品，需要预定商品，并修改库存数量。 可将一个完整操作中所包含的多条SQL语句创建为存储过程或函数，以方便应用。 存储过程和函数可以简单地理解为一组经过编译并保存在数据库中的SQL语句的集合，可以随时
mysql> DELIMITER $$ mysql> CREATE PROCEDURE proc(OUT num INT)
-> BEGIN -> SELECT COUNT(*) INTO num FROM goods; -> END $$ Query OK, 0 rows affected (0.03 sec)
mysql> DELIMITER;
➢ 3.2 定义条件和处理程序
条件和处理程序是MySQL提供的一种异常处理机制，定义条件是事先定义程序执行过程中可能会
遇到的问题；定义处理程序是定义在遇到问题时执行的相应处理方法，并且保证存储过程和函数在遇
到问题时不终止。
1
定义条件
提示
数值类型的错误代码不要使用0，因为0表示成功而不是错 误；字符串类型的错误代码不要使用'00'，因为'00'表示成功 而不是错误。
DECLARE var1 INT;
提示
变量的定义必须在复合语句开头，并且在任何其他语句前面。也就是说，DECLARE 语句在存储过程和函数中使用时，必须出现在BEGIN…END语句块的最前面，并且变量 名不区分大小写。可以一次声明多个相同类型的变量。
➢ 3.1 变量
2）为变量赋值 定义变量之后，可以使用SET关键字为变量赋值，语法形式如下：
CREATE PROCEDURE proc_name ( [proc_parameter[,…] ) [characteristic…] routine_body
proc_parameter表示存储过程的参数，参数形式如下：
[ IN | OUT | INOUT ] parameter_name TYPE
在创建存储函数前首先登录MySQL，并选择数据库db_shop。SQL语句的执行结果如下：
mysql> DELIMITER $$ mysql> CREATE FUNCTION func()
-> RETURNS INT(11) -> RETURN (SELECT COUNT(*) FROM goods) -> $$ Query OK, 0 rows affected (0.03 sec)
➢ 2.4 使用图形化工具创建存储过程和函数
步骤4 在BEGIN…END语句中编辑需要执行的SQL语句，如图13-3所示。
➢ 2.4 使用图形化工具创建存储过程和函数
步骤5 在“高级”选项卡中可以设置存储程序的特性，最后单击“保存”按钮，输入名称确定即可，如图 13-4所示。
关于存储过程和函数的表达式
局部变量（Local Variables）：没有前缀，一般用于SQL语句块中，比如存储过程的BEGIN…END 中。局部变量使用前需要先通过DECLARE声明。如没有声明，则初始值为NULL。
系统变量（Server System Variables）：带有前缀@@，MySQL有许多已经设置默认值的系统变 量。系统变量包含全局变量和会话变量。全局变量会影响整个服务器，而会话变量只影响个人客户 端连接。
mysql> SELECT func(); +----------+ | func() | +----------+ | 10 | +----------+ 1 row in set (0.04 sec)
➢ 2.4 使用图形化工具创建存储过程和函数
使用Navicat for MySQL也可以创建存储过程和函数，具体操作如下。
创建存储函数使用SQL语句CREATE FUNCTION来实现，其语法形式如下：
CREATE FUNCTION func_name ( [ parameter_name [,…] ) RETURNS TYPE [characteristic…] routine_body
【实例2】
创建一个名为func的简单存储函数，用于获取goods表中的记录数。
SET var_name = expr [,var_name = expr] …;
为前面定义的变量var1赋值，具体如下：
SET var1 = 3;
变量值可以为常量或询语句将查询结果赋给变量，这要求查询结果必须只有一行， 具体语法形式如下：
SELECT col_name[,……] INTO var_name[,……] FROM table_name;
➢ 3.1 变量
2
在存储过程和函数中应用变量
局部变量可以在子程序中定义并应用，其作用范围是BEGIN…END语句块。 1）定义变量 在存储过程中使用DECLARE语句定义局部变量，其语法形式如下：
DECLARE var_name[,…] type [DEFAULT value];
例如，定义一个INT类型的变量，名称为var1：
在MySQL中定义条件使用DECLARE…CONDITION语句，其语法形式如下：
DECLARE condition_name CONDITION FOR [condition_type];
mysql_error_code：表示数值类型错误代码。 sqlstate_value：表示长度为5的字符串类型错误代码。
mysql> SELECT @num;
+----------+
| @num |
+----------+
| 10
|
+----------+
1 row in set (0.01 sec)
调用存储过程的执行结果与直接执行查询语句 SELECT COUNT(*) FROM goods;的执行结果相 同，但是存储过程的好处在于处理逻辑都封装在数 据库端，调用者不需要了解中间的处理逻辑，当处 理逻辑发生变化时，只需要修改存储过程即可，而 对调用者的程序完全没有影响。
characteristic表示存储过程的特性，可取值及其意义如下：
LANGUAGE SQL：表示存储过程的routine_body部分使用SQL语言编写。 [NOT]DETERMINISTIC：DETERMINISTIC表示存储过程的执行结果是确定的；默认为NOT DETERMINISTIC，
INVOKER表示拥有权限的调用者可以执行。 COMMENT 'string'：表示存储过程或者函数的注释信息。
➢ 2.1 创建存储过程
【实例1】
创建一个名为proc的简单存储过程，用于获取goods表中的记录数。
在创建存储过程前首先登录MySQL，并选择数据库db_shop。然后执行以下语句：
mysql> DELIMITER;
提示
“DELIMITER $$”的作用是将语句的结束符“;”修改为“$$”，这样存储过程中的SQL 语句结束符“;”就不会被MySQL解释成语句的结束而提示错误。在存储过程创建完成后，应 使用“DELIMITER ;”语句将结束符修改为默认结束符。
➢ 2.2 创建存储函数
被调用。
允许标准组件式编程：存储过程和函数在创建后可以在程序中被多次调用。 较快的执行速度：如果某一操作包含大量的事务处理代码，并且被多次执行，那么存储过程要比批处理的执行
速度快很多。 减少网络流量：对于大量的SQL语句，将其组织成存储过程，会比一条一条的调用SQL语句要大大节省网络流量，
降低网络负载。 安全：数据库管理员通过设置执行某一存储过程的权限，从而限制相应数据的访问权限，避免非授权用户对数
据的访问，保证数据的安全。
创建并调用存储过程和函数
2
➢ 2.1 创建存储过程
存储程序可以分为存储过程和函数。存储过程和函数的操作主要包括创建存储过程和函数、调用 存储过程和函数、查看存储过程和函数，以及修改和删除存储过程和函数。
创建存储过程使用SQL语句CREATE PROCEDURE来实现，其语法形式如下：
3
➢ 3.1 变量
本节详细介绍存储过程和函数中所包含的表达式语句。此处的表达式同其他高级语言中一样，主 要由变量、运算符和流程控制语句构成。
变量是表达式中最基本的元素，可用于存储临时数据。本节简单介绍变量的分类，以及在存储过 程和函数中应用变量的方法。
1
变量的分类
用户变量（User-Defined Variables）：带有前缀@，只能被定义它的用户使用，作用于当前整个 连接，当前连接断开后，所定义的用户变量会被全部释放。用户变量不用提前定义就可以直接使用。
1
调用存储过程
通常使用关键字CALL调用存储过程，其语法形式如下：
CALL procedure_name([parameter[,…]]);
【实例3】
调用实例13-1创建的存储过程proc()，查看其返回值。
首先登录MySQL，并选择数据库db_shop。SQL语句及其执行结果如下：
mysql> CALL proc(@num); Query OK, 1 row affected (0.06 sec)
➢ 3.1 变量
【实例5】
执行SQL语句，定义变量并为其赋值。
首先登录MySQL，并选择数据库db_shop。然后创建存储过程并定义变量，SQL语句及其执行结 果如下：
mysql> DELIMITER $$ mysql> CREATE PROCEDURE proc1()
-> BEGIN -> DECLARE var1,var2,var3,g_id INT; -> SET var1=1,var2=2; -> SET var3=var1+var2; -> SELECT id into g_id FROM goods WHERE id=1; -> END $$ Query OK, 0 rows affected (0.10 sec)
mysql> DELIMITER;
提示
RETURNS TYPE子句对于存储函数而言是必须存在的，如果RETURN子句返回值的数据 类型与RETURNS TYPE子句指定的数据类型不同，MySQL会将返回值强制转换为RETURNS TYPE子句指定的类型。
➢ 2.3 调用存储过程和函数
存储过程必须使用关键字CALL调用，而存储函数与MySQL内置函数的调用相同，使用关键字 SELECT。