MySQL creation functions (function)

Goal

What about MySQL? Creating database functions (function)

Grammar

CREATE FUNCTION func_name ([func_parameter])//parentheses are required, the parameters are optional returns type[characteristic ...] routine_body

• Create function is used for creating functions of the keyword;
• Func_name represents the name of the function;
• Func_parameters is the list of functions, the form of the list of references is: [in| Out| INOUT] Param_name Type

1. In: Indicates the input parameters.
2. Out: Indicates the output parameter;
3. INOUT: Indicates that both input and output are possible;
4. Param_name: Indicates the name of the parameter;
5. Type: Represents the kind of the parameter, which can be a random type in the MySQL database;

• RETURNS Type: The statement indicates the types of data returned by the function;
• Characteristic: Specifies the properties of the stored function, as well as the stored procedure, please visit the-mysql stored procedure use;

Demo sample

Create a demo sample database, a demo sample table, and an Insert sample data script:

Create DATABASE Hr;use hr;create table Employees (employee_id Int (one) primary key not NULL auto_increment,employee_name var char (a) not null,employee_sex varchar (ten) Default ' Male ', hire_date datetime NOT NULL default Current_timestamp,employee_ Mgr Int (one), employee_salary float default 3000,department_id int (one)), 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 ', ' female ', 10,5300,1); INSERT into employees (Employee_name,employee_sex,employee_mgr,employee_salary, department_id) VALUES (' Sunshine Ma ', ' female ', 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 ', ' female ', 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 ', ' female ', 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's name and employee's salary by ID

DELIMITER//create FUNCTION Getemployeeinformationbyid (id INT) RETURNS VARCHAR Beginreturn (SELECT CONCAT (' Employee Name: ', Employee_Name, '---', ' salary: ', employee_salary) from Employees WHERE Employee_id=id); End//delimiter;

calling Functions

The use of the mysql--function is the same as the usage of the MySQL intrinsic function.

< lots of other exciting content. See later update ...>

Let me know if you are experiencing any problems in the course of your attempts or where my code is wrong.

Contact information: [Email protected]

Copyright @: Reprint please indicate the source.

Copyright notice: This article blog original articles, blogs, without consent, may not be reproduced.

MySQL creation functions (function)