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外的任何语句之前
全部为采集文章,文中的 联系方式 均不是 本人 的!

发表评论