存儲過程在數據庫課程教學中的講解

常志東

數據庫技術在軟件開發中應用廣泛，存儲過程又是數據庫課程教學中的重點和難點。教師在可以結合日常事物來講授存儲過程相關的知識點。

使用“組裝工具”概念引入存儲過程

在講解中，教師可向學生舉例，在日常生活中存在這樣一種工具，該工具經常會被用到，但是它是由幾個零件組裝而成。如果將工具提前組裝好，需要時直接取出使用，就能極大地提高工作效率，同時也能杜絕因為錯誤組裝造成工作失誤。而存儲過程就是數據庫技術中數據處理的這樣一個工具，它是由流程控制語句和SQL語句書寫的過程組成，經編譯和優化后存儲在數據庫服務器中以完成特定功能，可以被其他程序調用，用于執行頻繁使用的查詢、業務規則和其他過程使用的公共例行程序。概念中“流程控制語句、SQL語句”是組件，“編譯、優化”是組裝，“數據庫、服務器”是其存儲位置，“被其他程序調用、頻繁使用”是其應用場合。

使用“可變組裝工具”概念來介紹帶參數的存儲過程

而在介紹帶參數的存儲過程時，教師可以舉例假設，在保持工具基礎不變的情況下，通過調整若干個組件型號的方式來迎合不同的工作對象，這樣就讓組裝工具更加靈活、應用更加廣泛，如多用螺絲刀，不同型號的鉆頭針對不同的螺絲。帶參數的存儲過程就類似于“多用螺絲刀”，參數就是鉆頭工具包。參數名是數據表中的字段名，參數值也就是字段對應的、存在于數據表中的值。理解起來就是：鉆頭必須是螺絲刀的組件，且用到的型號必須存在于工具包中。

結合家用電器的自動調節功能引入觸發器

教師在針對觸發器的講解時應明確，觸發器包含了存儲過程這種工具的基本特性，另外還具備特有的功能。我們知道“電冰箱、空調、洗衣機”等家用電器在滿足一定設定條件后才能夠完成自動調節功能。觸發器就與這些家用電器相似，需要滿足一定條件才能觸發，從而執行指定操作。例如，電冰箱“達到設定溫度”是條件，“停機”是觸發結果。觸發器創建語法中“insert，update，delete，drop，alter”等關鍵字就是觸發條件，“as”關鍵字之后的內容就是觸發后執行的操作，即觸發結果。

案例

下面筆者以“學生成績管理系統”開發為例，通過實例演示來講解存儲過程的應用。取出數據庫中學生基本信息、課程信息、學生成績三個表，詳細信息如下：

學生基本信息表（student）：學號（stuno）、姓名（stuname）、班級（class）、狀態（state）；課程信息表（course）：課程號（couno）、課程名（couname）；學生成績表（results）：學號（stuno）、課程號（couno）、成績（score）、狀態（state）。三個表之間通過主外鍵建立數據關系。（注：僅用于方法說明需要）

1.學生成績查詢功能

學生根據學號查詢自己的成績，實現

的SQL語句為：“selectstuname，class，couname，score from student a，course b，results c where a.stuno=c.stuno and b.couno=c.couno and a.stuno=@stuno”@stuno為學號變量。

當不使用存儲過程時，是在應用端直接為學號變量賦值后將SQL語句提交給數據庫服務器，數據庫服務器執行SQL語句，在數據庫中找到這些表以及相應的字段后將查詢結果返回給應用端這個過程極其浪費時間和資源，違背程序設計的原則。

在使用存儲過程時，首先在數據庫服務器上創建名為checkresult的存儲過程，SQL語句如下：

create procedure checkresult

@stuno nvarchar（30）--定義學號變量，針對不同學號學生查詢

as

select stuname，class，couname，score from student a，course b，results c where a.stuno=c.stuno and b.couno=c.couno and a.stuno=@stuno

go

這樣checkresult存儲過程就被存儲于數據庫服務器上，形成一個組裝工具，并且可以更換不同型號的組件；存儲過程執行結果會以一個虛擬表的形式存在于數據庫服務器上。

在應用程序端，調用存儲過程：“declare @xh nvarchar（30） set @xh=`應用程序中對應的學號變量值`execute checkresult @xh go”，就可以直接從虛擬表中查詢數據，速度快，效率高。

2.學生休學功能

學生申請休學后，將基本信息表中的狀態字段（state）修改為休學，同時學生成績表中對應該學生的成績狀態修改為不可查。SQL語句為：“update student set state='休學` where stuno=@stuno”、“update results set state='不可查` where stuno=@stuno”，在應用程序端，先執行第一段SQL語句，再執行第二段，就可以實現該功能。不過這樣需要與數據庫服務器有兩次交互，并且必須保證第一段SQL語句正確執行，否則就會造成數據一致性出錯。另外，如果學生成績表中無該生成績，應用程序還可能報錯，這樣在應用程序端就需要增加驗錯功能，給程序員增加了工作量，也同時增加了數據庫服務器和應用程序服務器的負擔。

在使用觸發器時，在數據庫服務器端創建觸發器suspend，針對學生基本信息表的“修改”操作觸發后，針對學生成績表的狀態字段進行自動修改。SQL語句如下：

create trigger suspend

on student--執行修改操作的數據表

after update

as

update results set state='不可查'where stuno=（select stuno from deleted）

go

deleted表是建在數據庫服務器的內存中，由系統管理的邏輯表，對于修改記錄（update）操作，deleted表里存放的是修改前的記錄（修改完成后即被刪除）；對于刪除記錄（delete）操作，deleted表里存放的是被刪除的舊記錄。

創建完成后，在應用程序端只需要設計修改學生基本信息表中狀態字段的程序，只要SQL語句正確執行，就會觸發對學生成績表的修改操作。整個過程數據庫服務器與應用程序服務器只需要一次交互即可，并且很好地解決了成績表中記錄為空時出錯的問題。