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

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

聲明局部變量

DECLARE

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

賦值

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

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

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

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

BEGIN--相當于C#里的{

語句1

……

END--相當于C#里的}ELSE

BEGIN

語句1

……

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

BEGIN

--相當于C#里的{

語句……

BREAK

END--相當于C#里的}問題：

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

第一步，統(tǒng)計沒達標的本數；

第二步，如果有書沒通過，提價；

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

UPDATEbankSETuMoney=uMoney-1000WHEREuName='家寶‘

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

CREATE

PROC[EDURE]存儲過程名@參數1數據類型=默認值

OUTPUT,@參數n數據類型=默認值OUTPUT

AS

SQL語句參數說明：參數可選參數分為輸入參數、輸出參數輸入參數允許有默認值EXEC

過程名

[參數]調用帶參數的存儲過程無參數的存儲過程調用：Execpro_GetAge有參數的存儲過程兩種調用法：EXECproGetPageData60,55按次序EXECproGetPageData@labPass=55,@writtenPass=60--參數名參數有默認值時：EXECproGetPageData

--都用默認值EXECproGetPageData

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

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

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

TRIGGER

triggerName

ONTablefor

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

TRIGGERtestForFunONdbo.Categoryfor

UPDATEASbeginselect*frombookendupd