mysql存储过程工作日判断_MySQL存储过程

存储过程：

把若干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//#####################################