存储过程

什么是存储过程

简单的说，就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法；

存储过程跟触发器有点类似，都是一组SQL集，但是存储过程是主动调用的，且功能比触发器更加强大，触发器是某件事触发后自动调用；

相对于oracle数据库来说，MySQL的存储过程相对功能较弱，使用较少

创建一个简单的存储过程

存储过程proc_add功能很简单，两个整型输入参数a和b，一个整型输出参数sum，功能就是计算输入参数a和b的结果，赋值给输出参数sum；

DELIMITER $
CREATE PROCEDURE proc_add(IN a int, IN b int, OUT sum int)
BEGIN
    DECLARE c int;
    if a is null then set a = 0; 
    end if;
  
    if b is null then set b = 0;
    end if;

    set sum  = a + b;
END
$
DELIMITER ;

set @num = 2;
call proc_add(@num,5,@result);
select @result as sum;

存储过程中的控制语句

DELIMITER $
CREATE PROCEDURE `proc_if`(IN type int)
BEGIN
    DECLARE c varchar(500);
    IF type = 0 THEN
        set c = 'param is 0';
    ELSEIF type = 1 THEN
        set c = 'param is 1';
    ELSE
        set c = 'param is others, not 0 or 1';
    END IF;
    select c;
END
$
DELIMITER ;

set @type=10;
call proc_if(@type);

循环while语句

DELIMITER $
CREATE PROCEDURE `proc_while`(IN n int)
BEGIN
    DECLARE i int;
    DECLARE s int;
    SET i = 0;
    SET s = 0;
    WHILE i <= n DO
        set s = s + i;
        set i = i + 1;
    END WHILE;
    SELECT s;
END
$
DELIMITER ;

set @n = 100;
call proc_while(@n);

实战案例

-- 队员表
create table players(
id int(5) PRIMARY KEY not NULL auto_increment,-- 选手id
pname varchar(500) -- 选手名字
)
insert into players(pname) values ('ALLEN'),('BOB'),('CLARK')
create table matches(
id int(5) PRIMARY KEY not NULL auto_increment,-- 比赛id
mname varchar(500) -- 项目名称
)
insert into matches values (10,'跳水'),(11,'滑板'),(12,'冰壶')
-- 参赛表
create table join_matches(
id int(5) PRIMARY KEY not NULL auto_increment, -- 参赛id
mid int(5),-- 比赛id
pid int(5),-- 选手id
grade decimal(5,2)-- 比赛成绩
)
insert into join_matches(mid,pid,grade) 
values  (10,1,9.75),(11,2,7.35),(12,3,8.15),(11,3,6.55),(10,2,6.75)

创建一个存储过程，删除给定球员参加的所有比赛

select * from join_matches

DELIMITER $
CREATE PROCEDURE `delete_matches`(IN p_playerno int)
BEGIN
     DELETE FROM join_matches WHERE pid = p_playerno;
END
$
DELIMITER ;

call delete_matches(2)

缺点

不同数据库，语法差别很大，移植困难，换了数据库，需要重新编写；

不好管理，把过多业务逻辑写在存储过程不好维护，不利于分层管理，容易混乱，一般存储过程适用于个别对性能要求较高的业务，其它的必要性不是很大；