mysql存储过程详解mysql存储过程和函数
第20章：存储程序和函数
⽬录
20.1. 存储程序和授权表
20.2. 存储程序的语法
20.2.1. CREATE PROCEDURE和CREATE FUNCTION
20.2.2. ALTER PROCEDURE和ALTER FUNCTION
20.2.3. DROP PROCEDURE和和DROP FUNCTION
20.2.4. SHOW CREATE PROCEDURE和SHOW CREATE FUNCTION
20.2.5. SHOW PROCEDURE STATUS和SHOW FUNCTION STATUS
20.2.6. CALL语句
20.2.7. BEGIN ... END复合语句
20.2.8. DECLARE语句
20.2.9. 存储程序中的变量
20.2.10. 条件和处理程序
20.2.11. 光标
20.2.12. 流程控制构造
20.3. 存储程序、函数、触发程序和复制：常见问题
20.4. 存储⼦程序和触发程序的⼆进制⽇志功能
MySQL 5.1版⽀持存储程序和函数。⼀个存储程序是可以被存储在服务器中的⼀套SQL语句。⼀旦它被存储了，客户端不需要再重新发布单独的语句，⽽是可以引⽤存储程序来替代。
下⾯⼀些情况下存储程序尤其有⽤：
·
当⽤不同语⾔编写多客户应⽤程序，或多客户应⽤程序在不同平台上运⾏且需要执⾏相同的数据库操作之时。
· 安全极为重要之时。⽐如，银⾏对所有普通操作使⽤存储程序。这提供⼀个坚固⽽安全的环境，程序可以确保每⼀个操作都被妥善记⼊⽇志。在这样⼀个设置中，应⽤程序和⽤户不可能直接访问数据库表，但是仅可以执⾏指定的存储程序。
存储程序可以提供改良后的性能，因为只有较少的信息需要在服务器和客户算之间传送。代价是增加数据库服务器系统的负荷，因为更多的⼯作在服务器这边完成，更少的在客户端（应⽤程序）那边完成上。如果许多客户端机器（⽐如⽹页服务器）只由⼀个或少数⼏个数据库服务器提供服务，可以考虑⼀下存储程序。
存储程序也允许你在数据库服务器上有函数库。这是⼀个被现代应⽤程序语⾔共享的特征，它允许这样的内部设计，⽐如通过使⽤类。使⽤这些客户端应⽤程序语⾔特征对甚⾄于数据库使⽤范围以外的编程⼈员都有好处。
MySQL为存储程序遵循SQL:2003语法，这个语法也被⽤在IBM的DB2数据库上。
MySQL对存储程序的实现还在进度中。所有本章叙述的语法都被⽀持，在有限制或扩展的地⽅会恰当地指出来。有关使⽤存储程序的限制的更多讨论在附录 I, 特性限制⾥提到。
如20.4节，“存储⼦程序和触发程序的⼆进制⽇志功能”⾥所说的，存储⼦程序的⼆进制⽇志功能已经完成。
20.1. 存储程序和授权表
存储程序需要在mysql数据库中有proc表。这个表在MySQL 5.1安装过程中创建。如果你从早期的版本升级到MySQL 5.1 ，请确定更新你的授权表以确保proc表的存在。
在MySQL 5.1中，授权系统如下考虑存储⼦程序：
· 创建存储⼦程序需要CREATE ROUTINE权限。
· 提醒或移除存储⼦程序需要ALTER ROUTINE权限。这个权限⾃动授予⼦程序的创建者。
· 执⾏⼦程序需要EXECUTE权限。然⽽，这个权限⾃动授予⼦程序的创建者。同样，⼦程序默认的SQL SECURITY 特征是DEFINER，它允许⽤该⼦程序访问数据库的⽤户与执⾏⼦程序联系到⼀起。
20.2. 存储程序的语法
存储程序和函数是⽤CREATE PROCEDURE和CREATE FUNCTION语句创建的⼦程序。⼀个⼦程序要么是⼀个程序要么是⼀个函数。使⽤CALL语句来调⽤程序，程序只能⽤输出变量传回值。就像别其它函数调⽤⼀样，函数可以被从语句外调⽤（即通过引⽤函数名），函数能返回标量值。存储⼦程序也可以调⽤其它存储⼦程序。
在MySQL 5.1中，⼀个存储⼦程序或函数与特定的数据库相联系。这⾥有⼏个意思：
· 当⼀个⼦程序被调⽤时，⼀个隐含的USE db_name被执⾏（当⼦程序终⽌时停⽌执⾏）。存储⼦程序内的USE语句时不允许的。· 你可以使⽤数据库名限定⼦程序名。这可以被⽤来引⽤⼀个不在当前数据库中的⼦程序。⽐如，要引⽤⼀个与test数据库关联的存储程序p或函数f，你可以说CALL test.p()或test.f()。
· 数据库移除的时候，与它关联的所有存储⼦程序也都被移除。
MySQL ⽀持⾮常有⽤的扩展，即它允许在存储程序中使⽤常规的SELECT语句（那就是说，不使⽤光标或局部变量）。这个⼀个查询的结果包被简单地直接送到客户端。多SELECT语句⽣成多个结果包，所以客户端必须使⽤⽀持多结果包的MySQL客户端库。这意味这客户端必须使⽤⾄少MySQL 4.1以来的近期版本上的客户端库。
下⾯⼀节描述⽤来创建，改变，移除和查询存储程序和函数的语法。
20.2.1. CREATE PROCEDURE和CREATE FUNCTION
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
CREATE FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
routine_body:
Valid SQL procedure statement or statements
这些语句创建存储⼦程序。要在MySQL 5.1中创建⼦程序，必须具有CREATE ROUTINE权限，并且ALTER ROUTINE和EXECUTE权限被⾃动授予它的创建者。如果⼆进制⽇志功能被允许，你也可能需要SUPER权限，请参阅20.4节，“存储⼦程序和触发程序的⼆进制⽇志功能”。
默认地，⼦程序与当前数据库关联。要明确地把⼦程序与⼀个给定数据库关联起来，可以在创建⼦程序的时候指定其名字
为db_name.sp_name。
如果⼦程序名和内建的SQL函数名⼀样，定义⼦程序时，你需要在这个名字和随后括号中间插⼊⼀个空格，否则发⽣语法错误。当你随后调⽤⼦程序的时候也要插⼊。为此，即使有可能出现这种情况，我们还是建议最好避免给你⾃⼰的存储⼦程序取与存在的SQL函数⼀样的名字。
由括号包围的参数列必须总是存在。如果没有参数，也该使⽤⼀个空参数列()。每个参数默认都是⼀个IN参数。要指定为其它参数，可在参数名之前使⽤关键词 OUT或INOUT
注意: 指定参数为IN, OUT, 或INOUT 只对PROCEDURE是合法的。（FUNCTION参数总是被认为是IN参数）
RETURNS字句只能对FUNCTION做指定，对函数⽽⾔这是强制的。它⽤来指定函数的返回类型，⽽且函数体必须包含⼀个RETURN value语句。
routine_body包含合法的SQL过程语句。可以使⽤复合语句语法，请参阅20.2.7节，“BEGIN ... END复合语句”。复合语句可以包含声明，循环和其它控制结构语句。这些语句的语法在本章后免介绍，举
例，请参阅20.2.8节，“DECLARE语句”和20.2.12节，“流程控制构造”。
CREATE FUNCTION语句被⽤在更早的MySQL版本上以⽀持UDF （⾃定义函数）。请参阅27.2节，“给MySQL添加新函数”。 UDF继续被⽀持，即使现在有了存储函数。UDF会被认为⼀个外部存储函数。然⽽，不要让存储函数与UDF函数共享名字空间。
外部存储程序的框架将在不久的将来引⼊。这将允许你⽤SQL之外的语⾔编写存储程序。最可能的是，第⼀个被⽀持语⾔是PHP，因为核⼼PHP引擎很⼩，线程安全，且可以被⽅便地嵌⼊。因为框架是公开的，它希望许多其它语⾔也能被⽀持。
如果程序或线程总是对同样的输⼊参数产⽣同样的结果，则被认为它是“确定的”，否则就是“⾮确定”的。如果既没有给定DETERMINISTIC也没有给定NOT DETERMINISTIC，默认的就是NOT DETERMINISTIC。
之前下过mysql现在重新下载mysql 为进⾏复制，使⽤NOW()函数（或它的同义词）或RAND()函数会不必要地使得⼀个⼦程序⾮确定。对NOW()⽽⾔，⼆进制⽇志包括时间戳并被正确复制。RAND() 只要在⼀个⼦程序被内应⽤⼀次也会被正确复制。（你可以把⼦程序执⾏时间戳和随机数种⼦认为强制输⼊，它们在主从上是同样的。）
当前来讲，DETERMINISTIC特征被接受，但还没有被优化程序所使⽤。然⽽如果⼆进制⽇志功能被
允许了，这个特征影响到MySQL是否会接受⼦程序定义。请参阅20.4节，“存储⼦程序和触发程序的⼆进制⽇志功能”。
⼀些特征提供⼦程序使⽤数据的内在信息。CONTAINS SQL表⽰⼦程序不包含读或写数据的语句。NO SQL表⽰⼦程序不包含SQL语句。READS SQL DATA表⽰⼦程序包含读数据的语句，但不包含写数据的语句。MODIFIES SQL DATA表⽰⼦程序包含写数据的语句。如果这些特征没有明确给定，默认的是CONTAINS SQL。
SQL SECURITY特征可以⽤来指定⼦程序该⽤创建⼦程序者的许可来执⾏，还是使⽤调⽤者的许可来执⾏。默认值是DEFINER。在SQL:2003中者是⼀个新特性。创建者或调⽤者必须由访问⼦程序关联的数据库的许可。在MySQL 5.1中，必须有EXECUTE权限才能执⾏⼦程序。必须拥有这个权限的⽤户要么是定义者，要么是调⽤者，这取决于SQL SECURITY特征是如何设置的。
MySQL存储sql_mode系统变量设置，这个设置在⼦程序被创建的时候起作⽤，MySQL总是强制使⽤这个设置来执⾏⼦程序。
COMMENT⼦句是⼀个MySQL的扩展，它可以被⽤来描述存储程序。这个信息被SHOW CREATE PROCEDURE和 SHOW CREATE FUNCTION语句来显⽰。
MySQL允许⼦程序包含DDL语句，如CREATE和DROP。MySQL也允许存储程序（但不是存储函数）包含SQL 交互语句，如COMMIT。存储函数不可以包含那些做明确的和绝对的提交或者做回滚的语。SQL标准不要求对这些语句的⽀持，SQL标准声明每个DBMS 提供商可以决定是否允许⽀持这些语句。
存储⼦程序不能使⽤LOAD DATA INFILE。
返回结果包的语句不能被⽤在存储函数种。这包括不使⽤INTO给变量读取列值的SELECT语句，SHOW 语句，及其它诸如EXPLAIN这样的语句。对于可在函数定义时间被决定要返回⼀个结果包的语句，发⽣⼀个允许从函数错误返回结果包的
Not（ER_SP_NO_RETSET_IN_FUNC）。对于只可在运⾏时决定要返回⼀个结果包的语句，发⽣⼀个不能在给定上下⽂错误返回结果包的PROCEDURE %s （ER_SP_BADSELECT)。
下⾯是⼀个使⽤OUT参数的简单的存储程序的例⼦。例⼦为，在程序被定义的时候，⽤mysql客户端delimiter命令来把语句定界符从 ；变为//。这就允许⽤在程序体中的；定界符被传递到服务器⽽不是被mysql⾃⼰来解释。
mysql> delimiter //
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
-> BEGIN
-> SELECT COUNT(*) INTO param1 FROM t;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @a;
+------+
| @a |
+------+
| 3 |
+------+
1 row in set (0.00 sec)
当使⽤delimiter命令时，你应该避免使⽤反斜杠(‘\’)字符，因为那是MySQL的转义字符。
下列是⼀个例⼦，⼀个采⽤参数的函数使⽤⼀个SQL函数执⾏⼀个操作，并返回结果：
mysql> delimiter //
mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
-> RETURN CONCAT('Hello, ',s,'!');
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world! |
+----------------+
1 row in set (0.00 sec)
如果在存储函数中的RETURN语句返回⼀个类型不同于在函数的RETURNS⼦句中指定类型的值，返回值被强制为恰当的类型。⽐如，如果⼀个函数返回⼀个ENUM或SET值，但是RETURN语句返回⼀个整数，对于SET成员集的相应的ENUM成员，从函数返回的值是字符串。
20.2.2. ALTER PROCEDURE和ALTER FUNCTION
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]
characteristic:
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
这个语句可以被⽤来改变⼀个存储程序或函数的特征。在MySQL 5.1中，你必须⽤ALTER ROUTINE权限才可⽤此⼦程序。这个权限被⾃动授予⼦程序的创建者。如20.4节，“存储⼦程序和触发程序的⼆进制⽇志功能”中所述，如果⼆进制⽇志功能被允许了，你可能也需要SUPER权限。
在ALTER PROCEDURE和ALTER FUNCTION语句中，可以指定超过⼀个的改变。
20.2.3. DROP PROCEDURE和DROP FUNCTION
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name
这个语句被⽤来移除⼀个存储程序或函数。即，从服务器移除⼀个制定的⼦程序。在MySQL 5.1中，你必须有ALTER ROUTINE权限才可⽤此⼦程序。这个权限被⾃动授予⼦程序的创建者。
IF EXISTS ⼦句是⼀个MySQL的扩展。如果程序或函数不存储，它防⽌发⽣错误。产⽣⼀个可以⽤SHOW WARNINGS查看的警告。
20.2.4. SHOW CREATE PROCEDURE和SHOW CREATE FUNCTION
SHOW CREATE {PROCEDURE | FUNCTION} sp_name
这个语句是⼀个MySQL的扩展。类似于SHOW CREATE TABLE，它返回⼀个可⽤来重新创建已命名⼦程序的确切字符串。
mysql> SHOW CREATE FUNCTION test.hello\G
*************************** 1. row ***************************
Function: hello
sql_mode:
Create Function: CREATE FUNCTION `test`.`hello`(s CHAR(20)) RETURNS CHAR(50)
RETURN CONCAT('Hello, ',s,'!')
20.2.5. SHOW PROCEDURE STATUS和SHOW FUNCTION STATUS
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
这个语句是⼀个MySQL的扩展。它返回⼦程序的特征，如数据库，名字，类型，创建者及创建和修改⽇期。如果没有指定样式，根据你使⽤的语句，所有存储程序和所有存储函数的信息都被列出。
mysql> SHOW FUNCTION STATUS LIKE 'hello'\G
*************************** 1. row ***************************
Db: test
Name: hello
Type: FUNCTION
Definer: testuser@localhost
Modified: 2004-08-03 15:29:37
Created: 2004-08-03 15:29:37
Security_type: DEFINER
Comment:
你可以从INFORMATION_SCHEMA中的ROUTINES表获得有关存储⼦程序的信息。请参阅23.1.14节，“INFORMATION_SCHEMA ROUTINES 表”。
20.2.6. CALL语句
CALL sp_name([parameter[,...]])
CALL语句调⽤⼀个先前⽤CREATE PROCEDURE创建的程序。
CALL语句可以⽤声明为OUT或的INOUT参数的参数给它的调⽤者传回值。它也“返回”受影响的⾏数，客户端程序可以在SQL级别通过调⽤ROW_COUNT()函数获得这个数，从C中是调⽤the mysql_affected_rows() C API函数来获得。
20.2.7. BEGIN ... END复合语句
[begin_label:] BEGIN
[statement_list]
END [end_label]
存储⼦程序可以使⽤BEGIN ... END复合语句来包含多个语句。statement_list代表⼀个或多个语句的列表。statement_list之内每个语句
都必须⽤分号（；）来结尾。
复合语句可以被标记。除⾮begin_label存在，否则end_label不能被给出，并且如果⼆者都存在，他们必须是同样的。
请注意，可选的[NOT] ATOMIC⼦句现在还不被⽀持。这意味着在指令块的开始没有交互的存储点被设置，并且在上下⽂中⽤到的BEGIN⼦句对当前交互动作没有影响。
使⽤多重语句需要客户端能发送包含语句定界符；的查询字符串。这个符号在命令⾏客户端被⽤delimiter命令来处理。改变查询结尾定界符;（⽐如改变为//）使得; 可被⽤在⼦程序体中。
20.2.8. DECLARE语句
DECLARE语句被⽤来把不同项⽬局域到⼀个⼦程序：局部变量（请参阅20.2.9节，“存储程序中的变量”)，条件和处理程序（请参阅20.2.10节，“条件和处理程序”) 及光标（请参阅20.2.11节，“光标”)。SIGNAL和RESIGNAL语句当前还不被⽀持。
DECLARE仅被⽤在BEGIN ... END复合语句⾥，并且必须在复合语句的开头，在任何其它语句之前。
光标必须在声明处理程序之前被声明，并且变量和条件必须在声明光标或处理程序之前被声明。
20.2.9. 存储程序中的变量
20.2.9.1. DECLARE局部变量
20.2.9.2. 变量SET语句
20.2.9.3. SELECT ... INTO语句
你可以在⼦程序中声明并使⽤变量。
20.2.9.1. DECLARE局部变量
DECLARE var_name[,...] type [DEFAULT value]
这个语句被⽤来声明局部变量。要给变量提供⼀个默认值，请包含⼀个DEFAULT⼦句。值可以被指定为⼀个表达式，不需要为⼀个常数。如果没有DEFAULT⼦句，初始值为NULL。
局部变量的作⽤范围在它被声明的BEGIN ... END块内。它可以被⽤在嵌套的块中，除了那些⽤相同名字声明变量的块。
20.2.9.2. 变量SET语句
SET var_name = expr [, var_name = expr] ...
在存储程序中的SET语句是⼀般SET语句的扩展版本。被参考变量可能是⼦程序内声明的变量，或者是全局服务器变量。
在存储程序中的SET语句作为预先存在的SET语法的⼀部分来实现。这允许SET a=x, b=y, ...这样的扩展语法。其中不同的变量类型（局域声明变量及全局和集体变量）可以被混合起来。这也允许把局部变量和⼀些只对系统变量有意义的选项合并起来。在那种情况下，此选项被识别，但是被忽略了。
20.2.9.3. SELECT ... INTO语句
SELECT col_name[,...] INTO var_name[,...] table_expr
这个SELECT语法把选定的列直接存储到变量。因此，只有单⼀的⾏可以被取回。
SELECT id,data INTO x,y FROM test.t1 LIMIT 1;
注意，⽤户变量名在MySQL 5.1中是对⼤⼩写不敏感的。请参阅9.3节，“⽤户变量”。
重要: SQL变量名不能和列名⼀样。如果SELECT ... INTO这样的SQL语句包含⼀个对列的参考，并包含⼀个与列相同名字的局部变
量，MySQL当前把参考解释为⼀个变量的名字。例如，在下⾯的语句中，xname 被解释为到xname variable的参考⽽不是到xname column的：
CREATE PROCEDURE sp1 (x VARCHAR(5))
BEGIN
DECLARE xname VARCHAR(5) DEFAULT 'bob';
DECLARE newname VARCHAR(5);
DECLARE xid INT;
SELECT xname,id INTO newname,xid
FROM table1 WHERE xname = xname;
SELECT newname;
END;
当这个程序被调⽤的时候，⽆论ame列的值是什么，变量newname将返回值‘bob’。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论