1、SQL存储过程的基础知识------------------------------------------------------------
在深入理解MySq之前，我们先理下一些简单的问题
Q：什么是存储过程？（stored procedure）
A：是一段写好的SQL代码，特别的就是它是存在数据库的目录里。所以外部程序可以直接调用数据库里面定义好的存储过程，另外数据库内部的触发器(trigger)、或者其他存储过程也可以调用它。
Q：存储过程有什么好处？有什么坏处？
A：
先看看好处吧：
1、首先在性能上的提高，比起通过应用程序发送sql语句给数据库执行，让数据库自己内部执行存储过程效率更高、速度更快(存储过程将sql编译好后存在数据库目录下)；
2、存储过程还减少了应用程序同服务器之间的信息交互频率，可以想象在不是使用存储过程的情况，应用程序需要发送多条sql指令给服务器，而使用存储过程则只要一条调用存储过程的语句，然后获取需要的数据就ok了(一个存储过程中可以写N多的Sql语句)。
3、存储过程重用性比较高，并且是透明的，因为保存在数据库里面所以对任何应用来说都可以使用。新的应用只需要调用相应的存储过程就可以得到相应的数据服务。
4、存储过程也是种安全的做法，数据库管理员可以对那些没有权限访问数据库中的表格的应用，给他们使用存储过程的权限来获得数据服务，可以看到这个时候这些存储过程好像我们编程里面的”接口“这个概念。对于安全性要求很高的系统，例如银行，基本上常用的操作都是通过存储过程或者函数来进行的，这样完全对应用”隐藏“了表格。
当然也有坏处：
1、存储过程会使得数据库占用的系统资源加大(cpu、memory)，数据库毕竟主要用来做数据存取的，并不进行复杂的业务逻辑操作。
2、因为存储过程依旧是sql，所以没办法像编程语言那样写出复杂业务逻辑对应的存储过程。
3、存储过程不容易进行调试。
4、存储过程书写及维护难度都比较大。
了解这些优缺点对我们权衡使用存储过程有很大的帮助
看看如何创建一个存储过程。虽然通过命令行可以创建，但基本通过MySQL提供的Query browser来创建。
1、首先我们通过Administrator在test数据库中创建一个简单的表名叫”products“的，里面包括下面这两个字段：
create TABLE if not exists products(
id int(10),
name VARCHAR(45)
);
（使用navicat工具创建MySQL存储过程 http://ysj5125094.iteye.com/blog/2090315）
2、然后打开Query Browser，并且在test中创建存储过程，存储过程名称为”GetAll“。
3、可以看到系统会默认加入一些内容。这些内容当然可以不用理会，我们的存储过程就是从BEGIN到END中间的那些。点击Continue可以把存储进程加入到test数据库的目录中去。当然这里系统提供的代码也比较简单，一个就是重新定义存储过程代码的分隔符为”$$“，因为默认的是”；“而分号却是SQL的分隔符所以要重新定义一下分隔符，然后等存储过程写完后在把分隔符恢复为”；“。接着分隔符定义的下一句就是如果当前存在同名的进程，则进行删除。
4、在begin和end之间 写入一行select代码
delimiter $$
drop procedure if exists test.GetAll $$
create procedure test.GetAll()
BEGIN
#Routine body goes here...
select * from products;
END $$
delimiter ;
----------------------------------
delimiter $$
drop procedure if exists test.GetAllPjm $$
create procedure test.GetAllPjm()
BEGIN
#Routine body goes here...
select * from products;
END $$
delimiter ;
----------------------------------
5、在右边的窗口中双击GetAll这个存储进程，并且执行，则得到结果
----------------------------------
查询数据库中的存储过程
方法一：
select `name` from mysql.proc where db = 'your_db_name' and `type` = 'PROCEDURE'
方法二：
show procedure status;
查看存储过程或函数的创建代码
show create procedure proc_name;
show create function func_name;
删除存储过程 （GetAll 不能加 单引号''）
drop procedure GetAll ;
drop procedure test.GetAll ;
3、变量---------------------------------------------------------------------------------------------------------------------
1、变量的定义
在Mysql里面可以像我们写代码中一样定义变量来保持中间结果，看下面的格式：
- DECLARE variable_name datatype(size) DEFAULT default_value;
DECLARE相当于关键字，有点类似var，表示定义一个变量；然后variable_name是你指定的变量名称；datatype表示数据类型而括号内的size表示对应类型的格式比如varchar(50)这样子，这里需要注意的是datatype只能是MySql支持的那些基本数据类型；DEFAULT关键字指明了变量的默认值为default_value。
定义多个相同类型的变量可以这样子：
- DECLARE x, y INT DEFAULT 0
2、变量的赋值
同程序里面一样，定义了变量，我们也需要在程序中对他赋值，下面是示例代码：
<strong>DECLARE total_count INT DEFAULT 0
SET total_count = 10;</strong>
当然由于存储过程的特殊性(用于数据库的数据操作)，所以除了SET这种赋值方法，还可以通过SELECT INTO语句将返回的值赋给变量：
DECLARE total_products INT DEFAULT 0；
SELECT COUNT(*) INTO total_products FROM products；
看到这里把products的记录数赋给了total_products这个变量。所以如果上面我们表格内是3个记录的话，这里total_products就是等于3了
3、变量的Scope(作用范围)
变量的作用范围同编程里面类似，在这里一般是在对应的begin和end之间。在end之后这个变量就没有作用了，不能使用了。这个同编程一样。
另外有种变量叫做会话变量(session variable)，也叫做用户定义的变量(user defined variable)。这种变量要在变量名称前面加上“@”符号，叫做会话变量，代表整个会话过程他都是有作用的，这个有点类似于全局变量一样。这种变量用途比较广，因为只要在一个会话内(就是某个应用的一个连接过程中)，这个变量可以在被调用的存储过程或者代码之间共享数据。下面看个简单的例子，以理解这种变量和本地变量的区别：
首先修改下之前的存储过程如下：
delimiter $$
drop procedure if exists test.GetAll $$
create procedure test.GetAll()
BEGIN
DECLARE t1 int DEFAULT 1;
set t1 = t1+1;
set @t2 =@t2+1;
SELECT t1,@t2;
END $$
delimiter ;
这里定义了一个是local的变量，一个是session的变量，对于session的变量，不需要定义，mysql根据你赋给该变量的值来确定类型(更详细的需要另一篇文章来解释)。
然后我们在Query browser上面先执行这样一条语句来对@t2进行赋初值：
set @t2 =1;
赋予往初值后我们调用我们的存储进程。每次执行这个存储进程我们发现t1的值是不变的，而@t2每次会增加1，因为我们这是在一个会话里面所以@t2就一直作用着，每次自己增加1.
CALL GetAll();
需要注意的是 需要在同一个会话中才会有效。如果在Navicat中新建一个查询 只执行call getAll() 那么此次会话没有执行对@t2的赋值操作。故查不到结果。
4、参数--------------------------------------------------------------------------------------------------------------------
1、参数分类
参数，同编程一样，MySql中存储过程也可以带参数，前面的例子中我们没有使用参数，在下面的例子中我们将使用到参数；MySql的参数分为三类:IN、OUT、INOUT，即使是字面上这三种类型的参数也很好理解：
——IN：这是个默认的类型，也就是如果参数没指定类型的话，那么默认就是IN类型了，这种参数主要是传递进去的值，这个值提供给存储过程使用，另外存储过程对其做的改变不会对传入的参数发生作用。
——OUT：这个主要是存储过程要传递出去的值，也就是存储过程给你将它改变，并且传回去给调用它的程序。
——INOUT：则把上面两个的特点合在一起了，即可以传递值给存储过程使用，同时存储过程也可以改变这个值在传给调用它的程序。
2、参数定义
参数的定义如下：
MODE param_name param_type(param_size)；
这里MODE可以是IN、OUT、INOUT；param_name就是参数名称；param_type则是类型。当然要注意的是名字不能和表格里的字段重名。
看看下面的使用例子：
delimiter $$
drop procedure if exists test.GetAll $$
create procedure test.GetAll(in containString varchar(255))
BEGIN
#Routine body goes here...
select * from products where name like CONCAT('%',containString,'%');
END $$
delimiter ;这个例子里面，我们对存储过程传入了一个参数，这里参数是个字符串，我们希望通过这个存储过程来列出products表格中name字段中包含有传入的字符串的记录。这里数据主要用途就是传给存储过程使用，所以使用IN。下面是对应的执行结果：
call GetAll('ca')注意到传递给了GetAll这个存储过程一个数据为“ca”的参数 （ call GetAll(“ca”) 亦可 ）
在这个基础上我们把记录数作为存储过程输出给外部的数据来看一下OUT的使用。
delimiter $$
drop procedure if exists test.GetAll $$
create procedure test.GetAll(in containString varchar(255),OUT totalnum INT)
BEGIN
select count(name) into totalnum
from products
where name like CONCAT('%',containString,'%');
END $$
delimiter ;上面把记录数保持到输出参数 totalNum中。
CALL test.GetAll('p',@totalRecords);
select @totalRecords;这里使用一个会话变量作为输出参数，存储过程处理后，保持记录速到@totalRecords中。然后我们运行select @totalRecords，输出为2。
5、逻辑判断，条件控制---------------------------------------------------------------------------------------------------------------------
同编写程序类似，存储过程中也有对应的条件判断，功能类似于if、switch。在MySql里面对应的是IF和CASE
1、IF判断
IF判断的格式是这样的：
- IF expression THEN commands
- [ELSEIF expression THEN commands]
- [ELSE commands]
- END IF;
create table if not exists products2(
id int,
name VARCHAR(50),
category VARCHAR(50),
price FLOAT
);
INSERT into products2 VALUE
(1,'cake','dessert',10.00),
(2,'cheese','dairy',12.50),
(3,'cookie','dessert',50.00),
(4,'carpet','furture',100.00),
(5,'couch','furture',1250.00);然后下面是我们的存储过程：
delimiter $$
drop procedure if exists test.getPrice $$
CREATE PROCEDURE test.getPrice(in isTaxed boolean, in proname varchar(255))
BEGIN
DECLARE finalPrice NUMERIC(10,2) DEFAULT 0;
DECLARE taxRate NUMERIC(10,2) DEFAULT 1.07;
SELECT price into finalPrice
from test.products2
WHERE name = proname;
if isTaxed THEN
set finalPrice = finalPrice * taxRate;
end IF;
SELECT finalPrice;
END $$
delimiter ;存储过程的话有两个输入参数，第一个isTaxed表示是不是要带税价格，第二个是产品的名称；
在存储过程里面定义了两个变量，finalPrice用来保存价格，而taxRate表示税率。
这里代码比较简单，就是判断下如果是要加税，就把原来的价格乘上税率。下面是测试结果：
call getPrice(FALSE,'couch'); 1250
call getPrice(True,'couch'); 1337.5
true的情况表示是带税的价格。
2、CASE的使用
同编程里面的那个switch ....case.....类似，使用CASE同编程一样也是当判断比较多时便于阅读和维护，我们也来看下CASE的语法：
CASE case_expression
WHEN when_expression THEN commands
WHEN when_expression THEN commands
...
ELSE commands
END CASE;
b、然后WHEN则类似编程里面的case，when_expression类似case后面跟着的值，commands则对应于相应的case下执行的命令；
c、最后一个 ELSE 则类似于default，就是如果都没在上面那些WHEN里面的情况时执行的命令。
下面我们假设不同类型的商品对应的税收税率是不一样的情况来看个例子，这里假设甜点的税率是0.05，奶制品的是0.1，家具类的是0.2:，
下面是存储过程：
上面增加了一个保存产品类型的一个变量proType，用来保存商品类型。然后使用CASE来进行判断来设置税率，下面是测试的部分：
可以看到沙发的价格是1250*1.2=1500，而蛋糕的价格是10*1.05 = 10.5
6、循环---------------------------------------------------------------------------------------------------------------------
在MySql的存储过程中可使用的循环有三种：WHILE、REPEAT、LOOP
1、WHILE
WHILE的格式是这样的：
- WHILE expression DO
- Statements
- END WHILE
- DELIMITER $$
- DROP PROCEDURE IF EXISTS `test`.`WhileLoopProc` $$
- CREATE PROCEDURE `test`.`WhileLoopProc` ()
- BEGIN
- DECLARE x INT;
- DECLARE str VARCHAR(255);
- SET x = 1;
- SET str = '';
- WHILE x <= 5 DO
- SET str = CONCAT(str,x,',');
- SET x = x + 1;
- END WHILE;
- SELECT str;
- END $$
- DELIMITER ;
2、REPEAT
REPEAT的格式是这样的：
- REPEAT
- Statements;
- UNTIL expression
- END REPEAT
mysql> create procedure pro11()
Query OK, 0 rows affected (0.00 sec)
mysql> call pro11()//
Query OK, 1 row affected (0.00 sec) #虽然在这里显示只有一行数据受到影响，但是下面选择数据的话，还是插入了5行数据。
mysql> select * from t1//
+——-+
| filed |
+——-+
|
|
|
|
|
+——-+
5 rows in set (0.00 sec)
一行就是执行结果，实际的作用和使用while编写的存储过程一样，都是插入5行数据。
3、LOOP 及 LEAVE、ITERATE
这里LOOP用来标记循环；而LEAVE表示离开循环，好比编程里面的break一样；ITERATE则继续循环，类型与编程里面的continue。
- DELIMITER $$
- DROP PROCEDURE IF EXISTS `test`.`LoopProc` $$
- CREATE PROCEDURE `test`.`LoopProc` ()
- BEGIN
- DECLARE x INT;
- DECLARE str VARCHAR(255);
- SET x = 1;
- SET str = '';
- loop_label: LOOP
- IF x > 10 THEN
- LEAVE loop_label;
- END IF;
- SET x = x + 1;
- IF (x mod 2) THEN // 0 false 1 true
- ITERATE loop_label;
- ELSE
- SET str = CONCAT(str,x,',');
- END IF;
- END LOOP;
- SELECT str;
- END $$
- DELIMITER ;
7、游标(Cursor)---------------------------------------------------------------------------------------------------------------------
1、游标的作用及属性
游标的作用就是用于对查询数据库所返回的记录进行遍历，以便进行相应的操作；游标有下面这些属性：
a、游标是只读的，也就是不能更新它；
b、游标是不能滚动的，也就是只能在一个方向上进行遍历，不能在记录之间随意进退，不能跳过某些记录；
c、避免在已经打开游标的表上更新数据。
2、如何使用游标
使用游标需要遵循下面步骤：
a、首先用DECLARE语句声明一个游标
- DECLARE cursor_name CURSOR FOR SELECT_statement;
b、其次需要使用OPEN语句来打开上面你定义的游标
- OPEN cursor_name;
- FETCH cursor_name INTO variable list;
- CLOSE cursor_name;
下面的游标使用演示获取库存量小于100的产品的代码code，这个代码纯粹演示如何使用，在这里没有其他任何意义:)
- DELIMITER $$
- DROP PROCEDURE IF EXISTS `test`.`CursorProc` $$
- CREATE PROCEDURE `test`.`CursorProc` ()
- BEGIN
- DECLARE no_more_products, quantity_in_stock INT DEFAULT 0;
- DECLARE prd_code VARCHAR(255);
- DECLARE cur_product CURSOR FOR SELECT code FROM products;
- /*First: Delcare a cursor,首先这里对游标进行定义*/
- DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_products = 1;
- /*when "not found" occur,just continue,这个是个条件处理,针对NOT FOUND的条件*/
- /* for loggging information 创建个临时表格来保持*/
- CREATE TEMPORARY TABLE infologs (
- Id int(11) NOT NULL AUTO_INCREMENT,
- Msg varchar(255) NOT NULL,
- PRIMARY KEY (Id)
- );
- OPEN cur_product;
- /*Second: Open the cursor 接着使用OPEN打开游标*/
- FETCH cur_product INTO prd_code;
- /*Third:now you can Fetch the row 把第一行数据写入变量中,游标也随之指向了记录的第一行*/
- REPEAT
- SELECT quantity INTO quantity_in_stock
- FROM products
- WHERE code = prd_code;
- IF quantity_in_stock < 100 THEN
- INSERT INTO infologs(msg)
- VALUES (prd_code);
- END IF;
- FETCH cur_product INTO prd_code;
- UNTIL no_more_products = 1
- END REPEAT;
- CLOSE cur_product;
- /*Finally: cursor need be closed 用完后记得用CLOSE把资源释放掉*/
- SELECT * FROM infologs;
- DROP TABLE infologs;
- END $$
- DELIMITER ;
8、条件处理(Condition Handling) / 错误、异常处理-------------------------------------------------------------------------------------------
在游标里面我们有简要的介绍了一个NOT FOUND这个条件处理（错误、异常处理）的情况，条件处理涉及到两个语句，
一个是DECLARE...CONDITION，另一个是DECLARE....HANDLER。在游标的例子中是使用了一个DECLARE....HANDLER。先来看看DECLARE....HANDLER。
1、DECLARE....HANDLER语句
这个语句用于但数据库出现某种情况的时候(condition，大部分指发生某种错误时)，来定义具体的处理办法(handler)；所以这里涉及到包括：
a、就是个什么情况 b、如何处理它。
- DECLARE handler_action HANDLER FOR condition_value [, condition_value] ...
- statement
- handler_action:
- CONTINUE
- | EXIT
- | UNDO
- condition_value:
- mysql_error_code
- | SQLSTATE [VALUE] sqlstate_value
- | condition_name
- | SQLWARNING
- | NOT FOUND
- | SQLEXCEPTION
a、condition_value [,condition_value]，这个的话说明可以包括多种情况(方括弧表示可选的)，也就是一个handler可以定义成针对多种情况进行相应的操作；另外condition_value可以包括的值有上面列出来的6种：
1、mysql_error_code，这个表示mysql的错误代码，错误代码是一个数字，完成是由mysql自己定义的，这个值可以参考mysql数据库错误代码及信息。
2、SQLSTATE [VALUE] sqlstate_value，这个同错误代码类似形成一一对应的关系，它是一个5个字符组成的字符串，关键的地方是它从ANSI SQL和ODBC这些标准中引用过来的，因此更加标准化，而不像上面的error_code完全是mysql自己定义给自己用的，这个和第一个类似也可以参考mysql数据库错误代码及信息。
3、condtion_name，这个是条件名称，它使用DECLARE...CONDITION语句来定义，这个后面我们会介绍如何定义自己的condition_name。
4、SQLWARNING，表示SQLTATE中的字符串以‘01’起始的那些错误，比如Error: 1311 SQLSTATE: 01000(ER_SP_UNINIT_VAR)
5、NOT FOUND，表示SQLTATE中的字符串以‘02’起始的那些错误，比如Error: 1329 SQLSTATE: 02000(ER_SP_FETCH_NO_DATA)，其实这个错误就是用在我们介绍游标的那个问题所出现的情况，也就是没有fetch到记录，也就是我们游标到记录的尾巴了的情况。
6、SQLEXCEPTION，表示SQLSTATE中的字符串不是以'00'、'01'、'02' 起始的那些错误，这里'00'起始的SQLSTATE其实表示的是成功执行而不是错误，另外两个就是上面的4和5的两种情况。
上面的6种情况其实可以分为两类，一类就是比较明确的处理，就是对指定的错误情况进行处理，包括1、2、3这三种方式；另一类是对对应类型的错误的处理，就是对某一群错误的处理，包括4、5、6这三种方式。这个是介绍了condition_value。另外还要注意的一个内容是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的默认处理方法。
来看个简单的例子，这里创建一个对SQLSTATE的代码为'23000'的错误(重复的主键)进行处理的HANDLER，每次发生时我们对变量@x进行增加1：
- DELIMITER $$
- DROP PROCEDURE IF EXISTS `test`.`ConditionProc` $$
- CREATE PROCEDURE `test`.`ConditionProc` ()
- BEGIN
- DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x = @x+1;
- INSERT INTO products values(1,default,default,default,default,default);
- INSERT INTO products values(1,default,default,default,default,default);
- SELECT @x;
- END $$
- DELIMITER ;
测试比较简单，这里我们数据库里面本身就有记录了，然后在Qurey browser中先输入
SET @x = 0;
然后调用存储过程下面是结果：
通过结果我们知道出现了两次插入的记录同原有的记录出现主键重复的情况。当然这个是由下面这个代码触发的。
INSERT INTO products values(1,default,default,default,default,default);
2、DECLARE...CONDITION语句
这个语句其实是为了让我们的错误条件更加的清晰明了化的，对于上面的情况，像SQLSTATE '23000'这种表示是一种很不直观的方法，要通过相应的文档去对应，阅读起来比较不方便。而DECLARE....CONDITION可以对条件定义相对应的名称，看个例子就清楚了：
- DECLARE duplicate_key CONDITION FOR SQLSTATE '23000';
- DECLARE CONTINUE HANDLER FOR duplicate_key
- BEGIN
- -- body of handler
- END;
---------------------------------------------------------------------------------------------------------------------
form：http://blog.csdn.net/rdarda/article/details/7874910
---------------------------------------------------------------------------------------------------------------------
3万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
