MYSQL存储过程

MYSQL存储过程

存储过程通常有以下优点：

(1).存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

(2).存储过程允许标准组件是编程。存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。

(3).存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。

(4).存储过程能过减少网络流量。针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织程存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。

(5).存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

一、MySQL创建存储过程

创建MySQL 存储过程的简单语法为：

create procedure 存储过程名字()  

(  

[in|out|inout] 参数 datatype  

)  

begin  

MySQL 语句;  

end;  

例如：

DELIMITER$$

DROPPROCEDURE IF EXISTS `gpsdata`.`sp_insertTbDataAlarm`$$

##gpsdata是数据库名，sp_insertTbDataAlarm是存储过程名

CREATEDEFINER=`root`@`localhost` PROCEDURE `sp_insertTbDataAlarm`(

       gpstime_ varchar(128),

       gpsname_ varchar(128),

       gpsinfo_ varchar(256),

       )

BEGIN

##主过程；

END$$

DELIMITER;

二、调用MySQL 存储过程

call sp_insertTbDataAlarm(参数1, 参数n…);  //是在你的程序里面写这条语句

三、MySQL存储过程特点

1.MySQL 存储过程名字后面的“()”是必须的，即使没有一个参数，也需要“()”

2.MySQL 存储过程参数，不能在参数名称前加“@”，如：“@aint”。

3.MySQL 存储过程的参数不能指定默认值。

4.如果MySQL 存储过程中包含多条MySQL 语句，则需要begin end 关键字。

5.MySQL 存储过程中的每条语句的末尾，都要加上分号“;”

7.MySQL 存储过程中的注释。

/*  

这是个  

多行 MySQL 注释。  

*/  

declare c int; -- 这是单行 MySQL 注释 （注意 -- 后至少要有一个空格）  

if a is null then # 这也是个单行 MySQL 注释  

8.不能在MySQL 存储过程中使用“return” 关键字。

return abc; -- 不能在 MySQL 存储过程中使用。return 只能出现在函数中。  

9.因为MySQL 存储过程参数没有默认值，所以在调用 MySQL 存储过程时候，不能省略参数。可以用null 来替代。

 

 MySQL变量 

(1).变量作用域 

内部的变量在其作用域范围内享有更高的优先权，当执行到end。变量时，内部变量消失，此时已经在其作用域外，变量不再可见了，应为在存储过程外再也不能找到这个申明的变量，但是你可以通过out参数或者将其值指派 给会话变量来保存其值。 

变量分为用户变量和系统变量，系统变量又分为会话和全局级变量

用户变量：用户变量名一般以@开头，滥用用户变量会导致程序难以理解及管理

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

mysql>CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');
mysql> SET @greeting='Hello';
mysql> CALL GreetWorld( );

 

变量的使用

在存储过程和函数中，可以定义和使用变量。用户可以使用DECLARE关键字来定义变量。然后可以为变量赋值。这些变量的作用范围是BEGIN…END程序段中。本小节将讲解如何定义变量和为变量赋值。

 

1．定义变量

MySQL中可以使用DECLARE关键字来定义变量。定义变量的基本语法如下：

DECLARE  var_name[,...]  type [DEFAULT value]

其中，DECLARE关键字是用来声明变量的；var_name参数是变量的名称，这里可以同时定义多个变量；type参数用来指定变量的类型；DEFAULTvalue子句将变量默认值设置为value，没有使用DEFAULT子句时，默认值为NULL。

 

【示例14-3】 下面定义变量my_sql，数据类型为INT型，默认值为10。代码如下：

DECLARE  my_sql INT  DEFAULT 10 ;

 

2．为变量赋值

MySQL中可以使用SET关键字来为变量赋值。SET语句的基本语法如下：

SET  var_name = expr [, var_name = expr] ...

其中，SET关键字是用来为变量赋值的；var_name参数是变量的名称；expr参数是赋值表达式。一个SET语句可以同时为多个变量赋值，各个变量的赋值语句之间用逗号隔开。

 

【示例14-4】 下面为变量my_sql赋值为30。代码如下：

SET  my_sql = 30 ;

MySQL中还可以使用SELECT…INTO语句为变量赋值。其基本语法如下：

 

SELECT  col_name[,…] INTO  var_name[,…]   FROM table_name  WEHRE  condition

其中，col_name参数表示查询的字段名称；var_name参数是变量的名称；table_name参数指表的名称；condition参数指查询条件。

 

【示例14-5】 下面从employee表中查询id为2的记录，将该记录的d_id值赋给变量my_sql。代码如下：

SELECT  d_id INTO  my_sql   FROM employee  WEHRE  id=2 ;

 

 附注：MySQL存储过程函数，请参考：http://blog.sina.com.cn/s/blog_52d20fbf0100ofd5.html

Contact的用法 http://database.51cto.com/art/201010/229348.htm

Prepare的用法 http://maoyifa100.iteye.com/blog/1900305