首頁 / CompScience / Database / MySQL / MySQL自訂函式參數用法in

MySQL自訂函式參數用法in

2013-05-20_091051

這是MySQL 5實力養成暨評量裡的8-37.『在自訂函式中下列何項可用來指定參數傳遞的方式?』

答案:(A)

在作答這題時ㄚ琪很自然地以為是跟MySQL 預儲程序參數用法in, out, inout這類的回答類似,沒有確認清楚預儲程序跟自訂函式的差異就亂填答案了,所以就變成這樣。經過確認後,查考MySQL 5.7 Reference Manual :: 13 SQL Statement Syntax :: 13.1 Data Definition Statements :: 13.1.12 CREATE PROCEDURE and CREATE FUNCTION Syntax

MySQL 5.1参考手册 :: 20. 存储程序和函数::20.2.1. CREATE PROCEDURE和CREATE FUNCTION

轉譯成繁體中文列於下:

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

 sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

    proc_parameter:
    [ IN | OUT | INOUT ] param_name type

    func_parameter:
    param_name type

type:
    Any valid  data type

characteristic:
    LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
  | COMMENT 'string'

routine_body:
    Valid SQL procedure statement or statements

這些語句建立預儲程式。要在MySQL 5.1中建立預儲程式,必須具有CREATE ROUTINE權限,並且ALTER ROUTINE和EXECUTE權限被自動授予它的建立者。如果二進制日誌功能被允許,您也可能需要SUPER權限,請參閱20.4節,「預儲程式和觸發程式的二進制日誌功能”

預設地,預儲程式與當前資料庫關聯。要明確地把預儲程式與一個給定資料庫關聯起來,可以在建立預儲程式的時候指定其名字為db_name.sp_name

如果預儲程式名和內建的SQL函數名一樣,定義預儲程式時,您需要在這個名字和隨後括號中間插入一個空格,否則發生語法錯誤。當您隨後使用預儲程式的時候也要插入。為此,即使有可能出現這種情況,我們還是建議最好避免給您自己的預儲程式取與存在的SQL函數一樣的名字。

由括號包圍的參數列必須總是存在。如果沒有參數,也該使用一個空參數列()。每個參數預設都是一個IN參數。要指定為其它參數,可在參數名之前使用關鍵詞 OUT或INOUT

注意: 指定參數為IN, OUT, 或INOUT 只對PROCEDURE是合法的。(FUNCTION參數總是被認為是IN參數)

RETURNS字句只能對FUNCTION做指定,對函數而言這是強制的。它用來指定函數的返回類型,而且函數體必須包含一個RETURN value語句。

routine_body 包含合法的SQL過程語句。可以使用複合語句語法,請參閱20.2.7節,「BEGIN … END復合語句”。複合語句可以包含聲明,循環和其它控制結構語句。這些語句的語法在本章後面介紹,舉例,請參閱20.2.8節,「DECLARE語句”20.2.12節,「流程控制構造」

CREATE FUNCTION語句被用在更早的MySQL版本上以支援UDF (自行定義函數)。請參閱27.2節,「給MySQL新增新函數”。 UDF繼續被支援,即使現在有了儲存函數。UDF會被認為一個外部儲存函數。然而,不要讓儲存函數與UDF函數共享名字空間。

外部預儲程式的框架將在不久的將來引入。這將允許您用SQL之外的語言編寫預儲程式。最可能的是,第一個被支援語言是PHP,因為核心PHP引擎很小,線程安全,且可以被方便地嵌入。因為框架是公開的,它希望許多其它語言也能被支援。

如果程式或線程總是對同樣的輸入參數產生同樣的結果,則被認為它是“確定的”,否則就是“非確定”的。如果既沒有給定DETERMINISTIC也沒有給定NOT DETERMINISTIC,預設的就是NOT DETERMINISTIC。

為進行複製,使用NOW()函數(或它的同義詞)或RAND()函數會不必要地使得一個預儲程式非確定。對NOW()而言,二進制日誌包括時間戳並被正確複製。RAND() 只要在一個預儲程式被內應用一次也會被正確複製。(您可以把預儲程式執行時間戳和隨機數種子認為強制輸入,它們在主從上是同樣的。)

當前來講,DETERMINISTIC特徵被接受,但還沒有被最佳化程式所使用。然而如果二進制日誌功能被允許了,這個特徵影響到MySQL是否會接受預儲程式定義。請參閱20.4節,「預儲程式和觸發程式的二進制日誌功能”

一些特徵提供預儲程式使用數據的內在訊息。CONTAINS SQL資料表示預儲程式不包含讀或寫數據的語句。NO SQL資料表示預儲程式不包含SQL語句。READS SQL DATA資料表示預儲程式包含讀數據的語句,但不包含寫數據的語句。MODIFIES SQL DATA資料表示預儲程式包含寫數據的語句。如果這些特徵沒有明確給定,預設的是CONTAINS SQL。

SQL SECURITY特徵可以用來指定預儲程式該用建立預儲程式者的授權來執行,還是使用使用者的授權來執行。預設值是DEFINER。在SQL:2003中者是一個新特性。建立者或使用者必須由訪問預儲程式關聯的資料庫的授權。在MySQL 5.1中,必須有EXECUTE權限才能執行預儲程式。必須擁有這個權限的用戶要麼是定義者,要麼是使用者,這取決於SQL SECURITY特徵是如何設置的。

MySQL儲存sql_mode系統變數設置,這個設置在預儲程式被建立的時候起作用,MySQL總是強制使用這個設置來執行預儲程式。

COMMENT子句是一個MySQL的延伸,它可以被用來描述 儲存程式。這個訊息被SHOW CREATE PROCEDURE和 SHOW CREATE FUNCTION語句來顯示。

MySQL允許預儲程式包含DDL語句,如CREATE和DROP。MySQL也允許儲存程式(但不是 儲存函數)包含SQL 交互語句,如COMMIT。儲存函數不可以包含那些做明確的和絕對的提交或者做回滾的語。SQL標準不要求對這些語句的支援,SQL標準聲明每個DBMS提供商可以決定是否允許支援這些語句。

預儲程式不能使用LOAD DATA INFILE。

返回結果包的語句不能被用在儲存函數種。這包括不使用INTO給變數讀取 列值的SELECT語句,SHOW 語句,及其它諸如EXPLAIN這樣的語句。對於可在函數定義時間被決定要返回一個結果包的語句,發生一個允許從函數錯誤返回結果包的Not(ER_SP_NO_RETSET_IN_FUNC)。對於只可在運行時決定要返回一個結果包的語句, 發生一個不能在給定上下文錯誤返回結果包的PROCEDURE %s (ER_SP_BADSELECT)。

下面是一個使用OUT參數的簡單的預儲程式的例子。例子為,在 程式被定義的時候,用mysql客戶端delimiter命令來把語句定界符從 ;變為//。這就允許用在 程式體中的;定界符被傳遞到伺服器而不是被mysql自己來解釋。

mysql> delimiter //

mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t;
    -> END
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @a;
+------+
| @a   |
+------+
| 3    |
+------+
1 row in set (0.00 sec)

當使用delimiter命令時,您應該避免使用反斜槓(‘\’)字元,因為那是MySQL的 轉義字元。

下列是一個例子,一個採用參數的函數使用一個SQL函數執行一個操作,並返回結果:

mysql> delimiter //

mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
    -> RETURN CONCAT('Hello, ',s,'!');
    -> //
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> SELECT hello('world');
+----------------+
| hello('world') |
+----------------+
| Hello, world!  |
+----------------+
1 row in set (0.00 sec)

如果在自訂函式中的RETURN語句返回一個類型不同於在函式的RETURNS子句中指定類型的值,返回值被強制為恰當的類型。比如,如果一個函數返回一個ENUM或SET值,但是RETURN語句返回一個整數,對於SET成員集的相應的ENUM成員,從函數返回的值是字串。

Print Friendly, PDF & Email
馬上成為工作達人的Fans

About ㄚ琪

工作達人Fun Taiwan的創辦者及總編,可以在這裡更認識他。

發表迴響

你的電子郵件位址並不會被公開。 Required fields are marked *

*

這個網站採用 Akismet 服務減少垃圾留言。進一步瞭解 Akismet 如何處理網站訪客的留言資料

Scroll To Top