五香 J2EE的学习平台

人,不要說不行,要有理想,但不要理想化。

MySQL 5.0 新特性教程 存储过程:第二讲

Why MySQL Statements are Legal in a Procedure Body
  什么MySQL语句在存储过程体中是合法的?

   什么样的SQL语句在Mysql存储过程中才是合法的呢?你可以创建一个包含INSERT, UPDATE,DELETE, SELECT, DRO P, CREATE, REPLACE等的语句。你唯一需要记住的是如果代码中包含MySQL扩充功能,那么代码将不能移植。在标准SQL语句中:任何数据库定义语言都是合法的,如:

 

CREATE PROCEDURE p () DELETE FROM t; //
  SET、COMMIT以及ROLLBACK也是合法的,如:
CREATE PROCEDURE p () SET @x = 5; //

  MySQL的附加功能:任何数据操作语言的语句都将合法。
CREATE PROCEDURE p () DRO P 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, DRO P PROCEDURE, CREATE FUNCTION,
DRO P FUNCTION, CREATE TRIGGER, DRO P TRIGGER.

  不过你可以使用
CREATE PROCEDURE db5.p1 () DRO P 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)则是告诉服务器在这一步仍然要检查调用者的权限。

  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)
  当调用过程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)

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值是从过程中传出的。
  同样我们可以用语句
"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块也被称为复合语句,在这里你可以进行变量定义和流程控制。

  未完待续...

 
阅读更多
个人分类: 数据库
想对作者说点什么? 我来说一句

MySQL 5.0存储过程教程

2008年12月20日 468KB 下载

\MySQL 5.0 存储过程.pdf

2010年05月01日 2.12MB 下载

MySQL 5.0 存储过程

2009年02月09日 467KB 下载

MySQL 5.0存储过程

2010年10月25日 470KB 下载

没有更多推荐了,返回首页

不良信息举报

MySQL 5.0 新特性教程 存储过程:第二讲

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭