mysql 存储函数 - 好文

#定义函数 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语句或存储的程序中可重用的常用公式或业务规则。

与存储过程 <http://www.yiibai.com/mysql/stored-procedure.html>不同，
您可以在SQL语句中使用存储的函数，也可以在表达式中使用。这有助于提高程序代码的可读性和可维护性。

<>MySQL存储函数语法

以下说明了创建新存储函数的最简单语法：
CREATE FUNCTION function_name(param1,param2,…) RETURNS datatype [NOT]
DETERMINISTIC statements SQL
首先，在CREATE FUNCTION子句之后指定存储函数的名称。
其次，列出括号内存储函数的所有参数
<http://www.yiibai.com/mysql/stored-procedures-parameters.html>。默认情况下，所有参数均为IN
参数。不能为参数指定IN，OUT或INOUT修饰符。
第三，必须在RETURNS语句中指定返回值的数据类型。它可以是任何有效的MySQL数据类型
<http://www.yiibai.com/mysql/data-types.html>。
第四，对于相同的输入参数，如果存储的函数返回相同的结果，这样则被认为是确定性的，否则存储的函数不是确定性的。必须决定一个存储函数是否是确定性的。
如果您声明不正确，则存储的函数可能会产生意想不到的结果，或者不使用可用的优化，从而降低性能。
第五，将代码写入存储函数的主体中。它可以是单个语句或复合语句。在主体部分中，必须至少指定一个RETURN语句。RETURN语句用于返回一个值给调用者。
每当到达RETURN语句时，存储的函数的执行将立即终止。

<>MySQL存储函数示例

我们来看一下使用存储函数的例子，这里将使用示例数据库(yiibaidb)
<http://www.yiibai.com/mysql/sample-database.html>中的customers表进行演示。

以下示例是根据信用额度返回客户级别的功能。我们使用IF语句 <http://www.yiibai.com/mysql/if-statement.html>
来确定信用额度。
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 ; SQL
现在，我们在SELECT语句 <http://www.yiibai.com/mysql/select-statement-query-data.html>
中调用CustomerLevel()存储函数，如下所示：
SELECT customerName, CustomerLevel(creditLimit) FROM customers ORDER BY
customerName; SQL
执行上面查询语句，得到以下结果 -
+------------------------------------+----------------------------+ |
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
setShell
下面，来重写在MySQL IF语句教程 <http://www.yiibai.com/mysql/if-statement.html>中开发的
GetCustomerLevel()存储过程，如下所示：
DELIMITER $$ CREATE PROCEDURE GetCustomerLevel( IN p_customerNumber INT(11),
OUT p_customerLevel varchar(10) ) BEGIN DECLARE creditlim DOUBLE; SELECT
creditlimitINTO creditlim FROM customers WHERE customerNumber = p_customerNumber
; SELECT CUSTOMERLEVEL(creditlim) INTO p_customerLevel; END $$ DELIMITER ; SQL
如您所见，GetCustomerLevel()存储过程在使用CustomerLevel()存储函数时可读性更高。

请注意，存储函数仅返回单个值。如果没有包含INTO子句的SELECT语句，则将会收到错误。

另外，如果存储的函数包含SQL语句，则不应在其他SQL语句中使用它; 否则，存储的函数将减慢查询的速度。

参考：https://www.yiibai.com/mysql/stored-function.html

热门工具换一换