Mysql存储过程

1、关于MySQL的存储过程

存储过程是数据库存储的一个重要的功能，但是MySQL在5.0以前并不支持存储过程，这使得MySQL在应用上大打折扣。好在MySQL 5.0终于开始已经支持存储过程，这样即可以大大提高数据库的处理速度，同时也可以提高数据库编程的灵活性。

2、MySQL存储过程的创建

(1).格式

MySQL存储过程创建的格式：CREATE PROCEDURE过程名([过程参数[,...]]) procedure
[特性...]过程体

这里先举个例子：

1. mysql>DELIMITER// 

2. mysql>CREATEPROCEDUREproc1(OUTsint) 

3. ->BEGIN

4. ->SELECTCOUNT(*)INTOsFROMuser; 

5. ->END

6. ->// 

7. mysql>DELIMITER;

注：

（1）这里需要注意的是DELIMITER //和DELIMITER ;两句，DELIMITER是分割符的意思，因为MySQL默认以";"为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将";"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。

（2）存储过程根据需要可能会有输入、输出、输入输出参数，这里有一个输出参数s，类型是int型，如果有多个参数用","分割开。

（3）过程体的开始与结束使用BEGIN与END进行标识。

这样，我们的一个MySQL存储过程就完成了，是不是很容易呢?看不懂也没关系，接下来，我们详细的讲解。

(2).声明分割符

其实，关于声明分割符，上面的注解已经写得很清楚，不需要多说，只是稍微要注意一点的是：如果是用MySQL的Administrator管理工具时，可以直接创建，不再需要声明。

(3).参数

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：

CREATE PROCEDURE([[IN |OUT |INOUT ]参数名数据类形...])

IN输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值

OUT输出参数:该值可在存储过程内部被改变，并可返回

INOUT输入输出参数:调用时指定，并且可被改变和返回

Ⅰ. IN参数例子

创建:

1. mysql>DELIMITER// 

2. mysql>CREATEPROCEDUREdemo_in_parameter(INp_inint) 

3. ->BEGIN 

4. ->SELECTp_in;/*查询输入参数*/ 

5. ->SETp_in=2;/*修改*/ 

6. ->SELECTp_in;/*查看修改后的值*/ 

7. ->END; 

8. ->// 

9. mysql>DELIMITER;

执行结果:

1. mysql>SET@p_in=1; 

2. mysql>CALLdemo_in_parameter(@p_in); 

3. +------+ 

4. |p_in| 

5. +------+ 

6. |1| 

7. +------+ 

8. 

9. +------+ 

10.|p_in| 

11.+------+ 

12.|2| 

13.+------+ 

14.

15.mysql>SELECT@p_in; 

16.+-------+ 

17.|@p_in| 

18.+-------+ 

19.|1| 

20.+-------+ 

以上可以看出，p_in虽然在存储过程中被修改，但并不影响@p_id的值

Ⅱ.OUT参数例子

创建:

1. mysql>DELIMITER// 

2. mysql>CREATEPROCEDUREdemo_out_parameter(OUTp_outint) 

3. ->BEGIN

4. ->SELECTp_out;/*查看输出参数*/ 

5. ->SETp_out=2;/*修改参数值*/ 

6. ->SELECTp_out;/*看看有否变化*/ 

7. ->END; 

8. ->// 

9. mysql>DELIMITER;

执行结果:

1. mysql>SET@p_out=1; 

2. mysql>CALLsp_demo_out_parameter(@p_out); 

3. +-------+ 

4. |p_out| 

5. +-------+ 

6. |NULL| 

7. +-------+ 

8. /*未被定义，返回NULL*/ 

9. +-------+ 

10.|p_out| 

11.+-------+ 

12.|2| 

13.+-------+ 

14.

15.mysql>SELECT@p_out; 

16.+-------+ 

17.|p_out| 

18.+-------+ 

19.|2| 

20.+-------+ 

Ⅲ. INOUT参数例子

创建:

1. mysql>DELIMITER// 

2. mysql>CREATEPROCEDUREdemo_inout_parameter(INOUTp_inoutint) 

3. ->BEGIN

4. ->SELECTp_inout; 

5. ->SETp_inout=2; 

6. ->SELECTp_inout; 

7. ->END; 

8. ->// 

9. mysql>DELIMITER;

执行结果:

1. mysql>SET@p_inout=1; 

2. mysql>CALLdemo_inout_parameter(@p_inout); 

3. +---------+ 

4. |p_inout| 

5. +---------+ 

6. |1| 

7. +---------+ 

8. 

9. +---------+ 

10.|p_inout| 

11.+---------+ 

12.|2| 

13.+---------+ 

14.

15.mysql>SELECT@p_inout; 

16.+----------+ 

17.|@p_inout| 

18.+----------+ 

19.|2| 

20.+----------+

(4).变量

Ⅰ.变量定义

DECLARE variable_name [,variable_name...] datatype [DEFAULT value];

其中，datatype为MySQL的数据类型，如:int, float, date, varchar(length)

例如:

1. DECLAREl_intintunsigneddefault4000000; 

2. DECLAREl_numericnumber(8,2)DEFAULT9.95; 

3. DECLAREl_datedateDEFAULT'1999-12-31'; 

4. DECLAREl_datetimedatetimeDEFAULT'1999-12-3123:59:59'; 

5. DECLAREl_varcharvarchar(255)DEFAULT'Thiswillnotbepadded'; 

Ⅱ.变量赋值

SET变量名=表达式值[,variable_name = expression ...]

Ⅲ.用户变量

ⅰ.在MySQL客户端使用用户变量

1. mysql>SELECT'HelloWorld'into@x; 

2. mysql>SELECT@x; 

3. +-------------+ 

4. |@x| 

5. +-------------+ 

6. |HelloWorld| 

7. +-------------+ 

8. mysql>SET@y='GoodbyeCruelWorld'; 

9. mysql>SELECT@y; 

10.+---------------------+ 

11.|@y| 

12.+---------------------+ 

13.|GoodbyeCruelWorld| 

14.+---------------------+ 

15.

16.mysql>SET@z=1+2+3; 

17.mysql>SELECT@z; 

18.+------+ 

19.|@z| 

20.+------+ 

21.|6| 

22.+------+ 

ⅱ.在存储过程中使用用户变量

1. mysql>CREATEPROCEDUREGreetWorld()SELECTCONCAT(@greeting,'World'); 

2. mysql>SET@greeting='Hello'; 

3. mysql>CALLGreetWorld(); 

4. +----------------------------+ 

5. |CONCAT(@greeting,'World')| 

6. +----------------------------+ 

7. |HelloWorld| 

8. +----------------------------+ 

ⅲ.在存储过程间传递全局范围的用户变量

1. mysql>CREATEPROCEDUREp1()SET@last_procedure='p1'; 

2. mysql>CREATEPROCEDUREp2()SELECTCONCAT('Lastprocedurewas',@last_procedure); 

3. mysql>CALLp1(); 

4. mysql>CALLp2(); 

5. +-----------------------------------------------+ 

6. |CONCAT('Lastprocedurewas',@last_proc | 

7. +-----------------------------------------------+ 

8. |Lastprocedurewasp1| 

9. +-----------------------------------------------+ 

注意:

①用户变量名一般以@开头

②滥用用户变量会导致程序难以理解及管理