資料庫編程

第一節存盤程序

一、存盤程序的基本概念

存盤程序是一組為了完成某項特定功能的 SQL 陳述句集，其實質上就是一段存盤在資料庫中的代碼，它可以由宣告式的 SQL 陳述句（如 CREATE、UPDATE 和 SELECT 等陳述句）和程序式 SQL 陳述句（如 IF...THEN...ELSE 控制結構陳述句）組成，
這組陳述句集經過編譯后會存盤在資料庫中，用戶只需通過指定存盤程序的名字并給定引數（如果該存盤程序帶有引數），即可隨時呼叫并執行它，而不必重新編譯，因此這種通過定義一段程式存盤在資料庫中的方式，可加大資料庫操作陳述句的執行效率，
使用存盤程序通常具有以下一些好處：
- 可增強SQL語言的功能和靈活性
- 良好的封裝性
- 高性能
- 可減少網路流量
- 存盤程序可作為一種安全機制來確保資料庫的安全性和資料的完整性

二、創建存盤程序

DELIMITER 命令的使用語法格式是：

DELIMITER $$

$$ 是用戶定義的結束符，通常這個符號可以是一些特殊的符號，例如兩個“#”，或兩個“￥”等
當使用 DELIMITER 命令時，應該避免使用反斜杠（“\”）字符，因為它是MySQL的轉義字符

例子：將 MySQL 結束符修改為兩個感嘆號“!!”，

mysql> DELIMITER !!

換回默認的分行“;”

mysql> DELIMITER ;

創建存盤程序

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

"proc_parameter" 的語法格式：

[IN | OUT | INOUT] param_name type

# 引數的取名不要與資料表的列名相同

語法項“routine_body” 表示存盤程序的主體部分，也稱為存盤程序體，其包含了在程序呼叫的時候必須執行的SQL陳述句，
這個部分以關鍵字“BEGIN” 開始，以關鍵字“END”結束，
如若存盤程序體中只有一條 SQL陳述句時，可以省略 BEGIN...END 標志，
在存盤程序體中，BEGIN...END 復合陳述句可以嵌套使用

例子：在資料庫 mysql_test 中創建一個存盤程序，用于實作給定表 customers 中一個客戶 id 號即可修改表 customers 中該客戶的性別為一個指定的性別，

? mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1850
Server version: 8.0.32 Homebrew

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> use mysql_test;
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
mysql> delimiter $$
mysql> create procedure sp_update_sex(in cid int,in csex char(1))
    -> begin
    -> update customers set cust_sex=csex where cust_id=cid;
    -> end $$
Query OK, 0 rows affected (0.03 sec)

mysql>

三、存盤程序體

1 區域變數

用來存盤存盤程序體中的臨時結果

DECLARE var_name[,...] type [DEFAULT value]

例子：宣告一個整形區域變數 cid，

DECLARE cid INT(10);

區域變數只能在存盤程序體的 BEGIN...END 陳述句塊中宣告
區域變數必須在存盤程序體的開頭出宣告
區域變數的作用范圍僅限于宣告它的 BEGIN...END 陳述句塊，其他陳述句塊中的陳述句不可以使用它
區域變數不同于用戶變數，兩者的區別是：
- 區域變數宣告時，在其前面沒有使用 @ 符號，并且它只能被宣告它的 BEGIN...END 陳述句塊中的陳述句所使用
- 用戶變數在宣告時，會在其名稱前面使用 @ 符號，同時已宣告的用戶變數存在于整個會話之中

2 SET 陳述句

SET var_name = expr [, var_name = expr] ...

例子：為宣告的區域變數 cid 賦予一個整數值 910

SET cid=910;

3 SELECT...INTO 陳述句

把選定列的值直接存盤到區域變數中

SELECT col_name [,...] INTO var_name[,...] table_expr

存盤程序體中的 SELECT...INTO 陳述句回傳的結果集只能有一行資料

4 流程控制陳述句

（1）條件判斷陳述句

常用的條件判斷陳述句有 IF...THEN...ELSE 陳述句和 CASE 陳述句，
它們的使用語法及方式類似于高級程式設計語言，

（2）回圈陳述句

常用的回圈陳述句有 WHILE 陳述句、REPEAT 陳述句和 LOOP 陳述句，
它們的使用語法及方式同樣類似于高級程式設計語言，
回圈陳述句中還可以使用 ITERATE 陳述句，但它只能出現在回圈陳述句的 LOOP、REPEAT 和 WHILE 子句中，用于表示退出當前回圈，且重新開始一個回圈，

5 游標

在MySQL中，一條 SELECT...INTO 陳述句成功執行后，會回傳帶有值的一行資料，這行資料可以被讀取到存盤程序中進行處理，
然而，在使用 SELECT 陳述句進行資料檢索時，若該陳述句成功被執行，則會回傳一組稱為結果集的資料行，該結果集中可能擁有多行資料，這些資料無法直接被一行一行地進行處理，此時就需要使用游標，
游標是一個被 SELECT 陳述句檢索出來的結果集，
在存盤了游標后，應用程式或用戶就可以根據需要滾動或瀏覽其中的資料，

在MySQL中，使用游標的具體步驟如下：

（1）宣告游標

DECLARE cursor_name CURSOR FOR select_statement

語法項“select_statement”用于指定一個 SELECT 陳述句，其會回傳一行或多行的資料，且需注意此處的 SELECT 陳述句不能有 INTO 子句，

（2）打開游標

OPEN cursor_name

（3）讀取資料

FETCH cursor_name INTO var_name [, var_name] ...

游標相當于一個指標，它指向當前的一行資料，

（4）關閉游標

CLOSE cursor_name

如果沒有明確關閉游標，MySQL將會在到達 END 陳述句時自動關閉它，
在一個游標被關閉后，如果沒有重新被打開，則不能被使用，
對于宣告過的游標，則不需要再次宣告，可直接使用 OPEN 陳述句打開，

例子：在資料庫 mysql_test 中創建一個存盤程序，用于計算表 customers 中資料行的行數，

首先，在MySQL命令列客戶端輸入如下 SQL陳述句創建存盤程序 sq_sumofrow：

? mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2286
Server version: 8.0.32 Homebrew

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> use mysql_test;
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
mysql> delimiter $$
mysql> create procedure sp_sumofrow(OUT ROWS INT)
    -> begin
    -> declare cid int;
    -> declare found boolean default true;
    -> declare cur_cid cursor for
    -> select cust_id from customers;
    -> declare continue handler for not found
    -> set found=false;
    -> set rows=0;
    -> open cur_cid;
    -> fetch cur_cid into cid;
    -> while found do
    -> set rows=rows+1;
    -> fetch cur_cid into cid;
    -> end while;
    -> close cur_cid;
    -> end$$
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ROWS INT)
begin
declare cid int;
declare found boolean default true;
declare cur' at line 1
mysql> 


mysql> CREATE PROCEDURE sp_sumofrow(OUT ROWS INT)
    -> BEGIN
    -> DECLARE cid INT;
    -> DECLARE FOUND BOOLEAN DEFAULT TRUE;
    -> DECLARE cur_cid CURSOR FOR
    -> SELECT cust_id FROM customers;
    -> DECLARE CONTINUE HANDLER FOR NOT FOUND
    -> SET FOUND=FALSE;
    -> SET ROWS=0;
    -> OPEN cur_cid;
    -> FETCH cur_cid INTO cid;
    -> WHILE FOUND DO
    -> SET ROWS=ROWS+1;
    -> FETCH cur_cid INTO cid;
    -> END WHILE;
    -> CLOSE cur_cid;
    -> END$$
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ROWS INT)
BEGIN
DECLARE cid INT;
DECLARE FOUND BOOLEAN DEFAULT TRUE;
DECLARE cur' at line 1
mysql>


mysql> CREATE PROCEDURE sp_sumofrow(OUT `ROWS` INT) BEGIN DECLARE cid INT; DECLARE FOUND BOOLEAN DEFAULT TRUE; DECLARE cur_cid CURSOR FOR SELECT cust_id FROM customers; DECLARE CONTINUE HANDLER FOR NOT FOUND SET FOUND=FALSE; SET `ROWS`=0; OPEN cur_cid; FETCH cur_cid INTO cid; WHILE FOUND DO SET `ROWS`=`ROWS`+1; FETCH cur_cid INTO cid; END WHILE; CLOSE cur_cid; END$$
Query OK, 0 rows affected (0.01 sec)

mysql>

然后，在 MySQL 命令列客戶端輸入如下 SQL陳述句對存盤程序 sp_sumofrow 進行呼叫：

mysql> call sp_sumofrow(@rows);
    ->
    ->
    -> $$
Query OK, 0 rows affected (0.01 sec)


mysql> delimiter ;
mysql> select @rows;
+-------+
| @rows |
+-------+
|     4 |
+-------+
1 row in set (0.00 sec)

mysql>

最后，查看呼叫存盤程序 sp_sumofrow后的結果：

mysql> select @rows;
+-------+
| @rows |
+-------+
|     4 |
+-------+
1 row in set (0.00 sec)

mysql>

由此例可以看出：

定義了一個 CONTINUE HANDLER 句柄，它是在條件出現時被執行的代碼，用于控制回圈陳述句，以實作游標的下移
DECLARE 陳述句的使用存在特定的次序，即用 DECLARE 陳述句定義的區域變數必須在定義任意游標或句柄之前定義，而句柄必須在游標之后定義，否則系統會出現錯誤資訊，

在使用游標的程序中，需要注意以下幾點：

游標只能用于存盤程序或存盤函式中，不能單獨在查詢操作中使用，
在存盤程序或存盤函式中可以定義多個游標，但是在一個 BEGIN...END 陳述句塊中每一個游標的名字必須是唯一的，
游標不是一條 SELECT 陳述句，是被 SELECT 陳述句檢索出來的結果集，

四、呼叫存盤程序

CALL sp_name([parameter[,...]])
CALL sp_name[()]

當呼叫沒有引數的存盤程序時，使用 CALL sp_name() 陳述句與使用 CALL sp_name 陳述句是相同的，

例子：呼叫資料庫 mysql_test 中的存盤程序 sp_update_sex，將客戶 id 號位 909 的客戶性別修改為男性“M”，

mysql> call sp_update_sex(909,'M');
Query OK, 0 rows affected (0.00 sec)

mysql>

五、洗掉存盤程序

DROP PROCEDURE [IF EXISTS] sp_name

例子：洗掉資料庫 mysql_test 中的存盤程序 sp_update_sex，

mysql> DROP PROCEDURE sp_update_sex;
Query OK, 0 rows affected (0.01 sec)

mysql>

第二節存盤函式

存盤函式與存盤程序的區別：

存盤函式不能擁有輸出引數，這是因為存盤函式自身就是輸出引數；而存盤程序可以擁有輸出引數，
可以直接對存盤函式進行呼叫，且不需要使用 CALL 陳述句；而對存盤程序的呼叫，需要使用 CALL 陳述句，
存盤函式中必須包含一條 RETURN 陳述句，而這條特殊的SQL陳述句不允許包含于存盤程序中，

一、創建存盤函式

CREATE FUNCTION sp_name ([func_parameter[,...]])
	RETURNS type
	routine_body

其中，語法項“func_parameter”的語法格式是：

param_name type

存盤函式不能與存盤程序具有相同的名字，
存盤函式體中還必須包含一個 RETURN value 陳述句，其中 value 用于指定存盤函式的回傳值，

例子：在資料庫 mysql_test 中創建一個存盤函式，要求該函式能根據給定的客戶 id 號回傳客戶的性別，如果資料庫中沒有給定的 id 號，則回傳“沒有該客戶”，

? mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 659
Server version: 8.0.32 Homebrew

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql> use mysql_test;
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
mysql> DELIMITER $$
mysql> CREATE FUNCTION fn_search(cid INT)
    -> RETURNS CHAR(2)
    -> DETERMINISTIC
    -> BEGIN
    -> DECLARE SEX CHAR(2);
    -> SELECT cust_sex INTO SEX FROM customers
    -> WHERE cust_id=cid;
    -> IF SEX IS NULL THEN
    -> RETURN(SELECT '沒有該客戶');
    -> ELSE IF SEX='F' THEN
    -> RETURN(SELECT '女');
    -> ELSE RETURN(SELECT '男');
    -> END IF;
    -> END IF;
    -> END $$
Query OK, 0 rows affected (0.02 sec)

mysql>

二、呼叫存盤函式

SELECT sp_name ([func_parameter[,...]])

例子：呼叫資料庫 mysql_test 中的存盤函式 fn_search，

mysql> delimiter ;
mysql> SELECT fn_search(904);
+----------------+
| fn_search(904) |
+----------------+
| 男             |
+----------------+
1 row in set (0.00 sec)

mysql>

三、洗掉存盤函式

DROP FUNCTION [IF EXISTS] sp_name

例子：洗掉資料庫 mysql_test 中的存盤函式 fn_search，

mysql> DROP FUNCTION IF EXISTS fn_search;
Query OK, 0 rows affected (0.00 sec)

mysql>

本文來自博客園，作者：QIAOPENGJUN，轉載請注明原文鏈接：https://www.cnblogs.com/QiaoPengjun/p/17244918.html

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/547846.html

標籤：MySQL

上一篇：SQL之連表查詢

下一篇：MongoDB基礎

資料庫系統原理之資料庫編程