怎样在mysql中创建函数_编程技术_编程开发技术教程

怎样在mysql中创建函数_编程技术_编程开发技术教程

服务器 VIP24小时自助开通

编程技术

怎样在mysql中创建函数_编程技术_编程开发技术教程插图

推荐:《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中文网如有侵权请联系admin#tyuanma.cn删除

php如何修改域名的指定的根目录

分享到 :
服务器 VIP24小时自助开通
相关推荐

mysql怎么删除数据表中的行数据(记录)?_编程技术_编程开发技术教程

mysql怎么删除数据表中的行数据(记录)?_编程技术_编程开发技术教程

mysql中有没有事务?_亿码酷站_编程开发技术教程

mysql中有没有事务?_亿码酷站_编程开发技术教程

php5.3 mysql 无法加载怎么办_编程技术_编程开发技术教程

php5.3 mysql 无法加载怎么办_编程技术_编程开发技术教程

如何在控制台修改mysql字符集_编程技术_亿码酷站

如何在控制台修改mysql字符集_编程技术_亿码酷站