MySQL存储过程与函数

一提到存储过程可能就会引出另一个话题就是存储过程的优缺点，这里也不做讨论。因为mysql中存储过程和函数的语法非常接近所以就放在一起，主要区别就是函数必须有返回值（return），并且函数的参数只有IN类型而存储过程有IN、OUT、INOUT这三种类型。

权限

创建存储子程序需要CREATE ROUTINE权限,移除存储子程序需要ALTER ROUTINE权限。这个权限自动授予子程序的创建者,执行子程序需要EXECUTE权限。然而，这个权限自动授予子程序的创建者。同样，子程序默认的SQL SECURITY 特征是DEFINER，它允许用该子程序访问数据库的用户与执行子程序联系到一起

mysql函数即使重启mysqld服务后函数依然会存在，只存在指定的数据库，不会跨数据库

创建数据库

drop database if exists test_1;
create database test_1;
use test_1;

函数

第一个函数

delimiter $$ #定义结束符  $$
drop function if exists hello;  #判断hello函数是否存在,如果存在则删除
create function test_1.hello (name char(20),age int(2))  returns  char(225)   
#sql 顾名思义 是创建函数的意思注意：
#sql 语言是强类型语言因此要声明参数的类型和返回值的类型
    begin #函数体的开始
    #set @i=0; #set关键字 声明一个全局变量@i  sql也有局部和全局变量                             
    declare greeting char(20);
    #declare 关键字声明一个局部变量  
    #注意：函数内部不能同时有局部变量和全局变量的存在
    declare num      int(20);
    declare str      char(20);
    declare restr    char(225);
    declare max      int(20);
    if hour(now()) < 12 then
        set greeting ='早上好'; 
        #set 关键字； 一般用来赋值，赋值给局部变量，全局变量一样
    elseif hour(now()) >12 then
        set greeting ='下午好';
    end if;  #end if这里一定要分开
    if (age < 18) then #判断条件可以加上括号
        set str='未成年';
        else
        set str='成年';
    end if;
    set  num=0;
    while num < age do
        set num=age+1;
    end while;  #注意分开 end while
    set max=0;
    w:while age < 100 do
        set age=age+1;
        if age = 4  then
            #leave w;   
            #leave 关键字，相当于break跳出循环w是指明关键字leave跳出那个循环的
            iterate w; #iterate相当于continue跳过
            end if;
        set max=max+1;
    end while w;
    #select concat(name,greeting,'你的幸运数字是') into restr;
    select concat(name,greeting,'你的幸运数字是',max,str) into restr;
    #将运算后的结果赋值给restr
    return restr;
    end
$$
delimiter ; #还原mysql操作结束符
select test_1.hello('freax',12); #函数调用

创建一个自动生成订单序号的函数20140103001，可以减少连接数据库的次数，减少数据库的连接

drop function if exists create_sn;
delimiter $$
create function test_1.create_sn() returns bigint(15)   
#编写程序时要注意数据类型
    begin
    declare order_sn bigint(15);
    declare prev bigint(15);
    declare prevdatetime bigint(15);
    declare sn bigint(15);
    declare nowdate bigint(15);
    select  order_sn from  userorder order by userorder_id  desc limit 1 into prev;#赋值prev
    select concat(year(prev),month(prev),dayofmonth(prev)) into prevdatetime;
    select right(prev,4) into  sn;
    select concat(year(now()),month(now()),dayofmonth(now())) into nowdate;
    if isnull(prev)   then
        select concat(nowdate,'0001') into order_sn;
            return order_sn;
        elseif nowdate = prevdatetime then
            select concat(nowdate,'0001') into order_sn;
            return order_sn;
        else
        select concat(prevdatetime,(sn+1)) into order_sn;
        return order_sn;
        end if;
    end
    $$
    delimiter ;
select create_sn();#函数调用

产生随机字符串，用于测试数据库

drop function if exists  randstr;
delimiter $$
create function  test_1.randstr(num int(11))  returns char(255)
#为了容易区分那个函数或者存储过程是那个数据库的
#可以在函数名中加上数据库前缀test_randstr;
    begin
    declare str char(255)  default 'q1we23r4t5y6u7i8o9p0asdfghjklzxcvbnm';
    declare nums int(11);
    declare returnstr char(255);#SQL变量名不能和列名一样
    declare i int(11) default  0;
    select floor(truncate(rand(),1)*36)+1 into nums;
    select substring(str,nums,1)  into returnstr;
    #declare i int(11) default  0;
    #在声明变量时一定要在begin语句之后，除begin外的任何语句之前
    while  i <num do
    select floor(truncate(rand(),1)*36)+1 into nums;
    select concat(substring(str,nums,1),returnstr) into returnstr;
    #set returnstr=concat(substring(str,nums,1),returnstr);
    set i=i+1;
    end while;
    return returnstr;
    end
    $$
    delimiter ;

drop function if exists ceshi;
delimiter $$
create function ceshi() returns char(255)
    begin
    declare ceshistr1 char(255);
    declare ceshistr2 char(255);
    declare ceshistr char(255);
    #select  order_sn,userorder_id from userorder limit 1 into ceshistr;
#ERROR 1222 (21000): 
#The used SELECT statements have a different number of columns
    select order_sn,userorder_id into ceshistr1,ceshistr2 from userorder limit 1 ;
    #在mysql中一个列的数据必须占用一个变量，否则会出现上面的错误
    select concat(ceshistr1,ceshistr2) into ceshistr;
    #select * from  userorder;
    #存储函数的限制：不能再存储函数中返回整个表的数据
    #ERROR 1415 (0A000): Not allowed to return a result set from a function
    return ceshistr;
    end
    $$
    delimiter ;

存储过程

第一个存储过程

drop procedure if exists simpleproc;
delimiter $$
    CREATE PROCEDURE simpleproc (OUT param1 INT)
     BEGIN
     SELECT *  FROM userorder;#存储过程可以返回整张表的数据
     END
     $$
     delimiter ;
call  simpleproc();#调用存储过程

存储过程学习声明变量

drop procedure if exists test2;
delimiter $$
create procedure  test2 ()
    begin
    declare str char(255) default 'huangyanxiong';  
    #在存储过程声明局部变量并赋值
    set  @color='red';                              
    #在存储过程中声明全局变量并赋值
    #注意：函数内不能同时有局部变量和全局变量
    #select * from userorder;
    select @color as colors;                            、
    #一般采用这种方式输出到终端
    end
    $$
    delimiter ;
    call test2();

存储过程传递参数

drop procedure  if exists test3;
delimiter $$
create procedure test3(in username char(50))
    begin
    select username as user_name;
    end
    $$
    delimiter ;
    call test3('huangyanxiong');
drop procedure if exists test4;
delimiter $$

create procedure test4(username char(50))
    begin
    declare str char(50);
    select concat(username,'xxxxxx') into str;
    select str as string;#设置别名返回
    end;
    $$
delimiter ;
call test4('huangyanxiong');

drop procedure if exists test5;
delimiter $$
create procedure test5 (username char(50))  #默认使用in
    begin
    set @age=12;
    select username as usernames,@age as age;#使用同一张表返回
    end
$$
delimiter ;
call test5('huangyanxiong');

把函数改变为存储过程很简单，改改就可以,把上面的订单序号改为存储过程

drop procedure if exists create_sn;
delimiter $$
create procedure create_sn()
    begin
    declare order_sn bigint(15);
    declare prev bigint(15);
    declare prevdatetime bigint(15);
    declare sn bigint(15);
    declare nowdate bigint(15);
    select  order_sn  into prev from  userorder order by userorder_id  desc limit 1 ;
    #赋值prev
    SELECT prev;
    select concat(year(prev),month(prev),dayofmonth(prev)) into prevdatetime;
    select right(prev,4) into  sn;
    select concat(year(now()),month(now()),dayofmonth(now())) into nowdate;
    if isnull(prev)   then
        select concat(nowdate,'0001') into order_sn;
            select order_sn as ordersn;
        elseif nowdate != prevdatetime then
            select concat(nowdate,'00011') into order_sn;
            select order_sn as ordersn;
        else
        select concat(prevdatetime,(sn+1)) into order_sn;
        select order_sn as ordersn;
        end if;
    end
    $$
    delimiter ;
    select * from userorder;
call create_sn();

随机字符串

drop procedure if exists  randstr;
delimiter $$
create procedure  randstr(num int(11))
#为了容易区分那个函数或者存储过程是那个数据库的，
#可以在函数名中加上数据库前缀test_randstr
    begin
    declare str char(255)  default 'q1we23r4t5y6u7i8o9p0asdfghjklzxcvbnm';
    declare nums int(11);
    declare returnstr char(255);#SQL变量名不能和列名一样
    declare i int(11) default  0;
    select floor(truncate(rand(),1)*36)+1 into nums;
    select substring(str,nums,1)  into returnstr;
    while  i <num do
    select floor(truncate(rand(),1)*36)+1 into nums;
    select concat(substring(str,nums,1),returnstr) into returnstr;
    #set returnstr=concat(substring(str,nums,1),returnstr);
    set i=i+1;
    end while;
    select returnstr as randstr;
    end
    $$
    delimiter ;
call randstr(5);

查看函数和存储过程的详情

show function status like '%rand%'; 
#查看函数的状态 包括：函数所属数据库，函数名，类型，创建时间，创建者，
#安全类型 注释 ，数据库字符集，客户端字符集
show procedure status like '%procedure_name';  #同上,这是存储过程

总结

• set关键字 声明一个全局变量
• declare 关键字声明一个局部变量
• 函数内部不能同时有局部变量和全局变量的存在
• end if 一般条件控制语句都会分开写
• 建议为存储过程添函数添加数据库前缀
• 存储过程可以返回表的数据，函数不可以
• SQL变量名不能和列名一样
• 在声明变量时一定要在begin语句之后，除begin外的任何语句之前