存储过程
概述
存储过程是一组为了完成特定功能的 SQL 语句集合。使用存储过程的目的是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来,这个过程经编译和优化后存储在数据库服务器中,因此称为存储过程。当以后需要数据库提供与已定义好的存储过程的功能相同的服务时,只需调用“CALL存储过程名字”即可自动完成。
一个存储过程是一个可编程的函数,它在数据库中创建并保存,一般由 SQL 语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的特定功能时,存储过程尤为合适。
优点
存储过程通常有如下优点:
1. 封装性
存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的 SQL 语句,并且数据库专业人员可以随时对存储过程进行修改,而不会影响到调用它的应用程序源代码。
2. 可增强 SQL 语句的功能和灵活性
存储过程可以用流程控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。
3. 可减少网络流量
由于存储过程是在服务器端运行的,且执行速度快,因此当客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而可降低网络负载。
4. 高性能
存储过程执行一次后,产生的二进制代码就驻留在缓冲区,在以后的调用中,只需要从缓冲区中执行二进制代码即可,从而提高了系统的效率和性能。
5. 提高数据库的安全性和数据的完整性
使用存储过程可以完成所有数据库操作,并且可以通过编程的方式控制数据库信息访问的权限。
编写
基本的存储过程
CREATE PROCEDURE productpricing()
BEGIN
SELECT AVG(prod_price) as priceaverage FROM products;
END;
创建名为 productpricing 的储存过程。如果存储过程中需要传递参数,则将他们在括号中列举出来即可。括号必须有。BEGIN 和 END 关键字用来限制存储过程体。上述存储过程体本身是一个简单的 select 语句。注意这里只是创建存储过程并没有进行调用。
储存过程的使用:Call productpring();
使用参数的存储过程
一般存储过程并不显示结果,而是把结果返回给你指定的变量上。
变量:内存中一个特定的位置,用来临时存储数据。
CREATE PROCEDURE prod(
OUT pl DECIMAL(8,2),
OUT ph DECIMAL(8,2),
OUT pa DECIMAL(8,2)
)
BEGIN
SELECT MIN(prod_price) INTO pl FROM products;
SELECT MAX(prod_price) INTO ph FROM products;
SELECT AVG(prod_price) INTO pa FROM products;
END;
关键字 OUT 指出相应的参数用来从存储过程传出一个值(返回给调用者)。
MySQL 支持 in(传递给存储过程)、out(从存储过程传出,这里所用)和 inout(对存储过程传入和传出)类型的参数。存储过程的代码位于 begin 和 end 语句内。他们是一系列 select 语句,用来检索值。然后保存到相对应的变量(通过 INTO 关键字)。存储过程的参数允许的数据类型与表中使用的类型相同。注意记录集是不被允许的类型,因此,不能通过一个参数返回多个行和列,这也是上面为什么要使用 3 个参数和 3 条 select语句的原因。
使用:
call PROCEDURE(@pricelow,@pricehigh,@priceaverage);
select @pricelow;
select @pricehigh;
select @pricelow,@pricehigh,@priceaverage;
为调用此存储过程,必须指定 3 个变量名。如上所示。3 个参数是存储过程保存结果的 3 个变量的名字。调用时,语句并不显示任何数据,它返回以后可以显示的变量(或在其他处理中使用)。
注意:所有的 MySQL 变量都是以@开头。
使用IN的示例:
CREATE PROCEDURE ordertotal(
IN innumber int,
OUT outtotal decimal(8,2)
)
BEGIN
SELECT Sum(item_price * quantity) FROM orderitems WHERE order_num =
innumber INTO outtotal;
END;
CALL ordertotal(20005,@total);
select @total; // 得到 20005 订单的合计
CALL ordertotal(20009,@total);
select @total; //得到 20009 订单的合计
带有控制语句的存储过程
CREATE PROCEDURE ordertotal(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
-- declear variable for total
DECLARE total DECIMAL(8,2);
-- declear tax percentage
DECLARE taxrate INT DEFAULT 6;
-- get the order total
SELECT Sum(item_price * quantity) FROM orderitems WHERE order_num =
onumber INTO total;
-- IS this taxable?
IF taxable THEN
-- yes ,so add taxrate to the total
SELECT total+(total/100*taxrate)INTO total;
END IF;
-- finally ,save to out variable
SELECT total INTO ototal;
END;
在存储过程中我们使用了 DECLARE
语句,他们表示定义两个局部变量,DECLARE 要求指定变量名和数据类型。它也支持可选的默认值(taxrate 默认 6%),因为后期我们还要判断要不要增加税,所以,我们把 SELECT 查询的结果存储到局部变量 total 中,然后在 IF 和 THEN 的配合下,检查 taxable 是否为真,然后在真的情况下,我们利用另一条 SELECT 语句增加营业税到局部变量 total 中,然后我们再利用 SELECT 语句将 total(增加税或者不增加税的结果)保存到总的 ototal 中。
上面的 COMMENT 关键字可以给出或者不给出,如果给出,将在 SHOW PROCEDURE STATUS
的结果中显示。
所有语法:
变量相关:
DECLARE var_name[,...] type [DEFAULT value]
:声明变量SET var_name = expr [, var_name = expr] ...
:变量赋值select ... into var_name
:将查询的结果赋值给变量
分支循环:
if_then elseif_then else_then
if search_condition then statement_list
[elseif search_condition then statement_list] ...
[else statement_list]
end if;
case
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE;
while
while search_condition do
statement_list
end while;
repeat
while 是满足条件才执行,repeat 是满足条件就退出循环。
REPEAT
statement_list
UNTIL search_condition
END REPEAT;
loop
通常配合leave语句退出循环,也可以死循环
[begin_label:] LOOP
statement_list
END LOOP [end_label]
leave
退出循环
CREATE PROCEDURE pro_test11(n int)
BEGIN
declare total int default 0;
ins: LOOP
IF n <= 0 then
leave ins;
END IF;
set total = total + n;
set n = n - 1;
END LOOP ins;
select total;
END$
使用游标的存储过程
游标是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE,其语法分别如下。
声明光标:
DECLARE cursor_name CURSOR FOR select_statement ;
代表这个变量是一个cursor类型,对应的是select_statement语句的结果集。
OPEN 光标:
OPEN cursor_name ;
FETCH 光标:
FETCH cursor_name INTO var_name [, var_name] ...
CLOSE 光标:
CLOSE cursor_name ;
实例:
-- 查询emp表中数据, 并逐行获取进行展示
DELIMITER create procedure pro_test12()
begin
DECLARE id int(11);
DECLARE name varchar(50);
DECLARE age int(11);
DECLARE salary int(11);
DECLARE has_data int default 1;
DECLARE emp_result CURSOR FOR select * from emp;
DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;
open emp_result;
repeat
fetch emp_result into id , name , age , salary;
select concat('id为',id, ', name 为' ,name , ', age为 ' ,age , ', 薪水为: ', salary);
until has_data = 0
end repeat;
close emp_result;
end
DELIMITER ;
通过DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;
相当于在光标获取不到下一行的时候给has_data赋值为0。
修改存储过程
MySQL 中修改存储过程的语法格式如下:
ALTER PROCEDURE 存储过程名 [ 特征 ... ]
特征指定了存储过程的特性,可能的取值有:
CONTAINS SQL
表示子程序包含 SQL 语句,但不包含读或写数据的语句。NO SQL
表示子程序中不包含 SQL 语句。READS SQL DATA
表示子程序中包含读数据的语句。MODIFIES SQL DATA
表示子程序中包含写数据的语句。SQL SECURITY { DEFINER |INVOKER }
指明谁有权限来执行。DEFINER
表示只有定义者自己才能够执行。INVOKER
表示调用者可以执行。
COMMENT 'string'
表示注释信息。
ALTER PROCEDURE showstuscore MODIFIES SQL DATA SQL SECURITY INVOKER;
提示:ALTER PROCEDURE
语句用于修改存储过程的某些特征。如果要修改存储过程的内容,可以先删除原存储过程,再以相同的命名创建新的存储过程;如果要修改存储过程的名称,可以先删除原存储过程,再以不同的命名创建新的存储过程。
删除存储过程
MySQL 中使用 DROP PROCEDURE
语句来删除数据库中已经存在的存储过程。语法格式如下:
DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] <过程名>
IF EXISTS:指定这个关键字用于防止因删除不存在的存储过程而引发的错误。
触发器
概述
MySQL 数据库中触发器是一个特殊的存储过程,不同的是执行存储过程要使用 CALL 语句来调用,而触发器的执行不需要使用 CALL 语句来调用,也不需要手工启动,只要一个预定义的事件发生就会被 MySQL自动调用。
触发程序的优点如下:
+ 触发程序的执行是自动的,当对触发程序相关表的数据做出相应的修改后立即执行。
+ 触发程序可以通过数据库中相关的表层叠修改另外的表。
+ 触发程序可以实施比 FOREIGN KEY
约束、CHECK
约束更为复杂的检查和操作。
在 MySQL 中,只有执行 INSERT
、UPDATE
和 DELETE
操作时才能激活触发器。
创建触发器
在创建触发器时,需要给出 4 条语句(规则):
1. 唯一的触发器名;
2. 触发器关联的表;
3. 触发器应该响应的活动;
4. 触发器何时执行(处理之前或者之后)
CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT
'Product added' INTO @info;
CREATE TRIGGER 用来创建名为 newproduct 的新触发器。触发器可以在一个操作发生前或
者发生后执行,这里 AFTER INSERT 是指此触发器在 INSERT 语句成功执行后执行。这个触
发器还指定 FOR EACH ROW , 因此代码对每个插入行都会执行。文本 Product added 将
对每个插入的行显示一次
INSERT 触发器
是在 insert 语句执行之前或者执行之后被执行的触发器。
1. 在 insert 触发器代码中,可引入一个名为 new 的虚拟表,访问被插入的行(普通插入语句之后也存在这个表)
2. 在 before insert 触发器中,new 中的值也可以被更新(允许更改被插入的值)
3. 对于 auto_increment 列,new 在 insert 执行之前包含 0,在 insert 执行之后包含新的自动生成值
CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT
NEW.order_num;
该语句创建一个名为 neworder 的触发器,按照 AFTER INSERT ON orders
执行。在插入一个新订单到 orders 表时,MySQL 生成一个新的订单号并保存到 order_num 中。触发器从NEW.order_num 取得这个值并返回它。此触发器必须按照 AFTER INSERT 执行,因为在BEFORE INSERT 语句执行之前,新 order_num 还没有生成。 对于 orders 的每次插入使用这个触发器总是返回新的订单号。
DELETE 触发器
Delete 触发器在 delete 语句执行之前或者之后执行。
1. 在 delete 触发器的代码内,可以引用一个名为 OLD 的虚拟表,用来访问被删除的行。
2. OLD 中的值全为只读,不能更新。
CREATE TRIGGER deleteorder 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;
在任意订单被删除前将执行此触发器,它使用一条 INSERT 语句将 OLD 中的值(要被删除的订单) 保存到一个名为 archive_orders 的存档表中(为实际使用这个例子,我们需要用与orders 相同的列创建一个名为 archive_orders 的表)
使用 BEFORE DELETE 触发器的优点(相对于 AFTER DELETE 触发器来说)为,如果由于某种原因,订单不能存档,delete 本身将被放弃。
我们在这个触发器使用了 BEGIN 和 END 语句标记触发器体。这在此例子中并不是必须的,只是为了说明使用BEGIN END 块的好处是触发器能够容纳多条SQL 语句(在BEGIN END块中一条挨着一条)。
UPDATE 触发器
在 update 语句执行之前或者之后执行
- 在 update 触发器的代码内,可以引用一个名为 OLD 的虚拟表,用来访问以前(UPDATE 语句之前)的值,引用一个名为 NEW 的虚拟表访问新更新的值。
- 在 BEFORE UPDATE 触发器中,NEW 中的值可能也被用于更新(允许更改将要用于 UPDATE语句中的值)
- OLD 中的值全为只读,不能更新。
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors FOR EACH ROW SET
NEW.vend_state = Upper(NEW.vemd_state);
保证州名缩写总是大写(不管 UPFATE 语句中是否给出了大写),每次更新一行时,NEW.vend_state 中的值(将用来更新表行的值)都用 Upper(NEW.vend_state)替换。
总结
- 与其他 DBMS 相比,MySQL 5 中支持的触发器相当初级,未来的 MySQL 版本中估计会存在一些改进和增强触发器的支持。
- 通常 before 用于数据的验证和净化(为了保证插入表中的数据确实是需要的数据) 也适用于 update 触发器。
- 创建触发器可能需要特殊的安全访问权限,但是触发器的执行时自动的,如果 insert,update,或者 delete 语句能够执行,则相关的触发器也能执行。
- 用触发器来保证数据的一致性(大小写,格式等)。在触发器中执行这种类型的处理的优点就是它总是进行这种处理,而且透明的进行,与客户机应用无关。
- 触发器的一种非常有意义的使用就是创建审计跟踪。使用触发器,把更改(如果需要,甚至还有之前和之后的状态)记录到另外一个表是非常容易的。
- MySQL 触发器不支持 call 语句,无法从触发器内调用存储过程。
总体来说,触发器使用的过程中,MySQL 会按照以下方式来处理错误。
若对于事务性表,如果触发程序失败,以及由此导致的整个语句失败,那么该语句所执行的所有更改将回滚;对于非事务性表,则不能执行此类回滚,即使语句失败,失败之前所做的任何更改依然有效。
若 BEFORE 触发程序失败,则 MySQL 将不执行相应行上的操作。
若在 BEFORE 或 AFTER 触发程序的执行过程中出现错误,则将导致调用触发程序的整个语句失败。
仅当 BEFORE 触发程序和行操作均已被成功执行,MySQL 才会执行AFTER触发程序。
原创文章,作者:彭晨涛,如若转载,请注明出处:https://www.codetool.top/article/mysql%e5%9b%9e%e9%a1%be-%e5%ad%98%e5%82%a8%e8%bf%87%e7%a8%8b%e4%b8%8e%e8%a7%a6%e5%8f%91%e5%99%a8/