存储过程:
把若干sql封装起来,起个名字--过程
把此过程存储在数据库中--存储过程
create procedure procedureName()
begin--sql
end$
######################
show procedure status;
调用call procedure()
声明变量:
格式 declare 变量名 变量类型 【default默认值】
###############################
delimiter//create procedure p3()
begin
declare ageint default 18;
declare heightint default 180;select concat('age is', age, 'height is',height);
end;//call p3()//##################################set age := age + 20;
#################################ifcondition then
statementelseendif;
################
声明参数,传参
create procedure p5(widthint ,height int)
beginselect concat('area is' width*height ) asarea;if width >height thenselect 'fat'elseif width
else
select 'fang'endif;
end;//#########################
顺序 选择 循环
求1-100的和
create procedure p6()
begin
declare totalint default 0;
declare numint default 0;while num<=100 do
--add num to totalset total := total+num;set num := num+1;
endwhile;selecttotal
end;
#############################
p7 I wantget the sumary of 1to N;
in型
create procedure p7(in n int)while num <=n docall p7(10);
###############################
in和out型
create procedure p8(in n int, out total int)
begin
declare numint default 0;set total := 0;while num <=n do
set num := num+1;set total := total+num;
endwhile;
end;
call p8(100,@sumary)select@sumary
##################################
inout型
create procedure p9(inout ageint)
beginset age := age +20;
end;set @currage =18;
call p9(@currage)select@currage
###################################
how to use"case"create procedure p10()
begin
declare posint default 0;set pos := floor(5*rand());casepos
when1 then select "1";
when2 then select "2";
when3 then select "3";else select 'I dont know'endcase;
end;
call p10()
#####################################
repeate
create procedure p11()
begin
declare totalint default 0;
declare iint default 0;
repeatselecti;set i := i+1;
until i>100end repeat;selecttotal;
end;
######################################
游标:
create procedure p12()
begin
declare row_gidint;
declare row_numint;
declare row_name varchar(20);
declare getgoods cousorfor select gid,num,name fromgoods;
open getgoods;
fetch getgoods into row_gid,row_num,row_name;selectrow_num,row_name;
close getgoods;
end//call p12()//############################################
create procedure p13()
begin
declare row_gidint;
declare row_numint;
declare row_name varchar(20);
declare getgoods cousorfor select gid,num,name fromgoods;
open getgoods;
fetch getgoods into row_gid,row_num,row_name;selectrow_num,row_name;
fetch getgoods into row_gid,row_num,row_name;selectrow_num,row_name;
fetch getgoods into row_gid,row_num,row_name;selectrow_num,row_name;
fetch getgoods into row_gid,row_num,row_name;selectrow_num,row_name;
close getgoods;
end//call p13() 最后报错。
################################################
循环取出一张表的所有数据
create procedure p14
begin
declare row_gidint;
declare row_numint;
declare row_name varchar(20);
declare cntint default 0;
declare iint default 0;
declare getgoods cursorfor select gid,num,name fromgoods;select count(*) into cnt fromgooods;
open getgoods;
repeatset i := i+1;
fetch getgoods into into row_gid,row_num,row_name;selectrow_num,row_name;
until i>=cnt end repeat
cloes getgoods;
end//###################################
通过越界标志来结束。
mysql 游标,可以declarecontinuehandler来操作1个越界标识
declarecontinue handler fornot found statement;
create procedure p15
begin
declare row_gidint;
declare row_numint;
declare row_name varchar(20);
declare youint defalut 1;
declare getgoods cursorfor select gid,num,name fromgoods;
declarecontinue handler for not found set you :0;
open getgoods;
repeat
fetch getgoods into into row_gid,row_num,row_name;selectrow_num,row_name;
until you=0end repeat;
cloes getgoods;
end//###################################
多取出1行,如何解决?
思考:如果not found后,后面的select 不再执行,最后就不会多取一行。
申明不再是continue, exist就能达到目的,后面的语句不再执行。
declare exit handlerfor not found set you :0;
#####################################
除了continue,exit外,还有一种undo handler
continue是触发后,后面语句继续执行
exit触发后,后面不再执行
undo触发后,前面的语句撤销
###############################
一行都没有怎么办?
create procedure p17
begin
declare row_gidint;
declare row_numint;
declare row_name varchar(20);
declare youint defalut 1;
declare getgoods cursorfor select gid,num,name fromgoods;
declarecontinue handler for not found set you :0;
open getgoods;
fetch getgoods into into row_gid,row_num,row_name;
repeatselectrow_num,row_name;
fetch getgoods into into row_gid,row_num,row_name;
until you=0end repeat;
cloes getgoods;
end//create procedure p18
begin
declare row_gidint;
declare row_numint;
declare row_name varchar(20);
declare youint defalut 1;
declare getgoods cursorfor select gid,num,name fromgoods;
declarecontinue handler for not found set you :0;
open getgoods;
fetch getgoods into into row_gid,row_num,row_name;while you=1 do
selectrow_num,row_name;
fetch getgoods into into row_gid,row_num,row_name;
endwhile;
cloes getgoods;
end//#####################################