MySQL 创建函数(Function)

最新推荐文章于 2025-02-14 14:28:06 发布

weixin_34126557

最新推荐文章于 2025-02-14 14:28:06 发布

阅读量800

点赞数

文章标签：数据库

本文介绍如何使用MySQL创建数据库函数，并通过实例演示了如何定义一个获取员工姓名与工资的函数。

目标

怎么样MySQL创建数据库功能(Function)

语法

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

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

IN：表示输入參数。
OUT：表示输出參数；
INOUT：表示既能够输入也能够输出；
param_name：表示參数的名称；
type：表示參数的类型，该类型能够是MySQL数据库中的随意类型；

RETURNS type：语句表示函数返回数据的类型；
characteristic: 指定存储函数的特性，取值与存储过程时同样，具体请訪问－MySQL存储过程使用;

演示样例

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

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 ;