存储过程（一） 基础语句块、参数、变量

最近在学习mysql存储过程，简单整理了下自己看过的一些内容，跟各位分享。

基础语句块介绍：

LANGUAGE SQL

该子句是没有作用的。仅是为了说明下面过程的主体使用 SQL语言编写。这条是系统默认的，但你在这里声明是有用的，因为某些 DBMS（IBM 的 DB2）需要它，如果你关注 DB2 的兼容问题最好还是用上。此外，今后可能会出现除 SQL 外的其他语言支持的存储过程。

NOT DETERMINISTIC，

该字句是传递给系统的信息。这里一个确定过程的定义，就是那些每次输入一样输出也一样的程序。在这个案例中，既然主体中含有 SELECT 语句，

那返回肯定是未知的，因此我们称其 NOT DETERMINISTIC。但是MySQL内置的优化程序不会注意这个，至少在现在不注意。 

DETERMINISTIC

（确定性）子句是反映输出和输入依赖特性的子句...调用过程使用 CALL 过程名（参数列表）方式。

SQL SECURITY，可以定义为 SQL SECURITY DEFINER 或SQL SECURITYINVOKER---权限控制的领域：

SQL SECURITY DEFINER告诉服务器在调用时检查创建过程用户的权限

SQL SECURITY INVOKER告诉MySQL服务器在定义和调用时过程时，都要检查用户权限。

begin ... end 

完成过程体的构造就是 BEGIN/END 块。我们可以使用块去封装多条语句。如下所示实例，我们使用了多条设定会话变量的语句，然后完成了一些 insert 和 select语句。如果你的过程体中有多条语句，那么你就需要 BEGIN/END块了。BEGIN/END 块也被称为复合语句，在这里你可以进行变量定义和流程控制（顺序、选择、循环）。  

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. 

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 值是从过程中传出的。 

CREATE PROCEDURE sp_add(a int, b int,out c int)
begin
set c=a+ b;
end;
调用过程：
call sp_add (1,2,@a);
select @a;

Variables 变量

在复合语句中声明变量的指令是 DECLARE。

(1) Example with two DECLARE statements，

(2) Example with no DEFAULT clause and SET statement

CREATE PROCEDURE p8 ()

BEGIN

DECLARE a INT; /* there is no DEFAULT clause */

DECLARE b INT; /* there is no DEFAULT clause */

SET a = 5; /* there is a SET statement */

SET b = 5; /* there is a SET statement */

INSERT INTO t VALUES (a);

SELECT s1 * a FROM t WHERE s1 >= b;

END; 

在过程中定义的变量并不是真正的定义，你只是在 BEGIN/END 块内定义了而已（译注：也就是形参）。注意这些变量和会话变量不一样，不能使用修饰符@，你必须清楚的在

BEGIN/END 块中声明变量和它们的类型。变量一旦声明，你就能在任何能使用会话变量、文字、列名的地方使用。有很多初始化变量的方法。如果没有默认的子句，那么变量的初始值为 NULL。你可以在任何时候使用 SET 语句给变量赋值。

(3)Example with DEFAULT clause

CREATE PROCEDURE p10 ()

BEGIN

DECLARE a, b INT DEFAULT 5;

INSERT INTO t VALUES (a);

SELECT s1 * a FROM t WHERE s1 >= b;

END; //

我们在这里做了一些改变，但是结果还是一样的。在这里使用了 DEFAULT 子句来设定初始值，这就不需要把 DECLARE 和 SET 语句的实现分开了。

(4)Example of CALL

mysql> CALL p10() //

+--------+

| s1 * a |

+--------+

| 25 |

| 25 |

+--------+

结果显示了过程能正常工作

(5) Scope

CREATE PROCEDURE p11 ()

BEGIN

DECLARE x1 CHAR(5) DEFAULT 'outer';

BEGIN

DECLARE x1 CHAR(5) DEFAULT 'inner';

SELECT x1;

END;

SELECT x1;

END; //

现在我们来讨论一下作用域的问题。例子中有嵌套的 BEGIN/END 块，当然这是合法的。同时包含两个变量，名字都是 x1，这样也是合法的。内部的变量在其作用域内享有更高的优先权。当执行到 END 语句时，内部变量消失，此时已经在其作用域外，变量不再可见了，因此在存储过程外再也不能找到这个声明了的变量，但是你可以通过 OUT 参数或者将其值指派给会话变量来保存其值。调用作用域例子的过程：

mysql> CALL p11()

+-------+

| x1 |

+-------+

| inner |

+-------+

+-------+

| x1 |

+-------+

| outer |

+-------+

我们看到的结果时，第一个 SELECT 语句检索到最内层的变量，第二个检索到第二层的变量。

变量的种类

1)局部变量  2）用户自定义变量

局部变量必须声明后再使用；

用户自定义变量可以直接使用。

变量的声明方式：

DECLARE var_name [, var_name] ... type [DEFAULT value] (局部变量)

SET @var_name = expr [, @var_name = expr] ...(用户自定义变量)

赋值的方法:

第一种:SET var_name = expr [, var_name = expr] ...

例:Declare a int; set a := 100;

第二种:SELECT col_name [, col_name] ... INTO var_name [, var_name] ...

table_expr

例:Declare x int; select id into x from test.t1;