mysql存储过程procedure 触发器trigger 游标cusor 控制语句（条件，循环）

最新推荐文章于 2024-01-16 15:36:41 发布

Meiko丶

最新推荐文章于 2024-01-16 15:36:41 发布

阅读量943

点赞数 1

分类专栏： MySQL

MySQL 专栏收录该内容

46 篇文章 2 订阅

订阅专栏

什么是mysql存储例程?
存储例程是存储在数据库服务器中的一组sql语句，通过在查询中调用一个指定的名称来执行这些sql语句命令.

为什么要使用mysql存储过程？
我们都知道应用程序分为两种，一种是基于web，一种是基于桌面，他们都和数据库进行交互来完成数据的存取工作。假设现在有一种应用程序包含了这两种，现在要修改其中的一个查询sql语句，那么我们可能要同时修改他们中对应的查询sql语句，当我们的应用程序很庞大很复杂的时候问题就出现这，不易维护！另外把sql查询语句放在我们的web程序或桌面中很容易遭到sql注入的破坏。而存储例程正好可以帮我们解决这些问题。
存储过程(stored procedure)、存储例程(store routine)、存储函数区别
Mysql存储例程实际包含了存储过程和存储函数，它们被统称为存储例程。
其中存储过程主要完成在获取记录或插入记录或更新记录或删除记录，即完成select insert delete update等的工作。而存储函数只完成查询的工作，可接受输入参数并返回一个结果。

创建mysql存储过程、存储函数
create procedure 存储过程名(参数)
存储过程体
create function 存储函数名(参数)

下面是存储过程的例子：

mysql> DELIMITER // 
mysql> CREATE PROCEDURE proc1(OUT s int) 
   -> BEGIN
   -> SELECT COUNT(*) INTO s FROM user; 
   -> END
   -> // 
mysql> DELIMITER ;

1）这里需要注意的是DELIMITER//和DELIMITER;两句，DELIMITER是分割符的意思，因为MySQL默认以”;”为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将”;”当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。

（2）存储过程根据需要可能会有输入、输出、输入输出参数，这里有一个输出参数s，类型是int型，如果有多个参数用”,”分割开。

（3）过程体的开始与结束使用BEGIN与END进行标识。

参数

MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：
CREATEPROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名数据类形…])
IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
OUT 输出参数:该值可在存储过程内部被改变，并可返回
INOUT 输入输出参数:调用时指定，并且可被改变和返回

. IN参数例子

创建:

1.  mysql > DELIMITER //  
2.  mysql > CREATE PROCEDURE demo_in_parameter(IN p_in int)  
3.       -> BEGIN   
4.       -> SELECT p_in;   
5.       -> SET p_in=2;   
6.       -> SELECT p_in;   
7.       -> END;   
8.       -> //  
9.  mysql > DELIMITER ;

执行结果:

1.  mysql > SET @p_in=1;  
2.  mysql > CALL demo_in_parameter(@p_in);  
3.  +------+  
4.  | p_in |  
5.  +------+  
6.  |   1  |   
7.  +------+  
8.   
9.  +------+  
10.| p_in |  
11.+------+  
12.|   2  |   
13.+------+  
14. 
15.mysql> SELECT @p_in;  
16.+-------+  
17.| @p_in |  
18.+-------+  
19.|  1    |  
20.+-------+

以上可以看出，p_in虽然在存储过程中被修改，但并不影响@p_id的值

.OUT参数例子

创建:

1.  mysql > DELIMITER //  
2.  mysql > CREATE PROCEDURE demo_out_parameter(OUT p_out int)  
3.       -> BEGIN 
4.       -> SELECT p_out;  
5.       -> SET p_out=2;  
6.       -> SELECT p_out;  
7.       -> END;  
8.       -> //  
9.  mysql > DELIMITER ;

执行结果:

1.  mysql > SET @p_out=1;  
2.  mysql > CALL sp_demo_out_parameter(@p_out);  
3.  +-------+  
4.  | p_out |   
5.  +-------+  
6.  | NULL  |   
7.  +-------+  
8.   
9.  +-------+  
10.| p_out |  
11.+-------+  
12.|   2   |   
13.+-------+  
14. 
15.mysql> SELECT @p_out;  
16.+-------+  
17.| p_out |  
18.+-------+  
19.|   2   |  
20.+-------+

INOUT参数例子

创建:

1.  mysql > DELIMITER //   
2.  mysql > CREATE PROCEDURE demo_inout_parameter(INOUT p_inout int)   
3.       -> BEGIN 
4.       -> SELECT p_inout;  
5.       -> SET p_inout=2;  
6.       -> SELECT p_inout;   
7.       -> END;  
8.       -> //   
9.  mysql > DELIMITER ;

执行结果:

1.  mysql > SET @p_inout=1;  
2.  mysql > CALL demo_inout_parameter(@p_inout) ;  
3.  +---------+  
4.  | p_inout |  
5.  +---------+  
6.  |    1    |  
7.  +---------+  
8.   
9.  +---------+  
10.| p_inout |   
11.+---------+  
12.|    2    |  
13.+---------+  
14. 
15.mysql > SELECT @p_inout;  
16.+----------+  
17.| @p_inout |   
18.+----------+  
19.|    2     |  
20.+----------+

在存储过程中可以定义局部变量，还可以定义用户变量（相当于全局变量）。局部变量在存储过程中直接使用declare声明，作用域在begin和end之间，全局变量的话用@声明，在整个会话中可用。

触发器

 MySQL5版本后支持触发器
    只有表支持触发器，视图不支持触发器
    MySQL语句在需要的时被执行，存储过程也是如此，但是如果你想要某条语句（或某些语句）在事件发生时自动执行，那该怎么办呢：例如：
        1 每增加一个顾客到某个数据库表时，都检查其电话号码格式是否正确，区的缩写是否为大写
        2 每当订购一个产品时，都从库存数量中减少订购的数量
        3 无论何时删除一行，都在某个存档中保留一个副本
    这写例子的共同之处是他们都需要在某个表发生更改时自动处理。这就是触发器。触发器是MySQL响应一下任意语句而自动执行的一条MySQL语句
（或位于BEGIN和END语句之间的一组语句）
    1 DELETE
    2 INSERT
    3 UPDATE
    其他的MySQL语句不支持触发器
    创建触发器
        创建触发器需要给出4条信息
        1 唯一的触发器名；  //保存每个数据库中的触发器名唯一
        2 触发器关联的表；
        3 触发器应该响应的活动（DELETE、INSERT或UPDATE）
        4 触发器何时执行（处理前还是后,前是BEFORE 后是AFTER）
        创建触发器用CREATE TRIGGER
        CREATE TRIGGER newproduct AFTER INSERT ON products
        FOR EACH ROW SELECT'Product added'
       创建新触发器newproduct ，它将在INSERT语句成功执行后执行。这个触发器还镇定FOR EACH ROW,因此代码对每个插入的行执行。
这个例子作用是文本对每个插入的行显示一次product added
        FOR EACH ROW 针对每个行都有作用，避免了INSERT一次插入多条语句
    触发器定义规则
        触发器按每个表每个事件每次地定义，每个表每个事件每次只允许定义一个触发器，因此，每个表最多定义6个触发器
（每条INSERT UPDATE 和DELETE的之前和之后）。单个触发器不能与多个事件或多个表关联，所以，如果你需要一个对INSERT 和UPDATE存储执行的触发器，
则应该定义两个触发器
    触发器失败  如果BEFORE(之前)触发器失败，则MySQL将不执行SQL语句的请求操作，此外，如果BEFORE触发器或语句本身失败，
MySQL将不执行AFTER(之后)触发器
    删除触发器
        DROP TRIGGER newproduct；
        触发器不能更新或覆盖，所以修改触发器只能先删除再创建
    使用触发器
        我们来看看每种触发器以及它们的差别
    INSERT 触发器
        INSERT触发器在INSERT语句执行之前或之后执行。需要知道以下几点：
    1 在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行
    2 在BEFORE INSERT触发器中，NEW中的值也可以被更新（允许更改插入的值）
    3 对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值
        提示：通常BEFORE用于数据验证和净化（目的是保证插入表中的数据确实是需要的数据）。本提示也适用于UPDATE触发器
    DELETE 触发器
        DELETE触发器在语句执行之前还是之后执行，需要知道以下几点：
    1 在DELETE触发器代码内，你可以引用一个名为OLD的虚拟表，访问被删除的行；
    2 OLD中的值全部是只读的，不能更新
        例子演示适用OLD保存将要除的行到一个存档表中
        CREATE TRIGGERdeleteorder BEFORE DELETE ON orders
        FOR EACH ROW
        BEGIN  
        INSERT INTO archive_orders(order_num , order_date , cust_id)
        VALUES(OLD.order_num , OLD.order_date , OLD.cust_id);
        END;
        //此处的BEGIN  END块是非必需的，可以没有
    在任何订单删除之前执行这个触发器，它适用一条INSERT语句将OLD中的值（将要删除的值）保存到一个名为archive_orders的存档表中
    BEFORE DELETE触发器的优点是（相对于AFTER DELETE触发器），如果由于某种原因，订单不能被存档，DELETE本身将被放弃执行。
    多语言触发器  正如上面所见，触发器deleteorder 使用了BEGIN和END语句标记触发器体。这在此例中并不是必需的，不过也没有害处。
使用BEGIN  END块的好处是触发器能容纳多条SQL语句。
    UPDATE触发器
        UPDATE触发器在语句执行之前还是之后执行，需要知道以下几点：
        1 在UPDATE触发器代码中，你可以引用一个名为OLD的虚拟表访问（UPDATE语句前）的值，引用一名为NEW的虚拟表访问新更新的值
        2 在BEFORE UPDATE触发器中，NEW中的值可能被更新，（允许更改将要用于UPDATE语句中的值）
        3 OLD中的值全都是只读的，不能更新
            例子：保证州名的缩写总是大写（不管UPDATE语句给出的是大写还是小写）
            CREATE TRIGGER updatevendor BEFORE UPDATE ON vendores FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state)

游标

游标是啥玩意？
简单的说：游标（cursor）就是游动的标识，啥意思呢，通俗的这么说，一条sql取出对应n条结果资源的接口/句柄，就是游标，沿着游标可以一次取出一行。我给大家准备一张图：

游标缺点

游标的缺点是针对有点而言的，也就是只能一行一行操作，在数据量大的情况下，是不适用的，速度过慢。这里有个比喻就是：当你去ATM存钱是希望一次性存完呢，还是100一张一张的存，这里的100一张一张存就是游标针对行的操作。数据库大部分是面对集合的，业务会比较复杂，而游标使用会有死锁，影响其他的业务操作，不可取。当数据量大时，使用游标会造成内存不足现象。

2.怎么使用游标？
//1.声明/定义一个游标
declare 声明;declare 游标名 cursor for select_statement;
//2.打开一个游标
open 打开;open 游标名
//3.取值
fetch 取值;fetch 游标名 into var1,var2[,...]
//4.关闭一个游标
close 关闭;close 游标名;

3.游标实战
未使用游标：
create procedure p1()
begin
select * from category;
end$

call p1$
执行结果：

create procedure p2()
begin
declare row_cat_id int;
declare row_cat_name varchar(90);
declare row_parent_id int;
declare getcategory cursor for select cat_id,cat_name,parent_id from category;
open getcategory;
fetch getcategory into row_cat_id,row_cat_name,row_parent_id;
select row_cat_id,row_cat_name,row_parent_id;
close getcategory;
end$
call p2()$
执行结果如下：

这时候你会发现我们只得到了一个查询结果，想要都显示，我们需要自己控制

给游标定义一个越界的标识
//在mysql游标(cursor)中，可以定义continue handler来操作一个越界标识,使用语法：declare continue handler for NOT FOUND statemet(当没数据的时候要执行的语句)

//这句话的意思是说，我要声明一个句柄事件，你往后取，一旦发生NOT FOUND 事件就会出发set ergodic:=0这个语句
create procedure p5()
begin
declare row_cat_id int;
declare row_cat_name varchar(90);
declare row_parent_id int;
declare ergodic int default 1;//声明一个变量表明还有数据可遍历
declare getcategory cursor for select cat_id,cat_name,parent_id from category;
declare continue handler for NOT FOUND set ergodic:=0;
open getcategory;
repeat
fetch getcategory into row_cat_id,row_cat_name,row_parent_id;
select row_cat_id,row_cat_name,row_parent_id;
until ergodic=0 end repeat;
close getcategory;
end$
call p5()$
执行结果为：

发现问题没有？为啥第最后一个查了两次？这是什么原因？我们不妨来分析一下我们写的语句：

既然问题已经分析出来后，我们如何处理这个问题呢？
解决方案：声明处理的hanlder不再是continue，而是exit即可达到目的。即：declare exit handler for NOT FOUND set ergodic:=0;
//exit与continue的区别是：exit触发后，后面的语句不再执行，而continue还需要继续执行。
注意：除了这exit与continue两种方式外，还有一种方式就是undo handler。
//采用undo handler方式触发后，前面的语句直接撤销。【但目前好像这种方式，mysql还不支持】
create procedure p6()
begin
declare row_cat_id int;
declare row_cat_name varchar(90);
declare row_parent_id int;
declare ergodic int default 1;
declare getcategory cursor for select cat_id,cat_name,parent_id from category;
declare exit handler for NOT FOUND set ergodic:=0;
open getcategory;
repeat
fetch getcategory into row_cat_id,row_cat_name,row_parent_id;
select row_cat_id,row_cat_name,row_parent_id;
until ergodic=0 end repeat;
close getcategory;
end$
call p6()$
执行结果为：

由此，问题解决。

控制语句

条件语句

if-then -else语句

1.  mysql > DELIMITER //  
2.  mysql > CREATE PROCEDURE proc2(IN parameter int)  
3.       -> begin 
4.       -> declare var int;  
5.       -> set var=parameter+1;  
6.       -> if var=0 then 
7.       -> insert into t values(17);  
8.       -> end if;  
9.       -> if parameter=0 then 
10.     -> update t set s1=s1+1;  
11.     -> else 
12.     -> update t set s1=s1+2;  
13.     -> end if;  
14.     -> end;  
15.     -> //  
16.mysql > DELIMITER ;

case语句：

1.  mysql > DELIMITER //  
2.  mysql > CREATE PROCEDURE proc3 (in parameter int)  
3.       -> begin 
4.       -> declare var int;  
5.       -> set var=parameter+1;  
6.       -> case var  
7.       -> when 0 then   
8.       -> insert into t values(17);  
9.       -> when 1 then   
10.     -> insert into t values(18);  
11.     -> else   
12.     -> insert into t values(19);  
13.     -> end case;  
14.     -> end;  
15.     -> //  
16.mysql > DELIMITER ; 
case
        when var=0 then
               insert into t values(30);
        when var>0 then
        when var<0 then
        else

end case

循环语句

while ···· end while：

1.  mysql > DELIMITER //  
2.  mysql > CREATE PROCEDURE proc4()  
3.       -> begin 
4.       -> declare var int;  
5.       -> set var=0;  
6.       -> while var<6 do  
7.       -> insert into t values(var);  
8.       -> set var=var+1;  
9.       -> end while;  
10.     -> end;  
11.     -> //  
12.mysql > DELIMITER ; 
 while条件 do
--循环体
endwhile

repeat···· end repeat：

它在执行操作后检查结果，而while则是执行前进行检查。

1.  mysql > DELIMITER //  
2.  mysql > CREATE PROCEDURE proc5 ()  
3.       -> begin   
4.       -> declare v int;  
5.       -> set v=0;  
6.       -> repeat  
7.       -> insert into t values(v);  
8.       -> set v=v+1;  
9.       -> until v>=5  
10.     -> end repeat;  
11.     -> end;  
12.     -> //  
13.mysql > DELIMITER ;  
 repeat
--循环体
until循环条件     
endrepeat;

loop ·····endloop:

loop循环不需要初始条件，这点和while 循环相似，同时和repeat循环一样不需要结束条件, leave语句的意义是离开循环。

1.  mysql > DELIMITER //  
2.  mysql > CREATE PROCEDURE proc6 ()  
3.       -> begin 
4.       -> declare v int;  
5.       -> set v=0;  
6.       -> LOOP_LABLE:loop  
7.       -> insert into t values(v);  
8.       -> set v=v+1;  
9.       -> if v >=5 then 
10.     -> leave LOOP_LABLE;  
11.     -> end if;  
12.     -> end loop;  
13.     -> end;  
14.     -> //  
15.mysql > DELIMITER ;

f这个loop很不喜欢，不喜欢用。

Meiko丶

关注

1
点赞
踩
2

收藏

觉得还不错? 一键收藏
1
评论
mysql存储过程procedure 触发器trigger 游标cusor 控制语句（条件，循环）

什么是mysql存储例程? 存储例程是存储在数据库服务器中的一组sql语句，通过在查询中调用一个指定的名称来执行这些sql语句命令. 为什么要使用mysql存储过程？我们都知道应用程序分为两种，一种是基于web，一种是基于桌面，他们都和数据库进行交互来完成数据的存取工作。假设现在有一种应用程序包含了这两种，现在要修改其中的一个查询sql语句，那么我们可能要同时修改他们中对应的查询...
复制链接

扫一扫