實(shí)驗(yàn)六管理存儲(chǔ)過(guò)程

1、實(shí)驗(yàn)六 存儲(chǔ)過(guò)程與觸發(fā)器一、存儲(chǔ)過(guò)程【創(chuàng)建存儲(chǔ)過(guò)程】：CREATE PROCEDURE OWNER.PROCEDURE NAME AS <SQL塊> 如：Use pubsGoCreate procedure author_informationAs select au_lname,au_fname,title,pub_name from authors a join titleauthor ta on a.au_id=ta.au_id join titles t on t.title_id=ta.title_id join publishers p on t.pub_id=p.pu

2、b_idGo【管理存儲(chǔ)過(guò)程】：Ø 可以使用sp_helptext命令查看創(chuàng)建存儲(chǔ)過(guò)程的文本信息。Use pubsGoSp_helptext author_informationGoØ 可以用sp_help查看存儲(chǔ)過(guò)程的一般信息。Use pubsGoSp_help author_informationGoØ 可以使用系統(tǒng)存儲(chǔ)過(guò)程sp_rename修改存儲(chǔ)過(guò)程的名字。Use pubsGoSp_rename author_information ,authors_informationGoØ 也可以使用企業(yè)管理瀏覽存儲(chǔ)過(guò)程的信息，具體方法是：² 從樹(shù)

3、型結(jié)構(gòu)上選中存儲(chǔ)過(guò)程所在的數(shù)據(jù)庫(kù)節(jié)點(diǎn)，展開(kāi)該節(jié)點(diǎn)；2 / 11² 選中數(shù)據(jù)庫(kù)節(jié)點(diǎn)下的存儲(chǔ)過(guò)程節(jié)點(diǎn)，則右邊的列表列出了數(shù)據(jù)庫(kù)中目前所有的存儲(chǔ) 過(guò)程；² 選中存儲(chǔ)過(guò)程，右擊，執(zhí)行屬性命令，則系統(tǒng)將彈出如圖所示對(duì)話框。 ² 可以在對(duì)話框中修改存儲(chǔ)過(guò)程內(nèi)容，并保存修改。² 如果想知道某個(gè)表被存儲(chǔ)過(guò)程引用的情況，可以使用sp_depends，Sp_depends authors² 如果想知道某個(gè)存儲(chǔ)過(guò)程引用表的情況，則可以使用Sp_depends procedure_name【執(zhí)行存儲(chǔ)過(guò)程】：對(duì)于存儲(chǔ)過(guò)程的調(diào)用，應(yīng)使用EXECUTE或EXEC關(guān)鍵字。Us

4、e pubsGo Exec authors_informationGo【刪除存儲(chǔ)過(guò)程】：Ø DROP PROCEDURE procedure,nUse pubsGoDrop procedure authors_informationGoØ 也可以使用企業(yè)管理器來(lái)刪除存儲(chǔ)過(guò)程，方法是：² 從樹(shù)型結(jié)構(gòu)上選中存儲(chǔ)過(guò)程所在的數(shù)據(jù)庫(kù)節(jié)點(diǎn)，展開(kāi)該節(jié)點(diǎn)；² 選中數(shù)據(jù)庫(kù)節(jié)點(diǎn)下的存儲(chǔ)過(guò)程節(jié)點(diǎn)，則右邊的列表列出了數(shù)據(jù)庫(kù)中目前所有的存儲(chǔ)過(guò)程；選中要?jiǎng)h除的存儲(chǔ)過(guò)程，右擊，執(zhí)行刪除命令，則系統(tǒng)將彈出如圖所示對(duì)話框。單擊全部除去按鈕，將刪除所選中的存儲(chǔ)過(guò)程?！拘薷拇鎯?chǔ)過(guò)程】：SQL

5、 server允許在不改變存儲(chǔ)過(guò)程使用許可，不改變名字的情況下，對(duì)存儲(chǔ)過(guò)程進(jìn)行定義的修改。Alter procedure procedure_nameAS sql_statement 二、觸發(fā)器 觸發(fā)器（triegger）是一種特殊的存儲(chǔ)過(guò)程，它與表格緊密相連，可以看作是表格定義的一部分。當(dāng)使用UPDATE、INSERT或DELETE等語(yǔ)句對(duì)表進(jìn)行修改操作時(shí)，DBA常擴(kuò)觸發(fā)器來(lái)實(shí)現(xiàn)自動(dòng)觸發(fā)的處理方法。當(dāng)用戶修改指定表或視圖中的數(shù)據(jù)時(shí)，觸發(fā)器將會(huì)自動(dòng)執(zhí)行。觸發(fā)器基于一個(gè)表創(chuàng)建，但是可能針對(duì)多個(gè)表進(jìn)行操作。所以觸發(fā)器常被用來(lái)實(shí)現(xiàn)復(fù)雜的商業(yè)規(guī)則。 例如：在pubs數(shù)據(jù)庫(kù)晨，存放著出版商publish

6、ers的信息，出版物的信息titles、出版特與作者關(guān)聯(lián)的信息titleauthor以及作者作息authors。現(xiàn)在，有一條出版商的信息被刪除了，則所以由該出版商的出版物都應(yīng)該將pub_id修改為NULL，或者刪除有關(guān)的出版物信息。同樣titleauthor表中的信息也應(yīng)該相應(yīng)地得到修改。這樣關(guān)聯(lián)到三張表的一致性維護(hù)問(wèn)題，可以使用觸發(fā)器來(lái)實(shí)現(xiàn)。在publishers表上設(shè)置一個(gè)DELETE觸發(fā)器，當(dāng)刪除一條publishers信息時(shí)，觸發(fā)器自動(dòng)執(zhí)行，對(duì)titles表和titleauthor表進(jìn)行修改。 在SQL server中一張表可以有多個(gè)觸發(fā)器。用戶可以針對(duì)INSERT、UPDATE或DE

7、LETE語(yǔ)句分別設(shè)置觸發(fā)器，也可以針對(duì)一張表上的特定操作設(shè)置多個(gè)觸發(fā)器。解發(fā)器可以容納非常復(fù)雜的Transact-SQL語(yǔ)句。但是，不管觸發(fā)器所進(jìn)行的操作有多復(fù)雜，觸發(fā)器都只作為一個(gè)獨(dú)立的單元被執(zhí)行，被看作是一個(gè)事務(wù)。如果在執(zhí)行觸發(fā)器的過(guò)程中發(fā)生了錯(cuò)誤，則整個(gè)事務(wù)將會(huì)自動(dòng)回退。 觸發(fā)器在服務(wù)器將特定的操作（UPDATE、INSERT、DELETE）執(zhí)行結(jié)束后才執(zhí)行。如果在執(zhí)行特定數(shù)據(jù)庫(kù)操作的過(guò)程中，發(fā)生了系統(tǒng)錯(cuò)誤，則觸發(fā)器不會(huì)被觸發(fā)。這種觸發(fā)器類型是默認(rèn)的類型，好AFTER類型。 在SQL server 2000中引進(jìn)了一種新的觸發(fā)器類型：INSTEAD OF類型。這種類型的觸發(fā)器取代了觸發(fā)該

8、觸發(fā)器執(zhí)行的SQL代碼。換句話說(shuō)，它將覆蓋該代碼引起的變化。通常這種類型的觸發(fā)器用在需要維護(hù)數(shù)據(jù)一致性的地方。 觸發(fā)器主要提供下列功能：Ø 級(jí)聯(lián)修改數(shù)據(jù)庫(kù)中的所有相關(guān)表Ø 撤消或回退違反引用完整性的操作，防止非法修改數(shù)據(jù)Ø 執(zhí)行比核查約束CHECK更復(fù)雜的約束操作Ø 查找在數(shù)據(jù)修改前后的表狀態(tài)之間的差別，并根據(jù)差別分別采取相應(yīng)的措施Ø 在一張表的同一類型的操作（UPDATE、INSERT、DELETE）上設(shè)置多個(gè)觸發(fā)器，從而可以針對(duì)同樣的修改語(yǔ)句執(zhí)行不同的多種操作注意：u 只有表的擁有者才可以在表上創(chuàng)建或刪除觸發(fā)器，這樣權(quán)限不許轉(zhuǎn)授。u 盡管可

9、以在觸發(fā)器中引用視圖或臨時(shí)表，但不能在視圖或臨時(shí)表或系統(tǒng)表上創(chuàng)建觸發(fā)器。u 使用UPDATE語(yǔ)句可以一次對(duì)多個(gè)數(shù)據(jù)進(jìn)行修改，但不管修改了多少數(shù)據(jù)，觸發(fā)器都中觸發(fā)一次。u 在執(zhí)行修改語(yǔ)句過(guò)程中，觸發(fā)器的執(zhí)行是修改語(yǔ)句事務(wù)的一部分。所以，如果觸發(fā)器執(zhí)行不成功，則整個(gè)修改事務(wù)將會(huì)回退。u 當(dāng)使用約束、規(guī)則、默認(rèn)值就可以實(shí)現(xiàn)預(yù)定的數(shù)據(jù)完整性時(shí)，應(yīng)優(yōu)先考慮使用這三種措施u TRUNCATE TABLE 雖然在功能上與DELETE操作類似，但是 TRUNCATE TABLE不會(huì)觸發(fā)DELETE觸發(fā)器運(yùn)行?！緞?chuàng)建觸發(fā)器】：CREATE TRIGGER trigger_name ON table|view A

10、S <SQL塊> Use NorthwindIf exists (select name from sysobjects Where name=tr_procedure_update AND type=TRDrop trigger tr_product_updateGoCreate trigger tr_product_update ON productsFor UPDATEDeclare msg varchar(100)Select msg=str(rowcount)+”employees updated by this statement”P(pán)rint msgReturnGo這個(gè)

11、觸發(fā)器在用戶針對(duì)products表執(zhí)行UPDATE執(zhí)行，返回共修改了多少行數(shù)據(jù)。其中msg是一個(gè)變量，數(shù)據(jù)類型為varchar(100),rowcount是一個(gè)系統(tǒng)存儲(chǔ)過(guò)程。它返回當(dāng)前被修改的行數(shù)。創(chuàng)建觸發(fā)器的語(yǔ)句CREATE TRIGGER必須寫(xiě)在批處理的第一行，否則系統(tǒng)將會(huì)返回錯(cuò)誤信息。在CREATE TRIGGER語(yǔ)句中，不能使用SELECT語(yǔ)句返回針對(duì)表格查詢的數(shù)據(jù)，因?yàn)橛|發(fā)器不接收用戶應(yīng)用程序傳遞的參數(shù)，從而也無(wú)法向用戶應(yīng)用程序返回查詢表格數(shù)據(jù)所得到的結(jié)果。注意：由于系統(tǒng)表怕存儲(chǔ)數(shù)據(jù)的特殊性和重要性，所以建議用戶不要自己在系統(tǒng)表上建立觸發(fā)器?！竟芾碛|發(fā)器】： 觸發(fā)器是特殊的存儲(chǔ)過(guò)程，

12、所以適用于存儲(chǔ)過(guò)程的管理方式，都適用于觸發(fā)器。所以用戶完全可以使用sp_helptext，sp_help，sp_depens等系統(tǒng)存儲(chǔ)過(guò)程，以及使用企業(yè)管理器來(lái)瀏覽觸發(fā)器的有關(guān)信息，也可以使用sp_helptrigger來(lái)瀏覽指定表格上，指定類型的觸發(fā)器的信息，語(yǔ)法是： Sp_helptrigger table_name, type如：use northwind GoSp_helptrigger products, delete提示：如果不設(shè)置type的值，則返回定義在該表上的所有觸發(fā)器的信息?！緞h除觸發(fā)器】：Ø DROP TRIGGER trigger ,n當(dāng)用戶刪除某個(gè)表格時(shí)，所

13、有建立在該表上的觸發(fā)器都將被刪除。步驟為： 選中要?jiǎng)?chuàng)建觸發(fā)器的表所在的數(shù)據(jù)庫(kù)，展開(kāi)該節(jié)點(diǎn)； 展開(kāi)該數(shù)據(jù)庫(kù)節(jié)點(diǎn)下的表節(jié)點(diǎn)； 選中指定的表，右擊，從快捷菜單中展開(kāi)全部任務(wù)子菜單，執(zhí)行管理觸發(fā)器命令，將彈出如圖所示對(duì)話框； 從名稱下拉菜單里選擇要?jiǎng)h除的觸發(fā)器； 單擊刪除按鈕，刪除該觸發(fā)器； 單擊確定按鈕完成操作?！拘薷挠|發(fā)器】：Alter trigger trigger_nameON table . Transact-SQL編程：全局變量以兩個(gè)為標(biāo)記，如CONNECTION局部變量以一個(gè)為標(biāo)記，如A定義局部變量：DECLARE local_varible1 char(4), local_varibl

14、e2 char(8)顯示局部變量：SELECT local_varible1 ， SELECT local_varible2給局部變量賦值：SET local_varible=expressionTransact-SQL語(yǔ)句中以BEGIN和END為程序塊的關(guān)鍵字。無(wú)條件退出語(yǔ)句：RETURN.實(shí)驗(yàn)范例：1、用存儲(chǔ)過(guò)程查詢?nèi)背煽?jī)的學(xué)生學(xué)號(hào)和課程號(hào)create procedure pro1as select sname,cno from student,sc where student.sno=sc.sno and grade is null2、用存儲(chǔ)過(guò)程查詢指定課程選課的學(xué)生人數(shù)最高成績(jī)以及最低

15、成績(jī)和平均成績(jī)create procedure pro2 As Select count(cno), avg(grade) From sc Group by sno3、用存儲(chǔ)過(guò)程查詢指定課程選課的學(xué)生人數(shù)、最高成績(jī)、最低成績(jī)和平均成績(jī)create procedure pro3 cno char(4) As Select count(sno),max(grade),min(grade),avg(grade) From sc Where cno=cno Group by cno Exec pro3 24、用存儲(chǔ)過(guò)程求某系學(xué)生選修的課程號(hào)及成績(jī)create procedure pro4 sdept

16、char(15),sno char(9) AsSelect cno,grade From student,sc Where student.sno=sc.sno and sdept=sdept and student.sno=sno5、用存儲(chǔ)過(guò)程查詢某門(mén)課程成績(jī)大于80分的學(xué)生姓名create procedure pro5 cno char(4) As Select sname From student,sc Where student.sno=sc.sno and cno=cno and grade>806、創(chuàng)建sc1表，結(jié)構(gòu)與sc一樣，然后創(chuàng)建觸發(fā)器，當(dāng)SC表添加數(shù)據(jù)時(shí)，SC1表也同時(shí)添加create table sc1(sno char(9), Cno char(4), Grade int, Primary key(sno,cno), Foreign key (sno) references student(sno), Foreign key (cno) references cours