过程:若干语句,调用时执行封装的体。没有返回值的函数。 函数:是一个有返回值的过程 存储过程:把若干条sql封装起来,起个名字(过程),并存储在数据库中。 也有不存储的过程,匿名过程,用完就扔(mysql不支持匿名过程) create procedure p1() begin select 2+3; end$ show procedure status;//查看现有的存储过程: mysql> call p1();//调用存储过程,Call 存储过程名字(); //存储过程中,使用declare声明变量,declare n int [default 默认值] create procedure p2(age int,hei int) begin declare age smallint default 3; declare height int default 180; select concat('年龄是:', age, '身高是:' ,height); end$ mysql> call p2(1,2)$ +---------------------------------------------+ | concat('年龄是:', age, '身高是:' ,height) | +---------------------------------------------+ | 年龄是:3身高是:180 | +---------------------------------------------+ create procedure p3() begin declare age smallint default 3; set age := (age +20); select concat('年龄是:', age); end$ mysql> call p3()$ +-------------------------+ | concat('年龄是:', age) | +-------------------------+ | 年龄是:23 | +-------------------------+ create procedure p4(n int) begin select * from orde where gid=n; end$ mysql> call p4(3)$ +-----+-----+-----+ | oid | gid | num | +-----+-----+-----+ | 1 | 3 | 100 | | 1 | 3 | 100 | | 1 | 3 | 100 | | 1 | 3 | 100 | +-----+-----+-----+ create procedure p5(n int) begin declare age int default 18; if age>18 then select '已成年'; else select '未成年'; end if; end$ create procedure p7(n int,m char(1)) begin if m='t' then select * from orde where gid=3; else select * from orde where gid=2; end if; end$ delimiter $ create procedure p8(width int,height int) begin if width > height then select '胖'; elseif width < height then select '瘦'; else select '方' end if; end$ //编程:顺序、选择、循环。语法格式不一样。 create procedure t8() 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$ mysql> call t8()$ +-------+ | total | +-------+ | 5050 | +-------+ create procedure t8(in n int)//in表示传进去的参数, begin declare total int default 0; declare num int default 0; while num <= n do set total := total + num; set num = num +1; end while; select total; end$ mysql> create procedure t8(in n int,out total int)//in表示传进去的参数,out是传出去的参数, begin declare num int default 0; set total=0; while num <= n do set num = num +1; set total := total + num; end while; end$ mysql> call t8(100,@tt)$ //输出的值给@tt Query OK, 0 rows affected mysql> select @tt$ +------+ | @tt | +------+ | 5151 | +------+ mysql> create procedure t12(inout io1 int)//inout既可以传进去也可以传出来 begin declare num int default 0; while num <= io1 do set num = num +1; set io1 := io1 + num; end while; end$ mysql> set @total = 100$ Query OK, 0 rows affected mysql> call t12(@total)$ 1264 - Out of range value for column 'io' at row 1 mysql> select @total$ //case用法: create procedure t13() begin declare pos int default 0; set pos := floor(4*rand()); //不能用position是关键字 case pos when 1 then select "在飞"; when 2 then select "在海里"; when 3 then select "在地上"; else select "不知道"; end case; end$ mysql> call t13()$ +--------+ | 不知道 | +--------+ | 不知道 | +--------+ //repeat create procedure t14() begin declare total int default 0; declare i int default 0; repeat set i:=i+1; set total:=total+i; until i>=100 end repeat; select total; end$ mysql> call t14()$ +-------+ | total | +-------+ | 5151 | +-------+
本文转自农夫山泉别墅博客园博客,原文链接:http://www.cnblogs.com/yaowen/p/8144770.html,如需转载请自行联系原作者