本帖及回帖内容来自http://www.yesky.com

存储过程:第一讲

　　Introduction 简介

　　MySQL 5.0 新特性教程是为需要了解5.0版本新特性的MySQL老用户而写的。简单的来说是介绍了“存储过程、触发器、视图、信息架构视图”,在此感谢译者陈朋奕的努力.

　　希望本教程能像内行专家那样与您进行对话，用简单的问题、例子让你学到需要的知识。为了达到这样的目的，我会从每一个细节开始慢慢的为大家建立概念，最后会给大家展示较大的实用例，在学习之前也许大家会认为这个用例很难，但是只要跟着课程去学，相信很快就能掌握。

　　Conventions and Styles 约定和编程风格

　　每次我想要演示实际代码时，我会对mysql客户端的屏幕就出现的代码进行调整，将字体改成Courier，使他们看起来与普通文本不一样。

　　在这里举个例子：mysql> DROP FUNCTION f;Query OK, 0 rows affected (0.00 sec)

　　如果实例比较大，则需要在某些行和段落间加注释，同时我会用将“<--”符号放在页面的右边以表示强调。

　　例如：

　　mysql> CREATE PROCEDURE p ()

以下是引用片段： 　　-> BEGIN 　　-> /* This procedure does nothing */ <-- 　　-> END;//Query OK, 0 rows affected (0.00 sec)

　　有时候我会将例子中的"mysql>"和"->"这些系统显示去掉，你可以直接将代码复制到mysql客户端程序中(如果你现在所读的不是电子版的，可以在mysql.com网站下载相关脚本)所以的例子都已经在Suse 9.2 Linux、Mysql 5.0.3公共版上测试通过。

　　在您阅读本书的时候，Mysql已经有更高的版本，同时能支持更多OS了，包括Windows，Sparc，HP-UX。因此这里的例子将能正常的运行在您的电脑上。但如果运行仍然出现故障，可以咨询你认识的资深Mysql用户，以得到长久的支持和帮助。

　　A Definition and an Example 定义及实例

　　定义及实例存储过程是一种存储在书库中的程序(就像正规语言里的子程序一样)，准确的来说，MySQL支持的“routines(例程)”有两种：一是我们说的存储过程，二是在其他SQL语句中可以返回值的函数(使用起来和Mysql预装载的函数一样，如pi())。我在本书里面会更经常使用存储过程，因为这是我们过去的习惯，相信大家也会接受。

　　一个存储过程包括名字，参数列表，以及可以包括很多SQL语句的SQL语句集。

　　在这里对局部变量，异常处理，循环控制和IF条件句有新的语法定义。

　　下面是一个包括存储过程的实例声明：(译注：为了方便阅读，此后的程序不添任何中文注释)

以下是引用片段： 　　CREATE PROCEDURE procedure1 /* name存储过程名*/ 　　(IN parameter1 INTEGER) /* parameters参数*/ 　　BEGIN /* start of block语句块头*/ 　　DECLARE variable1 CHAR(10); /* variables变量声明*/ 　　IF parameter1 = 17 THEN /* start of IF IF条件开始*/ 　　SET variable1 = 'birds'; /* assignment赋值*/ 　　ELSE 　　SET variable1 = 'beasts'; /* assignment赋值*/ 　　END IF; /* end of IF IF结束*/ 　　INSERT INTO table1 VALUES (variable1);/* statement SQL语句*/ 　　END /* end of block语句块结束*/

　　下面我将会介绍你可以利用存储过程做的工作的所有细节。同时我们将介绍新的数据库对象—触发器，因为触发器和存储过程的关联是必然的。

　　Why Stored Procedures 为什么要用存储过程

　　由于存储过程对于MySQL来说是新的功能，很自然的在使用时你需要更加注意。

　　毕竟，在此之前没有任何人使用过，也没有很多大量的有经验的用户来带你走他们走过的路。然而你应该开始考虑把现有程序(可能在服务器应用程序中，用户自定义函数(UDF)中，或是脚本中)转移到存储过程中来。这样做不需要原因，你不得不去做。

　　因为存储过程是已经被认证的技术!虽然在Mysql中它是新的，但是相同功能的函数在其他DBMS中早已存在，而它们的语法往是相同的。因此你可以从其他人那里获得这些概念，也有很多你可以咨询或者雇用的经验用户，还有许多第三方的文档可供你阅读。

　　存储过程会使系统运行更快!虽然我们暂时不能在Mysql上证明这个优势，用户得到的体验也不一样。我们可以说的就是Mysql服务器在缓存机制上做了改进，就像Preparedstatements(预处理语句)所做的那样。由于没有编译器，因此SQL存储过程不会像外部语言(如C)编写的程序运行起来那么快。但是提升速度的主要方法却在于能否降低网络信息流量。如果你需要处理的是需要检查、循环、多语句但没有用户交互的重复性任务，你就可以使用保存在服务器上的存储过程来完成。这样在执行任务的每一步时服务器和客户端之间就没那么多的信息来往了。

　　所以存储过程是可复用的组件!想象一下如果你改变了主机的语言，这对存储过程不会产生影响，因为它是数据库逻辑而不是应用程序。存储过程是可以移植的!当你用SQL编写存储过程时，你就知道它可以运行在Mysql支持的任何平台上，不需要你额外添加运行环境包，也不需要为程序在操作系统中执行设置许可，或者为你的不同型号的电脑存储过程将被保存!如果你编写好了一个程序，例如显示银行事物处理中的支票撤消，那想要了解支票的人就可以找到你的程序。

　　它会以源代码的形式保存在数据库中。这将使数据和处理数据的进程有意义的关联这可能跟你在课上听到的规划论中说的一样。存储过程可以迁移!

　　Mysql完全支持SQL 2003标准。某些数据库(如DB2、Mimer)同样支持。但也有部分不支持的，如Oracle、SQL Server不支持。我们将会给予足够帮助和工具，使为其他DBMS编写的代码能更容易转移到Mysql上。

　　Setting up with MySQL 5.0 设置并开始MySQL 5.0服务

　　通过

以下是引用片段： 　　mysql_fix_privilege_tables 　　或者 　　~/mysql-5.0/scripts/mysql_install_db

　　来开始MySQL服务

　　作为我们练习的准备工作的一部分，我假定MySQL 5.0已经安装。如果没有数据库管理员为你安装好数据库以及其他软件，你就需要自己去安装了。不过你很容易忘掉一件事，那就是你需要有一个名为mysql.proc的表。

　　在安装了最新版本后，你必须运行

以下是引用片段： mysql_fix_privilege_tables

　　或者

以下是引用片段： mysql_install_db

　　(只需要运行其中一个就够了)——不然存储过程将不能工作。我同时启用在root身份后运行一个非正式的SQL脚本，如下：

以下是引用片段： mysql>source/home/pgulutzan/mysql-5.0/scripts/mysql_prepare_privilege_tables_for_5.sql

　　Starting the MySQL Client 启动MySQL客户端

　　这是我启动mysql客户端的方式。你也许会使用其他方式，如果你使用的是二进制版本或者是Windows系统的电脑，你可能会在其他子目录下运行以下程序：

以下是引用片段： [email=easy@phpv]easy@phpv[/email]:~> /usr/local/mysql/bin/mysql --user=root 　　Welcome to the MySQL monitor. Commands end with ; or \g. 　　Your MySQL connection id is 1 to server version: 5.0.3-alpha-debug 　　Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

　　在演示中，我将会展示以root身份登陆后的mysql客户端返回的结果，这样意味着我有极大的特权。

　　Check for the Correct Version 核对版本

　　为了确认使用的MySQL的版本是正确的，我们要查询版本。我有两种方法确认我使用的是5.0版本：

以下是引用片段： SHOW VARIABLES LIKE 'version';

　　or

以下是引用片段： SELECT VERSION();

　　例如：

以下是引用片段： mysql> SHOW VARIABLES LIKE 'version'; 　　+---------------+-------------------+ 　　| Variable_name | Value | 　　+---------------+-------------------+ 　　| version | 5.0.3-alpha-debug | 　　+---------------+-------------------+ 　　1 row in set (0.00 sec) 　　mysql> SELECT VERSION(); 　　+-------------------+ 　　| VERSION() | 　　+-------------------+ 　　| 5.0.3-alpha-debug | 　　+-------------------+ 　　1 row in set (0.00 sec)

　　当看见数字'5.0.x' 后就可以确认存储过程能够在这个客户端上正常工作。

　　The Sample "Database" 示例数据库

　　现在要做的第一件事是创建一个新的数据库然后设定为默认数据库实现这个步骤的SQL

　　语句如下：

以下是引用片段： CREATE DATABASE db5; 　　USE db5; 　　例如： 　　mysql> CREATE DATABASE db5; 　　Query OK, 1 row affected (0.00 sec) 　　mysql> USE db5; 　　Database changed

　　在这里要避免使用有重要数据的实际的数据库然后我们创建一个简单的工作表。

　　实现这个步骤的SQL

　　语句如下：

以下是引用片段： 　　mysql> CREATE DATABASE db5; 　　Query OK, 1 row affected (0.01 sec) 　　mysql> USE db5; 　　Database changed 　　mysql> CREATE TABLE t (s1 INT); 　　Query OK, 0 rows affected (0.01 sec) 　　mysql> INSERT INTO t VALUES (5); 　　Query OK, 1 row affected (0.00 sec)

　　你会发现我只在表中插入了一列。这样做的原因是我要保持表的简单，因为在这里并不需要展示查询数据的技巧，而是教授存储过程，不需要使用大的数据表，因为它本身已经够复杂了。

　　这就是示例数据库，我们将从这个名字为t的只包含一列的表开始Pick a Delimiter 选择分隔符

　　现在我们需要一个分隔符，实现这个步骤的SQL语句如下：

以下是引用片段： DELIMITER //

　　例如：

以下是引用片段： mysql> DELIMITER //

　　分隔符是你通知mysql客户端你已经完成输入一个SQL语句的字符或字符串符号。一直以来我们都使用分号“;”，但在存储过程中，这会产生不少问题，因为存储过程中有许多语句，所以每一个都需要一个分号因此你需要选择一个不太可能出现在你的语句或程序中的字符串作为分隔符。我曾用过双斜杠“//”，也有人用竖线“|”。我曾见过在DB2程序中使用“@”符号的，但我不喜欢这样。你可以根据自己的喜好来选择，但是在这个课程中为了更容易理解，你最好选择跟我一样。如果以后要恢复使用“;”(分号)作为分隔符，输入下面语句就可以了：

以下是引用片段： "DELIMITER ;//". 　　CREATE PROCEDURE Example 创建程序实例 　　CREATE PROCEDURE p1 () SELECT * FROM t; //

　　也许这是你使用Mysql创建的第一个存储过程。假如是这样的话，最好在你的日记中记下这个重要的里程碑。

以下是引用片段： CREATE PROCEDURE p1 () SELECT * FROM t; // <--

　　SQL语句存储过程的第一部分是“CREATE PROCEDURE”：

以下是引用片段： CREATE PROCEDURE p1 () SELECT * FROM t; // <--

　　第二部分是过程名，上面新存储过程的名字是p1。

　　Digression: Legal Identifiers 题外话：合法标识符的问题

　　存储过程名对大小写不敏感，因此‘P1’和‘p1’是同一个名字，在同一个数据库中你将不能给两个存储过程取相同的名字，因为这样将会导致重载。某些DBMS允许重载(Oracle支持)，但是MySQL不支持(译者话：希望以后会支持吧。)。

　　你可以采取“数据库名.存储过程名”这样的折中方法，如“db5.p1”。存储过程名可以分开，它可以包括空格符，其长度限制为64个字符，但注意不要使用MySQL内建函数的名字，如果这样做了，在调用时将会出现下面的情况：

以下是引用片段： mysql> CALL pi(); 　　Error 1064 (42000): You have a syntax error. 　　mysql> CALL pi (); 　　Error 1305 (42000): PROCEDURE does not exist.

　　在上面的第一个例子里，我调用的是一个名字叫pi的函数，但你必须在调用的函数名后加上空格，就像第二个例子那样。

以下是引用片段： 　CREATE PROCEDURE p1 () SELECT * FROM t; // <--

　　其中“()”是“参数列表”。

以下是引用片段： CREATE PROCEDURE

　　语句的第三部分是参数列表。通常需要在括号内添加参数。例子中的存储过程没有参数，因此参数列表是空的—所以我只需要键入空括号，然而这是必须的。

以下是引用片段： CREATE PROCEDURE p1 () SELECT * FROM t; // <-- 　　"SELECT * FROM t;"

　　是存储过程的主体。

　　然后到了语句的最后一个部分了，它是存储过程的主体，是一般的SQL语句。过程体中语句

以下是引用片段： 　"SELECT * FROM t;"

　　包含一个分号，如果后面有语句结束符号(//)时可以不写这个分号。

　　如果你还记得我把这部分叫做程序的主体将会是件好事，因为(body)这个词是大家使用的技术上的术语。通常我们不会将SELECT语句用在存储过程中，这里只是为了演示。所以使用这样的语句，能在调用时更好的看出程序是否正常工作。



[ 本帖最后由 非常菜 于 2007-6-19 15:56 编辑 ]

　　Why MySQL Statements are Legal in a Procedure Body

　　什么MySQL语句在存储过程体中是合法的？

　　什么样的SQL语句在Mysql存储过程中才是合法的呢？你可以创建一个包含INSERT, UPDATE,DELETE, SELECT, DROP, CREATE, REPLACE等的语句。你唯一需要记住的是如果代码中包含MySQL扩充功能，那么代码将不能移植。在标准SQL语句中：任何数据库定义语言都是合法的，如：

以下是引用片段： CREATE PROCEDURE p () DELETE FROM t; //

　　SET、COMMIT以及ROLLBACK也是合法的，如：

CREATE PROCEDURE p () SET @x = 5; //

　　MySQL的附加功能：任何数据操作语言的语句都将合法。

CREATE PROCEDURE p () DROP TABLE t; //

　　MySQL扩充功能：直接的SELECT也是合法的：

CREATE PROCEDURE p () SELECT 'a'; //

　　顺便提一下，我将存储过程中包括DDL语句的功能称为MySQL附加功能的原因是在SQL标准中把这个定义为非核心的，即可选组件。

　　在过程体中有一个约束，就是不能有对例程或表操作的数据库操作语句。例如下面的例子就是非法的：

CREATE PROCEDURE p1 ()
CREATE PROCEDURE p2 () DELETE FROM t; //

　　下面这些对MySQL 5.0来说全新的语句，过程体中是非法的:

CREATE PROCEDURE, ALTER PROCEDURE, DROP PROCEDURE, CREATE FUNCTION,
DROP FUNCTION, CREATE TRIGGER, DROP TRIGGER.

　　不过你可以使用

CREATE PROCEDURE db5.p1 () DROP DATABASE db5//

　　但是类似

"USE database"

　　语句也是非法的，因为MySQL假定默认数据库就是过程的工作场所。

　　Call the Procedure 调用存储过程

　　1.现在我们就可以调用一个存储过程了，你所需要输入的全部就是CALL和你过程名以及一个括号再一次强调，括号是必须的当你调用例子里面的p1过程时，结果是屏幕返回了t表的内容

以下是引用片段： mysql> CALL p1() // +------+ | s1 | +------+ | 5 | +------+ 1 row in set (0.03 sec) Query OK, 0 rows affected (0.03 sec)

　　因为过程中的语句是

"SELECT * FROM t;"

　　2. Let me say that again, another way.

　　其他实现方式

mysql> CALL p1() //

　　和下面语句的执行效果一样：

mysql> SELECT * FROM t; //

　　所以，你调用p1过程就相当于你执行了下面语句：

"SELECT * FROM t;"

　　好了，主要的知识点"创建和调用过程方法"已经清楚了。我希望你能对自己说这相当简单。但是很快我们就有一系列的练习，每次都加一条子句，或者改变已经存在的子句。那样在写复杂部件前我们将会有很多可用的子句。

　　Characteristics Clauses 特征子句

　　1.

以下是引用片段： CREATE PROCEDURE p2 () LANGUAGE SQL <-- NOT DETERMINISTIC <-- SQL SECURITY DEFINER <-- COMMENT 'A Procedure' <-- SELECT CURRENT_DATE, RAND() FROM t //

　　这里我给出的是一些能反映存储过程特性的子句。子句内容在括号之后，主体之前。这些子句都是可选的，他们有什么作用呢？

　　2.

CREATE PROCEDURE p2 ()
LANGUAGE SQL <--
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'A Procedure'
SELECT CURRENT_DATE, RAND() FROM t //

　　很好，这个LANGUAGE SQL子句是没有作用的。仅是为了说明下面过程的主体使用SQL语言编写。这条是系统默认的，但你在这里声明是有用的，因为某些DBMS（IBM的DB2）需要它，如果你关注DB2的兼容问题最好还是用上。此外，今后可能会出现除SQL外的其他语言支持的存储过程。

　3.

CREATE PROCEDURE p2 ()
LANGUAGE SQL
NOT DETERMINISTIC <--
SQL SECURITY DEFINER
COMMENT 'A Procedure'
SELECT CURRENT_DATE, RAND() FROM t //

　　下一个子句，NOT DETERMINISTIC，是传递给系统的信息。这里一个确定过程的定义就是那些每次输入一样输出也一样的程序。在这个案例中，既然主体中含有SELECT语句，那返回肯定是未知的因此我们称其NOT DETERMINISTIC。但是MySQL内置的优化程序不会注意这个，至少在现在不注意。

　　4.

CREATE PROCEDURE p2 ()
LANGUAGE SQL
NOT DETERMINISTIC
SQL SECURITY DEFINER <--
COMMENT 'A Procedure'
SELECT CURRENT_DATE, RAND() FROM t //

　　下一个子句是SQL SECURITY，可以定义为SQL SECURITY DEFINER或SQL SECURITY INVOKER。

　　这就进入了权限控制的领域了，当然我们在后面将会有测试权限的例子。

以下是引用片段： SQL SECURITY DEFINER

　　意味着在调用时检查创建过程用户的权限（另一个选项是SQLSECURITY INVOKER）。

　　现在而言，使用

SQL SECURITY DEFINER

　　指令告诉MySQL服务器检查创建过程的用户就可以了，当过程已经被调用，就不检查执行调用过程的用户了。而另一个选项（INVOKER）则是告诉服务器在这一步仍然要检查调用者的权限。

　　5.

CREATE PROCEDURE p2 ()
LANGUAGE SQL
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'A Procedure' <--
SELECT CURRENT_DATE, RAND() FROM t //

　　COMMENT 'A procedure'

　　是一个可选的注释说明。

　最后，注释子句会跟过程定义存储在一起。这个没有固定的标准，我在文中会指出没有固定标准的语句，不过幸运的是这些在我们标准的SQL中很少。

　　6.

CREATE PROCEDURE p2 ()
LANGUAGE SQL
NOT DETERMINISTIC
SQL SECURITY DEFINER
COMMENT ''
SELECT CURRENT_DATE, RAND() FROM t //

　　上面过程跟下面语句是等效的：

CREATE PROCEDURE p2 ()
SELECT CURRENT_DATE, RAND() FROM t //

　　特征子句也有默认值，如果省略了就相当于：

LANGUAGE SQL NOT DETERMINISTIC SQL SECURITY DEFINER COMMENT ''

　　Digressions一些题外话

　　Digression:

　　调用p2()//的结果

以下是引用片段： mysql> call p2() // +--------------+-----------------+ | CURRENT_DATE | RAND() | +--------------+-----------------+ | 2004-11-09 | 0.7822275075896 | +--------------+-----------------+ 1 row in set (0.26 sec) Query OK, 0 rows affected (0.26 sec)

　　当调用过程p2时，一个SELECT语句被执行返回我们期望获得的随机数。

　　Digression: sql_mode unchanging

　　不会改变的

sql_mode
mysql> set sql_mode='ansi' // mysql> create procedure p3()select'a'||'b'// mysql> set sql_mode=''// mysql> call p3()// +------------+
| 'a' || 'b' |
+------------+
| ab |
+------------+

　　MySQL在过程创建时会自动保持运行环境。例如：我们需要使用两条竖线来连接字符串但是这只有在sql mode为ansi的时候才合法。如果我们将sql mode改为non-ansi，不用担心，它仍然能工作，只要它第一次使用时能正常工作。

　　Exercise 练习

　　Question

　　问题

　　如果你不介意练习一下的话，试能否不看后面的答案就能处理这些请求。

　　创建一个过程，显示`Hello world`。用大约5秒时间去思考这个问题，既然你已经学到了这里，这个应该很简单。当你思考问题的时候，我们再随机选择一些刚才讲过的东西复习：

　　DETERMINISTIC

　　（确定性）子句是反映输出和输入依赖特性的子句…调用过程使用CALL过程名（参数列表）方式。好了，我猜时间也到了。

　　Answer

　　答案

　　好的，答案就是在过程体中包含

"SELECT 'Hello, world'"

　　语句

　　MySQL

mysql> CREATE PROCEDURE p4 () SELECT 'Hello, world' // Query OK, 0 rows affected (0.00 sec)
mysql> CALL p4()// +--------------+
| Hello, world |
+--------------+
| Hello, world |
+--------------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

　　Parameters 参数

　　让我们更进一步的研究怎么在存储过程中定义参数

　　1. CREATE PROCEDURE p5
　　() ...
　　2. CREATE PROCEDURE p5
　　([IN] name data-type) ...
　　3. CREATE PROCEDURE p5
　　(OUT name data-type) ...
　　4. CREATE PROCEDURE p5
　　(INOUT name data-type) ...

　　回忆一下前面讲过的参数列表必须在存储过程名后的括号中。上面的第一个例子中的参数列表是空的，第二个例子中有一个输入参数。这里的词IN可选，因为默认参数为IN（input）。

　　第三个例子中有一个输出参数，第四个例子中有一个参数，既能作为输入也可以作为输出。

　　IN example 输入的例子

mysql> CREATE PROCEDURE p5(p INT) SET @x = p // Query OK, 0 rows affected (0.00 sec)
mysql> CALL p5(12345)// Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x// +-------+
| @x |
+-------+
| 12345 |
+-------+
1 row in set (0.00 sec)

　　这个IN的例子演示的是有输入参数的过程。在过程体中我将会话变量x设定为参数p的值。然后调用过程，将12345传入参数p。选择显示会话变量@x，证明我们已经将参数值12345传入。

　　OUT example 输出的例子

mysql> CREATE PROCEDURE p6 (OUT p INT)
-> SET p = -5 // mysql> CALL p6(@y)// mysql> SELECT @y// +------+
| @y |
+------+
| -5 |
+------+

　　这是另一个例子。这次的p是输出参数，然后在过程调用中将p的值传入会话变量@y中。

　　在过程体中，我们给参数赋值-5，在调用后我们可以看出，OUT是告诉DBMS值是从过程中传出的。

　　同样我们可以用语句

"SET @y = -5;"

　　来达到同样的效果

　　Compound Statements 复合语句

　　现在我们展开的详细分析一下过程体：

以下是引用片段： CREATE PROCEDURE p7 () BEGIN SET @a = 5; SET @b = 5; INSERT INTO t VALUES (@a); SELECT s1 * @a FROM t WHERE s1 >= @b; END; // /* I won't CALL this. 这个语句将不会被调用 */

　　完成过程体的构造就是BEGIN/END块。这个BEGIN/END语句块和Pascal语言中的BEGIN/END是基本相同的，和C语言的框架是很相似的。我们可以使用块去封装多条语句。在这个例子中，我们使用了多条设定会话变量的语句，然后完成了一些insert和select语句。如果你的过程体中有多条语句，那么你就需要BEGIN/END块了。BEGIN/END块也被称为复合语句，在这里你可以进行变量定义和流程控制。