MySQL 建立函數(Function)，mysqlfunction

MySQL 建立函數(Function)，mysqlfunction
目標

如何在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

• 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)BEGINRETURN(SELECT CONCAT('employee name:',employee_name,'---','salary: ',employee_salary) FROM employees WHERE employee_id=id);END//DELIMITER ;

調用函數

在MySQL——函數的使用方法與MySQL內建函式的使用方法一樣。

<更多精彩內容，見後面更新...>

如果您們在嘗試的過程中遇到什麼問題或者My Code有錯誤的地方，請給予指正，非常感謝！

連絡方式：david.louis.tian@outlook.com

著作權＠：轉載請標明出處！

mysql 建立function

mysql中類似的叫預存程序

creat procedure ...

參見www.thosedays.us/...8.html
 
在mysql中建立函數不成功（確定可以建立函數）

你那個是 SQL Server 特有的 資料表值函式。
也就是一個函數， 返回一個結果集合的。
MySQL 好像是不支援資料表值函式的樣子。 （現在最新的版本支援不支援， 你需要去看看 文檔了）

你可以嘗試修改成 預存程序 返回結果集的處理。

DELIMITER //CREATE DEFINER=`root`@`%` PROCEDURE testProc()BEGIN SELECT 'Hello 1' AS A, '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)