怎样在mysql中创建函数

在mysql中可以通过语法“CREATE FUNCTION func_name ( [func_parameter] )”来创建函数，其中“CREATE FUNCTION”是用来创建函数的关键字。

推荐：《mysql视频教程》

在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: 指定存储函数的特性，取值与存储过程时相同，详细请访问－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 &＃39;男&＃39;,
    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 (&＃39;David Tian&＃39;,&＃39;男&＃39;,10,7500,1);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (&＃39;Black Xie&＃39;,&＃39;男&＃39;,10,6600,1);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (&＃39;Moses Wang&＃39;,&＃39;男&＃39;,10,4300,1);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (&＃39;Rena Ruan&＃39;,&＃39;女&＃39;,10,5300,1);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (&＃39;Sunshine Ma&＃39;,&＃39;女&＃39;,10,6500,2);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (&＃39;Scott Gao&＃39;,&＃39;男&＃39;,10,9500,2);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (&＃39;Warren Si&＃39;,&＃39;男&＃39;,10,7800,2);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (&＃39;Kaishen Yang&＃39;,&＃39;男&＃39;,10,9500,3);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (&＃39;Simon Song&＃39;,&＃39;男&＃39;,10,5500,3);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (&＃39;Brown Guan&＃39;,&＃39;男&＃39;,10,5000,3);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (&＃39;Eleven Chen&＃39;,&＃39;女&＃39;,10,3500,2);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (&＃39;Cherry Zhou&＃39;,&＃39;女&＃39;,10,5500,4);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (&＃39;Klause He&＃39;,&＃39;男&＃39;,10,4500,5);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (&＃39;Maven Ma&＃39;,&＃39;男&＃39;,10,4500,6);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (&＃39;Stephani Wang&＃39;,&＃39;女&＃39;,10,5500,7);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (&＃39;Jerry Guo&＃39;,&＃39;男&＃39;,10,8500,1);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (&＃39;Gerardo Garza&＃39;,&＃39;男&＃39;,10,25000,8);
    insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values (&＃39;Derek Wu&＃39;,&＃39;男&＃39;,10,5500,5);
select * from employees;

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

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

调用函数

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

以上就是怎样在mysql中创建函数的详细内容，更多请关注其它相关文章！