存储过程学习小结

发表于 | 分类于

存储过程的概述

是在大型数据库系统中，一组为了完成特定功能的SQL语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译。外部程序可以直接调用数据库里面定义好的存储过程，另外数据库内部的触发器(trigger)、或者其他存储过程也可以调用它。

优点

1.性能上的提高，减少了交互频率，提高了重用性。
2.类似于接口的概念，提高了安全性。尽管无法直接访问，但可以通过存储过程进行处理。

缺点

1.存储过程会加大数据库占用的系统资源。
2.无法编写复杂逻辑的存储过程，不容易调试。
3.存储过程书写及维护难度都比较大。

变量

定义

1
DECLARE variable_name datatype(size) DEFAULT default_value;

多个变量可以这样定义：

1
DECLARE x, y INT DEFAULT 0 ;

变量的赋值

可以通过set或者select...into...来进行赋值。

1
2
SET x =10;
select count(*) into x from usertabl;

变量的scope(作用范围)

作用范围一般在begin和end之间。
还有一种是会话变量(session variable)，也叫做用户定义的变量(user defined variable)。这种变量要在变量名称前面加上“@”符号，作用域是全局。这个变量可以在被调用的存储过程或者代码之间共享数据。

参数

MySql的参数分为三类:IN、OUT、INOUT。

IN(默认)：这是个默认的类型，也就是如果参数没指定类型的话，那么默认就是IN类型了，这种参数主要是传递进去的值，这个值提供给存储过程使用，另外存储过程对其做的改变不会对传入的参数发生作用。

OUT：这个主要是存储过程要传递出去的值，也就是存储过程给你将它改变，并且传回去给调用它的程序。

INOUT：则把上面两个的特点合在一起了，即可以传递值给存储过程使用，同时存储过程也可以改变这个值在传给调用它的程序。

参数的定义

1
MODE param_name param_type(param_size)；

这里的MODE指的就是以上的三类。

逻辑判断和循环

存储过程中也有对应的条件判断，功能类似于if、switch。在MySQL里面对应的是IF和CASE。MySQL的存储过程中可使用的循环有三种：WHILE、REPEAT、LOOP。

IF

1
2
3
4
IF expression THEN commands  
   [ELSEIF expression THEN commands]  
   [ELSE commands]  
   END IF;

CASE

1
2
3
4
5
6
CASE  case_expression  
   WHEN when_expression THEN commands  
   WHEN when_expression THEN commands  
   ...  
   ELSE commands  
END CASE;

WHILE

1
2
3
WHILE expression DO  
   Statements  
END WHILE

REPEAT

类似于do...while...

1
2
3
4
REPEAT  
Statements;  
UNTIL expression  
END REPEAT

LOOP 及 LEAVE、ITERATE

这里LOOP用来标记循环；而LEAVE表示离开循环，好比编程里面的break一样；ITERATE则继续循环，类型与编程里面的continue。
例.输出小于10的偶数：

1
2
3
4
5
6
7
8
9
10
11
12
13
loop_label:  LOOP  
  IF  x > 10 THEN  
   LEAVE  loop_label;  
  END  IF;  
  SET  x = x + 1;  
  IF  (x mod 2) THEN  
   ITERATE  loop_label;  
  ELSE  
   SET  str = CONCAT(str,x,',');  
  END  IF;  
  
END LOOP;  
select str;

游标

游标的作用就是用于对查询数据库所返回的记录进行遍历，以便进行相应的操作。

游标属性

游标有下列属性：
a、游标是只读的，也就是不能更新它；
b、游标是不能滚动的，也就是只能在一个方向上进行遍历，不能在记录之间随意进退，不能跳过某些记录；
c、避免在已经打开游标的表上更新数据。

游标的使用

a.首先用DECLARE语句声明一个游标

1
DECLARE cursor_name CURSOR FOR SELECT_statement;

上面这条语句就给我们执行的select语句返回的记录指定了一个游标。

b.其次需要使用OPEN语句来打开上面你定义的游标

1
OPEN cursor_name;

c.接下来你可以用FETCH语句来获得下一行数据，并且游标也将移动到对应的记录上(这个就类似java里面的那个iterator)。

1
FETCH cursor_name INTO variable list;

d.然后最后当我们所需要进行的操作都结束后我们要把游标释放掉。

1
CLOSE cursor_name;

在使用游标时需要注意的是，使用定义一个针对NOT FOUND的条件处理函数(condition handler)来避免出现“no data to fetch”这样的错误，条件处理函数就是当某种条件产生时所执行的代码，这里但我们游标指到记录的末尾时，便达到NOT FOUND这样条件，这个时候我们希望继续进行后面的操作，所以我们会加CONTINUE。然后判断no_more_flag标志位来进行进一步的操作。

1
2
3
4
DECLARE  CONTINUE HANDLER 
	FOR NOT FOUND  
	SET  no_more_flag = 1;
	/*when "not found" occur,just continue*/

接下来会解释一下上面这个用法的意思。

条件处理 / 错误、异常处理

在游标里面我们有简要的介绍了一个NOT FOUND这个条件处理（错误、异常处理）的情况，条件处理涉及到两个语句，一个是DECLARE...CONDITION，另一个是DECLARE....HANDLER。

1、DECLARE….HANDLER语句

这个语句用于但数据库出现某种情况的时候(condition，大部分指发生某种错误时)，来定义具体的处理办法(handler)；所以这里涉及到包括：a、就是个什么情况 b、如何处理它；下面是其格式。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
DECLARE handler_action HANDLER  
    FOR condition_value [, condition_value] ...  #可以包括多种情况
    statement  
 #处理方法有： 
handler_action:  
    CONTINUE  
  | EXIT  
  | UNDO  
 #存在情况有：
condition_value:  
    mysql_error_code  # MySQL的错误代码
  | SQLSTATE [VALUE] sqlstate_value  # 一个5个字符组成的字符串的错误信息，同错误代码类似形成一一对应的关系 
  | condition_name  # 这个是条件名称，它使用DECLARE...CONDITION语句来定义
  | SQLWARNING  # 表示SQLTATE中的字符串以‘01’起始的那些错误
  | NOT FOUND  # 表示SQLTATE中的字符串以‘02’起始的那些错误
  | SQLEXCEPTION  # 表示SQLSTATE中的字符串不是以'00'、'01'、'02' 起始的那些错误，这里'00'起始的SQLSTATE其实表示的是成功执行而不是错误，另外两个就是上面的`NOT FOUND`和`SQLWARNING`的两种情况。

a.condition_value

上面的6种情况其实可以分为两类，一类就是比较明确的处理，就是对指定的错误情况进行处理，包括前三种方式；另一类是对对应类型的错误的处理，就是对某一群错误的处理，包括后三种方式另外还要注意的一个内容是MySQL在默认情况下(也就是我们没有定义处理错误的方法-handler)自己的错误处理机制：
1、对于SQLWARNING和NOT FOUND的处理方法就是无视错误继续执行，所以在游标的例子里面如果我们没有对repeat的条件判断的那个值做个no_more_products=1的handler来处理，那么循环就会一直下去。
2、对于SQLEXCEPTION的话，其默认的处理方法是在出现错误的地方就终止掉了。

b.statement

这个比较简单就是当出现某种条件/错误时，我们要执行的语句，可以是简单的如 SET var = value这样的简单的语句，也可以是复杂的多行的语句，多行的话可以使用BEGIN ….. END这里把语句包括在里面(这个好比delphi里面的情况，注意到我们的存储过程也是多行的，所以也要BEGIN …. END)。

c.handler_action

这个表示当执行完上面的statement后，希望执行怎样的动作，这里包括CONTINUE、EXIT、UNDO，表示继续、退出、撤销(暂时不支持)。
CONTINUE就是一个是SQLWARNING和NOT FOUND的默认处理方法，而EXIT就是SQLEXCEPTION的默认处理方法。

2、DECLARE…CONDITION语句

这个语句其实是为了让我们的错误条件更加的清晰明了化的。例如，SQLSTATE ‘23000’并不清晰，要通过相应的文档去对应 。
DECLARE....CONDITION可以对条件定义相对应的名称。我们可以这样写：

1
2
3
4
5
DECLARE duplicate_key CONDITION FOR SQLSTATE '23000';  
DECLARE CONTINUE HANDLER FOR duplicate_key  
  BEGIN  
    -- body of handler  
  END;

这样更加直观清晰。