• 技术文章 >数据库 >mysql教程

    怎样在mysql中创建函数

    藏色散人藏色散人2020-10-08 17:30:55原创116

    在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 '男',
        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内部函数的使用方法一样。

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

    本文原创发布php中文网,转载请注明出处,感谢您的尊重!
    专题推荐:mysql
    上一篇:mysql求长度的函数是什么? 下一篇:如何解决mysql config 缺失问题
    第13期线上培训班

    相关文章推荐

    • mysql可以做日期处理吗• mysql里中文占多少个字节?• 怎么查看mysql是否成功启动?• mysql求长度的函数是什么?

    全部评论我要评论

  • 取消发布评论发送
  • 1/1

    PHP中文网