MySQL 创建函数(Function)

目标

如何在MySQL数据库中创建函数(Function)

语法

CREATE FUNCTION func_name ( [func_parameter] ) //括号是必须的，参数是可选的
RETURNS type
[ characteristic ...] routine_body

CREATE FUNCTION 用来创建函数的关键字； func_name 表示函数的名称； func_parameters为函数的参数列表，参数列表的形式为：[IN|OUT|INOUT] param_name type

IN：表示输入参数； OUT：表示输出参数； INOUT：表示既可以输入也可以输出； param_name：表示参数的名称； type：表示参数的类型，该类型可以是MySQL数据库中的任意类型；

RETURNS type：语句表示函数返回数据的类型； characteristic: 指定存储函数的特性，取值与存储过程时相同，;

示例

创建示例数据库、示例表与插入样例数据脚本：

create database hr;
use hr;

create table employees
(
	employee_id int(11) primary key not null auto_increment,
	employee_name varchar(50) not null,
	employee_sex varchar(10) default 男,
	hire_date datetime not null default current_timestamp,
	employee_mgr int(11),
	employee_salary float default 3000,
	department_id int(11)
);


insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (David Tian,男,10,7500,1);
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (Black Xie,男,10,6600,1);
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (Moses Wang,男,10,4300,1);
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (Rena Ruan,女,10,5300,1);
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (Sunshine Ma,女,10,6500,2);
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (Scott Gao,男,10,9500,2);
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (Warren Si,男,10,7800,2);
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (Kaishen Yang,男,10,9500,3);
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (Simon Song,男,10,5500,3);
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (Brown Guan,男,10,5000,3);
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (Eleven Chen,女,10,3500,2);
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (Cherry Zhou,女,10,5500,4);
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (Klause He,男,10,4500,5);
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (Maven Ma,男,10,4500,6);
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (Stephani Wang,女,10,5500,7);
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (Jerry Guo,男,10,8500,1);
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (Gerardo Garza,男,10,25000,8);
insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (Derek Wu,男,10,5500,5);


select * from employees;

创建函数－根据ID获取员工姓名与员工工资

DELIMITER //
CREATE FUNCTION GetEmployeeInformationByID(id INT)
RETURNS VARCHAR(300)
BEGIN
	RETURN(SELECT CONCAT(employee name:,employee_name,---,salary: ,employee_salary) FROM employees WHERE employee_id=id);
END//
DELIMITER ;

调用函数

在MySQL——函数的使用方法与MySQL内部函数的使用方法一样。

<更多精彩内容，见后面更新...>

如果您们在尝试的过程中遇到什么问题或者我的代码有错误的地方，请给予指正，非常感谢！

联系方式：david.louis.tian@outlook.com