關於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;#使用
存儲的函數是返回單個值的特殊類型的存儲程式。您使用存儲的函數來封裝在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語句中使用它; 否則，存儲的函數將減慢查詢的速度。