您好,登錄后才能下訂單哦!
這篇文章主要介紹在mysql中創(chuàng)建函數(shù)的方法,文中介紹的非常詳細(xì),具有一定的參考價(jià)值,感興趣的小伙伴們一定要看完!
在mysql中可以通過語法“CREATE FUNCTION func_name ( [func_parameter] )”來創(chuàng)建函數(shù),其中“CREATE FUNCTION”是用來創(chuàng)建函數(shù)的關(guān)鍵字。
在MySQL數(shù)據(jù)庫(kù)中創(chuàng)建函數(shù)(Function)
語法
CREATE FUNCTION func_name ( [func_parameter] ) //括號(hào)是必須的,參數(shù)是可選的 RETURNS type [ characteristic ...] routine_body
CREATE FUNCTION 用來創(chuàng)建函數(shù)的關(guān)鍵字;
func_name 表示函數(shù)的名稱;
func_parameters為函數(shù)的參數(shù)列表,參數(shù)列表的形式為:[IN|OUT|INOUT] param_name type
IN:表示輸入?yún)?shù);
OUT:表示輸出參數(shù);
INOUT:表示既可以輸入也可以輸出;
param_name:表示參數(shù)的名稱;
type:表示參數(shù)的類型,該類型可以是MySQL數(shù)據(jù)庫(kù)中的任意類型;
RETURNS type:語句表示函數(shù)返回?cái)?shù)據(jù)的類型;
characteristic: 指定存儲(chǔ)函數(shù)的特性,取值與存儲(chǔ)過程時(shí)相同,詳細(xì)請(qǐng)?jiān)L問-MySQL存儲(chǔ)過程使用;
示例
創(chuàng)建示例數(shù)據(jù)庫(kù)、示例表與插入樣例數(shù)據(jù)腳本:
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;
創(chuàng)建函數(shù)-根據(jù)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 ;
調(diào)用函數(shù)
在MySQL——函數(shù)的使用方法與MySQL內(nèi)部函數(shù)的使用方法一樣。
以上是在mysql中創(chuàng)建函數(shù)的方法的所有內(nèi)容,感謝各位的閱讀!希望分享的內(nèi)容對(duì)大家有幫助,更多相關(guān)知識(shí),歡迎關(guān)注億速云行業(yè)資訊頻道!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。