MYSQL的存储过程和函数简单写法(2)-木庄网络博客

当前第2页返回上一页

　　定义条件和处理程序是事先定义程序执行过程中可能遇到的问题。并且可以在处理程序中定义解决这些问题的办法。这种方式可以提前预测可能出现的问题，并提出解决办法。这样可以增强程序处理问题的能力，避免程序异常停止。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。

【示例6】下面定义"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表示一些存储过程或函数的执行语句。

【示例7】下面是定义处理程序的几种方式。代码如下：

//方法一：捕获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';

上述代码是6种定义处理程序的方法。

　　第一种方法是捕获sqlstate_value值。如果遇到sqlstate_value值为42S02，执行CONTINUE操作，并且输出"CAN NOT 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"信息。

MySQL存储过程写法总结

1、创建无参存储过程。

create procedure product()
begin
    select * from user;
end;

一条简单的存储过程创建语句，此时调用的语句为：

call procedure();

##注意，如果是在命令行下编写的话，这样的写法会出现语法错误，即再select 那一句结束
mysql就会进行解释了，此时应该先把结尾符换一下：

delimiter //
create procedure product()
begin
    select * from user;
end //

最后再换回来

delimiter ;

2、创建有参存储过程

有参的存储包括两种参数，
一个是传入参数;
一个是传出参数;
例如一个存储过程：

create procedure procedure2(
out p1 decimal(8,2),
out p2 decimal(8,2),
in p3 int
)
begin
select sum(uid) into p1 from user where order_name = p3;
select avg(uid) into p2 from user ;
end ;

从上面sql语句可以看出，p1和p2是用来检索并且传出去的值，而p3则是必须有调用这传入的具体值。
看具体调用过程：

call product(); //无参
call procedure2(@userSum,@userAvg,201708); //有参

当用完后，可以直接查询userSum和userAvg的值：

select @userSum, @userAvg;

结果如下：

+----------+----------+
| @userSum | @userAvg |
+----------+----------+
| 67.00 | 6.09 |
+----------+----------+
1 row in set (0.00 sec)

3、删除存储过程

一条语句： drop procedure product; //没有括号后面

4、一段完整的存储过程实例：

-- Name: drdertotal 
-- Parameters : onumber = order number 
--       taxable = 0 if not taxable,1if taxable 
--       ototal = order total variable 
 
create procedure ordertotal( 
in onumber int, 
in taxable boolean, 
out ototal decimal(8,2) 
) commit 'Obtain order total, optionally adding tax'  
begin 
  -- Declare variable for total 
  declare total decimal(8,2); 
  -- Declare tax percentage 
  declare taxrate int default 6; 
   
  --Get the order total 
  select Sum(item_price*quantity) 
  from orderitems 
  where order_num = onumber 
  into total; 
   
  --Is this taxable? 
  if taxable then 
    --Yes, so add taxrate to the total 
    select total+(total/100*taxrate) into total; 
  end if; 
   
  --Add finally, save to out variable 
  select total into ototal; 
end;

上面存储过程类似于高级语言的业务处理，看懂还是不难的，注意写法细节

commit关键字：它不是必需的，但如果给出，将在show procedure status的结果中给出。

if语句：这个例子给出了mysqlif语句的基本用法，if语句还支持elseif和else子句。

通过show procedure status可以列出所有的存储过程的详细列表，并且可以在后面加一个

like+指定过滤模式来进行过滤。

更多相关Mysql内容来自木庄网络博客

标签：Mysql

返回前面的内容