MySQL存储程序详述,语法及例子

kider

一、概念

一个存储程序是可以被存储在服务器中的一套SQL语句。一旦它被存储了，客户端不需要再重新发布单独的语句，而是可以引用存储程序来替代。

下面一些情况下存储程序尤其有用：

  ·当用不同语言编写多客户应用程序，或多客户应用程序在不同平台上运行且需要执行相同的数据库操作之时。

  ·安全极为重要之时。比如，银行对所有普通操作使用存储程序。这提供一个坚固而安全的环境，程序可以确保每一个操作都被妥善记入日志。在这样一个设置中，应用程序和用户不可能直接访问数据库表，但是仅可以执行指定的存储程序。

存储程序可以提供改良后的性能，因为只有较少的信息需要在服务器和客户算之间传送。代价是增加数据库服务器系统的负荷，因为更多的工作在服务器这边完成，更少的在客户端（应用程序）那边完成上。如果许多客户端机器（比如网页服务器）只由一个或少数几个数据库服务器提供服务，可以考虑一下存储程序。

存储程序也允许你在数据库服务器上有函数库。这是一个被现代应用程序语言共享的特征，它允许这样的内部设计，比如通过使用类。使用这些客户端应用程序语言特征对甚至于数据库使用范围以外的编程人员都有好处。

MySQL为存储程序遵循SQL:2003语法，这个语法也被用在IBM的DB2数据库上。


二、授权

存储程序需要在mysql数据库中有proc表。这个表在MySQL安装过程中创建。如果你从早期的版本升级到MySQL高版本，请确定更新你的授权表以确保proc表的存在。

在MySQL高版本中，授权系统如下考虑存储子程序：

  ·创建存储子程序需要CREATE ROUTINE权限。

  ·提醒或移除存储子程序需要ALTER ROUTINE权限。这个权限自动授予子程序的创建者。

  ·执行子程序需要EXECUTE权限。然而，这个权限自动授予子程序的创建者。同样，子程序默认的SQL SECURITY 特征是DEFINER，它允许用该子程序访问数据库的用户与执行子程序联系到一起。


三、语法

存储程序和函数是用CREATE PROCEDURE和CREATE FUNCTION语句创建的子程序。一个子程序要么是一个程序要么是一个函数。使用CALL语句来调用程序，程序只能用输出变量传回值。就像别其它函数调用一样，函数可以被从语句外调用（即通过引用函数名），函数能返回标量值。存储子程序也可以调用其它存储子程序。

在MySQL中，一个存储子程序或函数与特定的数据库相联系。这里有几个意思：

  ·当一个子程序被调用时，一个隐含的USE db_name 被执行（当子程序终止时停止执行）。存储子程序内的USE语句时不允许的。

  ·你可以使用数据库名限定子程序名。这可以被用来引用一个不在当前数据库中的子程序。比如，要引用一个与test数据库关联的存储程序p或函数f，你可以说CALL test.p()或test.f()。

  ·数据库移除的时候，与它关联的所有存储子程序也都被移除。

MySQL 支持非常有用的扩展，即它允许在存储程序中使用常规的SELECT语句（那就是说，不使用光标或局部变量）。这个一个查询的结果包被简单地直接送到客户端。多SELECT语句生成多个结果包，所以客户端必须使用支持多结果包的MySQL客户端库。这意味这客户端必须使用至少MySQL 4.1以来的近期版本上的客户端库。

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 中创建子程序，必须具有CREATE ROUTINE权限，并且ALTER ROUTINE和EXECUTE权限被自动授予它的创建者。如果二进制日志功能被允许，你也可能需要SUPER权限。

默认地，子程序与当前数据库关联。要明确地把子程序与一个给定数据库关联起来，可以在创建子程序的时候指定其名字为db_name.sp_name。

如果子程序名和内建的SQL函数名一样，定义子程序时，你需要在这个名字和随后括号中间插入一个空格，否则发生语法错误。当你随后调用子程序的时候也要插入。为此，即使有可能出现这种情况，我们还是建议最好避免给你自己的存储子程序取与存在的SQL函数一样的名字。

由括号包围的参数列必须总是存在。如果没有参数，也该使用一个空参数列()。每个参数默认都是一个IN参数。要指定为其它参数，可在参数名之前使用关键词 OUT或INOUT

注意: 指定参数为IN, OUT, 或INOUT 只对PROCEDURE是合法的。（FUNCTION参数总是被认为是IN参数）

RETURNS字句只能对FUNCTION做指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。

routine_body 包含合法的SQL过程语句。可以使用复合语句语法。复合语句可以包含声明，循环和其它控制结构语句。

CREATE FUNCTION语句被用在更早的MySQL版本上以支持UDF （自定义函数）。UDF继续被支持，即使现在有了存储函数。UDF会被认为一个外部存储函数。然而，不要让存储函数与UDF函数共享名字空间。

外部存储程序的框架将在不久的将来引入。这将允许你用SQL之外的语言编写存储程序。最可能的是，第一个被支持语言是PHP，因为核心PHP引擎很小，线程安全，且可以被方便地嵌入。因为框架是公开的，它希望许多其它语言也能被支持。

如果程序或线程总是对同样的输入参数产生同样的结果，则被认为它是“确定的”，否则就是“非确定”的。如果既没有给定DETERMINISTIC也没有给定NOT DETERMINISTIC，默认的就是NOT DETERMINISTIC。

为进行复制，使用NOW()函数（或它的同义词）或RAND()函数会不必要地使得一个子程序非确定。对NOW()而言，二进制日志包括时间戳并被正确复制。RAND() 只要在一个子程序被内应用一次也会被正确复制。（你可以把子程序执行时间戳和随机数种子认为强制输入，它们在主从上是同样的。）

当前来讲，DETERMINISTIC特征被接受，但还没有被优化程序所使用。然而如果二进制日志功能被允许了，这个特征影响到MySQL是否会接受子程序定义。

一些特征提供子程序使用数据的内在信息。CONTAINS SQL表示子程序不包含读或写数据的语句。NO SQL表示子程序不包含SQL语句。READS SQL DATA表示子程序包含读数据的语句，但不包含写数据的语句。MODIFIES SQL DATA表示子程序包含写数据的语句。如果这些特征没有明确给定，默认的是CONTAINS SQL。

SQL SECURITY特征可以用来指定子程序该用创建子程序者的许可来执行，还是使用调用者的许可来执行。默认值是DEFINER。在SQL:2003中者是一个新特性。创建者或调用者必须由访问子程序关联的数据库的许可。在MySQL中，必须有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)。


四、例子

Example 1 ：
下面是一个使用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的转义字符。


Example 2 ：
下列是一个例子，一个采用参数的函数使用一个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成员，从函数返回的值是字符串。

Example 3 ：
使用光标的例子

CREATE PROCEDURE curdemo() BEGIN   DECLARE done INT DEFAULT 0;   DECLARE a CHAR(16);   DECLARE b,c INT;   DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;   DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;   DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;   OPEN cur1;   OPEN cur2;   REPEAT     FETCH cur1 INTO a, b;     FETCH cur2 INTO c;     IF NOT done THEN        IF b < c THEN           INSERT INTO test.t3 VALUES (a,b);        ELSE           INSERT INTO test.t3 VALUES (a,c);        END IF;     END IF;   UNTIL done END REPEAT;   CLOSE cur1;   CLOSE cur2; END

mysqlkumao

Jobsz

To me as a beginger of MySQL it is very useful.

GhostSniper

正在学习，很有帮助阿

lstian2004

回复 kider 的帖子

谢谢,分享

yrmy

先顶起再说