sql編程存儲過程視圖觸發(fā)器

北京傳智播客教育數(shù)據(jù)庫開發(fā)及ADO.Net講師：鄒華棟智言在你生命的最初30年中，你養(yǎng)成習(xí)慣。在你生命的最后30年中，你的習(xí)慣決定了你。視圖_View基于書籍表和類別表，’產(chǎn)生’一張?zhí)摂M表—視圖selectb_idASID,c_nameas類別,b_titleas書名fromBook,Categorywhereb_cid=c_id視圖概述視圖是一張?zhí)摂M表，它表示一張表的部分?jǐn)?shù)據(jù)或多張表的綜合數(shù)據(jù)，其結(jié)構(gòu)和數(shù)據(jù)是建立在對表的查詢基礎(chǔ)上視圖在操作上和數(shù)據(jù)表沒有什么區(qū)別，但兩者的差異是其本質(zhì)是不同:數(shù)據(jù)表是實(shí)際存儲記錄的地方，然而視圖并不保存任何記錄，它存儲的實(shí)際上是查詢語句相同的數(shù)據(jù)表，根據(jù)不同用戶的不同需求，可以創(chuàng)建不同的視圖（不同的查詢語句）優(yōu)點(diǎn)：篩選表中的行防止未經(jīng)許可的用戶訪問敏感數(shù)據(jù)降低數(shù)據(jù)庫的復(fù)雜程度局部變量_先聲明再賦值

聲明局部變量

DECLARE

@變量名數(shù)據(jù)類型DECLARE@bookNamevarchar(20)DECLARE@bIdint

賦值

SET@變量名=值--set用于普通的賦值

SELECT@變量名=值--用于從表中查詢數(shù)據(jù)并賦值例如：SET@bookName=‘家寶’SELECT@bookName=b_titleFROMBookWHEREb_id=2例：declare@moneymoney--聲明變量set@money=2000–賦值select@money–查詢變量值請從Book表中找出比書名為.NETMVC3價(jià)格小的書籍信息declare@momoney--聲明變量select@mo=b_moneyfromBookwhere

b_title=‘.NETMVC3‘--賦值select*fromBookwhereb_money<@mo--使用變量變量種類變量分為：局部變量：局部變量必須以標(biāo)記@作為前綴，如@Ageint局部變量：先聲明，再賦值全局變量（系統(tǒng)變量）：全局變量必須以標(biāo)記@@作為前綴，如@@version全局變量由系統(tǒng)定義和維護(hù)，我們只能讀取，不能修改全局變量的值全局變量變量含義@@ERROR最后一個(gè)T-SQL錯(cuò)誤的錯(cuò)誤號@@IDENTITY最后一次插入的標(biāo)識值@@LANGUAGE當(dāng)前使用的語言的名稱@@MAX_CONNECTIONS可以創(chuàng)建的同時(shí)連接的最大數(shù)目@@ROWCOUNT 受上一個(gè)SQL語句影響的行數(shù)@@SERVERNAME本地服務(wù)器的名稱@@TRANSCOUNT 當(dāng)前連接打開的事務(wù)數(shù)@@VERSION

SQLServer的版本信息IFELSEIF(條件表達(dá)式)

BEGIN--相當(dāng)于C#里的{

語句1

……

END--相當(dāng)于C#里的}ELSE

BEGIN

語句1

……

END問題：統(tǒng)計(jì)并顯示所有書籍價(jià)格(b_money)的平均值，如果平均價(jià)格在50以上，顯示“A“，并顯示平均價(jià)格最高的2本書；如果在50以下，顯示“B“，并顯示平均價(jià)格最少的2本書。分析：第一步，統(tǒng)計(jì)平均價(jià)格存入臨時(shí)變量；第二步，用IF－ELSE判斷；例：WHILE循環(huán)WHILE(條件表達(dá)式)

BEGIN

--相當(dāng)于C#里的{

語句……

BREAK

END--相當(dāng)于C#里的}問題：

書籍價(jià)格上漲，確保每本書價(jià)格最少50元。提價(jià)：先每本都加2元，看是否都價(jià)格達(dá)標(biāo)，如果沒有全部達(dá)標(biāo)，每本再加2元，再看是否都達(dá)標(biāo)，如此反復(fù)提價(jià)，直到所有書都達(dá)標(biāo)為止。分析：

第一步，統(tǒng)計(jì)沒達(dá)標(biāo)的本數(shù)；

第二步，如果有書沒通過，提價(jià)；

第三步，循環(huán)判斷。例：事務(wù)-為什么需要事務(wù)如，借錢問題：假定錢從A轉(zhuǎn)到B，至少需要兩步：A的資金減少然后B的資金相應(yīng)增加

UPDATEbankSETuMoney=uMoney-1000WHEREuName='家寶‘

@@errorUPDATEbankSETuMoney=uMoney+1000WHEREuName='奧巴馬‘@@error--查看結(jié)果。SELECT*FROMbank會否出問題？什么是事務(wù)(Transaction)指訪問并可能更新數(shù)據(jù)庫中各種數(shù)據(jù)項(xiàng)的一個(gè)程序執(zhí)行單元(unit)--也就是由多個(gè)sql語句組成，必須作為一個(gè)整體執(zhí)行這些sql語句作為一個(gè)整體一起向系統(tǒng)提交，要么都執(zhí)行、要么都不執(zhí)行語法步驟：開始事務(wù)：BEGINTRANSACTION事務(wù)提交：COMMITTRANSACTION事務(wù)回滾：ROLLBACKTRANSACTION判斷某條語句執(zhí)行是否出錯(cuò)：全局變量@@ERROR；@@ERROR只能判斷當(dāng)前一條T-SQL語句執(zhí)行是否有錯(cuò)，為了判斷事務(wù)中所有T-SQL語句是否有錯(cuò)，我們需要對錯(cuò)誤進(jìn)行累計(jì)；例：SET@errorSum=@errorSum+@@error存儲過程存儲過程就像數(shù)據(jù)庫中運(yùn)行方法(函數(shù))和C#里的方法一樣，由存儲過程名/存儲過程參數(shù)組成/可以有返回結(jié)果。前面學(xué)的ifelse/while/變量等，都可以在存儲過程中使用優(yōu)點(diǎn)：執(zhí)行速度更快允許模塊化程序設(shè)計(jì)提高系統(tǒng)安全性減少網(wǎng)絡(luò)流通量系統(tǒng)存儲過程由系統(tǒng)定義，存放在master數(shù)據(jù)庫中名稱以“sp_”開頭或”xp_”開頭自定義存儲過程由用戶在自己的數(shù)據(jù)庫中創(chuàng)建的存儲過程系統(tǒng)存儲過程系統(tǒng)存儲過程說明sp_databases列出服務(wù)器上的所有數(shù)據(jù)庫。sp_helpdb報(bào)告有關(guān)指定數(shù)據(jù)庫或所有數(shù)據(jù)庫的信息sp_renamedb更改數(shù)據(jù)庫的名稱sp_tables返回當(dāng)前環(huán)境下可查詢的對象的列表sp_columns回某個(gè)表列的信息sp_help查看某個(gè)表的所有信息sp_helpconstraint查看某個(gè)表的約束sp_helpindex查看某個(gè)表的索引sp_stored_procedures列出當(dāng)前環(huán)境中的所有存儲過程。sp_password添加或修改登錄帳戶的密碼。sp_helptext顯示默認(rèn)值、未加密的存儲過程、用戶定義的存儲過程、觸發(fā)器或視圖的實(shí)際文本。創(chuàng)建存儲過程定義存儲過程的語法

CREATE

PROC[EDURE]存儲過程名@參數(shù)1數(shù)據(jù)類型=默認(rèn)值

OUTPUT,@參數(shù)n數(shù)據(jù)類型=默認(rèn)值OUTPUT

AS

SQL語句參數(shù)說明：參數(shù)可選參數(shù)分為輸入?yún)?shù)、輸出參數(shù)輸入?yún)?shù)允許有默認(rèn)值EXEC

過程名

[參數(shù)]調(diào)用帶參數(shù)的存儲過程無參數(shù)的存儲過程調(diào)用：Execpro_GetAge有參數(shù)的存儲過程兩種調(diào)用法：EXECproGetPageData60,55按次序EXECproGetPageData@labPass=55,@writtenPass=60--參數(shù)名參數(shù)有默認(rèn)值時(shí)：EXECproGetPageData

--都用默認(rèn)值EXECproGetPageData

1--頁容量(@pageSize)默認(rèn)值EXECproGetPageData

1,5--不用默認(rèn)值問題：如果我只想設(shè)置頁容量(第二個(gè)參數(shù))，頁碼使用默認(rèn)值呢？怎么辦？存儲過程中使用輸出參數(shù)如果希望在存儲過程中查詢當(dāng)前頁對應(yīng)的結(jié)果集，而且還想產(chǎn)生總頁數(shù)呢？觸發(fā)器觸發(fā)器是一種特殊類型的存儲過程，它不同于前面介紹過的一般的存儲過程。一般的存儲過程通過存儲過程名稱被直接調(diào)用，而觸發(fā)器主要是通過事件進(jìn)行觸發(fā)而被執(zhí)行。觸發(fā)器是一個(gè)功能強(qiáng)大的工具，在表中數(shù)據(jù)發(fā)生變化時(shí)自動強(qiáng)制執(zhí)行。觸發(fā)器可以用于SQLServer約束、默認(rèn)值和規(guī)則的完整性檢查，還可以完成難以用普通約束實(shí)現(xiàn)的復(fù)雜功能。那究竟何為觸發(fā)器？在SQLServer里面也就是對某一個(gè)表的一定的操作，觸發(fā)某種條件，從而執(zhí)行的一段程序。觸發(fā)器是一個(gè)特殊的存儲過程。

常見的觸發(fā)器有三種：分別應(yīng)用于Insert,Update,Delete事件常用語法CREATE

TRIGGER

triggerName

ONTablefor

UPDATE|INSERT|DELETEASbegin…end觸發(fā)器-更新CREATE

TRIGGERtestForFunONdbo.Categoryfor

UPDATEASbeginselect*frombookendupd