資料庫編程第一節存儲過程一、存儲過程的基本概念存儲過程是一組為了完成某項特定功能的 SQL 語句集，其實質上就是一段存儲在資料庫中的代碼，它可以由聲明式的 SQL 語句（如 CREATE、UPDATE 和 SELECT 等語句）和過程式 SQL 語句（如 IF...THEN...ELSE 控 ...

資料庫編程

第一節存儲過程

一、存儲過程的基本概念

存儲過程是一組為了完成某項特定功能的 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

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