SQL Stored Routines – Function

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

多筆的 SQL 敘述,可以儲存成為 Procedure – https://benjr.tw/102904 或是 Function ,下面來看一下.

先建立等一下 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)

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

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

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

建立 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 ;

建立好 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 權限,遇到權限問題請參考 https://benjr.tw/99060

檢視使用者權限,假設使用者名稱為 user1 ,資料庫為 testdb ,並給予使用者使用資料表與執行 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 登入並執行 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

移除函數

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

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

備份與還原

備份資料庫時需要多加參數 –routines 才能把, Procedures 與 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 與 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 與 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:

遇過的問題

在建立 Stored Function 時卻得到以下的錯誤訊息.

This function has none of DETERMINISTIC, NO SQL,or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

請參考說明 – https://benjr.tw/102425

沒有解決問題,試試搜尋本站其他內容

函數執行權限

移除函數

備份與還原

遇過的問題

發佈留言 取消回覆

發佈留言取消回覆