游标和触发器

MySQL从5.0.2版开始引入触发器,触发器就是一个已命名的数据库对象,这个对象和某张表
相关,而且当这张表发生某种特定事件后,触发器将被激活执行相应的动作,触发器允许这
些动作在这张表中的一行或多行的数据被操作的前后执行。在流行业务系统的处理过程中,开发和管理人员可以用触发器来实现数据审计和其他安全相关的功能,如在运行中的数据执行加密功能。
举个例子来说:一个客户数据库中包含客户的社会保险号,企业的安全和审计人员必须将这些信息加密后存入磁盘。针对这种情况,管理员可以通过创建一个触发器来自动获取并加密这些数据然后再插入相应的数据库表中。如下所示:

一、游标

mysql> delimiter // 
mysql> create trigger t_customer_insert before insert on customer
    -> for each row
    -> begin 
    -> set NEW.customer_ssn = aes_encrypt(NEW.customer_ssn,'password');
    -> end; 
    -> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;

mysql> insert into customer values (1,'fred','smith','456097234');
Query OK, 1 row affected (0.00 sec)
mysql> select * from customer;

定义:存储在MySQL服务器上的数据库查询,是一种被select语句检索出来的结果集。

结果显示如下图:
图片 1 
可以看出存在磁盘数据库表中的社会保险号ssn已经变成加密后的不可读乱码格式。
另外MySQL5也引进了对游标的支持,在第一个发行版本中游标有向前翻阅结果集和不可执行数据更新语句的特点。游标可以用在MySQL5的新编码对象,如存储过程和触发器,甚至是独立的存储过程逻辑块中。以下是一个在存储过程中的使用游标的简单例子:

作用:方便在检索出来的结果集中前进或后退一行或多行。

mysql> delimiter // 
mysql> CREATE PROCEDURE cursor_demo()
    -> BEGIN 
    ->   DECLARE a, b CHAR(16); 
    ->   DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
    -> OPEN cur1; 
    -> REPEAT  
    ->  FETCH cur1 INTO a,b;
    -> UNDONE END REPEAT;
    -> CLOSE cur1; 
    -> END
    -> //

游标主要用于交互式应用;MySQL中的游标只能用于存储过程(和函数)。

下面再举一个触发器的例子,该例子可以计算所有插入某表的某个列中的数值的和:

1、创建游标

mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
    -> FOR EACH ROW SET @sum = @sum + NEW.amount;

游标使用declare语句创建;declare命名游标,并定义响应的select语句,根据需要带where和其他子句;例如:

可见触发器功能可以提高管理人员管理数据库的灵活性。

create
procedure processorders()

(BKJIA.COM教程)

begin

相关,而且当这张表发生某种特定事件后,触发器将…

 
     declare ordernumbers CURSOR

 
     for

 
     select order_num from orders;

end;

这个存储过程中,declare定义和命名了游标ordernumbers,存储过程处理完成后,游标消失(因为它局限于存储过程内)。

 

2、打开和关闭游标

游标使用open
cursor语句来打开,例如:

open
ordernumbers;

在处理open语句时执行查询,存储检索出的数据以供浏览和滚动;

游标处理完成时,使用close语句关闭,例如:

close
ordernumbers;

close释放游标使用的所有内部内存和资源,因此在每个游标不在需要时都应该关闭。

PS:一个游标关闭后,如果没有重新打开,则不能使用;但如果该游标被声明过,则不需要再次声明,用open语句打开使用即可。

如果不明确游标是否关闭,MySQL将会在到达end语句时自动关闭该游标;比如:

create
procedure processorders()

begin

       —
declare the cursor

     
 declare ordernumbers cursor

     
 for

     
 select order_num from orders;

       —
open the cursor

       open
ordernumbers;

       —
close the cursor

       close
ordernumbers;

end;

这个存储过程声明、打开、关闭一个游标,但对检索出的数据没任何操作。

 

3、使用游标数据

游标被打开后,使用fetch语句分别访问它每一行;fetch指定检索什么数据(所需的列),检索的数据存储在什么地方,还向前移动游标中的内部行指针,使下一条fetch语句检索下一行(不重复读取);例如:

create
procedure processorders()

begin

     
 –declare local variables

     
 declare o int;

     
 –declare the cursor

     
 declare ordernumbers cursor for select order_num from orders;

     
 –open the cursor

       open
ordernumbers;

       –get
order number

       fetch ordernumbers into
o;

     
 –close the cursor

       close
ordernumbers;

end;

这条语句中fetch用来检索当前行的order_num列(自动从第一行开始)到一个名为o的局部声明变量中;对检索出的数据不做任何处理。

再看一个例子,循环检索数据,从第一行到最后一行:

create
procedure processorders()  –创建存储过程

begin

     
 –declare local variables

     
 declare done boolean default 0;

     
 declare 0 int;

     
 –declare the cursor

     
 declare ordernumbers cursor

     
 for

     
 select order_num from orders;  –结果集

     
 –declare continue handler

       declare continue handler for sqlstate
‘02000’ set done=1;  –在这里,done被设置为结束时为真

     
 –open the cursor

       open
ordernumbers;

     
 –loop through all rows

     
 repeat

       –get
order number

       fetch
ordernumbers into0;

       –end
of loop

       until
done end repeat;

     
 –close the cursor

       close
ordernumbers;

end;

这个例子与前一个例子一样,使用fetch检索当前order_num到声明的名为0的变量中。但区别在于:这个例子中fetch是在repeat内,因此它反复执行到done为真(由until
done end repeat;规定)。

网站地图xml地图