mysql存储过程几个小案例

#存储过程的学习!!!!!!!


一、入门案例的学习


DROP PROCEDURE IF EXISTS proc_aa;  ##删除已经存在的过程
DELIMITER //
CREATE  PROCEDURE `proc_aa`(IN a int,IN b int,OUT c int)
BEGIN
    DECLARE temp int;  
    if a is null then set a = 1;
    ELSE  set a = a;
end if;
    if b is null then set b = 1;
    ELSE  set b = b;
end if;
    set temp  = a + b;
set c = temp;
END
//
DELIMITER ; 




#调用
SET @a = 29;
set @b = 33;
set @c = 44;
call proc_aa(@a,@b,@c);
select @c;




二、表操作
2.1 查询
delimiter//
drop procedure if exists sp//
CREATE PROCEDURE sp() select * from user //
call sp()//
DELIMITER;


2.2 插入数据
delimiter//
drop procedure if exists cc//
CREATE PROCEDURE cc(IN id int ,In age int) 
begin
insert into user (id,age) VALUES(id,age);
end
//
DELIMITER;
set @id = 333;
set @age = 444;
call cc(@id,@age);


2.3 更新数据
delimiter//
drop procedure if exists dd//
CREATE PROCEDURE dd(IN id int) 
begin
update user set age = 444 where `user`.id = id;
end
//
DELIMITER;
set @id = 333;
call dd(@id);




三、流程控制语句
3.1 if  then   ; elseif  then  ;  else     ;end if;
DELIMITER //
drop procedure if exists proc_if//
create procedure proc_if(IN type int)
BEGIN
declare c varchar(500);
IF type = 0 THEN
        set c = '参数是 0';
ELSEIF type = 1 THEN
        set c = '参数是 1';
else
        set c = '参数不是 1和0';
end if;
   select c;
end 
//
DELIMITER;
set @a = '33';
call proc_if(@a);


3.2 case   when   then   ; when   then   ; else   ; end case;
DELIMITER //
drop procedure if exists proc_if//
create procedure proc_if(IN type int)
BEGIN
declare c varchar(500);
case when type = 0 THEN
        set c = '参数是 0';
when  type = 1 THEN
        set c = '参数是 1';
else
        set c = '参数不是 1和0';
end case;
   select c;
end 
//
DELIMITER;
set @a = '33';
call proc_if(@a);