|
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语句*/ 喻定整理 Mysql5.0存储过程介绍 END /* end of block语句块结束*/ create procedure myprocedure (in id integer) begin declare name char(20); if id=1 then set name="cccc"; else set name='ddddd'; end if; end 下面我将会介绍你可以利用存储过程做的工作的所有细节。同时我们将介绍新的数据库对象—触发器，因为触发器和存储过程的关联是必然的。 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 喻定整理 Mysql5.0存储过程介绍 来开始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客户端返回的结果，这样意味着我有极大的特权。 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" 示例数据库 现在要做的第一件事是创建一个新的数据库然后设定为默认数据库实现这个步骤的 喻定整理 Mysql5.0存储过程介绍 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; // <-- 喻定整理 Mysql5.0存储过程介绍 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语句中:任何数据库定义语言都是合法的，如: 喻定整理 Mysql5.0存储过程介绍 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;" 喻定整理 Mysql5.0存储过程介绍 好了，主要的知识点"创建和调用过程方法"已经清楚了。我希望你能对自己说这相当简单。但是很快我们就有一系列的练习，每次都加一条子句，或者改变已经存在的子句。那样在写复杂部件前我们将会有很多可用的子句。 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 // 喻定整理 Mysql5.0存储过程介绍 下一个子句是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) 喻定整理 Mysql5.0存储过程介绍 当调用过程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) 喻定整理 Mysql5.0存储过程介绍 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值是从过程中传出的。 同样我们可以用语句 喻定整理 Mysql5.0存储过程介绍 "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 */; 喻定整理 Mysql5.0存储过程介绍 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参数或者将其值 喻定整理 Mysql5.0存储过程介绍 指派 给会话变量来保存其值。 调用作用域例子的过程: 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 喻定整理 Mysql5.0存储过程介绍 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. 喻定整理 Mysql5.0存储过程介绍 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. 喻定整理 Mysql5.0存储过程介绍 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(译 喻定整理 Mysql5.0存储过程介绍 者语:最好不要用吧，用了就使流程混乱)。 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; 喻定整理 Mysql5.0存储过程介绍 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循环一样 也不需要结束条件。 喻定整理 Mysql5.0存储过程介绍 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 标号结束符 喻定整理 Mysql5.0存储过程介绍 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 喻定整理 Mysql5.0存储过程介绍 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; 喻定整理 Mysql5.0存储过程介绍 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(迭代)让循环又回到了循环的头部。 喻定整理 Mysql5.0存储过程介绍 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; ... 喻定整理 Mysql5.0存储过程介绍 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 喻定整理 Mysql5.0存储过程介绍 constraint fails(这里显示的是系统的出错信息) 我开始要创建一个主键表，以及一个外键表。我们使用的是InnoDB，因此外键关联检查是打 开的。然后当我向外键表中插入非主键表中的值时，动作将会失败。当然这种条件下可以很 快找到错误号1216。 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意思是当动作成功提交后退出这个复合语句。 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处理类似，不同在于它执行后，原主程序仍然继续运行，那么这个复合语句就没有出口了。 喻定整理 Mysql5.0存储过程介绍 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; 喻定整理 Mysql5.0存储过程介绍 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被插入到主键表中。 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); <-- 喻定整理 Mysql5.0存储过程介绍 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动作。 9. DECLARE CONTINUE HANDLER example mysql> CALL p23()// Query OK, 0 rows affected (0.00 sec) mysql> SELECT @x, @x2// +------+------+ | @x | @x2 | +------+------+ | 3 | 1 | +------+------+ 喻定整理 Mysql5.0存储过程介绍 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// 喻定整理 Mysql5.0存储过程介绍 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 喻定整理 Mysql5.0存储过程介绍 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; 喻定整理 Mysql5.0存储过程介绍 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错误代
信息发布：广州名易软件有限公司 http://www.myidp.net
|
名易MyOA协同综合办公平台
名易MyCRM客户关系管理平台
名易MyHR人力资源管理平台
名易MyIDP智能开发平台
名易MyIBP保险业务管理平台
名易MyHMS酒店综合管理系统
名易MyPM项目管理平台
名易MyTMS物流运输管理平台
名易MyVMS汽车综合管理系统
名易MyIMS贷款管理系统
名易MyEDU教育管理平台
名易MyPCS生产事务协调系统
名易MyWIDP微信智能开发平台
