`
1enny
  • 浏览: 69420 次
  • 性别: Icon_minigender_1
  • 来自: 深圳
文章分类
社区版块
存档分类
最新评论

使用存储过程、触发器、事件

 
阅读更多
mysql cookbook00016

使用存储过程、触发器、事件
16.0 引言
存储过程(函数和过程):存储函数执行计算并返回值,可放在表达式中;而存储过程只执行不需要返回值,不能使用在表达式中而是通过call调用
存储过程可以被用来更新表中的行或者产生结果集发送到客户端。
触发器:触发器被定义为当表被修改时产生动作的对象,用于增删改语句中。
事件:一个事件在预定时间执行sql语句的对象。
这些不同种类的独享具有共同的属性,即他们都是用户定义,但是存储在服务器以供日后使用执行,这与客户端向服务器发送sql语句并立即执行不
同。这些对象还有下面这些属性:
1. 都是被定义为在独享被调用时执行其他语句。2.对象的执行体为单个sql语句,但是该语句可使用包含多重语句的符合语句符号
(BEGIN....END块)
2. BEGIN ...END块包含多个语句,但是其本身视为单语句。可以调用其他过程
3. 在复合语句中的每个语句都需要以;来结束。但是在MYSQL客户端来定义使用复合语句就有问题,因为MYSQL本身将;解释为确定语句的边界
解决此问题可以通过定义复合语句对象时,重新定义分隔(DELIMITER)
16.1 创建复合语句对象
讨论:每个存储过程、触发器、时间都是必须放在一个sql语句中的。但是这些对象经常会执行多条sql语句,为了解决次问题,
你需要使BEGIN..ENF将语句封装起来形成一个复合的语句。
mysql> delimiter $$
mysql> create function avg_email(user varchar(30) )
-> returns float reads sql data
-> begin
-> if user is null then
-> return(select avg(size) from email);
-> else
-> return(select avg(size) from email where srcuser = user);
-> end if;
-> end ;
-> $$
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter ;
mysql> select srcuser,avg_email(srcuser) from email ;

+---------+--------------------+
| srcuser | avg_email(srcuser) |
+---------+--------------------+
| aa | 11111 |
| bb | 22222 |
| cc | 33333 |
| dd | 44444 |
| ee | 55555 |
| ff | 66666 |
+---------+--------------------+
6 rows in set (0.00 sec)

16.2 使用存储函数封装计算
create function sales_tax(state_param varchar(3),amount_param DECIMAL(10,2))
returns decimal(3,2)reads sql data//说明该函数只是读取而非修改表
begin
declare rate_val decimal(3,2);//['desɪm(ə)l]声明变量
//通过continue处理器来实现,如果NODATA条件发生(SQLSTATE值为02000),continue就会来处理
declarecontinue handler for sqlstate '02000'set rate_val = 0;
selecttax_rateintorate_val
from sales_tax_rate where state = state_param;
return amount_param * rate_val;
end;

16.3 使用存储过程来“返回”多个值
问题:假如你需要产生多个值,而存储函数这能返回一个值
解决:使用带OUT/INOUT参数的存储过程,并字调用过程中将用户定义变量传入这些参数,过程没有返回值,但是可以将值赋予参数,从而在过程
返回时,变量中也会含有这些值
讨论:存储函数的参数只是用来输入值,而过程参数有三种类型:
      1. IN参数是仅作为输入使用,如果你没有指定数据类型,那么它将是默认参数类型
      2. INOUT参数即可以传入一个值,也可传出一个值
      3. OUT参数只被用于传出
mysql> create procedure mail_sender_stats(in user varchar(30),
out messages int,
out total_size float,
out avg_size float)
-> begin
->selectcount(*),ifnull(sum(size),0) ,ifnull(avg(size),0)intomessages,total_size,avg_size
-> from mail where srcuser = user;
-> end;
-> $$
Query OK, 0 rows affected (0.55 sec)


16.4 使用触发器来定义动态的默认列值
问题:表中的一列需要被初始化为一个非常数的值,但是MYSQL只支持运行常数的默认值
解决:使用触发器BEFORE INSERT触发器,它使你能够使用任意表达式产生的值进行初始化,触发器通过计算默认值执行动态的初始化
讨论:除了可以根据当前日期和时间初始化TIMESTAMP列,MYSQL中列的默认值必须为常数值。你不能使用default子句定义指向函数
调用的列,并且也不能定义根据其他列赋初值的列
        1. d DATE DEFAULT NOW()
        2. I INT DEFAULT(...SOME SUBQUERY)
        3. hash_val char(32) default md5(blob_col)
假设你需要使用表来存储大量的PDF/XML/图片....,并且你还需要以后能够快速的查找他们。text或blob数据类型并不适合存储他们。因为他们并不适合对其进行查找。为了解决这些问题,可以通过使用一下策略:
    1. 为每个数据值计算某种hash值并且与数据一起存入表中
    2. 查找行中是否包含了特定的数据值,可以计算该值的hash值,然后在表中搜索相应的hash值,最好为hash列建立所用

mysql> create table doc_table(
-> author varchar(100) not null,
-> title varchar(100) not null,
-> document mediumblob not null,
-> doc_hash char(32) not null,
-> primary key(doc_hash));
-> $$
Query OK, 0 rows affected (0.16 sec)

mysql> delimiter ;
mysql> create trigger bi_doc_table before insert on doc_table
-> for each row set new.doc_hash = md5(new.document);
Query OK, 0 rows affected (0.23 sec)

mysql> insert into doc_table(author,title,document) values('zhangsan','sanmaoliuliangji','aaaaaaaaa');
ERROR 1364 (HY000): Field 'doc_hash' doesn't have a default value

mysql> show create table doc_table;
+-----------+---------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+-----------+---------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------+
| doc_table | CREATE TABLE `doc_table` (
`author` varchar(100) NOT NULL,
`title` varchar(100) NOT NULL,
`document` mediumblob NOT NULL,
`doc_hash` char(32) NOT NULL DEFAULT '',
PRIMARY KEY (`doc_hash`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------+---------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table doc_table engine=myisam;
Query OK, 0 rows affected (0.28 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> insert into doc_table(author,title,document) values('zhangsan','sanmaoliuliangji','aaaaaaaaa');
Query OK, 1 row affected (0.03 sec)


mysql> select * from doc_table;
+----------+------------------+-----------+----------------------------------+
| author | title | document | doc_hash |
+----------+------------------+-----------+----------------------------------+
| zhangsan | sanmaoliuliangji | aaaaaaaaa | 552e6a97297c53e592208cf97fbb3b60 |
+----------+------------------+-----------+----------------------------------+
1 row in set (0.00 sec)
//你会发现在使用了INNODB的存储引擎时,非空插入并且即使触发器会填充数据,它也汇报非空约束;但是使用了MYISAM的存储引擎时,非空插入并且触发器填充数据,它就能够成功的插入数据
mysql> create trigger up_doc_table beforeinsertondoc_table
-> for each row set new.doc_hash = md5(new.document);
ERROR 1235 (42000):This version of MySQL doesn't yet support 'multiple triggers withthe same actiontime and event for one table'//不能对同一张表创建相同触发事件
mysql> create trigger up_doc_table before update on doc_table
-> for each row setnew.doc_hash = md5(new.document);//指定将要被插入到指定列的新值
Query OK, 0 rows affected (0.22 sec)

mysql> update doc_table set document = 'bbbbbbbb'
-> where document = 'aaaaaaaaa';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from doc_table;
+----------+------------------+----------+----------------------------------+
| author | title | document | doc_hash |
+----------+------------------+----------+----------------------------------+
| zhangsan | sanmaoliuliangji | bbbbbbbb | 810247419084c82d03809fc886fedaad |
+----------+------------------+----------+----------------------------------+
1 row in set (0.00 sec)

16.5 为其他日期和时间类型模拟TIMESTAMP属性

mysql> select * from ts_emulate;
+------+------------+----------+---------------------+
| data | d | t | dt |
+------+------------+----------+---------------------+
|ccc | 2014-12-12 | 18:57:55 | 2014-12-12 18:57:55 |
| dd | 2014-12-12 | 18:57:59 | 2014-12-12 18:57:59 |
+------+------------+----------+---------------------+
2 rows in set (0.00 sec)
mysql> delimiter $$

mysql> create trigger up_ts_emulate before update on ts_emulate
-> for each row
-> begin
-> if new.data <> old.data then
-> set new.d = curdate(),new.t = curtime(),new.dt = now();
-> end if;
-> end;
-> $$
Query OK, 0 rows affected (0.23 sec)
mysql> update ts_emulate te set te.data = 'eeee' where te.data = 'cc
-> $$
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from ts_emulate;
-> $$
+------+------------+----------+---------------------+
| data | d | t | dt |
+------+------------+----------+---------------------+
|eeee | 2014-12-12 | 19:08:51 | 2014-12-12 19:08:51 |
| dd | 2014-12-12 | 18:57:59 | 2014-12-12 18:57:59 |
+------+------------+----------+---------------------+
2 rows in set (0.00 sec)

mysql>

16.6 使用触发器记录表的变化
注意:使用了after触发器,它只是在表变化操作成功以后才被激活,而before触发器即使在行变化操作由于某些原因而失败时,可能也被激活
mysql> create trigger up_ts_emulate after update on ts_emulate
-> for each row
-> set new.d = adddata(curdate(),interval 1 day);
ERROR 1362 (HY000): Updating of NEW row is not allowed in after trigger
mysql> create trigger up_ts_emulate after update on ts_emulate
-> for each row
-> set old.d = adddata(curdate(),interval 1 day);
ERROR 1362 (HY000): Updating of OLD row is not allowed in trigger
mysql>



16.7 使用事件调度数据库动作
问题:创建周期性运行的数据库操作,而无需要用户交互
例子:通过周期性的向日志表插入记录
mysql> create table log(
-> ts timestamp,message varchar(255));
Query OK, 0 rows affected (0.22 sec)

mysql> create event log
-> on schedule every 1 minute//用来设置执行的时间间隔;该表示每分钟执行一次
-> do insert into log(message) values('--mark--');
Query OK, 0 rows affected (0.13 sec)

mysql> select * from log;
Empty set (0.00 sec)

mysql> show variables like 'event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | OFF |
+-----------------+-------+
1 row in set (0.11 sec)

mysql> set global event_scheduler = 1;//默认是没开启,这种开启方式服务器运行期间,要想一直开启配置:event_scheduler = 1
Query OK, 0 rows affected (0.06 sec)

mysql> show variables like 'event_scheduler';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| event_scheduler | ON |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> select * from log;
+---------------------+----------+
| ts | message |
+---------------------+----------+
| 2014-12-12 20:22:02 | --mark-- |
+---------------------+----------+
1 row in set (0.05 sec)

mysql> set @@event_scheduler = 1;//不可以通过这种方式设置值
ERROR 1229 (HY000): Variable 'event_scheduler' is a GLOB
mysql> select @@event_scheduler;//可以通过这种方式引用
+-------------------+
| @@event_scheduler |
+-------------------+
| ON |
+-------------------+
1 row in set (0.00 sec)

mysql> select @@event_scheduler;
+-------------------+
| @@event_scheduler |
+-------------------+
| ON |
+-------------------+
1 row in set (0.00 sec)

mysql> select * from log;
+---------------------+----------+
| ts | message |
+---------------------+----------+
| 2014-12-12 20:22:02 | --mark-- |
| 2014-12-12 20:23:02 | --mark-- |
+---------------------+----------+
2 rows in set (0.00 sec)

mysql> alter event log disable;//悬挂事件的执行
Query OK, 0 rows affected (0.00 sec)

mysql> select * from log;
+---------------------+----------+
| ts | message |
+---------------------+----------+
| 2014-12-12 20:22:02 | --mark-- |
| 2014-12-12 20:23:02 | --mark-- |
| 2014-12-12 20:24:02 | --mark-- |
+---------------------+----------+
3 rows in set (0.00 sec)

mysql> select * from log;
+---------------------+----------+
| ts | message |
+---------------------+----------+
| 2014-12-12 20:22:02 | --mark-- |
| 2014-12-12 20:23:02 | --mark-- |
| 2014-12-12 20:24:02 | --mark-- |
+---------------------+----------+
3 rows in set (0.00 sec)

mysql> alter event log enable;//重新激活调度器
Query OK, 0 rows affected (0.00 sec)

mysql> select * from log;
+---------------------+----------+
| ts | message |
+---------------------+----------+
| 2014-12-12 20:22:02 | --mark-- |
| 2014-12-12 20:23:02 | --mark-- |
| 2014-12-12 20:24:02 | --mark-- |
| 2014-12-12 20:27:02 | --mark-- |
+---------------------+----------+
4 rows in set (0.00 sec)

mysql> create event delete_log//让其仍然执行,但是创建一个互补的事件来清除日志;相互写作
-> on schedule every 1 minute
-> do delete from log where ts < now() - interval 2
Query OK, 0 rows affected (0.03 sec)

mysql> select * from log;
+---------------------+----------+
| ts | message |
+---------------------+----------+
| 2014-12-12 20:29:02 | --mark-- |
| 2014-12-12 20:30:02 | --mark-- |
+---------------------+----------+
2 rows in set (0.02 sec)

mysql> select * from log;
+---------------------+----------+
| ts | message |
+---------------------+----------+
| 2014-12-12 20:29:02 | --mark-- |
| 2014-12-12 20:30:02 | --mark-- |
+---------------------+----------+
2 rows in set (0.00 sec)

mysql> select * from log;























分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics