怎樣在mysql中建立函數

在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內部函數的使用方法一樣。

以上是怎樣在mysql中建立函數的詳細內容。更多資訊請關注PHP中文網其他相關文章！