MySQL存储过程与函数

权限

创建存储子程序需要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外的任何语句之前
你的欣赏是我最大的动力

Yanxiong Huang

My name is Yanxiong Huang. graduated from Nanyang middle school.Love Linux,familiar with Node.js,Docker,Serverless... and more Web technology.Contact Me:QQ 31356617;Email:huangaynxiong2013@gmail.com

guangzhou,china http://www.myfreax.com

乐在分享