mysql存储过程和函数使用，if语句case语句while语句loop语句，批量插入大量随机记录示范

存储过程是用户定义的一系列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表，里面是用户自定义和系统的所有存储过程和函数。