赞
踩
例如,为了确认学生能否毕业,需要同时查询学生档案表、成绩表和综合表,此时就需要使用多条 SQL 语句来针对这几个数据表完成处理要求。
MySQL 5.0 版本以前并不支持存储过程,这使 MySQL 在应用上大打折扣。MySQL 从 5.0 版本开始支持存储过程,既提高了数据库的处理速度,同时也提高了数据库编程的灵活性
存储过程的优点 | 详述 |
---|---|
封装性 | 通常完成一个逻辑功能需要多条 SQL 语句,而且各个语句之间很可能传递参数,所以,编写逻辑功能相对来说稍微复杂些,而存储过程可以把这些 SQL 语句包含到一个独立的单元中,使外界看不到复杂的 SQL 语句,只需要简单调用即可达到目的。并且数据库专业人员可以随时对存储过程进行修改,而不会影响到调用它的应用程序源代码。 |
可增强 SQL 语句的功能和灵活性 | 存储过程可以用流程控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。 |
可减少网络流量 | 由于存储过程是在服务器端运行的,且执行速度快,因此当客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而可降低网络负载。 |
高性能 | 当存储过程被成功编译后,就存储在数据库服务器里了,以后客户端可以直接调用,这样所有的 SQL 语句将从服务器执行,从而提高性能。但需要说明的是,存储过程不是越多越好,过多的使用存储过程反而影响系统性能。 |
提高数据库的安全性和数据的完整性 | 存储过程提高安全性的一个方案就是把它作为中间组件,存储过程里可以对某些表做相关操作,然后存储过程作为接口提供给外部程序。这样,外部程序无法直接操作数据库表,只能通过存储过程来操作对应的表,因此在一定程度上,安全性是可以得到提高的。 |
使数据独立 | 数据的独立可以达到解耦的效果,也就是说,程序可以调用存储过程,来替代执行多条的 SQL 语句。这种情况下,存储过程把数据同用户隔离开来,优点就是当数据表的结构改变时,调用表不用修改程序,只需要数据库管理者重新编写存储过程即可。 |
CREATE PROCEDURE <过程名> ( [过程参数[,…] ] )
BEGIN
<过程体>
END
DELIMITER $$
mysql > DELIMITER ??
DELIMITER ;
DELIMITER //
CREATE PROCEDURE ShowStu()
BEGIN
SELECT * FROM tb_student_info;
END //
DELIMITER //
CREATE PROCEDURE GetScoreByStu (IN student_name VARCHAR(30))
BEGIN
SELECT sex FROM tb_student_info WHERE name =student_name;
END //
SHOW PROCEDURE STATUS LIKE 存储过程名;
CREATE TABLE studentinfo (
ID int(11) NOT NULL,
NAME varchar(20) DEFAULT NULL,
SCORE decimal(4,2) DEFAULT NULL,
SUBJECT varchar(20) DEFAULT NULL,
TEACHER varchar(20) DEFAULT NULL,
PRIMARY KEY (ID)
);
INSERT INTO studentinfo(id,name,score) VALUES(1,"zhangsan",80),(2,"lisi","70");
DELIMITER //
CREATE PROCEDURE showstuscore()
BEGIN
SELECT id,name,score FROM studentinfo;
END //
SHOW PROCEDURE STATUS LIKE 'showstuscore' \G
SHOW PROCEDURE STATUS LIKE 'show%' \G
SHOW CREATE PROCEDURE 存储过程名;
SHOW CREATE PROCEDURE showstuscore \G
SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME=存储过程名;
ALTER PROCEDURE 存储过程名 [ 特征 ... ]
ALTER PROCEDURE showstuscore MODIFIES SQL DATA SQL SECURITY INVOKER;
SHOW CREATE PROCEDURE showstuscore \G
DROP PROCEDURE [ IF EXISTS ] <过程名>
DROP PROCEDURE ShowStuScore;
SELECT * FROM information_schema.routines WHERE routine_name='ShowStuScore';
CALL sp_name([parameter[...]]);
CALL ShowStu();
[IN | OUT | INOUT] param_name type;
CREATE FUNCTION sp_name ([func_parameter[...]])
RETURNS type
[characteristic ...] routine_body
DELIMITER //
CREATE FUNCTION func_student_info(id INT(11))
RETURNS VARCHAR(25)
COMMENT '查询某个学生的姓名'
BEGIN
RETURN(SELECT name FROM tb_student_info WHERE tb_student_info.id = id);
END //
DELIMITER ;
SHOW FUNCTION STATUS LIKE 存储函数名;
SHOW CREATE FUNCTION 存储函数名;
SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME=存储函数名;
ALTER FUNCTION 存储函数名 [ 特征 ... ]
DROP FUNCTION [ IF EXISTS ] <函数名>
SELECT func_student_info(3);
DECLARE var_name[,...] type [DEFAULT value]
DECLARE my_sql INT DEFAULT 10;
SET var_name = expr[,var_name = expr]...
SET my_sql=30;
SELECT col_name [...] INTO var_name[,...]
FROM table_name WEHRE condition
SELECT id INTO my_sql FROM tb_student WEHRE id=2;
DECLARE condition_name CONDITION FOR condition_value
//方法一:使用sqlstate_value
DECLARE can_not_find CONDITION FOR SQLSTATE '42S02';
//方法二:使用 mysql_error_code
DECLARE can_not_find CONDITION FOR 1146;
DECLARE handler_type HANDLER FOR condition_value[...] sp_statement
定义处理程序的方式 | 代码 | 描述 |
---|---|---|
捕获 sqlstate_value | DECLARE CONTINUE HANDLER FOR SQLSTATE ‘42S02’ SET @info=‘CAN NOT FIND’; | 捕获 sqlstate_value 值。如果遇到 sqlstate_value 值为 42S02,执行 CONTINUE 操作,并且输出“CAN NOT FIND”信息。 |
捕获 mysql_error_code | DECLARE CONTINUE HANDLER FOR 1146 SET @info=‘CAN NOT FIND’; | 捕获 mysql_error_code 值。如果遇到 mysql_error_code 值为 1146, 执行 CONTINUE 操作,并且输出“CAN NOT FIND”信息。 |
先定义条件,然后调用 | DECLARE can_not_find CONDITION FOR 1146; DECLARE CONTINUE HANDLER FOR can_not_find SET @info=‘CAN NOT FIND’; | 先定义条件,然后再调用条件。这里先定义 can_not_find 条件,遇到 1146 错误就执行 CONTINUE 操作。 |
使用 SQLWARNING | DECLARE EXIT HANDLER FOR SQLWARNING SET @info=‘ERROR’; | 使用 SQLWARNING。SQLWARNING 捕获所有以 01 开头的 sqlstate_value 值,然后执行 EXIT 操作,并且输出“ERROR"信息。 |
使用 NOT FOUND | DECLARE EXIT HANDLER FOR NOT FOUND SET @info=‘CAN NOT FIND’; | 使用 NOT FOUND。NOT FOUND 捕获所有以 02 开头的 sqlstate_value 值,然后执行 EXIT 操作,并且输出“CAN NOT FIND”信息。 |
使用 SQLEXCEPTION | DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info=‘ERROR’; | 使用 SQLEXCEPTION。 SQLEXCEPTION 捕获所有没有被 SQLWARNING 或 NOT FOUND 捕获的 sqlstate_value 值,然后执行 EXIT 操作,并且输出“ERROR”信息。 |
CREATE PROCEDURE handlerdemo()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @X2=1;
SET @X=1;
INSERT INTO t8 VALUES(1);
SET @X=2;
INSERT INTO t8 VALUES(1);
SET @X=3;
END //
DELIMITER ;
CALL handlerdemo();
SELECT @X;
DECLARE cursor_name CURSOR FOR select_statement;
DELIMITER //
CREATE PROCEDURE processnames()
BEGIN
DECLARE nameCursor CURSOR
FOR
SELECT name FROM tb_student;
END//
OPEN cursor_name;
FETCH cursor_name INTO var_name [,var_name]...
CLOSE cursor_name;
CREATE TABLE users
(
ID BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
user_name VARCHAR(60),
user_pass VARCHAR(64),
PRIMARY KEY (ID)
);
INSERT INTO users VALUES(null,'sheng','sheng123'),
(null,'yu','yu123'),
(null,'ling','ling123');
DELIMITER // # 改语句结束标志为 // CREATE PROCEDURE test_cursor (in param INT(10),out result VARCHAR(90)) BEGIN DECLARE name VARCHAR(20); DECLARE pass VARCHAR(20); DECLARE done INT; DECLARE cur_test CURSOR FOR SELECT user_name,user_pass FROM users; #声明游标,指定数据集为users数据库中的user_name,user_pass字段 DECLARE continue handler FOR SQLSTATE '02000' SET done = 1; IF param THEN SELECT id INTO result FROM users WHERE id = param;#若输入参数非空,则将user数据库中id字段值等于输入参数param的记录赋值给输出结果result ELSE # 若输入参数为空 OPEN cur_test; #打开游标 repeat #循环 FETCH cur_test into name,pass; #将游标当前索引的数据保存在变量里 SELECT concat_ws(',',result,name,pass) INTO result;#对于查询结果中的每一行,将result、name和pass列的值用逗号连接起来,然后将这个组合后的字符串赋值给变量result until done END repeat; #结束循环 CLOSE cur_test; #关闭游标 END IF; #结束IF END // #结束存储过程
call test_cursor(3,@test)//
select @test//
CREATE PROCEDURE pro_users()
BEGIN
DECLARE result VARCHAR(100);
DECLARE no INT;
DECLARE cur_1 CURSOR FOR SELECT user_name FROM users;#声明游标,指定数据集为users数据库中的user_name字段
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no=1;
SET no=0;
OPEN cur_1;#打开游标
WHILE no=0 do #若no=0则进行循环
FETCH cur_1 into result;#将当前游标cur_1检索到的数据赋值给result
UPDATE users SET user_name='MySQL' WHERE user_name=result;#将users数据库中user_name字段值=result的记录user_name都改为MySQL
END WHILE;
CLOSE cur_1;
END //
call pro_users() //
SELECT * FROM users //
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list]...
[ELSE statement_list]
END IF
IF age>20 THEN SET @count1=@count1+1;
ELSEIF age=20 THEN @count2=@count2+1;
ELSE @count3=@count3+1;
END lF;
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list]...
[ELSE statement_list]
END CASE
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE
CASE age
WHEN 20 THEN SET @count1=@count1+1;
ELSE SET @count2=@count2+1;
END CASE;
CASE
WHEN age=20 THEN SET @count1=@count1+1;
ELSE SET @count2=@count2+1;
END CASE;
[begin_label:]LOOP
statement_list
END LOOP [end_label]
或
LOOP
statement_list
END LOOP
add_num:LOOP
SET @count=@count+1;
END LOOP add_num;
LEAVE label
add_num:LOOP
SET @count=@count+1;
IF @count=100 THEN
LEAVE add_num;
END LOOP add num;
ITERATE label
add_num:LOOP
SET @count=@count+1;
IF @count=100 THEN
LEAVE add_num;
ELSE IF MOD(@count,3)=0 THEN
ITERATE add_num;
SELECT * FROM employee;
END LOOP add_num;
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]
REPEAT
SET @count=@count+1;
UNTIL @count=100
END REPEAT;
[begin_label:] WHILE search_condition DO
statement list
END WHILE [end label]
WHILE @count<100 DO
SET @count=@count+1;
END WHILE;
那么为什么要使用触发器呢?比如,在实际开发项目时,我们经常会遇到以下情况:
- 在学生表中添加一条关于学生的记录时,学生的总数就必须同时改变。
- 增加一条学生记录时,需要检查年龄是否符合范围要求。
- 删除一条学生信息时,需要删除其成绩表上的对应记录。
- 删除一条数据时,需要在数据库存档表中保留一个备份副本。
虽然上述情况实现的业务逻辑不同,但是它们都需要在数据表发生更改时,自动进行一些处理。这时就可以使用触发器处理。例如,对于第一种情况,可以创建一个触发器对象,每当添加一条学生记录时,就执行一次计算学生总数的操作,这样就可以保证每次添加一条学生记录后,学生总数和学生记录数是一致的。
优点 | 缺点 |
---|---|
1. 触发器的执行是自动的,当对触发器相关表的 数据做出相应的修改后立即执行。 2. 触发器可以实施比 FOREIGN KEY 约束、CHECK 约束更为复杂的检查和操作。 3. 触发器 可以实现表数据的级联更改,在一定程度上保证了数据的完整性。 | 1. 使用触发器实现的业务逻辑在出现问题时很难进行定位,特别是涉及到多个触发器的情况下,会使 后期维护变得困难。 2. 大量使用触发器容易导致代码结构被打乱,增加了程序的复杂性。 3. 如果需要变动的 数据量较大时,触发器的执行效率会非常低。 |
MySQL 支持的触发器 | 响应位置 | 描述 |
---|---|---|
INSERT 触发器 | 在 INSERT 语句执行之前或之后响应的触发器。 例如:INSERT 的 BEFORE 触发器不仅能被 MySQL 的 INSERT 语句激活,也能被 LOAD DATA 语句激活。 | 1. 在 INSERT 触发器代码内,可引用一个名为 NEW (不区分大小写)的虚拟表来访问被插入的行。 2. 在 BEFORE INSERT 触发器中,NEW 中的值也可以被更新,即允许更改被插入的值(只要具有对应的操作权限)。 3. 对于 AUTO_INCREMENT 列,NEW 在 INSERT 执行之前包含的值是 0,在 INSERT 执行之后将包含新的自动生成值。 |
UPDATE 触发器 | 在 UPDATE 语句执行之前或之后响应的触发器。 例如:UPDATE 语句。 | 1. 在 UPDATE 触发器代码内,可引用一个名为 NEW(不区分大小写)的虚拟表来访问更新的值。 2. 在 UPDATE 触发器代码内,可引用一个名为 OLD(不区分大小写)的虚拟表来访问 UPDATE 语句执行前的值。 3. 在 BEFORE UPDATE 触发器中,NEW 中的值可能也被更新,即允许更改将要用于 UPDATE 语句中的值(只要具有对应的操作权限)。 4. OLD 中的值全部是只读的,不能被更新。 5. 当触发器设计对触发表自身的更新操作时,只能使用 BEFORE 类型的触发器,AFTER 类型的触发器将不被允许。 |
DELETE 触发器 | 在 DELETE 语句执行之前或之后响应的触发器。从表中删除某一行数据时激活触发器。 例如:DELETE 和 REPLACE 语句。 | 1. 在 DELETE 触发器代码内,可以引用一个名为 OLD(不区分大小写)的虚拟表来访问被删除的行。 2. OLD 中的值全部是只读的,不能被更新。 |
CREATE <触发器名> < BEFORE | AFTER >
<INSERT | UPDATE | DELETE >
ON <表名> FOR EACH Row<触发器主体>
CREATE TABLE tb_emp8(
id INT(11),
name VARCHAR(25),
deptid INT(11),
salary float
);
SELECT * FROM tb_emp8;
DESC tb_emp8;
CREATE TRIGGER SumOfSalary
BEFORE INSERT ON tb_emp8
FOR EACH ROW
SET @sum=@sum+NEW.salary;
SET @sum=0;
INSERT INTO tb_emp8
VALUES(1,'A',1,1000),(2,'B',1,500);
SELECT @sum;
CREATE TABLE tb_emp6(
id INT(11),
name VARCHAR(25),
deptid INT(11),
salary float
);
CREATE TABLE tb_emp7(
id INT(11),
name VARCHAR(25),
deptid INT(11),
salary float
);
CREATE TRIGGER double_salary
AFTER INSERT ON tb_emp6
FOR EACH ROW
INSERT INTO tb_emp7
VALUES (NEW.id,NEW.name,deptId,2*NEW.salary);
INSERT INTO tb_emp6 VALUES (1,'A',1,1000),(2,'B',1,500);
SELECT * FROM tb_emp6;
SELECT * FROM tb_emp7;
CREATE TABLE account(
accnum INT(4),
amount DECIMAL(10,2));
CREATE TABLE myevent(
id INT(11) DEFAULT NULL,
evtname CHAR(20) DEFAULT NULL);
CREATE TRIGGER trigupdate AFTER UPDATE ON account
FOR EACH ROW INSERT INTO myevent VALUES(1,'after update');
INSERT INTO account VALUES (1,2.5);
UPDATE account SET amount=3.14;
SELECT *FROM myevent;
SHOW TRIGGERS;
信息 | 描述 | 举例 |
---|---|---|
Trigger | 表示触发器的名称 | 在这里触发器的名称为 trigupdate; |
Event | 表示激活触发器的事件 | 这里的触发事件为更新操作 UPDATE; |
Table | 表示激活触发器的操作对象表 | 这里为 account 表; |
Statement | 表示触发器执行的操作 | 这里是向 myevent 数据表中插入一条数据; |
Timing | 表示触发器触发的时间 | 这里为更新操作之后(AFTER); |
一些其他信息,比如触发器的创建时间、SQL 的模式、触发器的定义账户和字符集等 | 这里不再一一介绍。 |
SHOW TRIGGERS \G
SELECT * FROM information_schema.triggers WHERE trigger_name= '触发器名';
SELECT * FROM information_schema.triggers \G
信息 | 描述 |
---|---|
TRIGGER_SCHEMA | 表示触发器所在的数据库; |
TRIGGER_NAME | 表示触发器的名称; |
EVENT_OBJECT_TABLE | 表示在哪个数据表上触发; |
ACTION_STATEMENT | 表示触发器触发的时候执行的具体操作; |
ACTION_ORIENTATION | 值为 ROW表示在每条记录上都触发; |
ACTION_TIMING | 表示触发的时刻是 AFTER; |
一些其他信息,比如触发器的创建时间、SQL 的模式、触发器的定义账户和字符集等 | 这里不再一一介绍。 |
SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME= 'trigupdate'\G
DROP TRIGGER [ IF EXISTS ] [数据库名] <触发器名>
DROP TRIGGER double_salary;
SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME= 'double_salary'\G
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。