mysql 存储过程第一讲

短命女 2022-08-12 01:09 118阅读 0赞

MySQL 5.0+版本新特性教程 存储过程:第一讲  
  
说明  
MySQL 5.0 +版本新特性教程是为需要了解5.0版本及其以上新特性的MySQL老用户而写的。简单的来说是介绍了“存储过程、触发器、视图、信息架构视图”,在此感谢译者陈朋奕的努力.  
　　希望以下内容能像内行专家那样与您进行对话，用简单的问题、例子让你学到需要的知识。为了达到这样的目的，我会从每一个细节开始慢慢的为大家建立概念，最后会给大家展示较大的实用例，在学习之前也许大家会认为这个用例很难，但是只要跟着课程去学，相信很快就能掌握。  
Conventions and Styles 约定和编程风格  
　　每次我想要演示实际代码时，我会对mysql客户端的屏幕就出现的代码进行调整，将字体改成Courier，使他们看起来与普通文本不一样。  
　　在这里举个例子：mysql>DROP FUNCTION f;  
                              mysql>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）编写的程序运行起来那么快。但是提升速度的主要方法却在于能否降低网络信息流量。如果你需要处理的是需要检查、循环、多语句但没有用户交互的重复性任务，你就可以使用保存在服务器上的存储过程来完成。这样在执行任务的每一步时服务器和客户端之间就没那么多的信息来往了。  
　　所以存储过程是可复用的组件！想象一下如果你改变了主机的语言，这对存储过程不会产生影响，因为它是数据库逻辑而不是应用程序。存储过程是可以移植的！当你用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客户端返回的结果，这样意味着我有极大的特权。  
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 p1();  
Error 1064 (42000): You have a syntax error.  
mysql> CALL p1();  
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语句用在存储过程中，这里只是为了演示。所以使用这样的语句，能在调用时更好的看出程序是否正常工作。