Shulou(Shulou.com)06/01 Report--

This article mainly introduces the method of creating functions in mysql. The introduction in this article is very detailed and has certain reference value. Interested friends must read it!

Functions can be created in mysql with the syntax "CREATE FUNCTION func_name ( [func_parameter] )," where "CREATE FUNCTION" is the keyword used to create the function.

Creating Functions in MySQL Database

syntax

CREATE FUNCTION func_name ( [func_parameter] ) //parenthesis is required, parameter is optional RETURNS type[ characteristic...] routine_body

CREATE FUNCTION The keyword used to create the function;

func_name indicates the name of the function;

func_parameters is the parameter list of the function, and the parameter list is of the form: [IN| OUT| INOUT] param_name type

IN: indicates input parameters;

OUT: indicates output parameters;

INOUT: indicates that both input and output are possible;

param_name: indicates the name of the parameter;

type: indicates the type of parameter, which can be any type in MySQL database;

RETURNS type: The statement indicates the type of data returned by the function;

characteristic: Specify the characteristic of the stored function, the value is the same as that of the stored procedure, please visit-MySQL stored procedure use for details;

example

Creating sample databases, sample tables, and inserting sample data scripts:

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 'male', 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','male', 10,7500,1); insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Black Xie','male', 10,6600,1); insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Moses Wang','male', 10,4300,1); insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Rena Ruan','women', 10,5300,1); insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Sunshine Ma','women', 10,6500,2); insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Scott Gao','male', 10,9500,2); insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Warren Si','male', 10,7800,2); insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Kaishen Yang','male', 10,9500,3); insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Simon Song','male', 10,5500,3); insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Brown Guan','male', 10,5000,3); insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Eleven Chen','Female', 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','male', 10,4500,5); insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Maven Ma','male', 10,4500,6); insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Stephani Wang','women', 10,5500,7); insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Jerry Guo','male', 10,8500,1); insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Gerardo Garza','male', 10,25000,8); insert into employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) values ('Derek Wu','male', 10,5500,5);select * from employees;

Create Function-Get employee name and employee salary based on ID

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

calling function

In MySQL--functions are used in the same way as MySQL internal functions.

That's all for creating functions in mysql, thanks for reading! Hope to share the content to help everyone, more relevant knowledge, welcome to pay attention to the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.