mysql的存储函数

创建存储函数的语法

create function function_name ([param_name type[,...]])
returns type  
[characteristic...]
beginroutine_body  
end;

– function_name：要创建的存储函数的名字

– param_name type：指定存储函数的参数及参数类型，属于可选项

– returns type：返回值的类型

– characteristic：存储函数的特性，可选项

– routine_body：函数体，sql代码

示例：

– 创建数据库demo6

create database demo_function;
use demo_function;

– 创建部门表，并插入数据

create table dept(dno varchar(8) primary key,dname varchar(8),local varchar(8)
)charset=utf8;insert into dept values(10, '研发部', '北京'),(20,'生产部','上海'),(30,'销售部','广州'),(40,'财务部','武汉');

– 创建员工表，并插入数据

create table emp(eno varchar(8) primary key,ename varchar(8),job varchar(16),mgr_id varchar(8),hiredate date,salary double,bonus double,e_dno varchar(8),
foreign key (e_dno) references dept (dno)
)charset=utf8;insert into emp values('1001','阿紫','文员','1013','2000-12-17',8000,null,'20'),
('1002','阿朱','销售员','1006','2001-02-20',16000,3000,'30'),
('1003','段誉','销售员','1006','2001-02-22',12500,5000,'30'),
('1004','乔峰','经理','1009','2001-04-02',29750,null,'20'),
('1005','诸葛亮','销售员','1006','2001-09-28',12500,14000,'30'),
('1006','洪七公','经理','1009','2001-05-01',28500,null,'30'),
('1007','黄药师','经理','1009','2001-09-01',24500,null,'10'),
('1008','黄蓉','分析师','1004','2007-04-19',30000,null,'20'),
('1009','成吉思汗','董事长',null,'2001-11-17',50000,null,'10'),
('1010','周瑜','销售员','1006','2001-09-08',15000,0,'30'),
('1011','黄盖','文员','1008','2007-05-23',11000,null,'20'),
('1012','黄忠','文员','1006','2001-12-03',9500,null,'30'),
('1013','鲁肃','分析师','1004','2001-12-03',30000,null,'20'),
('1014','蒋干','文员','1007','2002-01-23',13000,null,'10');

– 创建工资等级表并插入数据

create table grade(grade int(8),l_salary double,h_salary double
)charset=utf8;insert into grade values(1,7000,12000),(1,12010,14000),
(3,14010,20000),(4,20010,30000),(5,30010,99990);

–无参数方式返回emp表中员工的数量

set global log_bin_trust_function_creators = TRUE; -- 临时允许创建函数权限信任，永久允许需在ini文件中添加drop function if exists func001();delimiter //
create function func001()
returns int
begindeclare cun int default 0;select count(*) into cun from emp;return cun;
end //
delimiter ;select func001(); --调用存储函数

– 带参数方式，输入员工编号，返回员工姓名

set global log_bin_trust_function_creators = TRUE;drop function if exists func002;delimiter //
create function func002(in_id varchar(8))
returns varchar(8)
begindeclare name varchar(8);select ename into name from emp where eno = in_id;return name;
end //
delimiter ;select func002('1001')