如何使用 SQL 存儲過程簡化複雜的操作

来源:https://www.cnblogs.com/vin-c/archive/2022/05/19/16288002.html
-Advertisement-
Play Games

本文介紹什麼是 SQL 存儲過程,為什麼要使用存儲過程,如何使用存儲過程,以及創建和使用存儲過程的基本語法。 一、存儲過程 迄今為止,我們使用的大多數 SQL 語句都是針對一個或多個表的單條語句。並非所有操作都這麼簡單,經常會有一些複雜的操作需要多條語句才能完成,例如以下的情形。 為了處理訂單,必須 ...


目錄

本文介紹什麼是 SQL 存儲過程,為什麼要使用存儲過程,如何使用存儲過程,以及創建和使用存儲過程的基本語法。

一、存儲過程

迄今為止,我們使用的大多數 SQL 語句都是針對一個或多個表的單條語句。並非所有操作都這麼簡單,經常會有一些複雜的操作需要多條語句才能完成,例如以下的情形。

  • 為了處理訂單,必須核對以保證庫存中有相應的物品。
  • 如果物品有庫存,需要預定,不再出售給別的人,並且減少物品數據以反映正確的庫存量。
  • 庫存中沒有的物品需要訂購,這需要與供應商進行某種交互。
  • 關於哪些物品入庫(並且可以立即發貨)和哪些物品退訂,需要通知相應的顧客。

這顯然不是一個完整的例子,它甚至超出了本文的範圍,但足以表達我們的意思了。執行這個處理需要針對許多表的多條 SQL 語句。

此外,需要執行的具體 SQL 語句及其次序也不是固定的,它們可能會根據物品是否在庫存中而變化。

那麼,怎樣編寫代碼呢?可以單獨編寫每條 SQL 語句,並根據結果有條件地執行其他語句。在每次需要這個處理時(以及每個需要它的應用中),都必須做這些工作。

可以創建存儲過程。簡單來說,存儲過程就是為以後使用而保存的一條或多條 SQL 語句。可將其視為批文件,雖然它們的作用不僅限於批處理。

說明:不適用於 SQLite

SQLite 不支持存儲過程。

說明:還有更多內容

存儲過程很複雜,全面介紹它需要很大篇幅。

市面上有專門講存儲過程的書。

本文不打算講解存儲過程的所有內容,只給出簡單介紹,讓讀者對它們的功能有所瞭解。

因此,這裡給出的例子只提供 Oracle 和 SQL Server 的語法。

二、為什麼要使用存儲過程

我們知道了什麼是存儲過程,那麼為什麼要使用它們呢?理由很多,下麵列出一些主要的。

  • 通過把處理封裝在一個易用的單元中,可以簡化複雜的操作(如前面例子所述)。
  • 由於不要求反覆建立一系列處理步驟,因而保證了數據的一致性。如果所有開發人員和應用程式都使用同一存儲過程,則所使用的代碼都是相同的。
  • 上一點的延伸就是防止錯誤。需要執行的步驟越多,出錯的可能性就越大。防止錯誤保證了數據的一致性。
  • 簡化對變動的管理。如果表名、列名或業務邏輯(或別的內容)有變化,那麼只需要更改存儲過程的代碼。使用它的人員甚至不需要知道這些變化。
  • 上一點的延伸就是安全性。通過存儲過程限制對基礎數據的訪問,減少了數據訛誤(無意識的或別的原因所導致的數據訛誤)的機會。
  • 因為存儲過程通常以編譯過的形式存儲,所以 DBMS 處理命令所需的工作量少,提高了性能。
  • 存在一些只能用在單個請求中的 SQL 元素和特性,存儲過程可以使用它們來編寫功能更強更靈活的代碼。

換句話說,使用存儲過程有三個主要的好處,即簡單、安全、高性能。顯然,它們都很重要。不過,在將 SQL 代碼轉換為存儲過程前,也必須知道它的一些缺陷。

  • 不同 DBMS 中的存儲過程語法有所不同。事實上,編寫真正的可移植存儲過程幾乎是不可能的。

    不過,存儲過程的自我調用(名字以及數據如何傳遞)可以相對保持可移植。

    因此,如果需要移植到別的 DBMS,至少客戶端應用代碼不需要變動。

  • 一般來說,編寫存儲過程比編寫基本 SQL 語句複雜,需要更高的技能,更豐富的經驗。

    因此,許多資料庫管理員把限制存儲過程的創建作為安全措施(主要受上一條缺陷的影響)。

儘管有這些缺陷,存儲過程還是非常有用的,並且應該使用。事實上,多數 DBMS 都帶有用於管理資料庫和表的各種存儲過程。更多信息請參閱具體的 DBMS 文檔。

說明:不會寫存儲過程?你依然可以使用

大多數 DBMS 將編寫存儲過程所需的安全和訪問許可權與執行存儲過程所需的安全和訪問許可權區分開來。

這是好事情,即使你不能(或不想)編寫自己的存儲過程,也仍然可以在適當的時候執行別的存儲過程。

三、執行存儲過程

存儲過程的執行遠比編寫要頻繁得多,因此我們先介紹存儲過程的執行。

執行存儲過程的 SQL 語句很簡單,即 EXECUTE

EXECUTE 接受存儲過程名和需要傳遞給它的任何參數。

請看下麵的例子(你無法運行這個例子,因為 AddNewProduct 這個存儲過程還不存在):

EXECUTE AddNewProduct('JTS01',
                      'Stuffed Eiffel Tower',
                      6.49,
                      'Plush stuffed toy with the text La Tour Eiffel in red white and blue');

這裡執行一個名為 AddNewProduct 的存儲過程,將一個新產品添加到 Products 表中。

AddNewProduct 有四個參數,分別是:供應商 ID(Vendors 表的主鍵)、產品名、價格和描述。

這 4 個參數匹配存儲過程中 4 個預期變數(定義為存儲過程自身的組成部分)。此存儲過程將新行添加到 Products 表,並將傳入的屬性賦給相應的列。

我們註意到,在 Products 表中還有另一個需要值的列 prod_id 列,它是這個表的主鍵。

為什麼這個值不作為屬性傳遞給存儲過程?要保證恰當地生成此 ID,最好是使生成此 ID 的過程自動化(而不是依賴於最終用戶的輸入)。

這也是這個例子使用存儲過程的原因。以下是存儲過程所完成的工作:

  • 驗證傳遞的數據,保證所有 4 個參數都有值;
  • 生成用作主鍵的唯一 ID;
  • 將新產品插入 Products 表,在合適的列中存儲生成的主鍵和傳遞的數據。

這就是存儲過程執行的基本形式。對於具體的 DBMS,可能包括以下的執行選擇。

  • 參數可選,具有不提供參數時的預設值。
  • 不按次序給出參數,以“參數=值”的方式給出參數值。
  • 輸出參數,允許存儲過程在正執行的應用程式中更新所用的參數。
  • SELECT 語句檢索數據。
  • 返回代碼,允許存儲過程返回一個值到正在執行的應用程式。

四、創建存儲過程

正如所述,存儲過程的編寫很重要。為了獲得感性認識,我們來看一個簡單的存儲過程例子,它對郵件發送清單中具有郵件地址的顧客進行計數。

下麵是該過程的 Oracle 版本:

CREATE PROCEDURE MailingListCount (
  ListCount OUT INTEGER
)
IS
v_rows INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_rows
    FROM Customers
    WHERE NOT cust_email IS NULL;
    ListCount := v_rows;
END;

這個存儲過程有一個名為 ListCount 的參數。此參數從存儲過程返回一個值而不是傳遞一個值給存儲過程。

關鍵字 OUT 用來指示這種行為。Oracle 支持 IN(傳遞值給存儲過程)、OUT(從存儲過程返回值,如這裡)、INOUT(既傳遞值給存儲過程也從存儲過程傳回值)類型的參數。

存儲過程的代碼括在 BEGINEND 語句中,這裡執行一條簡單的 SELECT 語句,它檢索具有郵件地址的顧客。然後用檢索出的行數設置 ListCount(要傳遞的輸出參數)。

調用 Oracle 例子可以像下麵這樣:

var ReturnValue NUMBER
EXEC MailingListCount(:ReturnValue);
SELECT ReturnValue;

這段代碼聲明瞭一個變數來保存存儲過程返回的任何值,然後執行存儲過程,再使用 SELECT 語句顯示返回的值。

下麵是該過程的 SQL Server 版本。

CREATE PROCEDURE MailingListCount
AS
DECLARE @cnt INTEGER
SELECT @cnt = COUNT(*)
FROM Customers
WHERE NOT cust_email IS NULL;
RETURN @cnt;

此存儲過程沒有參數。調用程式檢索 SQL Server 的返回代碼提供的值。

其中用 DECLARE 語句聲明瞭一個名為 @cnt 的局部變數(SQL Server 中所有局部變數名都以 @ 起頭);

然後在 SELECT 語句中使用這個變數,讓它包含 COUNT() 函數返回的值;最後,用 RETURN @cnt 語句將計數返回給調用程式。

調用 SQL Server 例子可以像下麵這樣:

DECLARE @ReturnValue INT
EXECUTE @ReturnValue=MailingListCount;
SELECT @ReturnValue;

這段代碼聲明瞭一個變數來保存存儲過程返回的任何值,然後執行存儲過程,再使用 SELECT 語句顯示返回的值。

下麵是另一個例子,這次在 Orders 表中插入一個新訂單。此程式僅適用於 SQL Server,但它說明瞭存儲過程的某些用途和技術:

CREATE PROCEDURE NewOrder @cust_id CHAR(10)
AS
-- 為訂單號聲明一個變數
DECLARE @order_num INTEGER
-- 獲取當前最大訂單號
SELECT @order_num=MAX(order_num)
FROM Orders
-- 決定下一個訂單號
SELECT @[email protected]_num+1
-- 插入新訂單
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(@order_num, GETDATE(), @cust_id)
-- 返回訂單號
RETURN @order_num;

此存儲過程在 Orders 表中創建一個新訂單。

它只有一個參數,即下訂單顧客的 ID。訂單號和訂單日期這兩列在存儲過程中自動生成。

代碼首先聲明一個局部變數來存儲訂單號。接著,檢索當前最大訂單號(使用 MAX() 函數)並增加 1(使用 SELECT 語句)。

然後用 INSERT 語句插入由新生成的訂單號、當前系統日期(用 GETDATE() 函數檢索)和傳遞的顧客 ID 組成的訂單。

最後,用 RETURN @order_num 返回訂單號(處理訂單物品需要它)。

請註意,此代碼加了註釋,在編寫存儲過程時應該多加註釋。

說明:註釋代碼

應該註釋所有代碼,存儲過程也不例外。增加註釋不影響性能,因此不存在缺陷(除了增加編寫時間外)。

註釋代碼的好處很多,包括使別人(以及你自己)更容易地理解和更安全地修改代碼。

對代碼進行註釋的標準方式是在之前放置 --(兩個連字元)。

有的 DBMS 還支持其他的註釋語法,不過所有 DBMS 都支持 --,因此在註釋代碼時最好都使用這種語法。

下麵是相同 SQL Server 代碼的一個很不同的版本:

CREATE PROCEDURE NewOrder @cust_id CHAR(10)
AS
-- 插入新訂單
INSERT INTO Orders(cust_id)
VALUES(@cust_id)
-- 返回訂單號
SELECT order_num = @@IDENTITY;

此存儲過程也在 Orders 表中創建一個新訂單。這次由 DBMS 生成訂單號。

大多數 DBMS 都支持這種功能;SQL Server 中稱這些自動增量的列為標識欄位(identity field),而其他 DBMS 稱之為自動編號(auto number)或序列(sequence)。

傳遞給此過程的參數也是一個,即下訂單的顧客 ID。

訂單號和訂單日期沒有給出,DBMS 對日期使用預設值(GETDATE() 函數),訂單號自動生成。

怎樣才能得到這個自動生成的 ID?在 SQL Server 上可在全局變數 @@IDENTITY 中得到,它返回到調用程式(這裡使用 SELECT 語句)。

可以看到,藉助存儲過程,可以有多種方法完成相同的工作。不過,所選擇的方法受所用 DBMS 特性的制約。

五、小結

本文介紹了什麼是存儲過程,為什麼使用存儲過程。我們介紹了執行和創建存儲過程的語法,使用存儲過程的一些方法。

存儲過程是個相當重要的主題,一篇文章無法全部涉及。

各種 DBMS 對存儲過程的實現不一,你使用的 DBMS 可能提供了一些這裡提到的功能,也有其他未提及的功能,更詳細的介紹請參閱具體的 DBMS 文檔。

原文鏈接:https://www.developerastrid.com/sql/sql-stored-procedures/

(完)


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

-Advertisement-
Play Games
更多相關文章
  • Termius是微軟的一款SSH終端工具,它支持多平臺。而且操作界面十分ha好看且簡潔,今天分享給大家❤️ 軟體下載 關註下方公眾號,回覆termius獲取下載地址 軟體功能介紹 Termius Mac破解版是一款非常好用而且漂亮的SSH客戶端,能快速遠程式控制制伺服器,可以定製自己喜歡的主題,支持FT ...
  • 一、概述 Impala 直接針對存儲在 HDFS、HBase或 Amazon Simple Storage Service (S3)中的 Apache Hadoop 數據提供快速的互動式 SQL 查詢。Impala是一個基於Hive、分散式、大規模並行處理(MPP:Massively Paralle ...
  • 軟硬體環境 軟體 版本 操作系統 Redhat 7 及以上版本,麒麟V10 DM 資料庫 DM 8.0 及以上版本 CPU 架構 x86、ARM、龍芯、飛騰等國內外主流 CPU DM-Oracle 環境準備 在DM伺服器中完成Dblink環境準備工作。 配置Oracle oci客戶端 在Oracle ...
  • 導讀: 隨著全球數據量的不斷增長,越來越多的業務需要支撐高併發、高可用、可擴展、以及海量的數據存儲,在這種情況下,適應各種場景的數據存儲技術也不斷的產生和發展。與此同時,各種資料庫之間的同步與轉化的需求也不斷增多,數據集成成為大數據領域的熱門方向,於是SeaTunnel應運而生。SeaTunnel是 ...
  • hive 存儲格式有很多,但常用的一般是 TextFile、ORC、Parquet 格式,在我們單位最多的也是這三種 hive 預設的文件存儲格式是 TextFile。 除 TextFile 外的其他格式的表不能直接從本地文件導入數據,要先導入到 TextFile 格式的表中,再從表中用 inser ...
  • 本文介紹 SQL 所涉及的幾個數據處理特性:約束、索引和觸發器。約束是實施引用完整性的重要部分,索引可改善數據檢索的性能,觸發器可以用來執行運行前後的處理。 一、約束 SQL 已經改進過多個版本,成為非常完善和強大的語言。許多強有力的特性給用戶提供了高級的數據處理技術,如約束。 關聯表和引用完整性已 ...
  • 本文介紹什麼是 SQL 游標,為什麼使用游標,如何使用游標。你使用的 DBMS 可能會提供某種形式的游標,以及這裡沒有提及的功能。更詳細的內容請參閱具體的 DBMS 文檔。 一、游標 SQL 檢索操作返回一組稱為結果集的行,這組返回的行都是與 SQL 語句相匹配的行(零行到多行)。 簡單地使用 SE ...
  • 本文介紹什麼是 SQL 事務處理,如何利用 COMMIT 和 ROLLBACK 語句對何時寫數據、何時撤銷進行明確的管理;還學習瞭如何使用保留點,更好地控制回退操作。 一、事務處理 使用事務處理(transaction processing),通過確保成批的 SQL 操作要麼完全執行,要麼完全不執行 ...
一周排行
    -Advertisement-
    Play Games
  • 什麼是工廠模式 工廠模式是最常用的設計模式之一,屬於創建型模式。 有點: 解耦,可以把對象的創建和過程分開 減少代碼量,易於維護 什麼時候用? 當一個抽象類有多個實現的時候,需要多次實例化的時候,就要考慮使用工廠模式。 比如:登錄的抽象類ILoginBusiness,它有2個實現,一個用用戶名密碼登 ...
  • 這次iNeuOS升級主要升級圖形渲染引擎和增加豐富的圖元信息,可以很快的方案應用。總共增加41個通用和行業領域的圖元應用,增加2154個圖元信息,現在iNeuOS視圖建模功能模塊總共包括5894個行業圖元信息。現在完全支持製作高保真的工藝流程和大屏展示效果。 ...
  • 效果圖先附上: 首先 這是我是參考 教程:使用 SignalR 2 和 MVC 5 實時聊天 | Microsoft Docs 先附上教程: 在“添加新項 - SignalRChat”中,選擇 InstalledVisual> C#>WebSignalR>,然後選擇 SignalR Hub 類 (v ...
  • 一、前言 項目中之前涉及到胎兒心率圖曲線的繪製,最近項目中還需要添加心電曲線和血樣曲線的繪製功能。今天就來分享一下心電曲線的繪製方式; 二、正文 1、胎兒心率曲線的繪製是通過DrawingVisual來實現的,這裡的心電曲線我也是採用差不多相同的方式來實現的,只是兩者曲線的數據有所區別。心電圖的數據 ...
  • 安裝 Redis # 首先安裝依賴gcc, 後面需要使用make編譯redis yum install gcc -y # 進入 /usr/local/src 目錄, 把源碼下載到這裡 cd /usr/local/src # 下載 redis 7.0.2 的源碼,github被牆,可以使用國內的地址 ...
  • Redis 的定義? 百度百科: Redis(Remote Dictionary Server ),即遠程字典服務,是一個開源的使用ANSI C語言編寫、支持網路、可基於記憶體亦可持久化的日誌型、Key-Value資料庫,並提供多種語言的API。 中文官網: Redis是一個開源(BSD許可),記憶體存 ...
  • 事情的起因是收到了一位網友的請求,他的java課設需要設計實現迷宮相關的程式——如標題概括。 我這邊不方便透露相關信息,就只把任務要求寫出來。 演示視頻指路👉: 基於JavaFX圖形界面的迷宮程式演示_嗶哩嗶哩_bilibili 完整代碼鏈接🔎: 網盤:https://pan.baidu.com ...
  • Python中的字典 Python中的字典是另一種可變容器模型,且可存儲任意類型對象。鍵值使用冒號分割,你可以看成是一串json。 常用方法 獲取字典中的值 dict[key] 如果key不存在會報錯,建議使用dict.get(key),不存在返回None 修改和新建字典值 dict[key]=va ...
  • 迎面走來了你的面試官,身穿格子衫,挺著啤酒肚,髮際線嚴重後移的中年男子。 手拿泡著枸杞的保溫杯,胳膊夾著MacBook,MacBook上還貼著公司標語:“加班使我快樂”。 面試官: 看你簡歷上用過MySQL,問你幾個簡單的問題吧。什麼是聚簇索引和非聚簇索引? 這個問題難不住我啊。來之前我看一下一燈M ...
  • tunm二進位協議在python上的實現 tunm是一種對標JSON的二進位協議, 支持JSON的所有類型的動態組合 支持的數據類型 基本支持的類型 "u8", "i8", "u16", "i16", "u32", "i32", "u64", "i64", "varint", "float", "s ...