MySQL 5.0 新特征
发布时间：2022-04-01 10:27:43 所属栏目：编程 来源：互联网
导读：Introduction 简介 MySQL 5.0 新特性教程是为需要了解5.0版本新特性的MySQL老用户而写的。简单的来说是介绍了存储过程、触发器、视图、信息架构视图,我打算每天一部分,来 连载这个教程.如果你想看一次看完PDF版本的教程,请到down.phpv.net下载.在此感谢译者
|
Introduction 简介 MySQL 5.0 新特性教程是为需要了解5.0版本新特性的MySQL老用户而写的。简单的来说是介绍了“存储过程、触发器、视图、信息架构视图”,我打算每天一部分,来 连载这个教程.如果你想看一次看完PDF版本的教程,请到down.phpv.net下载.在此感谢译者陈朋奕的努力. 希望这本书能像内行专家那样与您进行对话，用简单的问题、例子让你学到需要的知识。为了达到这样的目的，我会从每一个细节开始慢慢的为大家建立概念，最后会给大家展示较大的实用例，在学习之前也许大家会认为这个用例很难，但是只要跟着课程去学，相信很快就能掌握。[@more@]MySQL 5.0 新特性教程 第一部分:第一讲 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语句用在存储过程中，这里只是为了演示。所以使用这样的语句，能在调用时更好的看出程序是否正常工作。 ySQL 5.0 新特性教程 第一部分存储过程:第二讲 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块也被称为复合语句，在这里你可以进行变量定义和流程控制。 第三讲:新SQL语句,Loops 循环语句 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; // 如果需要进行更多条件真假的判断我们可以使用CASE语句。CASE语句使用和IF一样简单。 我们可以参考上面的例子： 2. mysql> CALL p13(1)// Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM t// +------+ | s1 | +------+ | 6 | | 6 | | 19 | +------+ 3 rows in set (0.00 sec) 执行过程后，传入值1，如上面例子，值19被插入到表t中。 Question 问题 问题: CALL p13(NULL) //的作用是什么？ 另一个：这个CALL语句做了那些动作？ 你可以通过执行后观察SELECT做了什么，也可以根据代码判断，在5秒内做出。 Answer 答案 mysql> CALL p13(NULL)// Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM t// +------+ | s1 | +------+ | 6 | | 6 | | 19 | | 19 | +------+ 4 rows in set (0.00 sec) 答案是当你调用p13时，MySQL插入了另一条包含数值19的记录。原因是变量variable1的值为NULL，CASE语句的ELSE部分就被执行了。希望这对大家有意义。如果你回答不出来，没有问题，我们可以继续向下走。 Loops 循环语句 WHILE ... END WHILE LOOP ... END LOOP REPEAT ... END REPEAT GOTO 下面我们将会创建一些循环。我们有三种标准的循环方式： WHILE循环，LOOP循环以及REPEAT循环。还有一种非标准的循环方式：GO TO（译者语：最好不要用吧，用了就使流程混乱）。 WHILE ... END WHILE CREATE PROCEDURE p14 () BEGIN DECLARE v INT; SET v = 0; WHILE v < 5 DO INSERT INTO t VALUES (v); SET v = v + 1; END WHILE; END; // 这是WHILE循环的方式。我很喜欢这种方式，它跟IF语句相似，因此不需要掌握很多新的语法。这里的INSERT和SET语句在WHILE和END WHILE之间，当变量v大于5的时候循环将会退出。使用 "SET v = 0;" 语句使为了防止一个常见的错误，如果没有初始化，默认变量值为NULL，而NULL和任何值操作结果都为NULL。 WHILE ... END WHILE example mysql> CALL p14()// Query OK, 1 row affected (0.00 sec) 以上就是调用过程p14的结果不用关注系统返回是"one row affected"还是"five rows affected"，因为这里的计数只对最后一个INSERT动作进行计数。 WHILE ... END WHILE example: CALL mysql> select * from t; // +------+ | s1 | +------+ .... | 0 | | 1 | | 2 | | 3 | | 4 | +------+ 9 rows in set (0.00 sec) 调用后可以看到程序向数据库中插入了5行。 REPEAT ... END REPEAT CREATE PROCEDURE p15 () BEGIN DECLARE v INT; SET v = 0; REPEAT INSERT INTO t VALUES (v); SET v = v + 1; UNTIL v >= 5 END REPEAT; END; // 这是一个REPEAT循环的例子，功能和前面WHILE循环一样。区别在于它在执行后检查结果，而WHILE则是执行前检查。（译者语：可能等同于DO WHILE吧） REPEAT ... END REPEAT: look at the UNTIL： UNTIL的作用 CREATE PROCEDURE p15 () BEGIN DECLARE v INT; SET v = 0; REPEAT INSERT INTO t VALUES (v); SET v = v + 1; UNTIL v >= 5 <-- END REPEAT; END; // 注意到UNTIL语句后面没有分号，在这里可以不写分号，当然你加上额外的分号更好。 REPEAT ... END REPEAT: calling ：调用 mysql> CALL p15()// Query OK, 1 row affected (0.00 sec) mysql> SELECT COUNT(*) FROM t// +----------+ | COUNT(*) | +----------+ | 14 | +----------+ 1 row in set (0.00 sec) 我们可以看到调用p15过程后又插入了5行记录 LOOP ... END LOOP CREATE PROCEDURE p16 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END IF; END LOOP; END; // 以上是LOOP循环的例子。 LOOP循环不需要初始条件，这点和WHILE循环相似，同时它又和REPEAT循环一样也不需要结束条件。 LOOP ... END LOOP: with IF and LEAVE 包含IF和LEAVE的LOOP循环 CREATE PROCEDURE p16 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN <-- LEAVE loop_label; END IF; END LOOP; END; // 在循环内部加入IF语句，在IF语句中包含LEAVE语句。这里LEAVE语句的意义是离开循环。 LEAVE的语法是LEAVE加循环语句标号，关于循环语句的标号问题我会在后面进一步讲解。 LOOP ... END LOOP: calling ：调用 mysql> CALL p16()// Query OK, 1 row affected (0.00 sec) mysql> SELECT COUNT(*) FROM t// +----------+ | COUNT(*) | +----------+ | 19 | +----------+ 1 row in set (0.00 sec) 调用过程p16后，结果是另5行被插入表t中。 Labels 标号 CREATE PROCEDURE p17 () label_1: BEGIN label_2: WHILE 0 = 1 DO LEAVE label_2; END WHILE; label_3: REPEAT LEAVE label_3; UNTIL 0 =0 END REPEAT; label_4: LOOP LEAVE label_4; END LOOP; END; // 最后一个循环例子中我使用了语句标号。现在这里有一个包含4个语句标号的过程的例子。我们可以在BEGIN、 WHILE、REPEAT或者LOOP语句前使用语句标号，语句标号只能在合法的语句前面使用。因此"LEAVE label_3"意味着离开语句标号名定义为label_3的语句或复合语句。 End Labels 标号结束符 CREATE PROCEDURE p18 () label_1: BEGIN label_2: WHILE 0 = 1 DO LEAVE label_2; END WHILE label_2; label_3: REPEAT LEAVE label_3; UNTIL 0 =0 END REPEAT label_3 ; label_4: LOOP LEAVE label_4; END LOOP label_4 ; END label_1 ; // 你也可以在语句结束时使用语句标号，和在开头时使用一样。这些标号结束符并不是十分有用。 它们是可选的。如果你需要，他们必须和开始定义的标号名字一样当然为了有良好的编程习惯， 方便他人阅读，最好还是使用标号结束符。 LEAVE and Labels 跳出和标号 CREATE PROCEDURE p19 (parameter1 CHAR) label_1: BEGIN label_2: BEGIN label_3: BEGIN IF parameter1 IS NOT NULL THEN IF parameter1 = 'a' THEN LEAVE label_1; ELSE BEGIN IF parameter1 = 'b' THEN LEAVE label_2; ELSE LEAVE label_3; END IF; END; END IF; END IF; END; END; END;// LEAVE 语句使程序跳出复杂的复合语句。 ITERATE 迭代如果目标是ITERATE（迭代）语句的话，就必须用到LEAVE语句 CREATE PROCEDURE p20 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP IF v = 3 THEN SET v = v + 1; ITERATE loop_label; END IF; INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END IF; END LOOP; END; // ITERATE （迭代）语句和LEAVE语句一样也是在循环内部的循环引用，它有点像C语言中的“Continue”，同样它可以出现在复合语句中，引用复合语句标号，ITERATE（迭代）意思是重新开始复合语句。 那我们启动并观察下面这个循环，这是个需要迭代过程的循环： ITERATE: Walking through the loop 深入循环 CREATE PROCEDURE p20 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP <-- IF v = 3 THEN SET v = v + 1; ITERATE loop_label; END IF; INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END IF; END LOOP; END; // 让这个已经定义了标号的循环运行起来。 ITERATE: Walking through the loop CREATE PROCEDURE p20 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP IF v = 3 THEN <-- SET v = v + 1; ITERATE loop_label; END IF; INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END IF; END LOOP; END; // v的值变成3，然后我们把它增加到4。 ITERATE: walking through the loop CREATE PROCEDURE p20 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP IF v = 3 THEN SET v = v + 1; ITERATE loop_label; <-- END IF; INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END IF; END LOOP; END; // 然后开始ITERATE（迭代）过程。 ITERATE: walking through the loop CREATE PROCEDURE p20 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP <-- IF v = 3 THEN SET v = v + 1; ITERATE loop_label; END IF; INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END IF; END LOOP; END; // 这里的ITERATE（迭代）让循环又回到了循环的头部。 ITERATE: walking through the loop CREATE PROCEDURE p20 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP IF v = 3 THEN SET v = v + 1; ITERATE loop_label; END IF; INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; <-- END IF; END LOOP; END; // 当v的值变为5时，程序将执行LEAVE语句 ITERATE: walking through the loop CREATE PROCEDURE p20 () BEGIN DECLARE v INT; SET v = 0; loop_label: LOOP IF v = 3 THEN SET v = v + 1; ITERATE loop_label; END IF; INSERT INTO t VALUES (v); SET v = v + 1; IF v >= 5 THEN LEAVE loop_label; END IF; END LOOP; END; // <-- LEAVE的结果就是跳出循环，使运行指令到达复合语句的最后一步。 GOTO CREATE PROCEDURE p... BEGIN ... LABEL label_name; ... GOTO label_name; ... END; MySQL的存储过程中可以使用GOTO语句。虽然这不是标准SQL语句，而且在这里建立标号的方法也和惯例中的不一样。由于为了和其他DBMS兼容，这个语句会慢被淘汰，所以我们在MySQL 参考手册中没有提及。 Grand combination 大组合 CREATE PROCEDURE p21 (IN parameter_1 INT, OUT parameter_2 INT) LANGUAGE SQL DETERMINISTIC SQL SECURITY INVOKER BEGIN DECLARE v INT; label goto_label; start_label: LOOP IF v = v THEN LEAVE start_label; ELSE ITERATE start_label; END IF; END LOOP start_label; REPEAT WHILE 1 = 0 DO BEGIN END; END WHILE; UNTIL v = v END REPEAT; GOTO goto_label; END;// 上面例子中的语句包含了我们之前讲的所有语法，包括参数列表，特性参数，BEGIN/END块复合语句，变量声明， IF，WHILE，LOOP，REPEAT，LEAVE，ITERATE，GOTO。这是一个荒谬的存储过程，我不会运行它，因为里面有无尽的循环。但是里面的语法却十分合法。这些是新的流程控制和变量声明语句。下面我们将要接触更多新的东西。 第一部分存储过程:异常处理,游标,游标的特性,安全措施 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的 权力，因此过程可以执行。 MySQL 5.0 触发器 Introduction 本书是为需要了解5.0版本新特性的MySQL老用户而写的。简单的来说介绍了 "存储过程、触发器、视图、信息架构视图"，这是介绍MySQL 5.0新特性丛书的第一集。希望这本书能像内行专家那样与您进行对话，用简单的问题、例子让你学到需要的知识。为了达到这样的目的，我会从每一个细节开始 慢的为大家建立概念，最后会给大家展示较大的实用例，在学习之前也许大家会认为这个用例很难，但是只要跟着课程去学，相信很快就能掌握。 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用户，这样就能得到比较好的支持和帮助。转载请注明翻译者陈朋奕及转自:www.phpv.net Why Triggers 为什么要用触发器 我们在MySQL 5.0中包含对触发器的支持是由于以下原因: MySQL早期版本的用户长期有需要触发器的要求。 我们曾经许诺支持所有ANSI标准的特性。 您可以使用它来检查或预防坏的数据进入数据库。 您可以改变或者取消INSERT, UPDATE以及DELETE语句。 您可以在一个会话中监视数据改变的动作。 在这里我假定大家都读过"MySQL新特性"丛书的第一集--"MySQL存储过程"，那么大家都应该知道MySQL至此存储过程和函数，那是很重要的知识，因为在触发器中你可以使用在函数中使用的语句。特别举个例子： 复合语句(BEGIN / END)是合法的.转载请注明翻译者陈朋奕及转自:www.phpv.net 流控制（Flow-of-control）语句(IF, CASE, WHILE, LOOP, WHILE, REPEAT, LEAVE,ITERATE)也是合法的. 变量声明(DECLARE)以及指派(SET)是合法的. 允许条件声明. 异常处理声明也是允许的. 但是在这里要记住函数有受限条件:不能在函数中访问表.因此在函数中使用以下语句是非法的。 ALTER 'CACHE INDEX' CALL COMMIT CREATE DELETE DROP 'FLUSH PRIVILEGES' GRANT INSERT KILL LOCK OPTIMIZE REPAIR REPLACE REVOKE ROLLBACK SAVEPOINT 'SELECT FROM table' 'SET system variable' 'SET TRANSACTION' SHOW 'START TRANSACTION' TRUNCATE UPDATE 在触发器中也有完全一样的限制. 触发器相对而言比较新，因此会有（bugs）缺陷.所以我在这里给大家警告，就像我在存储过程书中所说那样.不要在含有重要数据的数据库中使用这个触发器，如果需要的话在一些以测试为目的的数据库上使用，同时在你对表创建触发器时确认这些数据库是默认的。 Syntax 语法 1. Syntax: Name 语法：命名规则转载请注明翻译者陈朋奕及转自:www.phpv.net CREATE TRIGGER <触发器名称> <-- { BEFORE | AFTER } { INSERT | UPDATE | DELETE } ON <表名称> FOR EACH ROW <触发器SQL语句> 触发器必须有名字，最多64个字符，可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象. 这里我有个习惯：就是用表的名字＋'_'＋触发器类型的缩写.因此如果是表t26，触发器是在事件UPDATE（参考下面的点（2）和（3））之前（BEFORE）的，那么它的名字就是t26_bu。 2. Syntax: Time 语法：触发时间 CREATE TRIGGER <触发器名称> { BEFORE | AFTER } <-- { INSERT | UPDATE | DELETE } ON <表名称> FOR EACH ROW <触发的SQL语句> 触发器有执行的时间设置：可以设置为事件发生前或后。 3. Syntax: Event语法：事件 CREATE TRIGGER <触发器名称> { BEFORE | AFTER } { INSERT | UPDATE | DELETE } <-- ON <表名称> FOR EACH ROW <触发的SQL语句> 同样也能设定触发的事件：它们可以在执行insert、update或delete的过程中触发。 4. Syntax: Table 语法：表 CREATE TRIGGER <触发器名称> { BEFORE | AFTER } { INSERT | UPDATE | DELETE } ON <表名称> <-- FOR EACH ROW <触发的SQL语句> 触发器是属于某一个表的:当在这个表上执行插入、更新或删除操作的时候就导致触发器的激活. 我们不能给同一张表的同一个事件安排两个触发器。 5. Syntax: Granularity 语法：（ ：（ 步长）触发间隔 CREATE TRIGGER <触发器名称> { BEFORE | AFTER } { INSERT | UPDATE | DELETE } ON <表名称> FOR EACH ROW <-- <触发的SQL语句> 触发器的执行间隔：FOR EACH ROW子句通知触发器每隔一行执行一次动作，而不是对整个表执行一次。 6. Syntax: Statement 语法：语句 CREATE TRIGGER <触发器名称> { BEFORE | AFTER } { INSERT | UPDATE | DELETE } ON <表名称> FOR EACH ROW <触发的SQL语句> <-- 触发器包含所要触发的SQL语句：这里的语句可以是任何合法的语句，包括复合语句，但是这里的语句受的限制和函数的一样。 Privileges权限 你必须拥有相当大的权限才能创建触发器（CREATE TRIGGER）。如果你已经是Root用户，那么就足够了。这跟SQL的标准有所不同，我也希望能尽快改成标准的。 因此在下一个版本的MySQL中，你完全有可能看到有一种叫做CREATE TRIGGER的新权限。然后通过这样的方法赋予： GRANT CREATE TRIGGER ON <表名称> TO <用户或用户列表>; 也可以通过这样收回权限： REVOKE CREATE TRIGGER ON <表名称> FROM <用户或用户列表>; Referring to OLD and NEW columns 关于旧的和新创建的列的标识 在触发器的SQL语句中，你可以关联表中的任意列。但你不能仅使用列的名称去标识，那会使系统混淆，因为那里可能会有列的新名（这可能正是你要修改的，你的动作可能正是要修改列名），还有列的旧名存在。因此你必须用这样的语法来标识： "NEW . column_name"或者"OLD . column_name".这样在技术上处理（NEW | OLD . column_name）新和旧的列名属于创建了过渡变量（"transition variables"）。 对于INSERT语句,只有NEW是合法的；对于DELETE语句，只有OLD才合法；而UPDATE语句可以在和NEW以及OLD同时使用。下面是一个UPDATE中同时使用NEW和OLD的例子。 CREATE TRIGGER t21_au BEFORE UPDATE ON t22 FOR EACH ROW BEGIN SET @old = OLD . s1; SET @new = NEW.s1; END;// 现在如果t21表中的s1列的值是55，那么执行了"UPDATE t21 SET s1 = s1 + 1"之后@old的值会变成55，而@new的值将会变成56。 Example of CREATE and INSERT CREATE和INSERT的例子 CREATE table with trigger创建有触发器的表 这里所有的例程中我都假定大家的分隔符已经设置成//（DELIMITER //）。 CREATE TABLE t22 (s1 INTEGER)// CREATE TRIGGER t22_bi BEFORE INSERT ON t22 FOR EACH ROW BEGIN SET @x = 'Trigger was activated!'; SET NEW.s1 = 55; END;// 在最开始我创建了一个名字为t22的表，然后在表t22上创建了一个触发器t22_bi，当我们要向表中的行插入时，触发器就会被激活，执行将s1列的值改为55的动作。 INSERT on table w ith a trigger使用触发器执行插入动作 mysql> INSERT INTO t22 VALUES (1)// 让我们看如果向表t2中插入一行数据触发器对应的表会怎么样？ 这里的插入的动作是很常见的，我们不需要触发器的权限来执行它。甚至不需要知道是否有触发器关联。 mysql> SELECT @x, t22.* FROM t22// +------------------------+------+ | @x | s1 | +------------------------+------+ | Trigger was activated! | 55 | +------------------------+------+ 1 row in set (0.00 sec) 大家可以看到INSERT动作之后的结果，和我们预期的一样，x标记被改动了，同时这里插入的数据不是我们开始输入的插入数据，而是触发器自己的数据。 Example of a "check" constraint "check"完整性约束例子 What's a "check" constraint 什么是"check"约束 在标准的SQL语言中，我们可以在（CREATE TABLE）创建表的过程中使用"CHECK (condition)"， 例如： CREATE TABLE t25 (s1 INT, s2 CHAR(5), PRIMARY KEY (s1), CHECK (LEFT(s2,1)='A')) ENGINE=INNODB; 这里CHECK的意思是"当s2列的最左边的字符不是'A'时，insert和update语句都会非法"，MySQL的视图不支持CHECK，我个人是很希望它能支持的。但如果你很需要在表中使用这样的功能，我建议大家使用触发器来实现。 CREATE TABLE t25 (s1 INT, s2 CHAR(5), PRIMARY KEY (s1)) ENGINE=INNODB// CREATE TRIGGER t25_bi BEFORE INSERT ON t25 FOR EACH ROW IF LEFT(NEW.s2,1)<>'A' THEN SET NEW.s1=0; END IF;// CREATE TRIGGER t25_bu BEFORE UPDATE ON t25 FOR EACH ROW IF LEFT(NEW.s2,1)<>'A' THEN SET NEW.s1=0; END IF;// 我只需要使用BEFORE INSERT和BEFORE UPDATE语句就行了，删除了触发器不会对表有影响，同时AFTER的触发器也不能修改NEW的过程变量。  （编辑：财气旺网 - 海宁网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐