MySQL一一sql的存储过程、触发器

213

当前数据库中有一张acount表

image

一、存储过程的创建:

(1)语法:mysql> CREATE PROCEDURE 创建的存储过程名字(OUT|IN|INOUT 参数名 数据类型,...,...) 特征 过程体**;**

解析如下图:

image

(2)使用:

列出存储过程的详细列表:show procedure status;

删除:drop procedure 要删除的存储过程的名字;

查看创建存储过程的信息:show create procedure 创建的存储过程名字;

查看所有存储过程:show procedure status \G;

(3)示例:

创建一个存储过程pshow,不带参数:

image

调用该存储过程pshow();注意调用的时候不带参数的不用加括号

image

创建一个带参数的存储过程pfun:

image

调用存储过程pfun

image

call procedure();//无参

call procedure(@x,@y,****);//有参数(此处x、y为输出参数,****为输入参数)

用完后,直接使用select @x,@y;来查询x、y的值。

查看目前所有的存储过程

image

二、存储函数:

(1)语法:CREATE FUNCTION 创建的存储函数名字(参数名称 参数类型,...,...)  RETURNS 返回值得类型  函数体;

image

(2)使用

调用存储函数:select 函数名字([参数]);//根据定义函数的形参,形参与实参保持一致

查看创建函数信息:show create function 函数名字  /G;

查看所有自定义函数:show function status \G;

删除存储函数:drop function 函数名字;

(3)示例:

创建一个存储函数fnameshow:

image

调用存储函数:

image

创建一个带参数的存储函数mydelete

image

调用:

image

再查看表,发现id=5的这一行被删除了: image

查看存储函数:

image

三、触发器:

(1)、语法:

CREATE TRIGGER 创建的触发器名字 BEFFOR|AFTER  INSERT|UPDATE|DELETE

ON 表名字 FOR  触发器的执行间隔   触发器的SQL语句

解析如下表:

image

注意(摘自网络):

触发程序与要创建触发器的表相关。该表必须引用永久性表。不能将触发程序与临时表表或视图关联起来。

触发程序的动作时间BEFORE或AFTER,以指明触发程序是在激活它的语句之前或之后触发。

激活触发程序的语句的类型。下述值之一:

·         INSERT:将新行插入表时激活触发程序,例如,通过INSERT、LOAD DATA和REPLACE语句。

·         UPDATE:更改某一行时激活触发程序,例如,通过UPDATE语句。

·         DELETE:从表中删除某一行时激活触发程序,例如,通过DELETE和REPLACE语句。

请注意,trigger_event与以表操作方式激活触发程序的SQL语句并不很类似,这点很重要。

例如,关于INSERT的BEFORE触发程序不仅能被INSERT语句激活,也能被LOAD DATA语句激活

(2)使用

查看所有的触发器:show triggers \G;

删除触发器:drop trigger 触发器名字;

(3)示例

创建一个插入型触发器

image

更新表中的数据,查看表,发现id=4的balance由原来的500变为了509

image

查看触发器

image

四、存储过程、存储函数和触发器的对比:

(1)基本区别:

image

(2)优缺点对比:

存储过程:

优点:

*有if/else,case,while等控制语句,通过编写存储过程,可以实现一些逻辑比较复杂的功能;

*模块化;对一些功能进行了封装,代码的复用;

*响应速度快,只有在首次执行时需要经过编译和优化步骤,后被调用直接执行,省去了重新编写代码计算的步骤。

*减少网络传输。存储过程直接就在数据库服务器上跑,所有的数据访问都在服务器内部进行,不需要传输数据到其它终端。

*方便DBA优化。所有的SQL集中在一个地方

缺点:

*复杂的业务逻辑。没办法应用缓存。

触发器

优点:

*安全。可以基于数据库的值使用户具有操作数据库的某种权利。可以跟踪用户对数据库的操作。   

*触发器能够拒绝或回退那些破坏相关完整性的变化,取消试图进行数据更新的事务。当插入一个与其主健不匹配的外部键时,这种触发器会起作用。

缺点: *数据集数据量又较大时,触发器效果会非常低(因为自动计算数据值,需要变动整个数据集导致效率下降)

*对于批量操作并不适合使用触发器,使用触发器实现的业务逻辑在出现问题时很难进行定位。

五、注意事项:

在MySQL中,默认以分号为分隔符,在写存储过程、存储函数、触发器时,编译器会当做SQL语句来进行处理,编译过程会出现错误,所以要事先用”delimiter @”声明@(或者其他,由用户自己定义)为当前的段分隔符,让编译器把两个@之间的内容当做存储过程、存储函数、触发器的代码,在写完整个内容完之后再”delimiter ;”将分隔符还原。