MySQL function usage (Getting started)

Goal

How to create a function in the MySQL database

Grammar

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

• The CREATE function keyword used for creating functions;
• Func_name represents the name of the function;
• Func_parameters is the parameter list for the function, and the parameter list is: [in| Out| INOUT] Param_name Type

1. In: indicates input parameters;
2. Out: Indicates the output parameter;
3. INOUT: Indicates that both input and output can be entered;
4. Param_name: Indicates the name of the parameter;
5. Type: Represents the types of parameters, which can be any type in the MySQL database;

• RETURNS Type: The statement indicates the types of data returned by the function;
• Characteristic: Specify the properties of the stored function, the same value as stored procedures, detailed access to the-mysql stored procedures used;

Example

Create a sample database, a 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 mysql--function is used in the same way as the MySQL internal function.

< more highlights, see later update ...>

If you have any problems in the process of trying, or if my code is wrong, please correct me, thank you very much!

Contact information: [Email protected]

Copyright @: Reprint please indicate the source!

MySQL function Use (Getting started)