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;