MySQL creates a Function and mysqlfunction
Target
How to Create a Function in a MySQL database)
Syntax
Create function func_name ([func_parameter]) // brackets are required. The parameter is optional. RETURNS type [characteristic...] routine_body
- Keywords used by create function to CREATE a FUNCTION;
- Func_name indicates the function name;
- Func_parameters is a list of function parameters IN the form of [IN | OUT | INOUT] param_name type.
- RETURNS type: the type of data returned by the function;
- Characteristic: Specifies the features of the stored function. The values are the same as those in the stored procedure. For details, see-MySQL Stored Procedure usage;
Example
Create a sample database, a sample table, and a script to insert sample data:
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, interval int (11); insert into employees) values ('David tid', 'male', 10, 7500, 1); insert into employees (employee_name, employee_sex, employee_mgr, employee_salary, department_id) values ('black xie ', 'male', 1); insert into employees (employee_name, employee_sex, employee_mgr, employee_salary, department_id) values ('Moses Wang ', 'male', 1 ); insert into employees (employee_name, employee_sex, employee_mgr, region, region) values ('rena ruand', 'female ', 1); insert into employees (employee_name, employee_sex, employee_mgr, employee_salary, department_id) values ('Sunshine Ma', 'female ', 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', 2); insert into employees (employee_name, employee_sex, employee_mgr, employee_salary, department_id) values ('kaishen yang', 'male', 3 ); insert into employees (employee_name, employee_sex, employee_mgr, region, region) values ('simon song', 'male', 3); insert into employees (employee_name, employee_sex, employee_mgr, employee_salary, department_id) values ('brown guany', 'male', 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 ', 'femal', 4); insert into employees (employee_name, employee_sex, employee_mgr, employee_salary, department_id) values ('klause He ', 'male', 5 ); insert into employees (employee name, employee) values ('maven Ma', 'male', 6); insert into employees (employee name, employee _ sex, employee _ Mgr, employee_salary, department_id) values ('stefan Wang ', 'female', 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', 8); insert into employees (employee_name, employee_sex, employee_mgr, employee_salary, department_id) values ('derek wu', 'male', 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 ;
Call a function
The usage of functions in MySQL is the same as that in MySQL internal functions.
<For more highlights, refer to the update later...>
If you encounter any problems during your attempt or my code is incorrect, please correct me. Thank you very much!
Contact: david.louis.tian@outlook.com
Copyright @: reprinted, please indicate the source!
Create a function in mysql
Similar to a stored procedure in mysql
Creat procedure...
See www. thosedays. us/...8.html
Failed to create a function in mysql (you can create a function)
Your table value function is unique to SQL Server.
That is, a function that returns a result set.
MySQL does not seem to support table-valued functions. (Currently, the latest version does not support this feature. You need to check the documentation)
You can try to modify it to the processing of the result set returned by the stored procedure.
DELIMITER // create definer = 'root' @ '%' PROCEDURE testProc () begin select 'Hello 1' as, 'World 1' as B union all select 'Hello 2' as a, 'World 2' as B; END // DELIMITER; mysql> call testProc (); + --------- + | A | B | + --------- + | Hello 1 | World 1 | Hello 2 | World 2 | + --------- + 2 rows in set (0.00 sec) query OK, 0 rows affected (0.01 sec)