第10章-存儲過程

1、精選優(yōu)質(zhì)文檔-傾情為你奉上第 章10存 儲 過 程10.1 練習(xí)題10及參考答案1什么是存儲過程？存儲過程分為哪幾類？使用存儲過程有什么好處？答：存儲過程是一系列預(yù)先編輯好的、能實(shí)現(xiàn)特定數(shù)據(jù)操作功能的SQL代碼集，它與特定的數(shù)據(jù)庫相關(guān)聯(lián)，存儲在SQL Server服務(wù)器上。用戶可以像使用函數(shù)一樣重復(fù)調(diào)用這些存儲過程，實(shí)現(xiàn)它所定義的操作。存儲過程分為3類：系統(tǒng)提供的存儲過程、用戶定義的存儲過程和擴(kuò)展存儲過程。使用存儲過程有如下好處如下：（1）存儲過程提供了處理復(fù)雜任務(wù)的能力：存儲過程提供了許多標(biāo)準(zhǔn)SQL語言所沒有的高級特性，通過傳遞參數(shù)和執(zhí)行邏輯表達(dá)式，能夠處理復(fù)雜任務(wù)。（2）增強(qiáng)代碼的重用性和

2、共享性：每一個存儲過程可以在系統(tǒng)中重復(fù)地調(diào)用，可以被多個有訪問權(quán)限的用戶訪問。存儲過程可以增強(qiáng)代碼的重用性和共享性，加快應(yīng)用系統(tǒng)的開發(fā)速度，提高開發(fā)的質(zhì)量和效率。（3）減少網(wǎng)絡(luò)數(shù)據(jù)流量：存儲過程是存放在服務(wù)器中并在服務(wù)器上運(yùn)行的，應(yīng)用系統(tǒng)調(diào)用存儲過程時(shí)只有觸發(fā)執(zhí)行存儲過程的命令和執(zhí)行結(jié)束返回的結(jié)果在網(wǎng)絡(luò)中傳輸。所以，使用存儲過程可以減少網(wǎng)絡(luò)中的數(shù)據(jù)流量。（4）加快系統(tǒng)運(yùn)行速度：第一次執(zhí)行后的存儲過程會駐留在內(nèi)存中，以后可以直接運(yùn)行，從而加快應(yīng)用系統(tǒng)的處理速度。（5）加強(qiáng)系統(tǒng)安全性：SQL Server可以不授予用戶某些表、視圖的訪問權(quán)限，但授予用戶執(zhí)行存儲過程的權(quán)限，通過存儲過程來對這些表或視

3、圖進(jìn)行訪問操作。從而保證表中數(shù)據(jù)的安全性。2修改存儲過程有哪幾種方法？假設(shè)有一個存儲過程需要修改但又不希望影響現(xiàn)有的權(quán)限，應(yīng)使用哪個語句來進(jìn)行修改？答：修改存儲過程有兩種方法：一種方法是把舊的存儲過程刪除，然后再重新建立該存儲過程。另一種方法是用單個的步驟更改該存儲過程。使用前一種方法修改存儲過程，所有與該存儲過程相關(guān)聯(lián)的權(quán)限都將丟失。而使用后一種方法可以更改過程或參數(shù)定義，但為該存儲過程定義的權(quán)限將保留。所以要修改一個存儲過程但又不希望影響現(xiàn)有的權(quán)限可使用后一種方法。使用的語句為ALTER PROCEDURE。10.2 上機(jī)實(shí)驗(yàn)題9及操作過程在上機(jī)實(shí)驗(yàn)題8的factory數(shù)據(jù)庫上，使用T-S

4、QL語句完成如下各題：（1）創(chuàng)建一個為worker表添加職工記錄的存儲過程Addworker。（2）創(chuàng)建一個存儲過程Delworker刪除worker表中指定職工號的記錄。（3）顯示存儲過程Delworker。（4）刪除存儲過程Addworker和Delworker。操作過程（1）建立存儲過程的程序如下：USE factoryGOCREATE PROCEDURE Addworker no int=NULL, name char(10)=NULL, sex char(2)=NULL, birthday datetime=NULL, na char(2)=NULL, wtime datetime=

5、NULL, depno int=NULLASIF no IS NULL OR name IS NULL OR sex IS NULL OR birthday IS NULL OR depno IS NULLBEGIN PRINT '請重新輸入該職工信息！' PRINT '你必須提供職工號、姓名、性別、出生日期、部門號' RETURNENDBEGIN TRANSACTION INSERT INTO worker VALUES(no,name,sex,birthday,na,wtime,depno) IF error<>0 BEGIN ROLLBACK

6、TRAN RETURN ENDCOMMIT TRANSACTIONPRINT '職工'+name+'的信息成功添加到表worker中'執(zhí)行下列語句，可驗(yàn)證存儲過程的正確性：USE factoryGOAddworker 20,'陳立','女','55/03/08','否','75/10/10',4GOSELECT 職工號,姓名,性別,黨員否 FROM workerGO執(zhí)行結(jié)果如下：職工陳立 的信息成功添加到表worker中職工號 姓名 性別 黨員否- - - -1 孫華 男 是10 陳

7、濤 男 否11 劉欣 男 否12 李涵 男 是13 王小燕 女 否14 李藝 女 否15 魏君 女 否2 孫天奇 女 否20 陳立 女 否3 陳明 男 否4 李華 男 否5 余慧 男 是6 歐陽少兵 男 否7 程西 女 是8 張旗 男 否9 劉夫文 男 是（2）建立存儲過程的程序如下：USE factoryGOCREATE PROCEDURE Delworker no int=NULLASIF no IS NULLBEGIN PRINT '必須輸入職工號！' RETURNENDBEGIN TRANSACTION DELETE FROM worker WHERE 職工號=no I

8、F error<>0 BEGIN ROLLBACK TRAN RETURN ENDCOMMIT TRANSACTIONPRINT '成功刪除職工號為'+CAST(no AS CHAR(2)+'的職工記錄'執(zhí)行下列語句，可驗(yàn)證存儲過程的正確性：USE factoryGODelworker 20GOSELECT 職工號,姓名,性別,黨員否 FROM workerGO執(zhí)行結(jié)果如下：成功刪除職工號為的職工記錄職工號 姓名 性別 黨員否- - - -1 孫華 男 是10 陳濤 男 否11 劉欣 男 否12 李涵 男 是13 王小燕 女 否14 李藝 女 否15

9、 魏君 女 否2 孫天奇 女 否3 陳明 男 否4 李華 男 否5 余慧 男 是6 歐陽少兵 男 否7 程西 女 是8 張旗 男 否9 劉夫文 男 是（3）對應(yīng)的程序如下：USE factoryGOEXEC sp_helptext DelworkerGO執(zhí)行結(jié)果如下：Text-CREATE PROCEDURE Delworker no int=NULLASIF no IS NULLBEGIN PRINT '必須輸入職工號！' RETURNENDBEGIN TRANSACTION DELETE FROM worker WHERE 職工號=no IF error<>0 BEGIN ROLLBACK TRAN RETURN ENDCOMMIT TRANSACTIONPRINT '成功刪除職工號為'+CAST(no AS CHAR(2)+'的職工記錄'（4）對應(yīng)的程序如下：USE factoryGOIF EXISTS (SELECT name FROM sysobjects WHERE name = 'Addworker' AND type ='P') DROP PROCEDURE A