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

来源:https://www.cnblogs.com/QiaoPengjun/archive/2023/03/22/17244918.html
-Advertisement-
Play Games

資料庫編程 第一節 存儲過程 一、存儲過程的基本概念 存儲過程是一組為了完成某項特定功能的 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


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 使用SSH管理Windows
    網上有很多關於使用win10-ssh客戶端登錄linux-ssh服務端的介紹，但很少介紹多台win10-ssh服務端之間互訪的。以下記錄如何免密登錄win10-ssh服務 根據微軟文檔描述,適用於Windows 10 1809 或 Windows Server 2019以上版本 以下內容如果無特別說 ...
  • RHEL無法配置網關問題一則
    故障問題：由於vSphere虛擬化平臺中的一臺VM虛擬機MAC地址與另一VC中的VM衝突需要修改，在VC中刪除VM的網卡並重新添加一張新的虛擬網卡之後，在VM的RHEL系統中配置網卡IP網關之後，發現IP能正常起，但網關無論如何都無法生效，使用ip r命令無法查看到網關。檢查/etc/sysconf ...
  • redis支持的數據類型有哪些
    前言 Redis之所以被廣泛使用，不只是因為它快，還因為它除了支持最簡單的字元串（String）類型外，它還支持好幾種數據類型： 哈希（Hash） 列表（List） 集合（Set） 有序集合（Sorted Set） 點陣圖 ( Bitmap ) String String為最簡單的類型，一個key對應 ...
  • 在Windows系統的基礎上安裝Ubuntu 22
    一、使用VMware安裝Ubuntu虛擬機 在Linux系統各個發行版中，Ubuntu系統在服務端和桌面端使用占比最高，網路上資料最是齊全，所以這裡使用Ubuntu LTS。 整體的系統安裝文件較大(>1G)，這裡採用了迅雷加速下載。迅雷下載地址：下載迅雷工具 下載地址：Download Ubunt ...
  • CentOS7-RHCE服務---Web
    網站需求：請給openlab搭建web網站 1.基於功能變數名稱www.openlab.com可以訪問網站內容為 welcome to openlab!!! 2.給該公司創建三個子界面分別顯示學生信息，教學資料和繳費網站，基於www.openlab.com/student 網站訪問學生信息，www.open ...
  • CentOS7-RHCE服務----DNS
    實驗要求： 1、建立DNS伺服器，負責解析的域為openedu.com 2、要求將MX記錄指向mail.openedu.com，且對應A記錄為本機IP 3、要求將NS記錄指向ns1.openedu.com，且對應A記錄為本機IP 4、建立www的A記錄指向本機IP； 建立ftp對應A記錄為x.x.x ...
  • MySQL基礎：多表查詢
    MySQL基礎：多表查詢 多表關係 概述 ​ 項目開發中，在進行資料庫表結構設計時，會根據業務需求及模塊之間的關係，分析並設計表結構，由於業務之間相互關聯，所以各個表結構之間也存在著各種聯繫，基本上分為三種： 一對多（多對一） 多對多 一對一 一對多（多對一） 案例：部門與員工的關係 關係：一個部門 ...
  • 資料庫系統原理之資料庫系統概述
    資料庫系統概述 一、資料庫基本概念 1 數據 數據（Data）是描述事物的符號記錄，是指用物理符號記錄下來的、可以鑒別的信息。 數據以及關於該數據的解釋是密切相關的。 數據的解釋是對數據含義的說明，也稱為數據的語義，即數據所蘊含的信息。 數據與其語義密不可分，沒有語義的數據是沒有意義和不完整的。 數 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類