存儲(chǔ)過程在數(shù)據(jù)庫課程教學(xué)中的講解

常志東

數(shù)據(jù)庫技術(shù)在軟件開發(fā)中應(yīng)用廣泛，存儲(chǔ)過程又是數(shù)據(jù)庫課程教學(xué)中的重點(diǎn)和難點(diǎn)。教師在可以結(jié)合日常事物來講授存儲(chǔ)過程相關(guān)的知識(shí)點(diǎn)。

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

在講解中，教師可向?qū)W生舉例，在日常生活中存在這樣一種工具，該工具經(jīng)常會(huì)被用到，但是它是由幾個(gè)零件組裝而成。如果將工具提前組裝好，需要時(shí)直接取出使用，就能極大地提高工作效率，同時(shí)也能杜絕因?yàn)殄e(cuò)誤組裝造成工作失誤。而存儲(chǔ)過程就是數(shù)據(jù)庫技術(shù)中數(shù)據(jù)處理的這樣一個(gè)工具，它是由流程控制語句和SQL語句書寫的過程組成，經(jīng)編譯和優(yōu)化后存儲(chǔ)在數(shù)據(jù)庫服務(wù)器中以完成特定功能，可以被其他程序調(diào)用，用于執(zhí)行頻繁使用的查詢、業(yè)務(wù)規(guī)則和其他過程使用的公共例行程序。概念中“流程控制語句、SQL語句”是組件，“編譯、優(yōu)化”是組裝，“數(shù)據(jù)庫、服務(wù)器”是其存儲(chǔ)位置，“被其他程序調(diào)用、頻繁使用”是其應(yīng)用場(chǎng)合。

使用“可變組裝工具”概念來介紹帶參數(shù)的存儲(chǔ)過程

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

結(jié)合家用電器的自動(dòng)調(diào)節(jié)功能引入觸發(fā)器

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

案例

下面筆者以“學(xué)生成績(jī)管理系統(tǒng)”開發(fā)為例，通過實(shí)例演示來講解存儲(chǔ)過程的應(yīng)用。取出數(shù)據(jù)庫中學(xué)生基本信息、課程信息、學(xué)生成績(jī)?nèi)齻€(gè)表，詳細(xì)信息如下：

學(xué)生基本信息表（student）：學(xué)號(hào)（stuno）、姓名（stuname）、班級(jí)（class）、狀態(tài)（state）；課程信息表（course）：課程號(hào)（couno）、課程名（couname）；學(xué)生成績(jī)表（results）：學(xué)號(hào)（stuno）、課程號(hào)（couno）、成績(jī)（score）、狀態(tài)（state）。三個(gè)表之間通過主外鍵建立數(shù)據(jù)關(guān)系。（注：僅用于方法說明需要）

1.學(xué)生成績(jī)查詢功能

學(xué)生根據(jù)學(xué)號(hào)查詢自己的成績(jī)，實(shí)現(xiàn)

的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為學(xué)號(hào)變量。

當(dāng)不使用存儲(chǔ)過程時(shí)，是在應(yīng)用端直接為學(xué)號(hào)變量賦值后將SQL語句提交給數(shù)據(jù)庫服務(wù)器，數(shù)據(jù)庫服務(wù)器執(zhí)行SQL語句，在數(shù)據(jù)庫中找到這些表以及相應(yīng)的字段后將查詢結(jié)果返回給應(yīng)用端這個(gè)過程極其浪費(fèi)時(shí)間和資源，違背程序設(shè)計(jì)的原則。

在使用存儲(chǔ)過程時(shí)，首先在數(shù)據(jù)庫服務(wù)器上創(chuàng)建名為checkresult的存儲(chǔ)過程，SQL語句如下：

create procedure checkresult

@stuno nvarchar（30）--定義學(xué)號(hào)變量，針對(duì)不同學(xué)號(hào)學(xué)生查詢

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存儲(chǔ)過程就被存儲(chǔ)于數(shù)據(jù)庫服務(wù)器上，形成一個(gè)組裝工具，并且可以更換不同型號(hào)的組件；存儲(chǔ)過程執(zhí)行結(jié)果會(huì)以一個(gè)虛擬表的形式存在于數(shù)據(jù)庫服務(wù)器上。

在應(yīng)用程序端，調(diào)用存儲(chǔ)過程：“declare @xh nvarchar（30） set @xh=`應(yīng)用程序中對(duì)應(yīng)的學(xué)號(hào)變量值`execute checkresult @xh go”，就可以直接從虛擬表中查詢數(shù)據(jù)，速度快，效率高。

2.學(xué)生休學(xué)功能

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

在使用觸發(fā)器時(shí)，在數(shù)據(jù)庫服務(wù)器端創(chuàng)建觸發(fā)器suspend，針對(duì)學(xué)生基本信息表的“修改”操作觸發(fā)后，針對(duì)學(xué)生成績(jī)表的狀態(tài)字段進(jìn)行自動(dòng)修改。SQL語句如下：

create trigger suspend

on student--執(zhí)行修改操作的數(shù)據(jù)表

after update

as

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

go

deleted表是建在數(shù)據(jù)庫服務(wù)器的內(nèi)存中，由系統(tǒng)管理的邏輯表，對(duì)于修改記錄（update）操作，deleted表里存放的是修改前的記錄（修改完成后即被刪除）；對(duì)于刪除記錄（delete）操作，deleted表里存放的是被刪除的舊記錄。

創(chuàng)建完成后，在應(yīng)用程序端只需要設(shè)計(jì)修改學(xué)生基本信息表中狀態(tài)字段的程序，只要SQL語句正確執(zhí)行，就會(huì)觸發(fā)對(duì)學(xué)生成績(jī)表的修改操作。整個(gè)過程數(shù)據(jù)庫服務(wù)器與應(yīng)用程序服務(wù)器只需要一次交互即可，并且很好地解決了成績(jī)表中記錄為空時(shí)出錯(cuò)的問題。