1. 对存储程序的说明
与应用程序(Java或者.NET或者PHP等应用程序)相比,存储程序可维护性高,更新存储程序通常比更改、测试以及重新部署应用程序需要更少的时间和精力。使用存储程序与使用大量离散的SQL语句写出的应用程序相比,更易于代码优化、重用和维护。
不过这并不意味着可以将所有的业务逻辑代码全部封装成存储程序,把业务处理的所有负担全部压在数据库服务器上。事实上数据库服务器的核心任务是存储数据,保证数据的安全性、完整性以及一致性,如果数据库承担了过多业务逻辑方面的工作,势必会对数据库服务器的性能造成负面影响。
对于简单的业务逻辑,在不影响数据库性能的前提下,为了节省网络资源,可以将业务逻辑封装成存储程序。
对于较为复杂的业务逻辑,建议使用高级语言(Java或者.NET或者PHP等)实现,让应用服务器(例如Apache、 IIS等) 承担更多的业务逻辑,保持负载均衡。
最后,无论初学者还是有经验的数据库开发人员,都应该对自己开发的存储程序进行严格的测试,并尽量保存测试步骤、测试数据以及测试结果。
2. 创建函数 create function
创建函数的语法如下:
create [definer = {user|current_user}] function [函数名](参数列表) returns [返回值类型] [函数特性] begin [SQL代码] end -- definer 用于指定存储过程由哪个用户定义,默认是当前用户,注意不是指定存储过程的使用权限 -- [参数列表],参数列表只支持in类型的参数,因此写参数时不需要写in,因为默认就是in -- [函数特征] 特征是存储过程的属性,它包括了如下几个可选特征 -- -- -- comment '' 注释信息,例如:commet '这是一个存储过程' -- -- -- language sql 指定存储过程使用的语言为sql -- -- -- [not] deterministic 是否指定一个输入仅对应一个输出(映射),包含下面2个可选参数 -- -- -- -- -- not deterministic(默认),表示不指定映射关系; -- -- -- -- -- deterministic,表示指定映射关系 -- -- -- [contains sql | no sql | reads sql data | modifies sql data] 明确子程序对数据的操作,包含下面4个可选参数 -- -- -- -- -- contains sql(默认),表示子程序不包含读或者写数据的语句 -- -- -- -- -- no sql,表示子程序不包含sql -- -- -- -- -- reads sql data,表示子程序包含读数据的语句,但是不包含写数据的语句 -- -- -- -- -- modifies sql data,表示子程序包含写数据的语句 -- -- -- sql security [definer|invoker] 指定调用权限,包含下面两个可选参数 -- -- -- -- -- sql security definer(默认),使用创建者权限调用存储过程,不受限制 -- -- -- -- -- sql security invoker,使用调用者权限调用存储过程,只有被赋予权限的调用者才能调用
例如:
create function test(id int, user_name varchar(20)) returns int(5) deterministic begin if user_name='111' then return 1; else return 0; end if; end
3. 调用函数 select
自定义函数的调用十分简单,和sql内置函数一样的使用方法,就是直接select就行
例如:
-- 定义函数 create function test(id int, user_name varchar(20)) returns int(5) deterministic begin if user_name='111' then return 1; else return 0; end if; end -- 调用函数 select test(1, '111');
4. 查看函数
4.1. 查看函数状态 show status
查看函数状态的语法如下:
show function status like '[函数名]';
例如:
show function status like 'test';
结果样例如下表所示:
4.2. 查看函数定义 show create
查看函数定义的语法如下:
show create function [函数名];
例如:
show create function test;
结果样例如下表所示:
4.3. 查看全部函数信息 information_schema.Routines
同存储过程一样,MySQL中函数的信息存储在information_schema数据库下的Routines表中。可以通过查询该表的记录来查询存储过程和函数的信息。
例如:
select * from information_schema.routines where routine_name='test5';
结果如下表所示:
5. 修改函数 alter function
修改函数的特性可以使用alter function关键字,语法如下:
alter function [函数名] [特性];
例如:
-- 修改函数test,使它可以写数据(modifies sql data) alter function test modifies sql data;
6. 删除函数 drop function
删除函数可以使用drop function关键字,语法如下:
drop function [if exists] [函数名]
例如有一个函数test,则要删除它,可以写成:
-- 如果函数test存在,则删除它 drop procedure if exists test;