在mysql中可以通過語法“CREATE FUNCTION func_name ( [func_parameter] )”來創建函數，其中“CREATE FUNCTION”是用來創建函數的關鍵字。
推薦：《mysql視頻教程》
在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
IN：表示輸入參數；
OUT：表示輸出參數；
INOUT：表示既可以輸入也可以輸出；
param_name：表示參數的名稱；
type：表示參數的類型，該類型可以是MySQL數據庫中的任意類型；
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) BEGIN RETURN(SELECT?CONCAT('employee?name:',employee_name,'---','salary:?',employee_salary)?FROM?employees?WHERE?employee_id=id); END// DELIMITER?;
調用函數
在MySQL——函數的使用方法與MySQL內部函數的使用方法一樣。
? 版權聲明
文章版權歸作者所有，未經允許請勿轉載。
THE END
喜歡就支持一下吧
相關推薦
