存储过程例子
存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL 语句集,存储在数据库中,经过第一次编译后再次调用不需要再次编译,用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它
create procedure p1()
begin
end$
查看存储过程
show procedure status \G;
call procedure()
create procedure p1()
begin
select 'hello' from dual;
end$
create procedure p2()
begin
declare age int default 90;
declare height int default 175;
select concat('年龄',age,'身高',height) from dual;
end$
create procedure p3()
beging
declare age tinyint unsigned default 90;
declare height tinyint unsigned default 175;
select age,height from dual;
set age:=age+20;
select concat('20年后的年龄',age,'岁') from dual;
end$
create procedure p4()
begin
declare age int default 18
if age>=18 then
select '已成年';
else
select '未成年';
end if;
end$
create procedure p5(width int ,height int)
begin
select concat('你的面积是',width*height) as area;
if width> height then
select '胖';
else if width<height then
select '瘦';
end if;
end$
call p5(3,5);
create procedure p6()
begin
declare total int default 0;
declare num int default 0;
while num<=100 do
set total := total + num;
set num:=num+1;
end while;
select total;
end$
drop procedure p6()
create procedure p7(in n int)
begin
declare total int default 0;
declare num int default 0;
while num<n do
set num:=num+1;
set total:=total+num;
end while;
select total;
end$
create procedure p8(in n int,out total int)
begin
declare num int default 0;
while num<n do
set num:=num+1;
set total:=total+num;
end while;
end$
call p8(100,@total) $
create procedure p9(inout age int)
begin
set age:=age+20
end$
end$
set @currentage=18$
call p9(@currentage)$
create procedure p10()
begin
declare pos int default 0;
set pos:=floor(5*rand());
case pos
when 1 then select 'still flying';
when 2 then select 'fall in sea';
when 3 then select 'in the island';
else select 'I don't know';
end case;
end$
create procedure p11()
begin
declare i int defult 0;
repeat
select i;
set i:=i+1;
until i>10 end repeat;
end$
create procedure p12()
begin
declare total int defult 0;
declare i int defult 0;
repeat
set i:=i+1;
set total:=total+i;
until i>=100 end repeat;
select total;
end$