存储过程

16-09-02 来源：[db:作者]

收藏我要投稿

首先，对于SQL的存储过程，就类似于Java里面的定义方法，调用方法。

1、创建存储过程

mysql中，存储过程的基本形式：

CREATE PROCEDURE sp_name ([proc_parameter[,...]])  
        [characteristic ...] routine_body

sp_name参数是存储过程的名称； proc_parameter表示存储过程的参数列表； characteristic参数指定存储过程的特性； routine_body参数是SQL代码的内容，可以用BEGIN…END来标志SQL代码的开始和结束。 proc_parameter中的每个参数由3部分组成。这3部分分别是输入输出类型、参数名称和参数类型。其形式如下：

[ IN | OUT | INOUT ] param_name type

IN表示输入参数； OUT表示输出参数； INOUT表示既可以是输入，也可以是输出； param_name参数是存储过程的参数名称； type参数指定存储过程的参数类型，该类型可以是MySQL数据库的任意数据类型。

简单化定义：

create procedure 过程名(参数...)
begin
    SQL语句...
end

调用：

call 过程名(实参)

注意：
在定义前，需要先把默认的语句结束’;’号改成其它，如’&&’,这样存储过程中定义的分号就不会被看成是语句结束(否则会直接被提交)。在定义完之后，还要把’;’还原成默认的结束符。

2、无参的存储过程

//建立存储过程
mysql> delimiter %%
mysql> create procedure p1()
    -> begin
    -> select Sname,Ssex from student;
    -> end %%

mysql> delimiter ;  

//调用存储过程
mysql> call p1(); 
+--------+------+
| Sname  | Ssex |
+--------+------+
| 赵英    | 女   |
| 郑美丽  | 女   |
| 李哲    | 男   |
| 王洋    | 女   |
| 王平    | 男   |
| 赵东新  | 男   |
| 王新    | 男   |
| 陶丽平  | 男   |
| 陈文    | 女   |
| 蔡天    | 女   |
| 杨洋    | 男   |
+--------+------+

3、有参的存储过程

//查询
mysql> select * from employee;
+------------+------------+------------+--------------+
| employeeID | name       | job        | departmentID |
+------------+------------+------------+--------------+
| 6651       | Ajay Patel | Programmer |          128 |
| 7513       | Nora Edwar | Programmer |          128 |
| 9006       | Candy Burn | Systems Ad |          128 |
| 9842       | Ben Smith  | DBA        |           42 |
| 9843       | Pert Park  | DBA        |           42 |
| 9845       | Ben Patel  | DBA        |          128 |
| 9846       | Red Right  | NULL       |          128 |
| 9847       | Run Wild   | NULL       |          128 |
| 9848       | Rip This J | NULL       |          128 |
| 9849       | Rip This J | NULL       |          128 |
| 9850       | Reader U   | NULL       |          128 |
| 6651       | Ajay Patel | Programmer |          128 |
+------------+------------+------------+--------------+

//建立存储过程
mysql> delimiter &&
mysql> create procedure p4(in employeeID char(4),in name varchar(20),in job varchar(20),in departmentID int(11))
    -> begin
    -> insert into employee value(employeeID,name,job,departmentID);
    -> select * from employee;
    -> end &&

mysql> delimiter ;  

//调用存储过程
mysql> call p4('7322','cid','udewhuuwdwq',127);
+------------+------------+-------------+--------------+
| employeeID | name       | job         | departmentID |
+------------+------------+-------------+--------------+
| 6651       | Ajay Patel | Programmer  |          128 |
| 7513       | Nora Edwar | Programmer  |          128 |
| 9006       | Candy Burn | Systems Ad  |          128 |
| 9842       | Ben Smith  | DBA         |           42 |
| 9843       | Pert Park  | DBA         |           42 |
| 9845       | Ben Patel  | DBA         |          128 |
| 9846       | Red Right  | NULL        |          128 |
| 9847       | Run Wild   | NULL        |          128 |
| 9848       | Rip This J | NULL        |          128 |
| 9849       | Rip This J | NULL        |          128 |
| 9850       | Reader U   | NULL        |          128 |
| 6651       | Ajay Patel | Programmer  |          128 |
| 7322       | cid        | udewhuuwdwq |          127 |
+------------+------------+-------------+--------------+

4、有返回值的存储过程

//建立存储过程
mysql> delimiter &&
mysql> create procedure p9(in employeeID char(4),in name varchar(20),in job varchar(20),in departmentID int(11),out num int)
    -> begin
    -> insert into employee value(employeeID,name,job,departmentID);
    -> select count(*) into num from employee;
    -> end &&

mysql> delimiter ; 

//调用存储过程
mysql> call p9('3632','dueh','xianggang',28,@num);//注意输出变量的输出

mysql> select @num;
+------+
| @num |
+------+
|   15 |
+------+

////////////////////////
//系统变量名称：@@变量名/// 
//用户变量名称：@变量名 ///

5、变量的使用

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

1．定义变量

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

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

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

DECLARE  my_sql  INT  DEFAULT 10 ; //定义变量my_sql，数据类型为INT型，默认值为10

2．为变量赋值

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

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

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

SET my_sql = 30 ; //为变量my_sql赋值为30

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

SELECT col_name[,…] INTO var_name[,…]
FROM table_name WEHRE condition
其中，col_name参数表示查询的字段名称；var_name参数是变量的名称；table_name参数指表的名称；condition参数指查询条件。

SELECT d_id INTO my_sql  
   FROM employee WEHRE id=2 ; //从employee表中查询id为2的记录，将该记录的d_id值赋给变量my_sql

6、定义条件和处理程序

定义条件和处理程序是事先定义程序执行过程中可能遇到的问题。并且可以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题，并提出解决办法。这样可以增强程序处理问题的能力，避免程序异常停止。MySQL中都是通过DECLARE关键字来定义条件和处理程序。本小节中将详细讲解如何定义条件和处理程序。

1．定义条件

MySQL中可以使用DECLARE关键字来定义条件。其基本语法如下：

DECLARE  condition_name  CONDITION  FOR  condition_value  
condition_value:  
      SQLSTATE [VALUE] sqlstate_value | mysql_error_code

其中，condition_name参数表示条件的名称；condition_value参数表示条件的类型；sqlstate_value参数和mysql_error_code参数都可以表示MySQL的错误。例如ERROR 1146 (42S02)中，sqlstate_value值是42S02，mysql_error_code值是1146。

//定义"ERROR 1146 (42S02)"这个错误，名称为can_not_find
//方法一：使用sqlstate_value  
DECLARE  can_not_find  CONDITION  FOR  SQLSTATE  '42S02' ;  
//方法二：使用mysql_error_code  
DECLARE  can_not_find  CONDITION  FOR  1146 ;

2．定义处理程序

MySQL中可以使用DECLARE关键字来定义处理程序。其基本语法如下：

DECLARE handler_type HANDLER FOR 
condition_value[,...] sp_statement  
handler_type:  
    CONTINUE | EXIT | UNDO  
condition_value:  
    SQLSTATE [VALUE] sqlstate_value |
condition_name  | SQLWARNING  
       | NOT FOUND  | SQLEXCEPTION  | mysql_error_code

handler_type参数指明错误的处理方式，该参数有3个取值。这3个取值分别是CONTINUE、EXIT和UNDO。 CONTINUE表示遇到错误不进行处理，继续向下执行； EXIT表示遇到错误后马上退出； UNDO表示遇到错误后撤回之前的操作，MySQL中暂时还不支持这种处理方式

注意：通常情况下，执行过程中遇到错误应该立刻停止执行下面的语句，并且撤回前面的操作。但是，MySQL中现在还不能支持UNDO操作。因此，遇到错误时最好执行EXIT操作。如果事先能够预测错误类型，并且进行相应的处理，那么可以执行CONTINUE操作。

condition_value参数指明错误类型，该参数有6个取值。sqlstate_value和mysql_error_code与条件定义中的是同一个意思。condition_name是DECLARE定义的条件名称。SQLWARNING表示所有以01开头的sqlstate_value值。NOT FOUND表示所有以02开头的sqlstate_value值。SQLEXCEPTION表示所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值。sp_statement表示一些存储过程或函数的执行语句。

//定义处理程序的几种方式
//方法一：捕获sqlstate_value  
DECLARE CONTINUE HANDLER FOR SQLSTATE '42S02'
SET @info='CAN NOT FIND';  
//方法二：捕获mysql_error_code  
DECLARE CONTINUE HANDLER FOR 1146 SET @info='CAN NOT FIND';  
//方法三：先定义条件，然后调用  
DECLARE  can_not_find  CONDITION  FOR  1146 ;  
DECLARE CONTINUE HANDLER FOR can_not_find SET 
@info='CAN NOT FIND';  
//方法四：使用SQLWARNING  
DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR';  
//方法五：使用NOT FOUND  
DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND';  
//方法六：使用SQLEXCEPTION  
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR';

第一种方法是捕获sqlstate_value值。如果遇到sqlstate_value值为42S02，执行CONTINUE操作，并且输出”CANNOT FIND”信息。第二种方法是捕获mysql_error_code值。如果遇到mysql_error_code值为1146，执行CONTINUE操作，并且输出”CAN NOT FIND”信息。第三种方法是先定义条件，然后再调用条件。这里先定义can_not_find条件，遇到1146错误就执行CONTINUE操作。第四种方法是使用SQLWARNING。SQLWARNING捕获所有以01开头的sqlstate_value值，然后执行EXIT操作，并且输出”ERROR”信息。第五种方法是使用NOT FOUND。NOT FOUND捕获所有以02开头的sqlstate_value值，然后执行EXIT操作，并且输出”CAN NOT FIND”信息。第六种方法是使用SQLEXCEPTION。SQLEXCEPTION捕获所有没有被SQLWARNING或NOT FOUND捕获的sqlstate_value值，然后执行EXIT操作，并且输出”ERROR”信息。