如何使用 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
  • 前言 本文將以 C# 語言來實現一個簡單的布隆過濾器,為簡化說明,設計得很簡單,僅供學習使用。 感謝@時總百忙之中的指導。 布隆過濾器簡介 布隆過濾器(Bloom filter)是一種特殊的 Hash Table,能夠以較小的存儲空間較快地判斷出數據是否存在。常用於允許一定誤判率的數據過濾及防止緩存 ...
  • 目錄 一.簡介 二.效果演示 三.源碼下載 四.猜你喜歡 零基礎 OpenGL (ES) 學習路線推薦 : OpenGL (ES) 學習目錄 >> OpenGL ES 基礎 零基礎 OpenGL (ES) 學習路線推薦 : OpenGL (ES) 學習目錄 >> OpenGL ES 轉場 零基礎 O ...
  • 「簡單有價值的事情長期堅持做」 這是成功最簡單,但也最難學的秘訣。不經過訓練,人很難意識到時間複利的威力。 仙劍奇俠傳的「十里坡劍神」和金庸群俠傳的「十級野球拳」,就是簡單的事情持之以恆反覆做,最後就有巨大的威力 唐家三少成為網文收入第一,最重要的一步是十四年從未斷日更 這樣的案例很多,一開始可能成 ...
  • 迎面走來了你的面試官,身穿格子衫,挺著啤酒肚,髮際線嚴重後移的中年男子。 手拿泡著枸杞的保溫杯,胳膊夾著MacBook,MacBook上還貼著公司標語:“我愛加班”。 面試開始,直入正題。 面試官: 看你簡歷上面寫著精通MySQL,我先問你事務的特性是什麼? 老生常談,這個還有誰不會背的嗎? 我: ...
  • 基礎知識 python是一門腳本語言,它是解釋執行的。 python使用縮進做為語法,而且python2環境下同一個py文件中不能同時存在tab和空格縮進,否則會出錯,建議在IDE中顯示縮進符。 python在聲明變數時不寫數據類型,可以type(xx)來獲取欄位的類型,然後可以int(),list ...
  • 為什麼要多線程下載 俗話說要以終為始,那麼我們首先要明確多線程下載的目標是什麼,不外乎是為了更快的下載文件。那麼問題來了,多線程下載文件相比於單線程是不是更快? 對於這個問題可以看下圖。 橫坐標是線程數,縱坐標是使用對應線程數下載對應文件時花費的時間,藍橙綠代表下載文件的大小,每個線程下載對應文件2 ...
  • 詳細講解python爬蟲代碼,爬微博搜索結果的博文數據。 爬取欄位: 頁碼、微博id、微博bid、微博作者、發佈時間、微博內容、轉發數、評論數、點贊數。 爬蟲技術: 1、requests 發送請求 2、datetime 時間格式轉換 3、jsonpath 快速解析json數據 4、re 正則表達式提... ...
  • 背景: 一般我們可以用HashMap做本地緩存,但是HashMap功能比較弱,不支持Key過期,不支持數據範圍查找等。故在此實現了一個簡易的本地緩存,取名叫fastmap。 功能: 1.支持數據過期 2.支持等值查找 3.支持範圍查找 4.支持key排序 實現思路: 1.等值查找採用HashMap2 ...
  • 目錄 一.簡介 二.效果演示 三.源碼下載 四.猜你喜歡 零基礎 OpenGL (ES) 學習路線推薦 : OpenGL (ES) 學習目錄 >> OpenGL ES 基礎 零基礎 OpenGL (ES) 學習路線推薦 : OpenGL (ES) 學習目錄 >> OpenGL ES 轉場 零基礎 O ...
  • 本章是系列文章的第八章,用著色演算法進行寄存器的分配過程。 本文中的所有內容來自學習DCC888的學習筆記或者自己理解的整理,如需轉載請註明出處。周榮華@燧原科技 寄存器分配 寄存器分配是為程式處理的值找到存儲位置的問題 這些值可以存放到寄存器,也可以存放在記憶體中 寄存器更快,但數量有限 記憶體很多,但 ...