MySql存储过程及函数

　　存储过程和函数类似于Java中的方法。

⒈存储过程

　　一组预先编译好的sql语句的集合，理解成批处理语句。

　　好处：

　　　　①提高代码的重用性

　　　　②简化操作

　　　　③减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

　　1.创建语法　

create procedure 存储过程名称(参数列表)
begin
    存储过程体(一组合法的sql语句)
end

　　*参数列表包含了三部分

　　　　①参数模式　　例如：IN

　　　　②参数名　　例如：username

　　　　③参数类型　　例如：varchar(20)

　　参数模式：

参数模式	说明
IN	该参数可以作为输入，也就是说该参数需要调用方传入值。
OUT	该参数可以作为输出，也就是说该参数可以作为返回值。
INOUT	该参数既可以作为输入又可以作为输出，也就是该参数既需要 传入值，又可以返回值

　　*如果存储过程体仅只有一句话，那么begin end可以省略

　　*存储过程中的每条sql语句的结尾必须加分号

　　*存储过程的结尾标记可以使用delimiter重新设置。

　　　　语法：delimiter 结束标记

　　　　示例：delimiter $

　　2.调用语法。

call 存储过程名(实参列表);

　　3.删除存储过程

drop procedure 存储过程名;    #一次只能删除一个

　　4.查看存储过程的信息

show create procedure 存储过程名;

 

　　示例：

　　　　①空参列表

　　　　　　插入到users表中2条记录

delimiter $$
create procedure test1()
begin
    insert into users(UserName,`PassWord`) values('fanqi','admin'),('gaoxing','admin');
end$$

call test1();

　　　　②创建带in模式参数的存储过程

　　　　　　创建存储过程用于判断用户是否登录成功

delimiter $$
create procedure test2(in username VARCHAR(25),in `password` varchar(25))
begin
    declare result int default 0;    #声明一个局部变量用于保存是否登录成功
    select count(*) into result from users where users.UserName = username and users.`PassWord` = password;
    select if(result > 0,'登录成功','登录失败');
end$$

call test2('fanqi','admin');

　　　　③创建带out模式参数的存储过程

　　　　　　根据用户名，返回该用户对应的密码

create procedure test3(in username VARCHAR(25),out `password` varchar(25))
begin
    select users.`PassWord` into `password` from users where users.UserName = username limit 1;
end$$

set @password;
call test3('fanqi',@password);
select @password;

　　　　④创建带inout模式参数的存储过程

　　　　　　传入a和b两个值，将a和b的值翻倍后返回

delimiter $$
create procedure test4(inout a int,inout b int)
begin
    set a = a * 2;
    set b = b * 2;
end$$

set @a = 10, @b = 20;
call test4(@a,@b);
select @a,@b;

 

⒉函数

　　函数和存储过程意义和好处都是一样的。它们的唯一区别就是：

　　　　存储过程可以没有返回值，也可以有多个返回值，适合做批量插入数据、批量更新等。

　　　　函数必须有返回值，而且只能有1个，适合做处理数据后返回1个结果。

 

　　1.创建语法

create function 函数名(参数列表) returns 返回类型
begin
    函数体
end

　　*参数列表包含两部分

　　　　①参数名　　例如：username

　　　　②参数类型　　例如：varchar(20)

　　*函数体中必须有return语句，如果没有会报错。建议将return语句放在函数体的末尾，当然，没在末尾也不报错。

　　*函数体中仅有一句话，则可以省略begin end

　　*函数的结尾标记也可以使用delimiter重新设置。

 

　　2.调用语法

select 函数名(参数列表);

　　3.删除函数

drop function 函数名;

　　4.查看函数

show create function 函数名;

 

　　示例：

　　　　①没有参数（函数必须有返回值，而且只能有1个）

　　　　　　返回users表的条目数

set global log_bin_trust_function_creators=TRUE;
delimiter $$
create function test1() returns int
begin
    declare num int default 0;
    select count(*)  into num from users;
    return num;
end$$

select test1();

　　　　②有参数（函数必须有返回值，而且只能有1个）

　　　　　　根据用户名，返回该用户对应的密码

set global log_bin_trust_function_creators=TRUE;
delimiter $$
create function test2(username varchar(20)) returns varchar(20)
begin
    set @password = '';
    select users.`PassWord` into @password from users where users.UserName = username;
    return @password;
end$$

select test2('fanqi');