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

与存储过程不同，您可以在SQL语句中使用存储的函数，也可以在表达式中使用。 这有助于提高程序代码的可读性和可维护性。

MySQL存储函数语法

以下说明了创建新存储函数的最简单语法：

CREATE FUNCTION function_name(param1,param2,…)
    RETURNS datatype
   [NOT] DETERMINISTIC
 statements

SQL

首先，在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 ;

SQL

现在，我们在SELECT语句中调用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 set

Shell

下面，来重写在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 ;

SQL

如您所见，GetCustomerLevel()存储过程在使用CustomerLevel()存储函数时可读性更高。

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

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

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