mysql 存储过程基本案例

mysql 存储过程基本案例

案例一： 基本语句  #格式    if 条件 then  ...  end if;    【...代表其他操作！】

drop  procedure if EXISTS `my_pro`;
delimiter //
create procedure `my_pro`(in a int,in b int ,out c int)
begin
     DECLARE d int; #演示定义一个d的变量
     if a is null then set a = 0;   
     end if;
     if b is null then set b = 0;
     end if;
     set c = a + b;
END
//
delimiter;
call my_pro(1,3,@c);
select @c as sum ;

案例二：if语句   #格式    if 条件 then ... ; elseif  条件 then ... ; else ... ; end if ;

drop procedure if EXISTS `my_if`;
delimiter //
create procedure `my_if`(in a int)
begin 
	declare c VARCHAR(100); #定义一个变量
 	if a=1 then 
	set c = 'param is 1';
	elseif a=2 then
	set c = 'param is 2';
	else 
	set c = 'param is not 1 and not 2';
	end if;
	select c;
end
//
delimiter;
call my_if(1);

案例三： case语句  #格式  case 变量 when 值 then ... ; when 值 then ... ; ELSE ... ; end case ;

drop procedure if exists `my_case`;
delimiter //
create procedure `my_case`(in a int)
begin
    declare c varchar(50);
    case a
    when 1
    then set c = 'param is 1';
    when 2
    then set c = 'param is 2';
    else
    set c ='param is not in 1 or 2';
    end case;
    select c;
end
//
delimiter;
call my_case(1);

案例四： while语句 #格式  while 条件 do ... ...  end while;

drop procedure if exists `my_while`;
delimiter //
create procedure `my_while`(in a int)
begin
        declare b int default 0;  #default表示变量b的默认值
        declare c int default 0;  #c表示从0开始递增的值
        set b = 0;
        set c = 0;
        while c<=a DO
        set b = b+c;
        set c = c+1;
        end while;
        select b;
END
//
delimiter;
call my_while(2)

案例五： repeat循环语句  #格式: repeat ... until 条件 end repeat;

drop procedure if exists `my_repeat`;  #repeat是在执行操作后检查结果，而while则是执行前进行检查
delimiter //
create procedure `my_repeat`()
BEGIN
    declare i int DEFAULT 0;
      drop table if EXISTS `xxx`;
      create table xxx (id INT PRIMARY KEY, name VARCHAR(30)) ENGINE=innodb;  #创建一个表
    repeat  
        insert into xxx values(i,CONCAT(i,'param'));  
        set i=i+1;  
    until i>=100 #此条件满足时，时跳出循环!和while相反   -->注意不要;
    end repeat;
end
//
delimiter;
call `my_repeat`();

案例六: loop循环语句  #标准格式: loop  ... end loop;

loop循环不需要条件,leave语句的意义是离开循环 (3个循环都可以用，格式： xxx:loop  ........ if true then leave xxx; end if; )

案例六: loop循环语句  
#标准格式: loop  ... end loop; 
#loop循环不需要初始条件,leave语句的意义是离开循环
drop procedure if exists `my_loop`;
delimiter //
create procedure `my_loop`()
BEGIN
	declare i INT DEFAULT 0;
	drop table if exists `xxx`;
	create table xxx(id int primary key, name VARCHAR(50)) ENGINE=innodb;
	loopflag:LOOP  #loopfalg 是循环的一个标志
	INSERT INTO xxx VALUES(i,CONCAT(i,'param'));
	set i = i+1;
	if i>=5 then leave loopflag; #当大于5的时候离开循环
	end if;
	end loop;
END
//
delimiter;
call my_loop();

案例七：综合小案例

drop procedure if exists my_update;
delimiter //
CREATE PROCEDURE my_update()
BEGIN
DECLARE i INT DEFAULT 1;
DROP TABLE IF EXISTS test_table ;
CREATE TABLE test_table (id INT PRIMARY KEY, some_data VARCHAR(30)) ENGINE=innodb;
    WHILE (i<=10) DO
    INSERT INTO TEST_TABLE VALUES(i,CONCAT("record ",i));
    SET i=i+1;
    END WHILE;
SET i=5;
UPDATE test_table
SET some_data=CONCAT("I updated row ",i)
WHERE id=i;

DELETE FROM test_table WHERE id>i;
END;
//
delimiter ;
call my_update();

案例八:#异常声明 --> 格式  declare [continue/exit] handler for [异常状态码或其他] ... ;

drop procedure if exists `my_exception`;
delimiter//
create procedure `my_exception`(out o int)
BEGIN
	declare error int DEFAULT 0; # 记录是否有异常
	declare continue handler for SQLEXCEPTION set error = 1; #有异常
	START TRANSACTION;
		set o = 0; #默认输出是0
		select * from dferghoergheorg;#此表示肯定不存在的,sql异常会被处理程序声明进行处理。
		if error= 1 then 
			set o=-1; 
			ROLLBACK;  
		else
			set o=0;
			COMMIT;  
		end if;  
END
//
delimiter;
call my_exception(@c);
select @c;