mysql 5.5 创建存储过程_Mysql5存储过程教程

[From] http://www.uplinux.com/www/dev/04/index.shtml

Introduction 简介

MySQL 5.0

新特性教程是为需要了解5.0版本新特性的MySQL老用户而写的。简单的来说是介绍了“存储过程、触发器、视图、信息架构视图”,我打算每天一部分,来

连载这个教程.如果你想看一次看完PDF版本的教程,请到down.phpv.net下载.在此感谢译者陈朋奕的努力.

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

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)编写的程序运行起来

那么快。但是提升速度的主要方法却在于能否降低网络信息流量。如果你需要处理的是需要检查、循环、多语句但没有用户交互的重复性任务，你就可以使用保存在

服务器上的存储过程来完成。这样在执行任务的每一步时服务器和客户端之间就没那么多的信息来往了。转载请注明翻译者陈朋奕及转自:www.phpv.net

所

以存储过程是可复用的组件！想象一下如果你改变了主机的语言，这对存储过程不会产生影响，因为它是数据库逻辑而不是应用程序。存储过程是可以移植的！当你

用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系统的电脑，你可能会在其他子目录下运行以下程序：

easy@phpv:~> /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客户端返回的结果，这样意味着我有极大的特权。转载请注明翻译者陈朋奕及转自:www.phpv.net

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语句用在存储过程中，这里只是为了演示。所以使用这样的语句，能在调用时更好的看出程序是否正常工作。

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标准中把这个定义为非核心的，即可选组件。转载请注明翻译者陈朋奕及转自:www.phpv.net

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

CREATE PROCEDURE p1 ()

CREATE PROCEDURE p2 () DELETE FROM t; //下面这些对MySQL 5.0来说全新的语句，过程体中是非法的?lt;BR>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;".好了，主要的知识点"创建和调用过程方法"已经清楚了。我希望你能对自己说这相当简单。但是很快我们就有一系列的练习，每次都加一条子句，或者改变已经存在的子句。那样在写复杂部件前我们将会有很多可用的子句。转载请注明翻译者陈朋奕及转自:www.phpv.net

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内置的优化程序不会注意这个，至少在现在不注意。转载请注明翻译者陈朋奕及转自:www.phpv.net

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)

则是告诉服务器在这一步仍然要检查调用者的权限。转载请注明翻译者陈朋奕及转自:www.phpv.net

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一些题外话

转载请注明翻译者陈朋奕及转自:www.phpv.net

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，不用担心，它仍然能工作，只要它第一次使用时能正常工作。转载请注明翻译者陈朋奕及转自:www.phpv.net

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中。转载请注明翻译者陈朋奕及转自:www.phpv.net

在过程体中，我们给参数赋值-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块也被称为复合语句，在这里你可以进行变量定义和流程控制。

The New SQL Statements 新SQL语句

Variables 变量

在复合语句中声明变量的指令是DECLARE。

(1) Example with two DECLARE statements

两个DECLARE语句的例子

CREATE PROCEDURE p8 ()

BEGIN

DECLARE a INT;

DECLARE b INT;

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块中声明变量和它们的类型。

变量一旦声明，你就能在任何能使用会话变量、文字、列名的地方使用。

(2) Example with no DEFAULT clause and SET statement

没有默认子句和设定语句的例子

CREATE PROCEDURE p9 ()

BEGIN

DECLARE a INT /* there is no DEFAULT clause */;

DECLARE b INT /* there is no DEFAULT clause */;

SET a = 5; /* there is a SET statement */

SET b = 5; /* there is a SET statement */

INSERT INTO t VALUES (a);

SELECT s1 * a FROM t WHERE s1 >= b;

END; // /* I won't CALL this */

有很多初始化变量的方法。如果没有默认的子句，那么变量的初始值为NULL。你可以在任何时候使用SET语句给变量赋值。

(3) Example with DEFAULT clause

含有DEFAULT子句的例子

CREATE PROCEDURE p10 ()

BEGIN

DECLARE a, b INT DEFAULT 5;

INSERT INTO t VALUES (a);

SELECT s1 * a FROM t WHERE s1 >= b;

END; //

我们在这里做了一些改变，但是结果还是一样的。在这里使用了DEFAULT子句来设定初

始值，这就不需要把DECLARE和SET语句的实现分开了。

(4) Example of CALL

调用的例子

mysql> CALL p10() //

+--------+

| s1 * a |

+--------+

| 25 |

| 25 |

+--------+

2 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

结果显示了过程能正常工作

(5) Scope

作用域

CREATE PROCEDURE p11 ()

BEGIN

DECLARE x1 CHAR(5) DEFAULT 'outer';

BEGIN

DECLARE x1 CHAR(5) DEFAULT 'inner';

SELECT x1;

END;

SELECT x1;

END; //

现在我们来讨论一下作用域的问题。例子中有嵌套

的BEGIN/END块，当然这是合法的。同时包含两个变量，名字都

是x1，这样也是合法的。内部的变量在其作用域内享有更高的优先权。当执行到END语句时，内部变量消失，此时已经在其作用域外，变量不再可见了，因此在

存储过程外再也不能找到这个声明了的变量，但是你可以通过OUT参数或者将其值指派 给会话变量来保存其值。

调用作用域例子的过程：

mysql> CALL p11()//

+-------+

| x1 |

+-------+

| inner |

+-------+

+-------+

| x1 |

+-------+

| outer |

+-------+

我们看到的结果时第一个SELECT语句检索到最内层的变量，第二个检索到第二层的变量

Conditions and IF-THEN-ELSE 条件式和IF-THEN-ELSE

1.

现在我们可以写一些包含条件式的例子：

CREATE PROCEDURE p12 (IN parameter1 INT)

BEGIN

DECLARE variable1 INT;

SET variable1 = parameter1 + 1;

IF variable1 = 0 THEN

INSERT INTO t VALUES (17);

END IF;

IF parameter1 = 0 THEN

UPDATE t SET s1 = s1 + 1;

ELSE

UPDATE t SET s1 = s1 + 2;

END IF;

END; //

这里是一个包含IF语句的过程。里面有两个IF语句，一个是IF语句END IF，另一个是IF语句ELSE语句END IF。我们可以在这里使用复杂的过程，但我会尽量使其简单让你能更容易弄清楚。

2.

CALL p12 (0) //

我们调用这个过程，传入值为0，这样parameter1的值将为0。

3.

CREATE PROCEDURE p12 (IN parameter1 INT)

BEGIN

DECLARE variable1 INT;

SET variable1 = parameter1 + 1;

IF variable1 = 0 THEN

INSERT INTO t VALUES (17);

END IF;

IF parameter1 = 0 THEN

UPDATE t SET s1 = s1 + 1;

ELSE

UPDATE t SET s1 = s1 + 2;

END IF;

END; //

这里变量variable1被赋值为parameter1加1的值，所以执行后变量variable1为1。

4.

CREATE PROCEDURE p12 (IN parameter1 INT)

BEGIN

DECLARE variable1 INT;

SET variable1 = parameter1 + 1;

IF variable1 = 0 THEN

INSERT INTO t VALUES (17);

END IF;

IF parameter1 = 0 THEN

UPDATE t SET s1 = s1 + 1;

ELSE

UPDATE t SET s1 = s1 + 2;

END IF;

END; //

因为变量variable1值为1，因此条件"if variable1 = 0"为假，

IF

……

END IF

被跳过，没有被执行。

5.

CREATE PROCEDURE p12 (IN parameter1 INT)

BEGIN

DECLARE variable1 INT;

SET variable1 = parameter1 + 1;

IF variable1 = 0 THEN

INSERT INTO t VALUES (17);

END IF;

IF parameter1 = 0 THEN

UPDATE t SET s1 = s1 + 1;

ELSE

UPDATE t SET s1 = s1 + 2;

END IF;

END; //

到第二个IF条件，判断结果为真，于是中间语句被执行了

6.

CREATE PROCEDURE p12 (IN parameter1 INT)

BEGIN

DECLARE variable1 INT;

SET variable1 = parameter1 + 1;

IF variable1 = 0 THEN

INSERT INTO t VALUES (17);

END IF;

IF parameter1 = 0 THEN

UPDATE t SET s1 = s1 + 1;

ELSE

UPDATE t SET s1 = s1 + 2;

END IF;

END; //

因为参数parameter1值等于0，UPDATE语句被执行。如果parameter1值为NULL，则下一条

UPDATE

语句将被执行现在表t中有两行，他们都包含值5，所以如果我们调用p12，两行的值会变成6。

7.

mysql> CALL p12(0)//

Query OK, 2 rows affected (0.28 sec)

mysql> SELECT * FROM t//

+------+

| s1 |

+------+

| 6 |

| 6 |

+------+

2 rows in set (0.01 sec)

结果也是我们所期望的那样。

CASE 指令

1.

CREATE PROCEDURE p13 (IN parameter1 INT)

BEGIN

DECLARE variable1 INT;

SET variable1 = parameter1 + 1;

CASE variable1

WHEN 0 THEN INSERT INTO t VALUES (17);

WHEN 1 THEN INSERT INTO t VALUES (18);

ELSE INSERT INTO t VALUES (19);

END CASE;

END; //

Error Handling 异常处理

好了，我们现在要讲的是异常处理

1. Sample Problem: Log Of Failures   问题样例：故障记录

当INSERT失败时，我希望能将其记录在日志文件中我们用来展示出错处理的问题样例是很

普通的。我希望得到错误的记录。当INSERT失败时，我想在另一个文件中记下这些错误的

信息，例如出错时间，出错原因等。我对插入特别感兴趣的原因是它将违反外键关联的约束

2. Sample Problem: Log Of Failures (2)

mysql> CREATE TABLE t2

s1 INT, PRIMARY KEY (s1))

engine=innodb;//

mysql> CREATE TABLE t3 (s1 INT, KEY (s1),

FOREIGN KEY (s1) REFERENCES t2 (s1))

engine=innodb;//

mysql> INSERT INTO t3 VALUES (5);//

...

ERROR 1216 (23000): Cannot add or update a child row: a foreign key

constraint fails(这里显示的是系统的出错信息)

我开始要创建一个主键表，以及一个外键表。我们使用的是InnoDB，因此外键关联检查是打

开的。然后当我向外键表中插入非主键表中的值时，动作将会失败。当然这种条件下可以很

快找到错误号1216。转载请注明翻译者陈朋奕及转自:www.phpv.net

3. Sample Problem: Log Of Failures

CREATE TABLE error_log (error_message

CHAR(80))//

下一步就是建立一个在做插入动作出错时存储错误的表。

4. Sample Problem: Log Of Errors

CREATE PROCEDURE p22 (parameter1 INT)

BEGIN

DECLARE EXIT HANDLER FOR 1216

INSERT INTO error_log VALUES

(CONCAT('Time: ',current_date,

'. Foreign Key Reference Failure For

Value = ',parameter1));

INSERT INTO t3 VALUES (parameter1);

END;//上面就是我们的程序。这里的第一个语句DECLARE EXIT HANDLER是用来处理异常的。意

思是如果错误1215发生了，这个程序将会在错误记录表中插入一行。EXIT意思是当动作成功提交后退出这个复合语句。转载请注明翻译者陈朋奕及转自:www.phpv.net

5. Sample Problem: Log Of Errors

CALL p22 (5) //

调用这个存储过程会失败，这很正常，因为5值并没有在主键表中出现。但是没有错误信息

返回因为出错处理已经包含在过程中了。t3表中没有增加任何东西，但是error_log表中记录

下了一些信息，这就告诉我们INSERT into table t3动作失败。

DECLARE HANDLER syntax 声明异常处理的语法

DECLARE

{ EXIT | CONTINUE }

HANDLER FOR

{ error-number | { SQLSTATE error-string } | condition }

SQL statement

上面就是错误处理的用法，也就是一段当程序出错后自动触发的代码。MySQL允许两种处理器，

一种是EXIT处理，我们刚才所用的就是这种。另一种就是我们将要演示的，CONTINUE处理，

它跟EXIT处理类似，不同在于它执行后，原主程序仍然继续运行，那么这个复合语句就没有出

口了。

1. DECLARE CONTINUE HANDLER example CONTINUE处理例子

CREATE TABLE t4 (s1 int,primary key(s1));//

CREATE PROCEDURE p23 ()

BEGIN

DECLARE CONTINUE HANDLER

FOR SQLSTATE '23000' SET @x2 = 1;

SET @x = 1;

INSERT INTO t4 VALUES (1);

SET @x = 2;

INSERT INTO t4 VALUES (1);

SET @x = 3;

END;//

这是MySQL参考手册上的CONTINUE处理的例子，这个例子十分好，所以我把它拷贝到这里。

通过这个例子我们可以看出CONTINUE处理是如何工作的。

2. DECLARE CONTINUE HANDLER声明CONTINUE异常处理

CREATE TABLE t4 (s1 int,primary key(s1));//

CREATE PROCEDURE p23 ()

BEGIN

DECLARE CONTINUE HANDLER

FOR SQLSTATE '23000' SET @x2 = 1;

SET @x = 1;

INSERT INTO t4 VALUES (1);

SET @x = 2;

INSERT INTO t4 VALUES (1);

SET @x = 3;

END;//

这次我将为SQLSTATE值定义一个处理程序。还记得前面我们使用的MySQL错误代码1216吗？

事实上这里的23000SQLSTATE是更常用的，当外键约束出错或主键约束出错就被调用了。

3. DECLARE CONTINUE HANDLER

CREATE TABLE t4 (s1 int,primary key(s1));//

CREATE PROCEDURE p23 ()

BEGIN

DECLARE CONTINUE HANDLER

FOR SQLSTATE '23000' SET @x2 = 1;

SET @x = 1;

INSERT INTO t4 VALUES (1);

SET @x = 2;

INSERT INTO t4 VALUES (1);

SET @x = 3;

END;//

这个存储过程的第一个执行的语句是"SET @x = 1"。

4. DECLARE CONTINUE HANDLER example

CREATE TABLE t4 (s1 int,primary key(s1));//

CREATE PROCEDURE p23 ()

BEGIN

DECLARE CONTINUE HANDLER

FOR SQLSTATE '23000' SET @x2 = 1;

SET @x = 1;

INSERT INTO t4 VALUES (1);

SET @x = 2;

INSERT INTO t4 VALUES (1);

SET @x = 3;

END;//

运行后值1被插入到主键表中。转载请注明翻译者陈朋奕及转自:www.phpv.net

5. DECLARE CONTINUE HANDLER

CREATE TABLE t4 (s1 int,primary key(s1));//

CREATE PROCEDURE p23 ()

BEGIN

DECLARE CONTINUE HANDLER

FOR SQLSTATE '23000' SET @x2 = 1;

SET @x = 1;

INSERT INTO t4 VALUES (1);

SET @x = 2;

INSERT INTO t4 VALUES (1);

SET @x = 3;

END;//

然后@x的值变为2。

6. DECLARE CONTINUE HANDLER example

CREATE TABLE t4 (s1 int,primary key(s1));//

CREATE PROCEDURE p23 ()

BEGIN

DECLARE CONTINUE HANDLER

FOR SQLSTATE '23000' SET @x2 = 1;

SET @x = 1;

INSERT INTO t4 VALUES (1);

SET @x = 2;

INSERT INTO t4 VALUES (1);

SET @x = 3;

END;//

然后程序尝试再次往主键表中插入数值，但失败了，因为主键有唯一性限制。

7. DECLARE CONTINUE HANDLER example

CREATE TABLE t4 (s1 int,primary key(s1));//

CREATE PROCEDURE p23 ()

BEGIN

DECLARE CONTINUE HANDLER

FOR SQLSTATE '23000' SET @x2 = 1;

SET @x = 1;

INSERT INTO t4 VALUES (1);

SET @x = 2;

INSERT INTO t4 VALUES (1);

SET @x = 3;

END;//

由于插入失败，错误处理程序被触发，开始进行错误处理。下一个执行的语句是错误处理的语

句，@x2被设为2。

8. DECLARE CONTINUE HANDLER example

CREATE TABLE t4 (s1 int,primary key(s1));//

CREATE PROCEDURE p23 ()

BEGIN

DECLARE CONTINUE HANDLER

FOR SQLSTATE '23000' SET @x2 = 1;

SET @x = 1;

INSERT INTO t4 VALUES (1);

SET @x = 2;

INSERT INTO t4 VALUES (1);

SET @x = 3;

END;//

到这里并没有结束，因为这是CONTINUE异常处理。所以执行返回到失败的插入语句之后，

继续执行将@x设定为3动作。转载请注明翻译者陈朋奕及转自:www.phpv.net

9. DECLARE CONTINUE HANDLER example

mysql> CALL p23()//

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @x, @x2//

+------+------+

| @x  | @x2 |

+------+------+

|  3  |  1 |

+------+------+

1 row in set (0.00 sec)

运行过程后我们观察@x的值，很确定的可以知道是3，观察@x2的值，为1。从这里可以

判断程序运行无误，完全按照我们的思路进行。大家可以花点时间去调整错误处理器，让

检查放在语句段的首部，而不是放在可能出现错误的地方，虽然那样看起来程序很紊乱，

跳来跳去的感觉。但是这样的代码很安全也很清楚。

1. DECLARE CONDITION

CREATE PROCEDURE p24 ()

BEGIN

DECLARE `Constraint Violation`

CONDITION FOR SQLSTATE '23000';

DECLARE EXIT HANDLER FOR

`Constraint Violation` ROLLBACK;

START TRANSACTION;

INSERT INTO t2 VALUES (1);

INSERT INTO t2 VALUES (1);

COMMIT;

END; //

这是另外一个错误处理的例子，在前面的基础上修改的。事实上你可给SQLSTATE或者错误代码其他的名字，你就可以在处理中使用自己定义的名字了。下面看它是怎么实现的：我把表t2

定义为InnoDB表，所以对这个表的插入操作都会ROLLBACK(回滚)，ROLLBACK(回滚事务)也是恰好会发生的。因为对主键插入两个同样的值会导致SQLSTATE 23000错误发生，这里SQLSTATE 23000是约束错误。

2. DECLARE CONDITION声明条件

CREATE PROCEDURE p24 ()

BEGIN

DECLARE `Constraint Violation`

CONDITION FOR SQLSTATE '23000';

DECLARE EXIT HANDLER FOR

`Constraint Violation` ROLLBACK;

START TRANSACTION;

INSERT INTO t2 VALUES (1);

INSERT INTO t2 VALUES (1);

COMMIT;

END; //

这个约束错误会导致ROLLBACK(回滚事务)和SQLSTATE 23000错误发生。

3. DECLARE CONDITION

mysql> CALL p24()//

Query OK, 0 rows affected (0.28 sec)

mysql> SELECT * FROM t2//

Empty set (0.00 sec)

我们调用这个存储过程看结果是什么，从上面结果我们看到表t2没有插入任何记录。全部事务都回滚了。这正是我们想要的。

4. DECLARE CONDITION

mysql> CREATE PROCEDURE p9 ()

-> BEGIN

-> DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;

-> DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN END;

-> DECLARE EXIT HANDLER FOR SQLWARNING BEGIN END;

-> END;//

Query OK, 0 rows affected (0.00 sec)

这里是三个预声明的条件：NOT FOUND (找不到行), SQLEXCEPTION (错误),SQLWARNING (

警告或注释)。因为它们是预声明的，因此不需要声明条件就可以使用。不过如果你去做这样的声明："DECLARE SQLEXCEPTION CONDITION ..."，你将会得到错误信息提示。

Cursors 游标

游标实现功能摘要：

DECLARE cursor-name CURSOR FOR SELECT ...;

OPEN cursor-name;

FETCH cursor-name INTO variable [, variable];

CLOSE cursor-name;

现在我们开始着眼游标了。虽然我们的存储过程中的游标语法还并没有完整的实现，但是

已经可以完成基本的事务如声明游标，打开游标，从游标里读取，关闭游标。

1. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)

BEGIN

DECLARE a,b INT;

DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;

DECLARE CONTINUE HANDLER FOR NOT FOUND

SET b = 1;

OPEN cur_1;

REPEAT

FETCH cur_1 INTO a;

UNTIL b = 1

END REPEAT;

CLOSE cur_1;

SET return_val = a;

END;//

我们看一下包含游标的存储过程的新例子。

2. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)

BEGIN

DECLARE a,b INT;

DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;

DECLARE CONTINUE HANDLER FOR NOT FOUND

SET b = 1;

OPEN cur_1;

REPEAT

FETCH cur_1 INTO a;

UNTIL b = 1

END REPEAT;

CLOSE cur_1;

SET return_val = a;

END;//

这个过程开始声明了三个变量。附带说一下，顺序是十分重要的。首先要进行变量声明，

然后声明条件，随后声明游标，再后面才是声明错误处理器。如果你没有按顺序声明，

系统会提示错误信息。

3. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)

BEGIN

DECLARE a,b INT;

DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;

DECLARE CONTINUE HANDLER FOR NOT FOUND

SET b = 1;

OPEN cur_1;

REPEAT

FETCH cur_1 INTO a;

UNTIL b = 1

END REPEAT;

CLOSE cur_1;

SET return_val = a;

END;//

程序第二步声明了游标cur_1，如果你使用过嵌入式SQL的话，就知道这和嵌入式SQL差不多。

4. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)

BEGIN

DECLARE a,b INT;

DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;

DECLARE CONTINUE HANDLER FOR NOT FOUND

SET b = 1;

OPEN cur_1;

REPEAT

FETCH cur_1 INTO a;

UNTIL b = 1

END REPEAT;

CLOSE cur_1;

SET return_val = a;

END;//

最后进行的是错误处理器的声明。这个CONTINUE处理没有引用SQL错误代码和SQLSTATE值。

它使用的是NOT FOUND系统返回值，这和SQLSTATE 02000是一样的。

转载请注明翻译者陈朋奕及转自:www.phpv.net

5. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)

BEGIN

DECLARE a,b INT;

DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;

DECLARE CONTINUE HANDLER FOR NOT FOUND

SET b = 1;

OPEN cur_1;

REPEAT

FETCH cur_1 INTO a;

UNTIL b = 1

END REPEAT;

CLOSE cur_1;

SET return_val = a;

END;//

过程第一个可执行的语句是OPEN cur_1，它与SELECT s1 FROM t语句是关联的，过程将执行

SELECT s1 FROM t，返回一个结果集。

6. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)

BEGIN

DECLARE a,b INT;

DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;

DECLARE CONTINUE HANDLER FOR NOT FOUND

SET b = 1;

OPEN cur_1;

REPEAT

FETCH cur_1 INTO a;

UNTIL b = 1

END REPEAT;

CLOSE cur_1;

SET return_val = a;

END;//

这里第一个FETCH语句会获得一行从SELECT产生的结果集中检索出来的值，然而表t中有多

行，因此这个语句会被执行多次，当然这是因为语句在循环块内。

7. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)

BEGIN

DECLARE a,b INT;

DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;

DECLARE CONTINUE HANDLER FOR NOT FOUND

SET b = 1;

OPEN cur_1;

REPEAT

FETCH cur_1 INTO a;

UNTIL b = 1

END REPEAT;

CLOSE cur_1;

SET return_val = a;

END;//

最后当MySQL的FETCH没有获得行时，CONTINUE处理被触发，将变量b赋值为1。

8. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)

BEGIN

DECLARE a,b INT;

DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;

DECLARE CONTINUE HANDLER FOR NOT FOUND

SET b = 1;

OPEN cur_1;

REPEAT

FETCH cur_1 INTO a;

UNTIL b = 1

END REPEAT;

CLOSE cur_1;

SET return_val = a;

END;//

到了这一步UNTIL b=1条件就为真，循环结束。在这里我们可以自己编写代码关闭游标，

也可以由系统执行，系统会在复合语句结束时自动关闭游标，但是最好不要太依赖系统的

自动关闭行为(译注：这可能跟Java的Gc一样，不可信)。

9. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)

BEGIN

DECLARE a,b INT;

DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;

DECLARE CONTINUE HANDLER FOR NOT FOUND

SET b = 1;

OPEN cur_1;

REPEAT

FETCH cur_1 INTO a;

UNTIL b = 1

END REPEAT;

CLOSE cur_1;

SET return_val = a;

END;//

这个例程中我们为输出参数指派了一个局部变量，这样在过程结束后的结果仍能使用。

转载请注明翻译者陈朋奕及转自:www.phpv.net

10. Cursor Example

CREATE PROCEDURE p25 (OUT return_val INT)

BEGIN

DECLARE a,b INT;

DECLARE cur_1 CURSOR FOR SELECT s1 FROM t;

DECLARE CONTINUE HANDLER FOR NOT FOUND

SET b = 1;

OPEN cur_1;

REPEAT

FETCH cur_1 INTO a;

UNTIL b = 1

END REPEAT;

CLOSE cur_1;

SET return_val = a;

END;//

mysql> CALL p25(@return_val)//

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @return_val//

+-------------+

| @return_val |

+-------------+

|    5     |

+-------------+

1 row in set (0.00 sec)

上面是过程调用后的结果。可以看到return_val参数获得了数值5，因为这是表t的最后一行。

由此可以知道游标工作正常，出错处理也工作正常。

Cursor Characteristics 游标的特性

摘要：转载请注明翻译者陈朋奕及转自:www.phpv.net

READ ONLY只读属性

NOT SCROLLABLE顺序读取

ASENSITIVE敏感

在5.0版的MySQL中，你只可以从游标中取值，不能对其进行更新。因为游标是(READ

ONLY)只读的。你可以这样做：

FETCH cursor1 INTO variable1;

UPDATE t1 SET column1 = 'value1' WHERE CURRENT OF cursor1;

游标也是不可以滚动的，只允许逐一读取下一行，不能在结果集中前进或后退。下面代码就

是错误的：

FETCH PRIOR cursor1 INTO variable1;

FETCH ABSOLUTE 55 cursor1 INTO variable1;

同时也不允许在已打开游标进行操作的表上执行updates事务，因为游标是(ASENSITIVE)敏感的。因为如果你不阻止update事务，那就不知道结果会变成什么。如果你使用的是InnoDB

而不是MyISAM存储引擎的话，结果也会不一样。

Security 安全措施

摘要

Privileges (1) CREATE ROUTINE

Privileges (2) EXECUTE

Privileges (3) GRANT SHOW ROUTINE?

Privileges (4) INVOKERS AND DEFINERS

这里我们要讨论一些关于特权和安全相关的问题。但因为在MySQL安全措施的功能并没有完全，所以我们不会对其进行过多讨论。

1. Privileges CREATE ROUTINE

GRANT CREATE ROUTINE

ON database-name . *

TO user(s)

[WITH GRANT OPTION];

现在用root就可以了

在这里要介绍的特权是CREATE ROUTINE，它不仅同其他特权一样可以创建存储过程和函数，

还可以创建视图和表。Root用户拥有这种特权，同时还有ALTER ROUTINE特权。

2. Privileges EXECUTE

GRANT EXECUTE ON p TO peter

[WITH GRANT OPTION];

上面的特权是决定你是否可以使用或执行存储过程的特权，过程创建者默认拥有这个特权。

3. Privileges SHOW ROUTINE?

GRANT SHOW ROUTINE ON db6.* TO joey

[WITH GRANT OPTION];

因为我们已经有控制视图的特权了：GRANT SHOW VIEW。所以在这个基础上，为了保证兼容，

日后可能会添加GRANT SHOW ROUTINE特权。这样做是不太符合标准的，在写本书的时候，MySQL还没实现这个功能。

4. Privileges Invokers and Definers 特权调用者和定义者

CREATE PROCEDURE p26 ()

SQL SECURITY INVOKER

SELECT COUNT(*) FROM t //

CREATE PROCEDURE p27 ()

SQL SECURITY DEFINER

SELECT COUNT(*) FROM t //

GRANT INSERT ON db5.* TO peter; //

现在我们测试一下SQL SECURITY子句吧。Security是我们前面提到的程序特性的一部分。你root

用户，将插入权赋给了peter。然后使用peter登陆进行新的工作，我们看peter可以怎么使用存储过程，注意：peter没有对表t的select权力，只有root用户有。

5. Privileges Invokers and Definers

/* Logged on with current_user = peter */使用帐户peter登陆

mysql> CALL p26();

ERROR 1142 (42000): select command denied to user

'peter'@'localhost'

for table 't'

mysql> CALL p27();

+----------+

| COUNT(*) |

+----------+

|    1     |

+----------+

1 row in set (0.00 sec)

当peter尝试调用含有调用保密措施的过程p26时会失败。那是因为peter没有对表的select的权力。

但是当petre调用含有定义保密措施的过程时就能成功。原因是root有select权力，Peter有root的

权力，因此过程可以执行。

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

［From］http://www.linuxeden.com/doc/23907.html

mysql5存储过程编写实践作者：松哥

email：jccz_zys@tom.com

MSN: jccz_zys@163.net

QQ: 15210449

MySql5.0以后均支持存储过程，最近有空，研究了一把这个

格式：

CREATE PROCEDURE 过程名 ([过程参数[,...]])

[特性 ...] 过程体

CREATE FUNCTION 函数名 ([函数参数[,...]])

RETURNS 返回类型

[特性 ...] 函数体

过程参数:

[ IN | OUT | INOUT ] 参数名 参数类型

函数参数:

参数名 参数类型

返回类型:

有效的MySQL数据类型即可

特性:

LANGUAGE SQL

| [NOT] DETERMINISTIC

| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }

| SQL SECURITY { DEFINER | INVOKER }

| COMMENT 'string'

过程体/函数体:格式如下：

BEGIN

有效的SQL语句

END

我在这里不关心专有的特性，这些与SQL规范不兼容，所以characteristic(特性)的相关内容不作考虑。

//

在开发过程中有几点要注意：

1、存储过程注释:MySQL支持采用--或者/**/注释，其中前者是行注释，后者是段式注释

2、变量首先用declare申明，其中临时变量可以直接以@前缀修饰以供引用

3、直接采用MySQL的Administrator管理器编辑时，可以直接采用如下函数文本录入；

但若在脚本中自动导入存储过程或函数时，由于MySQL默认以";"为分隔符，则过程体的每一句

都被MySQL以存储过程编译，则编译过程会报错；所以要事先用DELIMITER关键字申明当前段分隔符

用完了就把分隔符还原。  如下所示：

DELIMITER ＄＄

Stored Procedures and Functions

DELIMITER ;

4、MySQL支持大量的内嵌函数，有些是和大型商用数据库如oracle、informix、sybase等一致，

但也有些函数名称不一致，但功能一致；或者有些名称一致，但功能相异，这个特别对于从

这些数据库开发转过来的DBA要注意。

5、存储过程或函数的调试：我目前还没有研究MySQL所带的各种工具包，还不清楚其提供了调试工具

没有，不过编译错误相对好查找；至于业务流程的调试，可以采用一个比较笨的方法，就是创建一

个调试表，在包体中各个流程点都插入一条记录，以观察程序执行流程。这也是一个比较方便的笨

办法。^_^

下面是2个例子，提供了一种字串加密的算法，每次以相同的入参调用都会得到不同的加密结果，

算法相对比较简单，不具备强度。分别以函数和过程的形式分别实现如下：

(1)函数

eg:

CREATE FUNCTION fun_addmm(inpass varchar(10)) RETURNS varchar(11)

BEGIN

declare string_in varchar(39);

declare string_out varchar(78);

declare offset tinyint(2);

declare outpass varchar(30) default ';

declare len tinyint;

/*declare i tinyint;*/

/**/

set len=LENGTH(inpass);

if((len<=0) or (len>10)) then

return "";

end if;

set offset=(SECOND(NOW()) mod 39)+1; /*根据秒数取模*/

/*insert into  testtb values(offset,'offset: ');*/

set string_out='YN8K1JOZVURB3MDETS5GPL27AXWIHQ94C6F0#＄_';  /*密钥*/

set string_in='_＄#ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';

set outpass=CONCAT(outpass,SUBSTRING(string_out,offset,1));

/*     insert into  testtb values(2,outpass);*/

set string_out=CONCAT(string_out,string_out);

set @i=0;

REPEAT

set @i=@i+1;

set outpass=CONCAT(outpass,SUBSTR(string_out,INSTR(string_in,SUBSTRING(inpass,@i,1))+offset,1));

/*       insert into  testtb values(@i+2,outpass);*/

UNTIL (@i>=len)

end REPEAT;

return outpass;

END

(2)过程

CREATE PROCEDURE `pro_addmm`(IN inpass varchar(10),OUT outpass varchar(11))

BEGIN

declare string_in varchar(39);

declare string_out varchar(78);

declare offset tinyint(2);

declare len tinyint;

set outpass=';

set len=LENGTH(inpass);

if((len<=0) or (len>10)) then

set outpass=';

else

set offset=(SECOND(NOW()) mod 39)+1;

set string_out='YN8K1JOZVURB3MDETS5GPL27AXWIHQ94C6F0#＄_';

set string_in='_＄#ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';

set outpass=CONCAT(outpass,SUBSTRING(string_out,offset,1));

set string_out=CONCAT(string_out,string_out);

set @i=0;

REPEAT

set @i=@i+1;

set outpass=CONCAT(outpass,SUBSTR(string_out,INSTR(string_in,SUBSTRING(inpass,@i,1))+offset,1));

UNTIL (@i>=len)

end REPEAT;

end if;

END

//

执行结果如下：

mysql> call pro_addmm('zhouys',@a);

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a;

+---------+

| @a      |

+---------+

| U_PI6＄4 |

+---------+

1 row in set (0.00 sec)

mysql> call pro_addmm('zhouys',@a);

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a;

+---------+

| @a      |

+---------+

| 9P8UEGM |

+---------+

1 row in set (0.00 sec)

mysql> select fun_submm('U_PI6＄4');

+----------------------+

| fun_submm('U_PI6＄4') |

+----------------------+

| ZHOUYS               |

+----------------------+

1 row in set (0.00 sec)

加密算法有几个弱点：

1、不支持大小写

2、不支持中文

3、加密强度不够

有兴趣的人可以研究一下解密函数如何编写，这里就不赘述了。