mysql 存储过程编程

mysql 存储过程编程
原著:Steven Feuerstein, Guy Harrison
译者:drekey (at) gmail dot com
出版:O'Reilly
出版日期:2006年五月
ISBN-10: 0-596-10089-2 ISBN-13: 978-0-59-610089-6 页数: 636页
概览
MySQL5.0中存储过程的实现作为早已非常著名mysql数据库的一部分,在使得mysql成为广泛的企业级应用的领头羊的过程中有着里程碑的意义。如果你打算严肃的来面对建立基于web的数据库应用程序的将来,你必须快速的掌握怎样用存储过程进行工作(并且用正确的方法建立他们),这本书的目的就是打算成为一本存储过程编程的圣经,作为一种资源,来共同面对这一MySQL程序员所无法逃避的现状。 在MySQL突如其来降生的靠十年中,它已然是现今具有统治地位的开源数据库,无论是性能和表现都能和那些诸如Oracle和SQL Server这样的商用RDBMS(关系型数据库)相竞争。并且MySQL能够和linuxphp结合在一起,成为数百万应用程序的心脏, 现在,MySQL5.0结合了对于存储过程,函数和触发器的支持,提供了作为真正企业级应用的编程能力。 MySQL新的过程语言有着直白化的语法,我们可以用它很容易的写出简单的程序,但是要写出安全,容易维护,高效并且容易调试的程序却并非易事。作为一个新生事务,还很少有人能在MySQL领域拥有对存储过程实质性的编程经验,但是Guy Harrison 和Steven Feuerstein正是这极少数的一员,他们在这一专业领域拥有靠十年的经验 在MySQL存储过程编程这本书中,作者们很好的利用了自身积累的难得的经验,他们将充满代码的示例和各种语言级的基础知识融入进应用程序的构建所需要的各种协调和实践中,使之成为一本高可阅读性的MySQL开发一站式指南,这本书由以下四个方面组成:
MySQL存储过程编程基础 -- 指南,基本语句,存储过程中的SQL和错误处理
创建MySQL存储过程程序 -- 事务处理,内建函数,存储过程函数和触发器
在应用程序中使用MySQL存储过程程序 -- 在php,Java,perlpython 和 .NET(c# 和 VB.NET)中使用存储过程编程
优化MySQL存储过程程序 -- 安全性,基本和高级SQL调试,优化存储过程代码,并且进行最有效的编程实践
这本书的主题网站拥有数千行的代码,方便你随时运行 Guy Harrison 是Quest软件公司数据库解决方案的总构架师,并且有大量关于MySQL的主题演说和文章,Steven Feuerstein 是Oracle PL/SQL 编程(Oracle PL/SQL Programming) 一书的作者,在Oracle存储过程编程工作超过十年,他们俩都在数据库开发领域拥有十年以上的经验,并且出版了大量的书籍
MySQL 存储过程编程

这本书的目标
存储过程,函数和触发器所提供的新的能力(我们可以大致称他们为存储过程程序)给MySQL的开发者制定了新的游戏规则,只有在别的关系型数据库中已经拥有过经验,才能在MySQL应用程序中做的更好,更可靠及更有效。当然,不恰当的使用存储过程程序,或者差劲的存储过程程序构架,可能导致应用程序性能表现低劣,难以维护和不稳定。 基于这些原因,我们预见到了写一本书来帮追MySQL从业人员认识MySQL存储过程程序潜在能力的重要性。我们希望这本书能帮助你恰当的使用MySQL存储过程,并且写出可靠正确,有效且易于维护的过程,函数和触发器。 编写出可靠存储过程应用程序的实践依赖与以下四点: 恰当的使用 使用恰当的存储过程程序能够帮助你改善MySQL应用程序的性能,可靠性和可维护性,当然存储过程程序并非万能,他们只能在适当的场合被使用,在书中我们描述了存储过程程序可能被用来改善性能的场合,并且勾画了一些大致的模式(及不使用模式的)存储过程程序 可靠性 包括MySQL存储过程程序语言在能的所有编程语言都允许你写出在任何场合都有可预见行为的代码,但是这些语言也允许你写出受制于故障和各种不可预料场合的错误代码。我们勾画了怎样才能稳妥及可预见的面对错误,来轻松的面对各种程序错误 易维护性 我们总是对修改自己同事或自己所写的代码感到情绪低落,并且发现这些代码的意图,逻辑和机制几乎不能理解。所谓的“意大利面式的”代码可以用任何语言来写,这方面,MySQL存储程序也不例外,我们将介绍如何通过对管理,程序结构,注释和其他机制的实践来写出易于维护的代码 性能 任何非凡的应用程序都要面对潜在或显然的既定性能要求。数据库的SQL代码和存储程序代码的性能往往是影响应用程序全局性能的重要方面。此外,在要处理的数据和事务的体积增加时,落后的数据库代码经常不能彻底甚至完全不能达到预期的目的。在本书中,我们将向你展示什么时候该使用存储程序来改善性能以及如何用存储过程代码交付最高性能的应用程序。当然,结合SQL在内的存储程序经常是高性能应用程序的一个重要组成部分,所以我们也将在一定深度上来阐述如何写高性能的SQL代码
MySQL 存储过程编程

这本书的结构
MySQL存储过程编程主要分为四个部分 第一部分,存储编程基础 第一部分主要介绍存储过程编程语言和详细的描述,语言结构及用法。 第1章,介绍MySQL存储过程程序,回答几个基本的问题:这种语言是怎么来的?它的好处有哪些?语言的主要功能有哪些? 第2章,MySQL存储编程指南,作为一个指南来让你最快度的开始使用语言,它向你展示了如何创建各种基本类型的存储程序,并提供了有关这种语言功能的交互式例子 第3章,语言基础,展示了如何使用变量,字面量,操作符和表达式 第4章,语句块,条件语句和迭代编程,并主要阐述了如何实现条件命令(IF和CASE)以及循环结构 第5章,在存储程序中使用SQL,讨论怎样才能把SQL和这种语言结合起来 第6章,错误处理,提供了错误是怎样被处理的 第二部分,存储程序结构解析 这一部分将描述如何使用第一部分中的各个元素来创建功能强大而实用的程序 第7章,创建和维护存储程序,勾画用可用的语句创建和修改存储程序并提供了如何管理你的代码的一些建议 第8章,事务管理,阐述了在存储程序中使用事务的一些基础知识 第9章,MySQL内建函数,详细介绍了可以用于存储程序的内建函数 第10章,存储函数,向你解释如何使用存储函数 -- 这一特别的存储程序 第11章,触发器,描述了如何使用另一种特别的存储程序:触发器 -- 在数据库表中被激活用来响应DML(数据库操纵语言) 第三部分,在应用程序中的MySQL存储程序 存储程序可以被用来做各种不同的事情,包括提供给MySQL管理员和开发者的存储例程,当然,大多数重要的使用范畴都是像本章中所描述的和应用程序一起是使用的,存储程序允许我们将一些原本属于应用程序逻辑的代码移到数据库服务器内部;如果能够这部分,将能给我们的应用程序的安全性,有效性和易维护性带来很大的好处 第12章,在应用程序中使用MySQL存储程序,思考并在实践中体会在当今基于web的标准应用程序中使用存储程序的重大意义。其他的章节将向你展示如何在其他开发语言中和MySQL的存储过程和函数协同工作 第13章,在PHP中使用MySQL存储程序,描述如何在PHP中调用存储程序,我们将讨论myslqi及最近被绑定与PHP的MySQL连接器PDO和他们对于存储程序的支持 第14章,在Java中使用MySQL存储程序,介绍如何在Java的JDBC,servlets,企业级JavaBeans,Hibernate和spring中调用MySQL存储程序 第15章,在perl中使用MySQL存储程序,介绍如何在Perl中使用MySQL存储程序 第16章,在python中使用MySQL存储程序,介绍如何在Python中如何使用MySQL存储程序 第17章,在.NET中使用MySQL存储程序,介绍在c#和VB.NET中使用MySQL存储程序 第四部分,优化存储程序
本书的最后一个部分希望将“好”变得“更好”,能够使程序正确的运行是一件了不起的事情:任何正在运行的程
MySQL 存储过程编程

序都是一个好程序,而一个杰出的程序则需要性能优良,安全易维护且能应对一切 第18章,存储程序安全问题,独立的讨论安全性问题及由存储过程和函数引发的问题 第19章,调试存储程序和SQL代码,这一章节和接下来的20章,21,22章将介绍存储程序的优化,这章将首先介绍性能优化的工具和一些技巧 第20章,基本SQL调试,你的存储程序的性能绝大部分取决于内部SQL代码,所以这一章将对SQL代码的调试基础给出指导 第21章,高级SQL调试,这一章是基于第20章,介绍了更多SQL高级调试的途径 第22章,优化存储程序代码,包含存储程序自身的性能优化 第23章,最好的存储程序开发实践,合上书本来看一下最好的存储程序开发实践,这些指导将让你写出快速安全,以维护,易调试的程序 你会发现本书在内容分配上比较均衡,这一点不仅体现在存储程序开发的章节中,同样也存在于例如PHP或者Java这些别的开发语言中,个别来说,我们假设你在不经SQL调试的情况下无法写出高性能的程序,所以我们在SQL调试上投入了大量的篇幅,再则,这样做无论SQL代码是否被内嵌与你的程序中都会有好处,同样的,讨论事务设计和安全问题在其他语言中也是可以接受的
MySQL 存储过程编程

本书中使用的约定
下面的约定将在本书中应用 斜体 将被用于URL和首次使用的术语的强调 等宽 将被用于代码示例中的SQL关键字 等宽加粗 在代码示例中,高亮当前被讨论的语句 等宽斜体 在代码示例中,指示应该由你提供的元素(比如:filename) 大写 在代码片段中指示MySQL关键字 小写 在代码片断中指示用户定义的变量或参数等 符号 在代码片段中为了严密输入 缩进 在代码片段中为了清楚的显示代码结构,当然这不是必须的 // 在代码片段中,单行注释将影响至这一行的结束 // 在代码片段中,多行注释定界符可以作用于多行 . 在代码片段和相关的讨论中,起到将对象名和成员名相分割的作用 [ ] 在语法描述中,表示可选参数 { } 在语法描述中,表示你必须从中选取一个的参数列表 | 在语法描述中,分割在大挂号中的元素,例如{TRUE|FALSE}。
MySQL 存储过程编程

... 在语法描述中,指示重复的项,也做和讨论无关的内容
指示提示,建议和注意事项,例如:我们将告诉你某个设定是不是与版本相关
指示警告或需要引起警惕,例如:我们将告诉你某些设定是否会与操作系统冲突
MySQL 存储过程编程

本书网站上的可用资源
我们在O'Reilly的站点上提供了所有本书的相关代码,进入

http://www.oreilly.com/catalog/mysqlspp 并且点击Examples来到本书的网站 要找到特定的代码片段,可以查找相应的文件或者指出代码出现的位置,比方说,要找到Example 3-1,你可以进入example0301.sql. 在网站上你也可以下载到在本书中使用的样例数据库的数据文件,源代码中包含了我们在开发过程中用到的样例文件,勘误表和附录 特别要指出,我们将用网站交流有关使用MySQL存储程序和其他工具的信息,因为MySQL存储程序相对来说是一个新生事务,MySQL公司将在不同的MySQL数据库服务器版本中不断的修改,精炼这种语言的功能和行为,同样的在本书出版之时,在别的语言工具(PHP,Perl,Python,Hibernate)中对于存储程序的支持也是不完整的,所以我们将在网站中对这些语言的改进保持更新
MySQL 存储过程编程

怎样联系我们
我们在出版之前已经尽我们的所能对书中的信息和源代码进行了校验,但是由于巨大的数目以及不断发展的技术,你也许会发现许多功能都在发生变化,因而我们的书本出现了错误,如果那样的话,请联系我们 O'Reilly Media, Inc. 1005 Gravenstein Highway North Sebastopol, CA 95472 800-998-9938 (in the United States or Canada) 707-829-0515 (international or local) 707-829-0104 (fax) 你也可以给我们发电子邮件,或者发到邮件列表或目录中
info@oreilly.com 有关技术问题的答疑和对本书的评论,请发送至
bookquestions@oreilly.com 同样,在前些章节已经提到过我们有一个和本书配到的网站,你在上面可以找到本书的源代码,勘误表(以前的错误报告和现在的错误公开)和其他一些本书的信息,你可以进入网址

http://www.oreilly.com/catalog/mysqlspp 其他有关本书的信息和别的事项,可以进入O'Reilly的官方网站
http://www.oreilly.com 翻译作者
drekey(at)gmail dot com MySQL 存储过程编程

第一章 MySQL存储程序介绍
当MySQL于上个世纪90年代中期在IT界刚刚成型的时候,它只具有少量的商用关系型数据库所具备的特性。出现了比如事务处理,子查询,视图和存储过程这样的功能的明显缺失,后继版本提供了大量缺失的功能,现在介绍的MySQL5.0的存储过程,函数和触发器(还有可更新视图以及数据目录)等功能大大的缩短了MySQL和其他关系型数据库系统的差距。 现在介绍的存储程序(我们通常所说的存储过程,函数和触发器)在和其他竞争对手的功能战上赢得了简单的胜利。如果没有存储程序,MySQL就无法以一种完整的姿态和别的竞争对手相比拼,因为在ANSI、ISO的标准当中也要求数据库管理系统应具备执行存储程序的功能。此外,正确的使用存储程序也有助于加强数据库的安全性和完整性及改善你的应用程序的性能和易维护性。我们将在本章的稍后部分介绍这些优势的具体内容。 简短的说,存储程序,过程,函数和触发器是一种MySQL的强大能力,而用好这些编程工具是MySQL专业上所必须具备的 本章将介绍MySQL存储程序语言,她的起源和她的能力,同样我们为MySQL存储程序开发者提供了一些附属资源的导引和综合开发的建议
MySQL 存储过程编程

1.1 什么是存储程序
数据库存储程序有时也被称为存储模块或者存储例程 -- 一种被数据库服务器所存储和执行的计算机程序(有一系列不同的称呼),存储程序的源代码(有时)可能是二进制编译版本几乎总是占据着数据库服务器系统的表空间,程序总是位于其数据库服务器的进程或线程的内存地址中被执行。 主要有三种类型的数据库存储程序 存储过程 存储过程是最常见的存储程序,存储过程是能够接受数个输入和输出参数并且能够在请求时被执行的程序单元。 存储函数 存储函数和存储过程很相像,但是它的执行结果会返回一个值。最重要的是存储函数可以被用来充当标准的SQL语句,允许程序员有效的扩展SQL语言的能力 触发器 触发器是用来响应激活或者数据库行为,事件的存储程序,通常,触发器用来作为DML(数据库操纵语言)的响应而被调用,触发器可以被用来作为数据校验和自动反向格式化。
其他的数据库提供了别的存储程序,包括包和类 -- 允许你定义和组织一堆上下文过程和函数,MySQL现在还不提供这种结构,每个程序都是一个单独的实体
在这本书中,我们将使用术语:存储程序来带指存储过程,函数和触发器,术语:存储程序语言带指用来写这些程序的语言,大多数程序的基本构建都可以在存储过程,函数和触发器中被使用;但是,存储函数和触发器在使用这些构建时是严格受限的。所以我们将另辟章节来专门介绍这些程序类型的限制
1.1.1为什么使用存储程序
开发者总是有很多种编程语言可以选择,这意味着大多数的语言并非位于数据库内,并受其管理,存储程序相对于这些多用途语言而言拥有很多优势:
存储程序的是使用可以使你的数据库更安全
存储程序提供了一种数据访问的抽象机制,它能够极大的改善你的代码在底层数据结构演化过程中的易维护性
存储程序可以降低网络拥阻,因为属于数据库服务器的内部数据,这相比在网上传输数据要快的多
存储程序可以替多种使用不同构架的外围应用实现共享的访问例程,无论这些构架是基于数据库服务器外部还是内部。
以数据为中心的逻辑可以被独立的放置于存储程序中,这样可以为程序员带来更高,更为独特的数据库编程体验
在某些情况下,使用存储程序可以改善应用程序的可移植性
MySQL 存储过程编程

作为对这些优势的最初映像(大多数将在本书的稍后深入的探讨),我们不建议你立即将所有的程序逻辑移至存储程序中,在今天丰富而复杂的软件技术背景下,你必须清楚的认识你的软件中每个技术细节的优势和弱势,并且将这些优势最大化,我们将在第12章详细的讨论在什么地方,怎样使用MySQL存储程序 使用存储程序的底线是:正确的使用存储程序,过程,函数和触发器能够帮助你改善应用程序的性能,安全性,易维护性和稳定性 后续的章节将带你探索怎样来构建MySQL存储程序和他们的最大好处,在详细介绍之前,让我们来看一下有关这项技术的发展和语言特性的快速浏览
1.1.2 MySQL的简史
MySQL植根于由瑞典的Tcx公司于1980年开发的一个叫做Unireg的的非SQL数据库系统,作为数据仓库的优化,Unireg的作者Michael "Monty" Widenius于1995年为其加入了SQL接口,这就是MySQL的第一个版本。来自Detron HB的David Axmark为了让MySQL取得成功为其提供了双许可证,这使得MySQL能够广泛的免费得到,同时它也具备了商业使用上的优势,于是,Allan Larsson, David 和Monty成为MySQL公司的创建者 第一个被广泛使用的MySQL版本是于1996年发布的3.11,随着MySQL及相关开源技术的快速发展,到2005年,MySQL宣称其数据库已经拥有6百万的安装数 MySQL3适合的应用层面很广,(当然适应于web应用程序是非常可以理解的),但是缺乏一些常规数据库做应具备的功能,举例来说:事务,视图和子查询都没有在这个最初的版本中被支持 不管怎样,MySQL系统在诞生之初就被设计为具有可扩者数据访问结构,SQL层用能够为底层的数据和文将访问层解偶。这允许使用各种自定义数据引擎来替换原有的本地ISAM(索引顺序存取方法)数据库引擎,2001年,BDB(Berkeley-DB )数据引擎(由Sleepycat负责开发维护)被作为3.23.34的可选组件被集成进MySQL,BDB提供了MySQL最初的事务处理能力,同时,开源的InnoDB很快成为MySQL用户的可选本地数据引擎。 2002年早期发布的4.0版完整的整合了InnoDB这一选项,这让MySQL用户非常容易的获得了事务处理的支持,以及改进的数据同步能力,在2004年发布的4.1版本提供了对于子查询和Unicode的支持 2005年晚期发布的MySQL 5.0在向商用关系型数据库系统的功能靠近的方面做出了改进,它开始支持存储过程,函数和触发器,及数据目录(INFORMATION_SCHEMA的SQL标准),并支持了可更新视图 5.1版本预计将于2006年中晚期发布,将提供例如内部作业调度,表分区,基于记录的同步功能和其他一些有意义的改进
1.1.3 MySQL存储过程,函数和触发器
MySQL选择将其存储程序作为ANSI SQL:2003 SQL/PSM(数据持久模块)的一个子集来实现,本质上MySQL的存储程序过程,函数和触发器只是遵循了ANSI对于这些程序类型的开放标准。
MySQL 存储过程编程

很多MySQL和其他开源爱好者则希望将存储程序语言实现为基于其他开源语言,例如PHP或者Python及Java的版本,虽然最后选择了ANSI的规范 -- 同样的规范也被IBM的DB2数据库所采用,但这是MySQL在ANSI委员会中做了大量长期的工作的结果,这个标准在大量的商用关系型数据库公司所采纳的标准中具有相当的典型性 MySQL存储程序语言是一种类似于Pascal的块语句结构的语言,包含了大量人们熟知的命令,包括变量操纵,条件语句的实现方式,迭代编程和错误处理等,其他现有的数据库存储程序用户(例如Oracle 的PL/SQL或者SQL Server的Transact-SQL)将发觉他们大体上看起来非常相似,与PHP或者Java相比,这种语言可能显的过于单薄,但是很快你将发现它很好的适应了数据库编程的常规需求。
MySQL 存储过程编程

1.2快速浏览
让我们看一些包含MySQL存储程序结构和功能的基本要点的简单示例,完整的内容详见第二章。
1.2.1 和SQL集成

MySQL存储过程语言一个非常重要的方面就是和SQL的紧密结合,你不需要借助于像ODBC或者JDBC这样的软件粘合剂来为你的存储程序创建独立的SQL语句,只要简单的将UPDATE,INSERT和SELECT这样的语句直接写进你的存储程序代码中,就像Example 1-1所显示的那样 Example 1-1. 内嵌SQL的存储程序 1 CREATE PROCEDURE example1( ) 2 BEGIN 3 DECLARE l_book_count INTEGER; 4 5 SELECT COUNT(*) 6 INTO l_book_count 7 FROM books 8 WHERE author LIKE '%HARRISON,GUY%'; 9 10 SELECT CONCAT('Guy has written (or co-written) ', 11 l_book_count , 12 ' books.'); 13 14 -- Oh, and I changed my name, so... 15 UPDATE books 16 SET author = REPLACE (author, 'GUY', 'GUILLERMO') 17 WHERE author LIKE '%HARRISON,GUY%'; 18 19 END
下表阐述了更为详细的代码信息 行号 解释 1 这个区块,是程序的头部,定义了程序的名称(example1)以及类型(PROCEDURE) 2 BEGIN关键字指示了程序体的开始,其中包含了存储过程的变量申明和可执行代码,如果程序体包含了超过一个语句(就像这个程序中所看到的一样),那么要将多个语句包含在BEGIN-END块中 3 这里我们申明了一个整型的变量来保存我们将要执行的数据库查询代码 5-8 我们执行了一个数据库查询来获得Guy所编写和执笔的属的总数,特别关注一些第6行:INTO子句和SELECT连用表示将数据库查询结果传递给存储程序的本地变量 10-12 我们是用了一个简单的SQL语句(例如:没有带FROM字句)来显示书的个数。如果我们使用了没有带INTO MySQL 存储过程编程

字句的SELECT语句,那么结果将返回给调用程序,这是一个能够简单的得到结果集的非ANSI扩展(SQL Server和其他关系型数据库所采用的方式) 14 单行注释解释了UPDATE的用意 15-17 Guy大概想和他的fans讨论有关Oracle,并想改变他姓的拼写方法,所以我们对books表使用了UPDATE,得益于内建的REPLACE函数我们能将表中所有包含“GUY”的实例替换为“GUILLERMO”。
1.2.2 控制和条件逻辑 当然,现实世界中的代码具有特定的用途并且相当复杂,你不可能仅仅在其中使用一系列的SQL语句,存储过程语言提供了我们非常丰富的条件和控制语句,这使我们能够编写出适应给定情景的程序,它们包括: IF和CASE语句 这些语句都使用不同的逻辑来实现条件逻辑,这允许我们表达像“如果书本的页数大于1000,然后...”这样的逻辑 完整的循环和迭代控制 它包含简单循环,WHILE循环和REPEAT UNTIL循环 Example 1-2 是一个用来显示帐户金额收支平衡的存储过程,给出了MySQL的控制语句示例 Example 1-2 包含控制和条件逻辑的存储过程 1 CREATE PROCEDURE pay_out_balance 2 (account_id_in INT) 3 4 BEGIN 5 6 DECLARE l_balance_remaining NUMERIC(10,2); 7 8 payout_loop:LOOP 9 SET l_balance_remaining = account_balance(account_id_in); 10 11 IF l_balance_remaining < 1000 THEN 12 LEAVE payout_loop; 13 14 ELSE 15 CALL apply_balance(account_id_in, l_balance_remaining); 16 END IF; 17 18 END LOOP; 19 20 END
下表阐述了更为详细的代码信息 行号 解释 1-3 这是存储过程的头部;第二行包含了过程的参数列表,接受一个数据参数(帐户的id) 6 申明了一个保存帐余额的变量
MySQL 存储过程编程

8-18 一个简单循环(这样称呼是因为使用LOOP关键字来和WHILE及REPEAT进行区别)直到帐户余额少于1000,在MySQL中我们可以命名一个循环(第8行,payout_loop),这使得我们可以在随后的代码中使用LEAVE语句(见第12行)来结束这个特定的循环,结束循环后,MySQL引擎将执行END LOOP(见第18行)之后的代码 9 调用了函数account_balance(当然这个函数必须已经在前面的代码中被定义)来获得帐户的收支状况。MySQL允许我们在存储程序中调用别的存储程序,这可以有效的实现代码复用,如果这是一个函数,那么它将返回一个值并且能被其他存储程序和MySQL作业调度所调用 11-16 IF语句引发了当帐户余额少于1000美元时的循环终结条件,此外(ELSE语句)能够对收支平衡进行进一步处理,你可以用ELSEIF构建更为负责的布尔表达式 15 调用了存储过程apply_balance,这是一个代码复用的例子,与其重复apply_balance的逻辑,我们还是调用一个共享例程比较方便
1.2.3 存储函数
存储函数是能够返回一个值的存储程序,它也可以当作内建函数一样对待(调用)。Example 1-3将在存在出生年月的情况下返回这个人的年龄 Example 1-3. 用出生年月计算年龄的存储函数 1 CREATE FUNCTION f_age (in_dob datetime) returns int 2 NO SQL 3 BEGIN 4 DECLARE l_age INT; 5 IF DATE_FORMAT(NOW( ),'00-%m-%d') >= DATE_FORMAT(in_dob,'00-%m-%d') THEN 6 -- This person has had a birthday this year 7 SET l_age=DATE_FORMAT(NOW( ),'%Y')-DATE_FORMAT(in_dob,'%Y'); 8 ELSE 9 -- Yet to have a birthday this year 10 SET l_age=DATE_FORMAT(NOW( ),'%Y')-DATE_FORMAT(in_dob,'%Y')-1; 11 END IF; 12 RETURN(l_age); END;
接下来让我们看一下具体解释 行号 解释 1 定义函数:名称,参数(日期)和返回值(整型数)。 2 这个函数没有使用SQL语句,这将在第3章和第10章进行详细的讨论 4 申明一个用来保存我们计算出的年龄的本地变量 5-11 在这个IF-ELSE-END块中,IF块用来检测出生年月是否存在 7 如果出生年月存在,那么我们就可以用现在的年份减去出生的年份得到年龄 10 此外(如果出生年月不存在),我们必须在岁数计算中简单的减去当前的年份 12 返回年份计算的函数调用
MySQL 存储过程编程

我们可以在任何其他的存储程序,SET语句或者在Example 1-4所显示的那样,在SQL语句中调用我们的存储函数 Example 1-4.在SQL语句中使用存储函数(延续上一部分) mysql> SELECT firstname,surname, date_of_birth, f_age(date_of_birth) AS age -> FROM employees LIMIT 5; +-----------+---------+---------------------+------+ | firstname | surname | date_of_birth | age | +-----------+---------+---------------------+------+ | LUCAS | FERRIS | 1984-04-17 07:04:27 | 21 | | STAFFORD | KIPP | 1953-04-22 06:04:50 | 52 | | GUTHREY | HOLMES | 1974-09-12 08:09:22 | 31 | | TALIA | KNOX | 1966-08-14 11:08:14 | 39 | | JOHN | MORALES | 1956-06-22 07:06:14 | 49 | +-----------+---------+---------------------+------+
1.2.4.当发生错误时
即使我们的程序被反复检查并没有bug,我们的输入错误仍然可能发生,MySQL存储程序语言提供了一种错误处理的强大机制,在Example 1-5中,我们创建一个产品代号,如果产品已存在,我们就用新的名称更新它,存储过程的错误处理机制检测到我们试图使用一个重复的值,如果尝试插入失败,错误将被捕获并且使用UPDATE替换INSERT,如果没有错误处理,存储程序将被终止执行,并且错误将被返回给它的调用程序 Example 1-5 错误处理 1 CREATE PROCEDURE sp_product_code 2 (in_product_code VARCHAR(2), 3 in_product_name VARCHAR(30)) 4 5 BEGIN 6 7 DECLARE l_dupkey_indicator INT DEFAULT 0; 8 DECLARE duplicate_key CONDITION FOR 1062; 9 DECLARE CONTINUE HANDLER FOR duplicate_key SET l_dupkey_indicator =1; 10 11 INSERT INTO product_codes (product_code, product_name) 12 VALUES (in_product_code, in_product_name); 13 14 IF l dupkey_indicator THEN 15 UPDATE product_codes 16 SET product_name=in_product_name 17 WHERE product_code=in_product_code; 18 END IF; 19 20 END
MySQL 存储过程编程

让我们看一下有关错误处理的详细内容 行号 解释 1-4 这是程序的头部,包含了两个输入参数产品代号和产品名称 7 申明了一个用来检测重复值出现的标志变量,这个变量被初始化为0(false);后续的代码将保证在重复值被替换时将把这个变量设置为1(true)。 8 命名一个条件duplicate_key和MySQL系统错误1062相匹配,虽然这一步并非必需,但是我们建议你定义一个条件来改善你代码的可靠性(现在你可以使用错误名称而不是代码来引用这个错误) 9 定义一个错误处理器,它将在后续的代码中重复值出现的时候将l_dupkey_indicator变量的值设置为1(true) 11-12 插入用户提供的代号和名称 14 检测变量l_dupkey_indicator的值,如果仍为0,那么说明我们的插入成功了,如果值被修改成了1(true),我们就知道出现了重复值,我们可以在地15-17行代码使用UPDATE语句将原来的产品名称和代号进行更新
1.2.5 触发器
触发器是一种用来相应数据库事件是自动回调的存储程序,在MySQL5的实现中,触发器将在特定表的DML(数据库操纵语言)激活时被回调,触发器可以用来自动计算引用值或者格式化值。Example 1-6展示了用于维护引用值的触发器;当员工salary的值被改变是,contrib_401K列将被自动修改为特定值。 Example 1-6.维护引用列的触发器 1 CREATE TRIGGER employees_trg_bu 2 BEFORE UPDATE ON employees 3 FOR EACH ROW 4 BEGIN 5 IF NEW.salary <50000 THEN 6 SET NEW.contrib_401K=500; 7 ELSE 8 SET NEW.contrib_401K=500+(NEW.salary-50000)*.01; 9 END IF; 10 END
下表用来解释这个短小的触发器的代码 行号 解释 1 一个触发器有它独立的名称,通常,我们用名称来描述它的作用,举例来说,名称中的“bu”表示BEFORE UPDATE(在更新前)使用的触发器 2 定义一个触发器激活条件,在这个例子中,触发器代码将在UPDATE语句起作用前被触发 3 FOR EACH ROW关键字指示了触发器将在所有的记录被DML语句作用前被触发。这个字句在MySQL5的触发器实现中是强制执行的 4-10 BEGIN-END定义了将被激活的触发器代码 5-9 自动修改emplyees表中contrib_401K列。如果salary列的值小于50000,contrib_401K列将被设置为500,否则,这个值就参与执行第8行的计算
当然,还有有关MySQL存储过程值得说的很多故事,所以你还有上百页的书要看,这些基础的代码主要是让你培养一种对存储程序语言良好的感觉,其中的一些是很重要的语法点,请轻松的面对你所要写和阅读的语言代码。
MySQL 存储过程编程

1.3 为开发者准备的存储过程参考资料
已经介绍过MySQL5的存储程序在整个MySQL语言的演进过程中具有里程碑的意义。为了能够全神贯注于任何新技术的需要,技术用户需要大量的技术资料的支持,我们的目标是介绍尽可能全面资料来覆盖MySQL存储程序语言。 我们确信,无论如何你需要很多不同的帮助,所以我们在下面的章节中介绍了完整的书籍(作为其他MySQL技术的参考信息)或者社区支持和只需的新闻内容。在这些章节中我们将提供很多相关信息的概要。这些有极大作用的资源大多数都可以免费获得或以低廉的价格得到,你将在高MySQL开发体验上获得巨大的帮助
1.3.1 书
长期以来,O'Reilly的MySQL系列积累了大量的书籍。在此我们列出了一些我们认为MySQL存储程序开发者应该关心的书籍。当然也包括了其他出版社的书籍,请在O'Reilly OnLAMP 网站的MySQL区查看相关完整列表(http://www.onlamp.com/onlamp/general/mysql.csp) MySQL Stored Procedure Programming, by Guy Harrison with Steven Feuerstein This is the book you are holding now (or maybe even viewing online). This book was designed to be a complete and comprehensive guide to the MySQL stored program language. However, this book does not attempt complete coverage of the MySQL server, the SQL language, or other programming languages that you might use with MySQL. Therefore, you might want to complement this book with one or more other topics from the O'Reilly catalog or evenheaven forbidfrom another publisher! MySQL in a Nutshell, by Russell Dyer This compact quick-reference manual covers the MySQL SQL language, utility programs, and APIs for Perl, PHP, and C. This book is the ideal companion for any MySQL user (O'Reilly). Web Database Applications with PHP and MySQL, by Hugh Williams and David Lane This is a comprehensive guide to creating web-based applications using PHP and MySQL. It covers PEAR (PHP Extension and Application Repository) and provides a variety of complete case studies (O'Reilly). MySQL, by Paul DuBois This classic referencenow in its third editionis a comprehensive reference to MySQL development and administration. The third edition includes prerelease coverage of MySQL 5.0, including some information about stored procedures, functions, and triggers (SAMS). High Performance MySQL, by Jeremy Zawodny and Derek Balling This book covers the construction of high-performance MySQL server environments, along with how you can tune applications to take advantage of these environments. The book focuses on optimization, benchmarking, backups, replication, indexing, and load balancing (O'Reilly).
MySQL 存储过程编程

MySQL Cookbook, by Paul DuBois This cookbook provides quick and easily applied recipes for common MySQL problems ranging from program setup to table manipulation and transaction management to data import/export and web interaction (O'Reilly). Pro MySQL, by Michael Krukenberg and Jay Pipes This book covers many advanced MySQL topics, including index structure, internal architecture, replication, clustering, and new features in MySQL 5.0. Some coverage of stored procedures, functions, and triggers is included, although much of the discussion is based on early MySQL 5 beta versions (APress). MySQL Design and Tuning, by Robert D. Schneider This is a good source of information on advanced development and administration topics, with a focus on performance (MySQL Press). SQL in a Nutshell, by Kevin Kline, et al. MySQL stored procedures, functions, and triggers rely on the SQL language to interact with database tables. This is a reference to the SQL language as implemented in Oracle, SQL Server, DB2, and MySQL (O'Reilly). Learning SQL, by Alan Beaulieu This book provides an excellent entry point for those unfamiliar with SQL. It covers queries, grouping, sets, filtering, subqueries, joins, indexes, and constraints, along with exercises (O'Reilly).
1.3.2网络资源 网络上同样有大量关于MySQL程序员的极好的网站,包括一些关心存储过程的领域 当然我们于书籍相配套的网站上面也有更新和勘误表以及MySQL的信息,你也应该关心一下 MySQL MySQL AB offers the most comprehensive collection of white papers, documentation, and forums on MySQL in general and MySQL stored programming in particular. Start at http://www.mysql.com. We outline some specific areas later. MySQL Developer Zone http://dev.mysql.com/ is the main entry point for MySQL programmers. From here you can easily access software downloads, online forums, white papers, documentation, and the bug-tracking system. MySQL online documentation
The MySQL reference manualincluding sections on stored procedures, functions, and triggersis available online at http://dev.mysql.com/doc/. You can also download the manual in various formats from here, or you can order various
MySQL 存储过程编程
selections in printed book format at http://dev.mysql.com/books/mysqlpress/index.html. MySQL forums MySQL forums are great places to discuss MySQL features with others in the MySQL community. The MySQL developers are also frequent participants in these forums. The general forum index can be found at http://forums.mysql.com/. The stored procedure forum includes discussions of both procedures and functions, and there is a separate forum for triggers. MySQL blogs There are many people blogging about MySQL nowadays, and MySQL has consolidated many of the most significant feeds on the Planet MySQL web site at http://www.planetmysql.org/. MySQL stored routines library Giuseppe Maxia initiated this routine library, which collects general-purpose MySQL 5 stored procedures and functions. The library is still young, but already there are some extremely useful routines available. For example, you will find routines that emulate arrays, automate repetitive tasks, and perform crosstab manipulations. Check it out at http://savannah.nongnu.org/projects/mysql-sr-lib/. O'Reilly's OnLAMP MySQL section O'Reilly hosts the OnLAMP site, which is dedicated to the LAMP stack (linux, apache, MySQL, PHP/Perl/Python) of which MySQL is such an important part. OnLAMP includes numerous MySQL articles, which you can find at http://www.onlamp.com/onlamp/general/mysql.csp.
MySQL 存储过程编程

1.4 给开发者的建议
事实上,每个人对于MySQL存储程序开发都会感到陌生,因为存储程序本身对于MySQL就是新事物,但是,Guy和Steven在其他关系型数据库的存储程序编程上拥有大量的经验,特别是Steven,他在Oracle PL/SQL(Oracle的存储程序语言)的开发商拥有超过十年的经验。我们希望这些建议能帮助你更有效的理解MySQL编程语言的强大魅力
1.4.1万事不能操之过急
我们总是为限定的工期拼命的工作,追逐一个又一个的新事物,新潮流,我们没有时间可以浪费,我们有大量的代码要写,要怎样才能让我们恢复正常? 如果你想一下子接触深度的代码结构,奴隶般的将需求转化为数百行,千行甚至数万行,那么你将被巨大的混乱所摧毁,你的程序将变得难以调试和维护,不要被紧张的开发期限所压垮,我们更希望你能在紧张的期限中做好周密的计划。 我们强烈建议你顶住时间的压力,在你开始新的应用之前做好以下准备: 在你写代码之前建立良好的测试机制和测试脚本 你必须在动手写第一行代码之前给怎样才算一个成功的实现下一个定义。你更像是在为你的程序的该做什么建立一个接口,并彻底搞清楚这些功能的区别 为开发人员在应用程序中所写的SQL语句建立清晰的规则 总体来说,我们建议你认清这样一个事实:开发者并不需要写一大堆的SQL代码,相反的,各种对数据的查询,插入和更新操作都必须隐藏在我们预先建立并通过大量测试的存储过程函数中(这被称为数据封装),这样做你的程序就能比使用大量离散的SQL语句写出的程序更易于被优化,测试和维护。 为开发人员在错误处理上建立清晰的规则 如果你不树立标准,那么每个人都会有他自己的错误处理方法或者根本就不处理,这会造成软件混乱。最好的方法是将错误的处理逻辑集中在一个存储过程集合中,这个集合中的过程是专注于错误消息保存,错误的引发和传播方式的内部代码块(言下之意就是将错误处理的复杂度封装在这个过程集合中),并且保证你的开发者不需要为为了错误处理而建立非常复杂的代码。 必须分配充足的时间,使用抽丝剥茧的方法(逐层封装复杂度,也就是a.k.a的从上之下的设计模式)来消除你需求中的复杂度 我们时常要面对非常复杂的需求实现,如果你把所有的东西都放在一个“万能”的程序中,那么很快你就会发现这些意大利面式的代码将是你在随后的日子里对代码的理解造成极大的困难,把你的巨大挑战分解为一个个更小的问题,并把这些容易解决的小问题写成大小可以接受的程序,这样做,你将发现程序的可执行段明显的缩小,可读性也提高了,你的代码将变得易于维护又节省了时间
MySQL 存储过程编程

当你开始写代码的时候其实只有很少事情是你要放在心上的,请记住:在软件开发也盲目求速只能造成更大的浪费和更多bug。
1.4.2 不要害怕请教问题
事实是这样,如果你是个专业的软件从业者,那么你一定很聪明,受过良好的教育,你学习认真,你经验丰富,而且你写出了如此生动的代码,你能解决大多数的问题,这是你骄傲。 不幸的是,你的成功却是你自大,傲慢,不再听取别人的意见(我想大家都知道答案)软件开发也是动态发展的,而这也使之成为最有危机的行业。 软件是有人写的:因此认识人的心理成为软件开发的重要坏节。这里有一个例子: Joe,一个由6人组成的高级软件开发团队的头目,在他的软件里出现了一个问题,他在这个问题上花了大量的时间,也经受了大量的挫败,但他始终未能指出bug的源头。他不想去问他的助手,因为他们的经验都没有自己丰富,最后他穷尽脑汁也未能奏效,他只得放弃,在叹息声中他打电话求援:"Sandra,你能不能过来看看我程序中的问题,我不知道错在哪里?"Sandra停下手中的活儿很快的浏览了以下代码,并很快指出了长期以来他没有注意到的问题,程序就这样被修复了,Joe表示感谢,但事实上他内心非常尴尬。 就像“为什么我没看到”还有“如果我在自己这儿多做五分钟的调试就能发现它”始终在Joe的心中环绕,他无法理解,但事实上他被误导了,原因就在于我们已经太熟悉自己写的代码了,有些时候我们需要的仅仅是一个新的视角,某人和善的一个建议可能就能打开新的视野,这与资历,能力和经验无关 此外,Sandra并不认为Joe很差劲,相反的,通过相互的帮助,Joe是他自己更具人情味儿,这对团队开发有很大的好处 我们强烈的建议您在团队的管理中贯彻以下方针 原谅无知 在应用程序开发过程中隐藏你的无知是件及其危险的事情,培养一种能够把“I don't know”说出口的氛围并且鼓励问问题 请求帮助 如果你在30分钟内不能指出代码中的bug,那么请立即请教别人,这样也就建立了一种“责任机制”,使得每一个被你问起的人都有一种责任感。不要让你一个人孤立的寻找问题的答案 建立一种代码互查机制 不要让你的代码敲上“金牌质量”的标签或者经不起你团队中任何人的批评(建设性的意见)
MySQL 存储过程编程

1.4.3 打破条条框框
我们都会落入俗套,在这一点上每个人你的方方面面都是相同的,人有创造的天性:你只用学过的一种方法编写代码;你的产品的功能限制有不自觉的假定;你不加思索就抛弃了可能的解决方案,开发者对他们的程序都有自己的偏见,他们总是不恰当的说出这种话:
“它就像猪一样不可能让它再快了”
“我不能按照用户的预想来实现,这得等到下一个版本”
“我用过很多产品,但是他们都是小儿科,放在你眼前的这个不同,它的任何方面都已经做得相当出色”
但事实是你的程序总能运行的更快一些,别人总能把功能做的符合用户的要求,产品虽然都有它的限制,能力范围和弱点,但你永远都不要指望在下一个版本中去完善它,这种不需要借口,没有等待的解决问题的风格不是更能让人满意吗? 你究竟在干什么?打破你自以为是的那成百上千的阅历(或是你自己的小世界),估量你平日里养成的编程习惯。抛弃那些旧方法和你对产品固有性能的一切偏见已经成为现在商业社会的一股强大力量。 尝试各种新事物:用违背常理的方法去实验,你将发现作为一个程序员或者问题解决大师你将能学到多么不可思议的东西。长期以来,在一次次追问自己什么才是终极目标时总是惊奇的发现,当我们谦虚的点点头,轻声询问自己:“如果这样做会发生什么”总会比傲慢的说“用不可能做到”获得的更多。
MySQL 存储过程编程

1.5 结语
在这一章中,我们带大家快速浏览了MySQL关系型数据库及MySQL存储过程语言的大致情况。我们同样提供了大量的可用资源和建议,希望能给你带来帮助 在下一掌中,我们将正式开始进行MySQL存储过程,函数和触发器的学习,并会提供更多的相关内容
MySQL 存储过程编程

第二章 MySQL存储过程编程指南
MySQL存储过程编程是一个复杂的主题,我们将在本章中为你提供完成基本任务的内容,其中包括:
怎样创建存储程序
存储程序怎样进行输入输出
怎样和数据库交互
怎样用MySQL存储编程语言创建过程,函数和触发器
我们不会在本章中对主题进行深入,这些内容的主要不敌仅仅是让你对存储过程程序有一个大体的映像,在随后的章节中,我们将对本章的内容进行升华
MySQL 存储过程编程

2.1 你所需要的工具
下面的工具是你运行本书中的示例代码所需要的工具:

 MySQL 5 server
一个文本编辑器(vi, emacs或者 notepad) MySQL Query Browser(MySQL 官方GUI TOOLS内的查询工具) 您能够在http://dev.mysql.com上面得到MySQL Server 及 MySQL Query Browser MySQL 存储过程编程

2.2 第一个存储过程
开始,我们将创建一些非常简单的存储过程,你将需要一个编辑环境来写存储过程,还有一些工具把你的存储过程请求发送给MySQL服务器 你可以使用任何文本编辑器,下面提供了一些MySQL的代码提交工具:
MySQL命令行客户端

 MySQL Query Browser
第三方工具,例如Toad for MySQL 着这片文章中,我们假定你没有安装任何工具,所以我们是用了古老却又经典的MySQL命令行客户端 让我们用root帐户登录localhost的3306端口,我们将在Example 2-1使用MySQL预安装的“test”数据库 Example 2-1 连接MySQL命令行客户端 [gharriso@guyh-rh4-vm2 ~]$mysql -uroot -psecret -hlocalhost Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.16-nightly-20051017-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> 2.2.1 创建存储过程 你可以用CREATE PROCEDURE , CREATE FUNCTION ,或者CREATE TRIGGER 语句来创建存储程序。可以直接把这些语句直接输入MySQL命令行,但是对于一般的存储程序大小而言,这有些不太实际,所以我们建议你使用文本编辑器创建一个文本文件来容纳我们的存储程序,然后我们就可以使用命令行客户端和其他工具来递交这个文件 我们将使用MySQL Query Browser作为文本编辑器,如果你没有这个工具,你可以从http://dev.mysql.com/downloads/得到,你也可以使用任何操作系统上的编辑器例如vi, emacs或者notepad,当然我们喜欢MySQL Query Browser的原因是它具备内建的帮助系统,语法高亮,执行SQL语句的能力以及其他一些功能 遵照下面的步骤 运行MySQL Query Browser,在Windows上,从开始菜单中选择程序->MySQL->MySQL Query Browser。在Linux上从终端中输入mysql-query-browser 从菜单中选择File->New Script tab来创建一个空白的脚本窗口 输入你的存储程序代码 MySQL 存储过程编程

Figure 2-1 显示了我们的第一个存储过程
我们可以使用File->Sava As菜单来把我们的文件保存,这样就可以用mysql客户端来执行它 第一个存储过程非常的简单,但是还是让我们一行行的来解释确保你能够完整的理解他们 行号 解释 1 DELIMITER命令确保把‘$$’作为语句的终结条件,通常,MySQL会把“;”作为语句的终结,但是因为存储过程在其过程体中 3 DROP PROCEDURE IF EXISTS语句用来确保在同名存储过程已经存在的情况下将其移除,如果我们不这样做,那么在同名存储过程已存在的情况下将收到一个MySQL的修改重复执行的错误 4 CREATE PROCEDURE语句指示一个存储过程定义的开始,注意,存储过程名“HelloWorld”的后面跟这一对内容为空的圆括号“( )”。如果存储过程有任何参数,那么我们就可以把参数放在里面。但是如果没有参数,我们同样要把圆括号放上,否则,我们将会收到一个语法错误 5 BEGIN语句指示了存储程序的开始,所有超过一个语句的存储程序必须用至少一个BEGIN-END块来定义存储程序的开始和结束 6 这是存储过程中的一个单个语句:一个SELECT语句将“Hello World”返回给它的调用程序,马上将像我们看到的一样,存储程序中的SELECT能够向控制台和调用程序返回数据,就像我们直接把SELECT语句输入MySQL命令行一样 7 END结束存储过程的定义。注意用$$来结束对存储过程的定义,这样MySQL就知道我们完整的结束了CREATE PROCEDURE语句
随着对存储过程的定义结束,我们可以用mysql客户端创建并执行我们的HelloWorld存储过程,就像Example 2-2所展示的那样 Example 2-2. 创建我们第一个存储过程 $ mysql -uroot -psecret -Dprod
MySQL 存储过程编程

Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 16 to server version: 5.0.18-nightly-20051208-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> SOURCEHelloWorld.sql Query OK, 0 rows affected, 1 warning (0.01 sec) Query OK, 0 rows affected (0.00 sec) mysql> CALLHelloWorld( ) $$ +-------------+ | Hello World | +-------------+ | Hello World | +-------------+ 1 row in set (0.01 sec) Query OK, 0 rows affected (0.01 sec) mysql>
下面是我们用于完成这一切MySQL命令的解释 命令 解释 SOURCE HelloWorld.sql 从指定的文件中读取命令,在这个例子中,我们用MySQL Query Browser制定了我们所保存的文件,没有错误返回,这说明我们已经成功的创建了存储过程 CALL HelloWorld( ) $$ 执行存储过程,我们成功的执行了存储过程并且返回了“Hello World”作为结果集。我们用‘$$’来作为CALL命令的终结,这是因为DELIMITER的设置仍然在起作用 2.2.2 用MySQL Query Browser创建存储过程 在这个指南以及整本书中,我们要用一些过时的工具--MySQL命令行终端创建大多数的存储程序代码示例。而你要做的仅仅是复制这些代码片段。因此,你可以是用一些图形化的工具来创建存储程序:网上有大量使用的第三方MySQL图形化工具,其中一个很好的选择是MySQL Query Browser,你可以在此得到
http://dev.mysql.com/downloads/ 在这一章节中我们将给出如何是用MySQL Query Browser创建存储过程,MySQL Query Browser对于创建存储过程更为友好,虽然目前为止并非所有的操作系统平台都支持这一工具,所以你可以使用MySQL命令行或者其他第三方工具来代替 在Windows上,从开始菜单中选择程序->MySQL->MySQL Query Browser。在Linux上从终端中输入mysql-query-browser 当查询工具被打开,它会提示你输入MySQL服务器的连接信息,然后将显示一个空白的图形化窗口。你可以使用菜单项Script->Create Stored Procedure/Function菜单创建存储程序,它会提示你按照名称来创建存储程序,然后会显示一个空白的存储程序模板Figure 2-2显示了这样一个模板的例子 Figure 2-2用MySQL Query Browser创建存储过程
MySQL 存储过程编程

你可以在适当的位置存储过程代码(在BEGIN和END语句之间,光标将被自动的置于合适的位置方便你的输入)。当你输入完成,你可以简单的按下Execute按钮来执行存储过程,如果你的代码发生了错误,Query Browser将在底部显示错误并用高亮标识发生错误的行,否则,你将在左侧的Schemata选项卡中发现你的存储过程已被成功的创建 Figure 2-3用Execute按钮执行存储程序
我们希望这个清晰的示例对于你用MySQL Query Browser创建和执行存储程序起到帮助,Query Browser提供了一个简便的存储程序开发环境,但这一切都取决于你如何使用Query Browser,第三方工具和你喜欢的编辑器及MySQL命令行终端
MySQL 存储过程编程

2.3 变量
本地变量可以用DECLARE语句进行声明。变量名称必须遵循MySQL的列名规则,并且可以使MySQL内建的任何数据类型。你可以用DEFAULT字句给变量一个初始值,并且可以用SET语句给变量赋一个新值,就像Figure 2-5所展示的那样。 Figure 2-5.在存储过程中使用变量
MySQL 存储过程编程

2.4 参数
我们大多数所写的存储程序都会包括一两个参数。参数可以使我们的存储程序更为灵活,更为实用,下面,让我们创建一个包含参数的存储过程 Figure 2-4在Query Browser中执行存储过程
Figure 2-6的存储过程接受一个整型数input-number作为参数,并且计算出了这个数的平方根,计算出的结果作为返回的结果集 把参数放置在紧随过程名的圆括号内,每一个参数都有自己的名称,数据类型还有可选的输入输出模式,有效的模式包括IN(只读模式),INOUT(可读写模式)和OUT(只写模式)。因为IN模式作为缺省的参数模式,所有没有出现在 Figure 2-6当中 我们将通过示例对参数模式进行细致的观察 此外,MySQL存储程序引入了两种有关参数的不同的特性: DECLARE 一个用于创建存储程序内部使用的本地变量,在这个示例中,我们创建了一个名为l_sqrt的浮点数。
Figure 2-5在存储过程中使用变量
MySQL 存储过程编程

SET 一个用来给变量赋值的语句,在这个示例中我们将参数的平方根(使用内置的SQRT函数)赋于那个用DECLARE命令声明的变量 我们可以在MySQL客户端中执行并测试存储过程的运行结果,就像Example 2-3所做的那样 Example 2-3 创建并执行使用参数的存储过程 mysql> SOURCEmy_sqrt.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> CALLmy_sqrt(12)$$ +-----------------+ | l_sqrt | +-----------------+ | 3.4641016151378 | +-----------------+ 1 row in set (0.12 sec) Query OK, 0 rows affected (0.12 sec)
MySQL 存储过程编程

Figure 2-6一个使用参数的存储过程
2.4.1参数模式
MySQL的参数模式可以被定义为IN,OUT和INOUT。 IN 这是缺省的模式,它说明参数可以被传入存储程序内部,但是任何对于该参数的修改都不会被返回给调用它的程序 OUT 这个模式意味着存储程序可以对参数赋值(修改参数的值),并且这个被修改的值会被返回给它的调用程序 INOUT 这个模式意味着存储程序既可以读取传入的参数,而且任何对于该参数的修改对于它的调用程序而言都是可见的 你可以在存储过程的参数中使用上述所有的模式,但是对于存储函数而言,你只能使用IN模式(参考随后的“存储函数”章节) 让我们改变这个平方根程序,使它将计算结果放到OUT值中去,就像Figure 2-7所做的 Figure 2-7 在存储过程中使用OUT参数
MySQL 存储过程编程

在MySQL客户端中,我们提供了一个用来保存值的OUT参数,当存储过程执行完毕,我们可以回头检验这个变量的输出情况,就像Example 2-4. Example 2-4 创建和执行使用OUT参数的存储过程 mysql> SOURCEmy_sqrt2.sql Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.02 sec) mysql> CALLmy_sqrt(12,@out_value) $$ Query OK, 0 rows affected (0.03 sec) mysql> SELECT@out_value $$ +-----------------+ | @out_value | +-----------------+ | 3.4641016151378 | +-----------------+ 1 row in set (0.00 sec)
MySQL 存储过程编程

2.5 条件执行
你可以用IF或者CASE语句来控制存储程序的执行流程。它们具有相同的功能,因此,我们只用示例演示了IF(CASE的功能是相同的) Figure2-8 演示了通过购买量的多少来计算出贴现率的存储程序,Example2-5演示了它的执行结果,购买量超过$500可以返还20%,购买量超过$100可以返还10%。 Figure 2-8使用IF语句的条件执行
Example 2-5 创建和执行包含IF语句的存储过程 mysql> SOURCEdiscounted_price.sql Query OK, 0 rows affected (0.01 sec) Query OK, 0 rows affected (0.00 sec) mysql> CALLdiscounted_price(300,@new_price) $$ Query OK, 0 rows affected (0.00 sec) mysql> SELECT@new_price$$ +------------+ | @new_price | +------------+ | 270.0 | +------------+
MySQL 存储过程编程

1 row in set (0.00 sec)
IF语句允许你测试表达式的真实性(就像normal_price > 500),并且基于表达式的结果执行一定的行为,作为一种编程语言,ELSEIF可以被用来作为IF起始循环的条件转移,ELSE字句将在IF和ELSEIF字句的布尔表达式为假时执行 CASE具有相同的功能,并且当你对单个表达式进行对比是可以获得更清晰的值,这两个条件语句将在第4章做更为细致的探究和比对。 MySQL 存储过程编程

2.6 循环
循环允许在你的存储程序中重复性的执行某些行为,MySQL存储程序语言提供了三种类型的循环
使用LOOP和END LOOP字句的简单循环
当条件为真时继续执行的循环,使用WHILE和END WHILE字句
循环直至条件为真,使用REPEAT和UNTIL字句
在这三种循环中,你都可以使用LEAVE子句来终止循环 在三种循环都将在第4章详细解释;我们只会在这个指南中给出LOOP-LEAVE-END LOOP(简单循环)的例子 Figure 2-9 简单循环演示 Figure 2-9.存储过程中的简单循环
下面是对于代码的详细解释 行号 解释 7 声明了一个名为counter,初始值为0的简单数字变量 9-14 简单循环,所有在LOOP和END LOOP之间的部分都将在LEAVE子句被执行后终止 9 LOOP语句带有前缀为my_simple_loop的标签,LEAVE子句要求循环被标识,这样才能知道要退出哪个循环 10 给counter变量的值增加1 11-13 测试counter的值,如果值为10,则退出循环,否则,我们继续下一个迭代 15 我们骄傲的宣称我们可以数到10
MySQL 存储过程编程

2.7 错误处理
当存储程序发生错误时,MySQL默认的行为是终止程序的执行并把错误返回给它的调用程序。如果你需要以不同的方式来相应错误,你可以定义一个或多个可以被存储程序所响应的错误情况 如下两个相关联的情景被称为错误处理的定义:
如果你认为内嵌的SQL语句会返回空记录,或者你想用游标捕获所有SELECT语句所返回的记录,那么一个NOT FOUND错误处理可以防止存储程序过早的被终止
如果你认为SQL语句可能返回错误(比如:违背约束条件),你可以创建一个错误处理来阻止程序终止。这个处理将代替你的默认错误处理并继续程序的执行。
第6章将详细解释错误处理,在下一节中我们将演示一个使用NOT FOUND错误处理并结合游标显示的例子
MySQL 存储过程编程

2.8 和数据库交互
大多数存储过程包含了各种和数据库表的交互,它们包括四种主要的交互:
将一个SQL语句所返回的单个记录放入本地变量中
创建一个“游标”来迭代SQL语句所返回的结果集
执行一个SQL语句,将执行后的结果集返回给它的调用程序
内嵌一个不返回结果集的SQL语句,如INSERT, UPDATE, DELETE等
我们暂时来大致的看一下这几种和数据库交互的情况
为了能运行本节的示例,你必须安装和本书配套的sample数据库,这个可以在本书的网站找到(详见前言)
2.8.1对本地变量使用SELECT INTO
当需要在单个记录数据中获取查询信息,你就可以使用SELECT INTO语法(无论是使用单个记录,多个记录的混合数据,还是多个表连接)。在这种情况下,你可以在SELECT语句中跟随一个INTO子句,告诉MySQL得到的查询数据返回给谁 Figure 2-10 演示了更具customer ID的不同来获取和显示销售量的存储过程。 Figure 2-6 是执行结果 Figure 2-10在存储过程中使用SELECT INTO语句
MySQL 存储过程编程

Example 2-6执行包含SELECT INTO语句的存储过程 mysql> CALL customer_sales(2) $$ +--------------------------------------------------------------+ | CONCAT('Total sales for ',in_customer_id,' is ',total_sales) | +--------------------------------------------------------------+ | Total sales for 2 is 7632237 | +--------------------------------------------------------------+ 1 row in set (18.29 sec) Query OK, 0 rows affected (18.29 sec)
2.8.2 使用游标
SELECT INTO定义了单记录查询,但是很多应用程序要求查询多记录数据,你可以使用MySQL中的游标来实现这一切,游标允许你将一个或更多的SQL结果集放进存储程序变量中,通常用来执行结果集中各个但记录的处理。 在Figure 2-11中,存储程序使用游标来捕获所有employees表的记录 下面是对于代码的详细解释 Figure 2-11. 在存储过程中使用游标
MySQL 存储过程编程

行号 解释 8-12 声明本地变量,前面的三个是用来存放SELECT语句的结果。第四个(done)能让我们确认所有的记录行都已被读取 14-16 定义我们的游标,这是基于一个简单SELECT语句从employees表中所返回的结果集 18 声明一个“handler”,它定义了当我们无法从SELECT语句得到更多记录时的行为。handler可以用来捕获所有的错误类型,但是像示例中所演示的handler只是在我们需要的时候警告我们已经没有更多的记录可以被读取而已 20 打开游标 21-26 用一个简单循环来从游标中获取所有的记录 22 用FETCH子句将从游标中获取单个记录,然后放进我们的本地变量中。 23-25 检测变量done的值,如果它被设置成1,那么就说明我们已经获取了最后一个数据,那么我们就用LEAVE语句来终止循环。
2.8.3 返回结果集的存储过程
在这本书的前些部分,我们已经在和存储过程和数据库的交互中使用过一些个并不包含INTO子句和游标的沉长的SELECT语句,它们被用于在存储过程中返回一些状态数据和结果集,迄今为止,我们只使用过单记录结果集,但是你也可以在存储过程中包含一些复杂的SQL语句来返回多个结果。
如果我们在MySQL命令行中执行这样的存储过程,结果集将像我们执行SELECT和SHOW语句一个被返回。Figure 2-12
MySQL 存储过程编程

向我们展示了包含了沉长的SELECT语句的存储过程 Figure 2-12 包含沉长SELECT语句的存储过程
如果我们在执行这个存储过程时为其参数提供适当的值,那么