SQL Stored Routines – PROCEDURE

測試環境為 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:

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

proc_parameter

執行權限

移除 Procedure

備份與還原

發佈留言 取消回覆

發佈留言取消回覆