存储过程,变量,sql逻辑判断,循环和函数

存储过程和函数是事先经过编译并存储在数据库中的sql语句的集合,二者的区别在于函数有返回值,存储过程没有

创建存数过程:

delimiter $

create procedure procedure_name()

begin

sql语句;

end $

delimiter ;

调用存储过程

call procedure_name();

查看所有存储过程:

SELECT name from mysql.proc where db='database_name'

查看某个存储过程的创建语句

show create  procedure procedure_name

删除存储过程

drop procedure procedure_name

变量

声明变量  declare variable_name type  例如 declare num int default 10,只能在begin end之间使用.

create procedure procedure_name2()

begin

declare num int ;

select count(*)  into num from test;

select concat('test表中的记录数为:',num);

end $

给变量赋值:

set variable_name = value 或 select into 语句 

sql逻辑判断-if

delimiter $
create procedure pro4()
BEGIN
declare height int DEFAULT 175;
declare description varchar(50) default '';
if height >=180 THEN
set description='高挑身材';
elseif height>=170 and height <180 THEN
set description='标准身材';
else
set description='一般身材';
end if;
select concat('身高',height,'的身材标准:',description);
end $

注意,存储过程的创建不能在sqlyog或navicat里,要在mysql的命令行窗口里执行

传递参数

create procedure procedure_name ([in/out/inout] 参数名 参数类型)

in 输入,默认类型,  out 输出,inout输入输出都可以

delimiter $
create procedure pro5(in height int,out description varchar(10))
BEGIN
if height >=180 THEN
set description='高挑身材';
elseif height>=170 and height <180 THEN
set description='标准身材';
else
set description='一般身材';
end if;
end $

调用执行过程:CALL pro6(165,@description),@description是创建一个用户会话变量

查看变量值  SELECT @description

逻辑判断-case when

 

CREATE PROCEDURE `pro6`( in month int)
begin

declare result varchar(10);

case

when month>=1 and month <=3 then

set result='第一季度';

when month>=4 and month <=6 then

set result='第二季度';

when month>=7 and month <=9 then

set result='第三季度';

when month>=10 and month <=12 then

set result='第四季度';

end case;

select concat('传入的月份是',month,'计算的结果是',result) as content;

end $

call pro6(7)

 

 

循环--- while

1到n累加

CREATE PROCEDURE `pro10`(in n int)
begin

declare total int default 0;

declare num int default 1;

while num<=n do

set total= total+num;

set num=num+1;

end while;

select concat('从1到',n,'的累加值是',total);

end$

 

 循环---repeat

满足条件则退出循环

create procedure pro8 (n int)
begin
declare total int default 0;
repeat
set total=total+n;
set n=n-1;
until n=0
end repeat;
select total;
end $

call pro8(100)$

注意until最后没有;分号

 游标 :用来存储结果集,移动一次游标可以获取一行记录,对游标的操作有创建游标,打开游标,获取数据,关闭游标

创建:declear cursor_name cursor for select_statement 

打开:open cursor_name

获取: fetch cursor_name into var1,var2...  将一行数据赋值给变量var1,var2 ...

关闭:close cursor_name

create procedure pro_cursur2()
BEGIN
DECLARE c_id int(11); --对应test表的id字段,类型要完全一样
DECLARE c_name VARCHAR(255);--对应test表的name字段,类型要完全一样
DECLARE has_data int default 1;
DECLARE test_result cursor for select * from test limit 5;--创建游标,test_result这个游标存储的是select * from test limit 5这个查询的结果集
DECLARE exit HANDLER for not found set has_data=0; --这句必须紧跟在游标创建语句后面,用于循环获取数据
open test_result;
repeat
fetch test_result into c_id,c_name;--fetch一次就获取一行数据,这里是用repeat循环控制的fetch次数,
select concat('id=',c_id,',name=',c_name);
until has_data=0
end repeat ;
close test_result;
end $

call pro_cursur2()$

 函数 : 只是比存储过程多了返回值,存储过程的输出参数也可以当返回值用,功能上函数和存储过程没区别

create function function_name ([param type])
returns type
begin
...;
return var;
end$

执行函数:select function_name;