SQL Stored Routines – PROCEDURE

Loading

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

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

先建立等一下 Procedure 要使用的資料庫.

[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)

檢視一下 Procedure 怎麼建立.

MariaDB [testdb]> HELP CREATE PROCEDURE;
Name: 'CREATE PROCEDURE'
Description:
Syntax:
CREATE
    [DEFINER = { user | CURRENT_USER }]
    PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body

CREATE
    [DEFINER = { user | CURRENT_USER }]
    FUNCTION 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 MySQL data type

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

routine_body:
    Valid SQL routine statement

直接從範例來說明:

DROP PROCEDURE IF EXISTS simpleproc;
DELIMITER //

CREATE PROCEDURE simpleproc (
         IN DeptName VARCHAR(50) , 
         OUT Total INT
)
BEGIN
         SELECT COUNT(*) 
         INTO Total 
         FROM employee 
         WHERE Dept=DeptName;
END//

DELIMITER ;
  • DROP PROCEDURE IF EXISTS simpleproc;
    如果宣告過相同的 Procedure 就刪除.
  • DELIMITER // //
    Mysql (MariaDB) 透過分號 “;” 來表示這 SQL Statement 的敘述完成,但 Procedure 裡面可能會有多筆 SQL 敘述 ,這時候可以使用 DELIMITER // … // ,來表示裡面是一整個敘述.
  • DELIMITER ;
    把結束符號修改回為 分號 “;” .
  • CREATE PROCEDURE simpleproc (OUT param1 INT)
    建立一個 Procedure ,須包含 Procedure 名稱與傳入(IN),傳回值(OUT)與 INOUT,後面說明其不同.
  • BEGIN END
    Procedure 程式宣告必須包含在裡面.

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

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

建立 Procedure .

MariaDB [testdb]> DROP PROCEDURE IF EXISTS simpleproc;
Query OK, 0 rows affected (0.001 sec)

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

MariaDB [testdb]> 
MariaDB [testdb]> DELIMITER ;

建立好 Procedure 之後,可以透過下面指令來檢視.

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

執行看看,主要需要兩個變數 IN 變數傳入部門名稱,並透過 OUT 回傳部門人數.

MariaDB [testdb]> CALL simpleproc('HR',@a);
Query OK, 1 row affected (0.002 sec)

MariaDB [testdb]> SELECT @a;
+------+
| @a   |
+------+
|    3 |
+------+
1 row in set (0.001 sec)

MariaDB [testdb]> CALL simpleproc('SW',@a);
Query OK, 1 row affected (0.001 sec)

MariaDB [testdb]> SELECT @a;
+------+
| @a   |
+------+
|    1 |
+------+
1 row in set (0.001 sec)

proc_parameter

[ IN | OUT | INOUT ] param_name type

這幾個參數差別如下:

  • IN 參數
    沒指定時預設是 IN 參數,主要是將 “值”(非變數) 傳遞到 Procedure 裡.
  • OUT 參數
    主要是將值(儲存到 OUT 變數)從 Procedure 傳遞回去, Procedure 會幫該變數初始為 NULL (不管你前面幫該變數設定為多少,到 Procedure 皆為 NULL), Procedure 結束後 caller 可以使用該變數並知道其改變.
  • INOUT 參數
    我們可以在使用 caller 前可以先幫該變數設定其值並傳到 Procedure 裡使用,Procedure 過程中修改了變數值,Procedure 結束後 caller 可以使用該變數並知道其改變.

IN 不會太有問題,我們來看一下 OUT 與 INOUT 以下的範例.

DROP PROCEDURE IF EXISTS callertest1;

DELIMITER //

CREATE PROCEDURE callertest1 (OUT param1 INT , INOUT param2 INT)
BEGIN
SET param1=param1+1;
SET param2=param2+1;
END//

DELIMITER ;

建立 Procedure (主要是將參數 param1 與 param2 加 1).

MariaDB [testdb]> DROP PROCEDURE IF EXISTS callertest1;
Query OK, 0 rows affected (0.001 sec)

MariaDB [testdb]> 
MariaDB [testdb]> DELIMITER //
MariaDB [testdb]> 
MariaDB [testdb]> CREATE PROCEDURE callertest1 (OUT param1 INT , INOUT param2 INT)
    -> BEGIN
    -> SET param1=param1+1;
    -> SET param2=param2+1;
    -> END//
Query OK, 0 rows affected (0.001 sec)

MariaDB [testdb]> DELIMITER ;

執行前有先將 變數 @a (OUT 變數) 與 @b (INOUT 變數) 設定為 3,執行結果只有 @b 有改變.

MariaDB [testdb]> SET @a=3;
Query OK, 0 rows affected (0.000 sec)

MariaDB [testdb]> SET @b=3;
Query OK, 0 rows affected (0.000 sec)

MariaDB [testdb]> CALL callertest1 (@a,@b);
Query OK, 0 rows affected (0.001 sec)

為什麼 @a 是 NULL 而 @b 是 4, @a (IN 變數) 在進入 Procedure 後會被初始化為 NULL (加任何直都是 NULL).

MariaDB [testdb]> SELECT @a,@b;
+------+------+
| @a   | @b   |
+------+------+
| NULL |    4 |
+------+------+
1 row in set (0.000 sec)

執行權限

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

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

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 登入並執行 Procedure .

[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]> CALL simpleproc('HR',@a);
Query OK, 1 row affected (0.001 sec)

MariaDB [testdb]> SELECT @a;
+------+
| @a   |
+------+
|    3 |
+------+
1 row in set (0.000 sec)

MariaDB [(none)]> exit
Bye

移除 Procedure

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

MariaDB [testdb]> DROP PROCEDURE callertest1;
Query OK, 0 rows affected (0.002 sec)

備份與還原

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

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

還原時則不需要額外參數

[root@localhost ~]# mysql -u root -p tempdb < testdb.sql.proc
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: 
沒有解決問題,試試搜尋本站其他內容

發佈留言

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

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