MySQL 5.0 新特性--存储过程

2024-07-24 12:55:59

字体：大中小

来源：转载

供稿：网友

最大的网站源码资源下载站，

　　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语句*/

　　end /* end of block语句块结束*/

　　下面我将会介绍你可以利用存储过程做的工作的所有细节。同时我们将介绍新的数据库对象—触发器，因为触发器和存储过程的关联是必然的。

　　why stored procedures 为什么要用存储过程

　　由于存储过程对于mysql来说是新的功能，很自然的在使用时你需要更加注意。

　　毕竟，在此之前没有任何人使用过，也没有很多大量的有经验的用户来带你走他们走过的路。然而你应该开始考虑把现有程序（可能在服务器应用程序中，用户自定义函数（udf）中，或是脚本中）转移到存储过程中来。这样做不需要原因，你不得不去做。

|||

|||

收集最实用的网页特效代码！

　　show variables like 'version';

　　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);

|||中国最大的web开发资源网站及技术社区，

　　query ok, 1 row affected (0.00 sec)

　　你会发现我只在表中插入了一列。这样做的原因是我要保持表的简单，因为在这里并不需要展示查询数据的技巧，而是教授存储过程，不需要使用大的数据表，因为它本身已经够复杂了。

　　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标准中把这个定义为非核心的，即可选组件。

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

　　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;"

　　好了，主要的知识点"创建和调用过程方法"已经清楚了。我希望你能对自己说这相当简单。但是很快我们就有一系列的练习，每次都加一条子句，或者改变已经存在的子句。那样在写复杂部件前我们将会有很多可用的子句。

　　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 //

　　下一个子句是sql security，可以定义为sql security definer或sql security invoker。

　　这就进入了权限控制的领域了，当然我们在后面将会有测试权限的例子。

　　sql security definer

　　意味着在调用时检查创建过程用户的权限（另一个选项是sqlsecurity invoker）。

　　现在而言，使用

　　sql security definer

　　指令告诉mysql服务器检查创建过程的用户就可以了，当过程已经被调用，就不检查执行调用过程的用户了。而另一个选项（invoker）则是告诉服务器在这一步仍然要检查调用者的权限。

　　最后，注释子句会跟过程定义存储在一起。这个没有固定的标准，我在文中会指出没有固定标准的语句，不过幸运的是这些在我们标准的sql中很少。

　　当调用过程p2时，一个select语句被执行返回我们期望获得的随机数。

　　mysql在过程创建时会自动保持运行环境。例如：我们需要使用两条竖线来连接字符串但是这只有在sql mode为ansi的时候才合法。如果我们将sql mode改为non-ansi，不用担心，它仍然能工作，只要它第一次使用时能正常工作。

　　如果你不介意练习一下的话，试能否不看后面的答案就能处理这些请求。

　　创建一个过程，显示`hello world`。用大约5秒时间去思考这个问题，既然你已经学到了这里，这个应该很简单。当你思考问题的时候，我们再随机选择一些刚才讲过的东西复习：

　　（确定性）子句是反映输出和输入依赖特性的子句…调用过程使用call过程名（参数列表）方式。好了，我猜时间也到了。

　　回忆一下前面讲过的参数列表必须在存储过程名后的括号中。上面的第一个例子中的参数列表是空的，第二个例子中有一个输入参数。这里的词in可选，因为默认参数为in（input）。