存储过程是用户定义的一系列sql语句的集合,方便数据库表字段的隐藏和封装,减小频繁数据库i/o操作的负担。相对于函数来说,存储过程来说可以返回多个值,参数可以有IN,OUT,INOUT三种类型,而函数只能有一个返回值。存储过程一般是作为一个独立的部分来执行,使用call 存储过程名,而函数可以作为查询语句的一个部分来调用(SELECT调用)。存储过程完成的功能也更加复杂强大,函数则是只针对一个功能。
注意:在定义过程时,使用 DELIMITER $ 命令将语句的结束符号从分号 ; 临时改为$,使得过程体中使用的分号被直接传递到服务器,而不会被客户端(如mysql)解释。
比如有一个employee表,再使用loop语句创建了一个这样的批量插入记录的存储过程:
mysql> delimiter $
create procedure insertemp(in nInsertNum int)
BEGIN
#Routine body goes here...
DECLARE n int DEFAULT 0; //声明一个整形n变量,默认值是0
myloop:LOOP //启动一个loop循环 insert into employee(name,age,dept_id)
values(substring(md5(rand()),1,6),
floor(rand()*30)+20,
floor(rand()*(select max(id) from department))+1);
SET n=n+1; //递增1 IF n = nInsertNum THEN //如果n等于外部传入的参数,就是要插入的数目,则退出loop循环
LEAVE myloop;
END IF; //结束if语句 END LOOP myloop; //结束loop循环
END$mysql> delimiter ;//恢复原来的;分号解释器。
说明:substring(md5(rand()),1,6,这个是产生一个6个长度的随机字符串,floor(rand()*30)+20,这是产生一个30以内的随机整数(0-29之间),然后再加20,最终的随机数就是20-49之间。
然后执行这个存储过程,call insertemp(100); 插入100条记录,结果如下:
下面是一个if语句的范例,存储过程的参数:age int,out text varchar(20),输入一个int的age,返回一个字符串text。
begin
if age>49 then #如果大于49
set text='tuixiu'; #设置返回的字符串内容
elseif age>29 then #elseif 如有其他条件
set text='dashu';
else #else 默认条件
set text='chengnian';
end if; #结束if语句
end
下面是一个case语句的范例,存储过程的参数:mon int,只有一个int的mon。
begin
declare result varchar(20);
case #开始case分支
when mon>6 and mon<13 then #when条件分支,当是某个条件时
set result="xiabannian";
when mon>=1 and mon<=6 then
set result="shangbannian";
else #其他的默认分支
set result="feifa";
end case; #结束case分支
select concat("in :",mon,",ret :",result) as content; ##返回字符串,命名为content
end
下面是一个while语句的范例,存储过程的参数:n int,只有一个int的n,用来计算1-n的总和。
begin
declare totalsum int default 0; #定义返回的总和,初始为0
declare num int default 1; #定义刚开始的数值,从1开始计算
while num<=n do #如果不大于传递的变量,则一直循环
set totalsum=totalsum+num; #累加计算
set num=num+1; #递增要计算的数值
end while;
select concat('sum is ',totalsum);#返回字符串
end
对于函数来说,它是有一个返回值,定义格式如下:这个函数f_getemname,是输入一个int的emid,然后返回一个字符串。
mysql> delimiter $
mysql> create function f_getemname(emid int)
-> returns varchar(20) character set utf8
-> begin
-> declare emname varchar(20) character set utf8;
-> select name into emname from employee where id=emid;
-> return emname;
-> end$
函数的调用,就不能使用call了,而是直接用select,因为它是有返回值的。
下面是一些关于存储过程、函数的常用命令:
mysql> show create procedure(function,table) InsertEmp\G;//查看存储过程的定义,跟查看函数,表的定义是一样格式,后面加\G是显示更直观些。
mysql> select * from proc where db="mysql"\G;//查看mysql这个db里的所有存储过程和函数记录,在mysql里有个proc表,里面是用户自定义和系统的所有存储过程和函数。