#定义函数
delimiter $$
create function getGameName(gameid int) returns VARCHAR(45)
DETERMINISTIC
begin
declare name VARCHAR(45);
set name=(select gamename from cy_game where id=gameid);
return (name);
end$$
delimiter;
select getGameName(4);#使用
select id,getGameName(4) from cy_game where id=4;#使用
show function status; //函数
show create function func_name;
DELIMITER $$
drop function if exists getPayOkSettlementAgent;
CREATE FUNCTION getPayOkSettlementAgent(regagent varchar(45)) returns VARCHAR(45)
DETERMINISTIC
BEGIN
DECLARE userId,secondAgentUserId,oneAgentUserId,settlementId,secondAgentSettlementId,oneAgentSettlementId int;
DECLARE curAgent,secondAgent,oneAgent,settlementAgent varchar(45);
SELECT id,agent,second_agent,one_agent INTO userId,curAgent,secondAgent,oneAgent FROM cy_ct_cps_user WHERE agent=regagent;
SET settlementId=(select id from cy_ct_cps_settlement where uid=userId and status=0);
IF(settlementId) THEN
SET settlementAgent=curAgent;
ELSE
SELECT id INTO secondAgentUserId FROM cy_ct_cps_user WHERE agent=secondAgent;
SET secondAgentSettlementId=(select id from cy_ct_cps_settlement where uid=secondAgentUserId and status=0);
IF(secondAgentSettlementId) then
SET settlementAgent=secondAgent;
ELSE
SELECT id INTO oneAgentUserId FROM cy_ct_cps_user WHERE agent=oneAgent;
SET oneAgentSettlementId=(select id from cy_ct_cps_settlement where uid=oneAgentUserId and status=0);
IF(oneAgentSettlementId) THEN
SET settlementAgent=oneAgent;
ELSE
SET settlementAgent='';
END IF;
END IF;
END IF;
RETURN (settlementAgent);
END$$
DELIMITER;
//创建函数
create function addAge(age int) returns int
return age + 5;
//使用函数:
select addAge(age) from temp;
//删除函数
drop function if exists addAge;
drop function addAge;
//显示创建语法
show create function addAge;
存储的函数是返回单个值的特殊类型的存储程序。您使用存储的函数来封装在SQL语句或存储的程序中可重用的常用公式或业务规则。
与存储过程不同,您可以在SQL语句中使用存储的函数,也可以在表达式中使用。 这有助于提高程序代码的可读性和可维护性。
MySQL存储函数语法
以下说明了创建新存储函数的最简单语法:
CREATE FUNCTION function_name(param1,param2,…)
RETURNS datatype
[NOT] DETERMINISTIC
statements
首先,在CREATE FUNCTION
子句之后指定存储函数的名称。
其次,列出括号内存储函数的所有参数。 默认情况下,所有参数均为IN
参数。不能为参数指定IN
,OUT
或INOUT
修饰符。
第三,必须在RETURNS
语句中指定返回值的数据类型。它可以是任何有效的MySQL数据类型。
第四,对于相同的输入参数,如果存储的函数返回相同的结果,这样则被认为是确定性的,否则存储的函数不是确定性的。必须决定一个存储函数是否是确定性的。 如果您声明不正确,则存储的函数可能会产生意想不到的结果,或者不使用可用的优化,从而降低性能。
第五,将代码写入存储函数的主体中。 它可以是单个语句或复合语句。 在主体部分中,必须至少指定一个RETURN
语句。RETURN
语句用于返回一个值给调用者。 每当到达RETURN
语句时,存储的函数的执行将立即终止。
MySQL存储函数示例
我们来看一下使用存储函数的例子,这里将使用示例数据库(yiibaidb)中的customers
表进行演示。
以下示例是根据信用额度返回客户级别的功能。 我们使用IF语句来确定信用额度。
DELIMITER $$
CREATE FUNCTION CustomerLevel(p_creditLimit double) RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
DECLARE lvl varchar(10);
IF p_creditLimit > 50000 THEN
SET lvl = 'PLATINUM';
ELSEIF (p_creditLimit <= 50000 AND p_creditLimit >= 10000) THEN
SET lvl = 'GOLD';
ELSEIF p_creditLimit < 10000 THEN
SET lvl = 'SILVER';
END IF;
RETURN (lvl);
END $$
DELIMITER ;
现在,我们在SELECT语句中调用CustomerLevel()
存储函数,如下所示:
SELECT
customerName, CustomerLevel(creditLimit)
FROM
customers
ORDER BY customerName;
执行上面查询语句,得到以下结果 -
+------------------------------------+----------------------------+
| customerName | CustomerLevel(creditLimit) |
+------------------------------------+----------------------------+
| Alpha Cognac | PLATINUM |
| American Souvenirs Inc | SILVER |
| Amica Models & Co. | PLATINUM |
| ANG Resellers | SILVER |
| Anna's Decorations, Ltd | PLATINUM |
| Anton Designs, Ltd. | SILVER |
| Asian Shopping Network, Co | SILVER |
| Asian Treasures, Inc. | SILVER |
| Atelier graphique | GOLD |
| Australian Collectables, Ltd | PLATINUM |
| Australian Collectors, Co. | PLATINUM |
|************** 此处省略了一大波数据 *********************************|
| Vitachrome Inc. | PLATINUM |
| Volvo Model Replicas, Co | PLATINUM |
| Warburg Exchange | SILVER |
| West Coast Collectables Co. | PLATINUM |
+------------------------------------+----------------------------+
122 rows in set
下面,来重写在MySQL IF语句教程中开发的GetCustomerLevel()
存储过程,如下所示:
DELIMITER $$
CREATE PROCEDURE GetCustomerLevel(
IN p_customerNumber INT(11),
OUT p_customerLevel varchar(10)
)
BEGIN
DECLARE creditlim DOUBLE;
SELECT creditlimit INTO creditlim
FROM customers
WHERE customerNumber = p_customerNumber;
SELECT CUSTOMERLEVEL(creditlim)
INTO p_customerLevel;
END $$
DELIMITER ;
如您所见,GetCustomerLevel()
存储过程在使用CustomerLevel()
存储函数时可读性更高。
请注意,存储函数仅返回单个值。 如果没有包含INTO
子句的SELECT
语句,则将会收到错误。
另外,如果存储的函数包含SQL语句,则不应在其他SQL语句中使用它; 否则,存储的函数将减慢查询的速度。
参考:https://www.yiibai.com/mysql/stored-function.html