SQL Stored Routines – User-Defined Function (UDF)

測試環境為 CentOS 8 x86_64 (虛擬機)

多筆的 SQL 敘述,可以儲存成為 Procedure – http://benjr.tw/102904 或是 User-Defined Function (UDF) ,下面來看一下.

先建立等一下 User-Defined Function 要使用的資料庫.

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.17-MariaDB MariaDB Server
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
MariaDB [(none)]> CREATE DATABASE testdb;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> USE testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [testdb]> CREATE TABLE employee (K1 INT(11) NOT NULL AUTO_INCREMENT ,  Dept VARCHAR(50) NOT NULL , Member VARCHAR(50) NOT NULL, PRIMARY KEY (K1));
Query OK, 0 rows affected (0.010 sec)

MariaDB [testdb]> DESCRIBE employee;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| K1     | int(11)     | NO   | PRI | NULL    | auto_increment |
| Dept   | varchar(50) | NO   |     | NULL    |                |
| Member | varchar(50) | NO   |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.003 sec)

MariaDB [testdb]> INSERT INTO employee (Dept , Member) VALUES('HR' , 'Ben') , ('HW' , 'Jason') , ('SW' , 'Thomas') , ('HW' , 'Jack') , ('HR' , 'Andy') , ('HR' , 'Chuck');
Query OK, 6 rows affected (0.003 sec)
Records: 6  Duplicates: 0  Warnings: 0

MariaDB [testdb]> SELECT * FROM employee;
+----+------+--------+
| K1 | Dept | Member |
+----+------+--------+
|  1 | HR   | Ben    |
|  2 | HW   | Jason  |
|  3 | SW   | Thomas |
|  4 | HW   | Jack   |
|  5 | HR   | Andy   |
|  6 | HR   | Chuck  |
+----+------+--------+
6 rows in set (0.001 sec)

檢視一下 User-Defined Function 怎麼建立.

MariaDB [testdb]> HELP CREATE FUNCTION UDF;
Name: 'CREATE FUNCTION UDF'
Description:
Syntax:
CREATE [AGGREGATE] FUNCTION function_name RETURNS {STRING|INTEGER|REAL|DECIMAL}
    SONAME shared_library_name

直接從範例來說明:

DROP FUNCTION IF EXISTS simplefun;
DELIMITER //
CREATE FUNCTION simplefun (DeptName VARCHAR(50))
RETURNS INT

BEGIN

DECLARE Total INT;
SELECT COUNT(*) INTO Total FROM employee WHERE Dept=DeptName ;
RETURN Total;

END//
DELIMITER ;
  • DROP FUNCTION IF EXISTS simplefun;
    如果宣告過相同的 User-Defined Function 就刪除.
  • DELIMITER // //
    mysql 透過分號 “;” 來表示分隔(一個敘述完成),但函數裡面可能會有多個 分隔,這時候可以使用 DELIMITER // … // ,來表示裡面是一整個敘述.
  • DELIMITER ;
    把結束符號修改回為 分號 “;” .
  • CREATE FUNCTION simplefun (DeptName VARCHAR(50))
    建立一個函數,須包含函數名稱與傳入變數與型態.
  • RETURNS INT
    回傳值的型態.
  • BEGIN END
    函數程式宣告必須包含在裡面.
  • DECLARE Total INT;
    宣告一個 User-Defined Function 裡面使用的變數.
  • SELECT COUNT(*) INTO Total FROM employee WHERE Dept=DeptName ;
    把 SELECT 後的結果 COUNT(*) 儲存到 變數 Total.
  • RETURN Total;
    回傳變數 Total 的值.

User-Defined Function 裡面有用到一個系統預設函數.

  • COUNT(*)
    計算資料的筆數.

建立 User-Defined Function .

MariaDB [testdb]> DROP FUNCTION IF EXISTS simplefun;
Query OK, 0 rows affected (0.002 sec)

MariaDB [testdb]> DELIMITER //
MariaDB [testdb]> CREATE FUNCTION simplefun (DeptName VARCHAR(50))
    -> RETURNS INT
    -> 
    -> BEGIN
    -> 
    -> DECLARE Total INT;
    -> SELECT COUNT(*) INTO Total FROM employee WHERE Dept=DeptName ;
    -> RETURN Total;
    -> 
    -> END//
Query OK, 0 rows affected (0.002 sec)

MariaDB [testdb]> DELIMITER ;

建立好 User-Defined Function 之後,可以透過下面指令來檢視.

MariaDB [testdb]> SHOW FUNCTION STATUS;
MariaDB [testdb]> SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE SPECIFIC_NAME='simplefun';

執行看看,主要需要傳入 “部門名稱”.

MariaDB [testdb]> SELECT simplefun("HR");
+-----------------+
| simplefun("HR") |
+-----------------+
|               3 |
+-----------------+
1 row in set (0.001 sec)

MariaDB [testdb]> SELECT simplefun("SW");
+-----------------+
| simplefun("SW") |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.001 sec)

函數執行權限

使用者需要有執行 EXECUTE 權限,遇到權限問題請參考 http://benjr.tw/99060

檢視使用者權限,假設使用者名稱為 user1 ,資料庫為 testdb ,並給予使用者使用資料表與執行 User-Defined Function 權限.

MariaDB [(none)]> CREATE USER user1@localhost IDENTIFIED BY '111111';
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> GRANT SELECT ON testdb.* TO user1@localhost;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> GRANT EXECUTE ON testdb.* TO user1@localhost;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> SHOW GRANTS FOR user1@localhost;
+--------------------------------------------------------------------------------------------------------------+
| Grants for user1@localhost                                                                                   |
+--------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user1'@'localhost' IDENTIFIED BY PASSWORD '*FD571203974BA9AFE270FE62151AE967ECA5E0AA' |
| GRANT SELECT, EXECUTE ON `testdb`.* TO 'user1'@'localhost'                                                   |
+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

MariaDB [(none)]> EXIT
Bye

使用 user1 登入並執行 User-Defined Function.

[root@localhost ~]# mysql -u user1 -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.3.17-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> USE testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [testdb]> SELECT simplefun("SW");
+-----------------+
| simplefun("SW") |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.001 sec)

MariaDB [(none)]> exit
Bye

移除函數

最後要移除 User-Defined Function 可以透過下面的方式.

MariaDB [testdb]> DROP FUNCTION simplefun;
Query OK, 0 rows affected (0.01 sec)

備份與還原

備份資料庫時需要多加參數 –routines 才能把, Procedures 與 User-Defined Function 備份下來.

[root@localhost ~]# mysqldump -u root -p --routines testdb > testdb.sql.fun
Enter password: 

還原時則不需要額外參數

[root@localhost ~]# mysql -u root -p tempdb < testdb.sql.fun
Enter password: 

剛剛只有備份 Procedures 與 User-Defined Function 本體,相關資訊與權限需透過下面的方式來完成.

[root@localhost ~]# mysqldump -u root -p mysql proc > mysql_proc.sql
Enter password: 
[root@localhost ~]# mysqldump -u root -p mysql user > mysql_user.sql
Enter password: 
[root@localhost ~]# mysqldump -u root -p mysql db > mysql_db.sql
Enter password: 

還原 Procedures 與 User-Defined Function 相關資訊與權限.

[root@localhost ~]# mysql -u root -p mysql < mysql_proc.sql
Enter password: 
[root@localhost ~]# mysql -u root -p mysql < mysql_user.sql
Enter password: 
[root@localhost ~]# mysql -u root -p mysql < mysql_db.sql
Enter password: 
沒有解決問題,試試搜尋本站其他內容

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *

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