MySQL存储过程

MySQL5.0新特性教程存储过程Introduction简介MySQL5.0新特性教程是为需要了解5.0版本新特性的MySQL老用户而写的。简单的来说是介绍了“存储过程、触发器、视图、信息架构视图”。希望这本书能像内行专家那样与您进行对话，用简单的问题、例子让你学到需要的知识。为了达到这样的目的，我会从每一个细节开始慢慢的为大家建立概念，最后会给大家展示较大的实用例，在学习之前也许大家会认为这个用例很难，但是只要跟着课程去学，相信很快就能掌握。ConventionsandStyles约定和编程风格每次我想要演示实际代码时，我会对mysql客户端的屏幕出现的代码进行调整，将字体改成Courier，使他们看起来与普通文本不一样。在这里举个例子：mysqlDROPFUNCTIONf;QueryOK,0rowsaffected(0.00sec)如果实例比较大，则需要在某些行和段落间加注释，同时我会用将“--”符号放在页面的右边以表示强调。例如：mysqlCREATEPROCEDUREp()-BEGIN-/*Thisproceduredoesnothing*/---END;//QueryOK,0rowsaffected(0.00sec)有时候我会将例子中的mysql和-这些系统显示去掉，你可以直接将代码复制到mysql客户端程序中（如果你现在所读的不是电子版的，可以在mysql.com网站下载相关脚本）所以的例子都已经在Suse9.2Linux、Mysql5.0.3公共版上测试通过。在您阅读本书的时候，Mysql已经有更高的版本，同时能支持更多OS了，包括Windows，Sparc，HP-UX。因此这里的例子将能正常的运行在您的电脑上。但如果运行仍然出现故障，可以咨询你认识的资深Mysql用户，以得到长久的支持和帮助。ADefinitionandanExample定义及实例定义及实例存储过程是一种存储在书库中的程序（就像正规语言里的子程序一样），准确的来说，MySQL支持的“routines（例程）”有两种：一是我们说的存储过程，二是在其他SQL语句中可以返回值的函数（使用起来和Mysql预装载的函数一样，如pi()）。我在本书里面会更经常使用存储过程，因为这是我们过去的习惯，相信大家也会接受。一个存储过程包括名字，参数列表，以及可以包括很多SQL语句的SQL语句集。在这里对局部变量，异常处理，循环控制和IF条件句有新的语法定义。下面是一个包括存储过程的实例声明：（译注：为了方便阅读，此后的程序不添任何中文注释）CREATEPROCEDUREprocedure1/*name存储过程名*/(INparameter1INTEGER)/*parameters参数*/BEGIN/*startofblock语句块头*/DECLAREvariable1CHAR(10);/*variables变量声明*/IFparameter1=17THEN/*startofIFIF条件开始*/SETvariable1='birds';/*assignment赋值*/ELSESETvariable1='beasts';/*assignment赋值*/ENDIF;/*endofIFIF结束*/INSERTINTOtable1VALUES(variable1);/*statementSQL语句*/END/*endofblock语句块结束*/下面我将会介绍你可以利用存储过程做的工作的所有细节。同时我们将介绍新的数据库对象——触发器，因为触发器和存储过程的关联是必然的。WhyStoredProcedures为什么要用存储过程由于存储过程对于MySQL来说是新的功能，很自然的在使用时你需要更加注意。毕竟，在此之前没有任何人使用过，也没有很多大量的有经验的用户来带你走他们走过的路。然而你应该开始考虑把现有程序（可能在服务器应用程序中，用户自定义函数（UDF）中，或是脚本中）转移到存储过程中来。这样做不需要原因，你不得不去做。因为存储过程是已经被认证的技术！虽然在Mysql中它是新的，但是相同功能的函数在其他DBMS中早已存在，而它们的语法往往是相同的。因此你可以从其他人那里获得这些概念，也有很多你可以咨询或者雇用的经验用户，还有许多第三方的文档可供你阅读。存储过程会使系统运行更快！虽然我们暂时不能在Mysql上证明这个优势，用户得到的体验也不一样。我们可以说的就是Mysql服务器在缓存机制上做了改进，就像Preparedstatements（预处理语句）所做的那样。由于没有编译器，因此SQL存储过程不会像外部语言（如C）编写的程序运行起来那么快。但是提升速度的主要方法却在于能否降低网络信息流量。如果你需要处理的是需要检查、循环、多语句但没有用户交互的重复性任务，你就可以使用保存在服务器上的存储过程来完成。这样在执行任务的每一步时，服务器和客户端之间就没那么多的信息来往了。所以存储过程是可复用的组件！想象一下如果你改变了主机的语言，这对存储过程不会产生影响，因为它是数据库逻辑而不是应用程序。存储过程是可以移植的！当你用SQL编写存储过程时，你就知道它可以运行在Mysql支持的任何平台上，不需要你额外添加运行环境包，也不需要为程序在操作系统中执行设置许可，或者为你的不同型号的电脑存储过程将被保存！如果你编写好了一个程序，例如显示银行事物处理中的支票撤消，那想要了解支票的人就可以找到你的程序。它会以源代码的形式保存在数据库中。这将使数据和处理数据的进程有意义的关联，这可能跟你在课上听到的规划论中说的一样。存储过程可以迁移！Mysql完全支持SQL2003标准。某些数据库（如DB2、Mimer）同样支持。但也有部分不支持的，如Oracle、SQLServer不支持。我们将会给予足够帮助和工具，使为其他DBMS编写的代码能更容易转移到Mysql上。SettingupwithMySQL5.0设置并开始MySQL5.0服务通过mysql_fix_privilege_tables或者~/mysql-5.0/scripts/mysql_install_db来开始MySQL服务作为我们练习的准备工作的一部分，我假定MySQL5.0已经安装。如果没有数据库管理员为你安装好数据库以及其他软件，你就需要自己去安装了。不过你很容易忘掉一件事，那就是你需要有一个名为mysql.proc的表。在安装了最新版本后，你必须运行mysql_fix_privilege_tables或者mysql_install_db（只需要运行其中一个就够了）——不然存储过程将不能工作。我同时启用在root身份后运行一个非正式的SQL脚本，如下：mysql/mysql-5.0/scripts/mysql_prepare_privilege_tables_for_5.sqlStartingtheMySQLClient启动MySQL客户端这是我启动mysql客户端的方式。你也许会使用其他方式，如果你使用的是二进制版本或者是Windows系统的电脑，你可能会在其他子目录下运行以下程序：easy@phpv:~/usr/local/mysql/bin/mysql--user=rootWelcometotheMySQLmonitor.Commandsendwith;or\g.YourMySQLconnectionidis1toserverversion:5.0.3-alpha-debugType'help;'or'\h'forhelp.Type'\c'toclearthebuffer.在演示中，我将会展示以root身份登陆后的mysql客户端返回的结果，这样意味着我有极大的特权。CheckfortheCorrectVersion核对版本为了确认使用的MySQL的版本是正确的，我们要查询版本。我有两种方法确认我使用的是5.0版本：SHOWVARIABLESLIKE'version';orSELECTVERSION();例如：mysqlSHOWVARIABLESLIKE'version';+---------------+-------------------+|Variable_name|Value|+---------------+-------------------+|version|5.0.3-alpha-debug|+---------------+-------------------+1rowinset(0.00sec)mysqlSELECTVERSION();+-------------------+|VERSION()|+-------------------+|5.0.3-alpha-debug|+-------------------+1rowinset(0.00sec)当看见数字'5.0.x'后就可以确认存储过程能够在这个客户端上正常工作。TheSampleDatabase示例数据库现在要做的第一件事是创建一个新的数据库然后设定为默认数据库,实现这个步骤的SQL语句如下：CREATEDATABASEdb5;USEdb5;例如：mysqlCREATEDATABASEdb5;QueryOK,1rowaffected(0.00sec)mysqlUSEdb5;Databasechanged在这里要避免使用有重要数据的实际的数据库,然后我们创建一个简单的工作表。实现这个步骤的SQL语句如下：mysqlCREATEDATABASEdb5;QueryOK,1rowaffected(0.01sec)mysqlUSEdb5;DatabasechangedmysqlCREATETABLEt(s1INT);QueryOK,0rowsaffected(0.01sec)mysqlINSERTINTOtVALUES(5);QueryOK,1rowaffected(0.00sec)你会发现我只在表中插入了一列。这样做的原因是我要保持表的简单，因为在这里并不需要展示查询数据的技巧，而是教授存储过程，不需要使用大的数据表，因为它本身已经够复杂了。WhyMySQLStatementsareLegalinaProcedureBody什么MySQL语句在存储过程体中是合法的？什么样的SQL语句在Mysql存储过程中才是合法的呢？你可以创建一个包含INSERT,UPDATE,DELETE,SELECT,DROP,CREATE,REPLACE等的语句。你唯一需要记住的是，如果代码中包含MySQL扩充功能，那么代码将不能移植。在标准SQL语句中：任何数据库定义语言都是合法的，如：CREATEPROCEDUREp()DELETEFROMt;//SET、COMMIT以及ROLLBACK也是合法的，如：CREATEPROCEDUREp()SET@x=5;//MySQL的附加功能：任何数据操作语言的语句都将合法。CREATEPROCEDUREp()DROPTABLEt;//MySQL扩充功能：直接的SELECT也是合法的：CREATEPROCEDUREp()SELECT'a';//顺便提一下，我将存储过程中包括DDL语句的功能称为MySQL附加功能的原因是，在SQL标准中把这个定义为非核心的，即可选组件。在过程体中有一个约束，就是不能有对例程或表操作的数据库操作语句。例如下面的例子就是非法的：CREATEPROCEDUREp1()CREATEPROCEDUREp2()DELETEFROMt;下面这些对MySQL5.0来说全新的语句，过程体中是非法的:CREATEPROCEDURE,ALTERPROCEDURE,DROPPROCEDURE,CREATEFUNCTION,DROPFUNCTION,CREATETRIGGER,DROPTRIGGER.不过你可以使用CREATEPROCEDUREdb5.p1()DROPDATABASEdb5//但是类似USEdatabase语句也是非法的，因为MySQL假定默认数据库就是过程的