sql事務和存儲過程

一、sql事務

1.什麼是事務：事務是一個不可分割的工作邏輯單元，在資料庫系統上執行并發操作時事務是做為最小的控制單元來使用的。他包含的所有資料庫操作指令作為一個整體一起向系送出或撤消，這一組資料庫操作指令要麼都執行，要麼都不執行。

2.事務的語句

開始事物：BEGIN TRANSACTION

送出事物：COMMIT TRANSACTION

復原事務：ROLLBACK

TRANSACTION

3.事務的4個特性

①原子性(Atomicity)：事務中的所有元素作為一個整體送出或復原，是不可折分的，事務是一個完整的操作。

②一緻性(Consistemcy)：事物完成時，資料必須是一緻的，也就是說，和事物開始之前，資料存儲中的資料處于一緻狀态。保證資料的無損。

③隔離性(Isolation)：對資料進行修改的多個事務是彼此隔離的。這表明事務必須是獨立的，不應該以任何方式來影響其他事務。

④持久性(Durability)：事務完成之後，它對于系統的影響是永久的，該修改即使出現系統故障也将一直保留，真實的修改了資料庫

4.事務的分類.

按事務的啟動與執行方式，可以将事務分為3類:

  ①顯示事務

：也稱之為使用者定義或使用者指定的事務，即可以顯式地定義啟動和結束的事務。分布式事務屬于顯示事務

②自動送出事務：預設事務管理模式。如果一個語句成功地完成，則送出該語句；如果遇到錯誤，則復原該語句。

③隐性事務：當連接配接以此模式進行操作時，sql将在送出或復原目前事務後自動啟動新事務。無須描述事務的開始，隻需送出或復原每個事務。它生成連續的事務鍊。

5.執行個體

BEGIN TRANSACTION--開始事務

DECLARE @errorSun INT --定義錯誤計數器

SET @errorSun=0 --沒錯為0

UPDATE a SET id=232 WHERE a=1 --事務操作SQL語句

SET @errorSun=@errorSun+@@ERROR

--累計是否有錯

UPDATE aa SET id=2 WHERE a=1 --事務操作SQL語句

IF @errorSun<>0

BEGIN

PRINT ‘有錯誤，復原‘

ROLLBACK

TRANSACTION--事務復原語句

END

ELSE

PRINT ‘成功，送出‘

COMMIT

TRANSACTION--事務送出語句

6、不能用于事務的操作

建立資料庫 create database

修改資料庫 alter database

删除資料庫

drop database

恢複資料庫 restore database

加載資料庫 load database

備份日志檔案 backup

log

恢複日志檔案 restore log

更新統計資料 update

statitics

授權操作             

grant

複制事務日志 dump

tran

磁盤初始化           disk

init

更新使用sp_configure後的系統配置          

reconfigure

二、存儲過程

1．存儲過程的優勢

(1)

能實作子產品化程式設計。存儲過程是根據實際功能的需要建立的一個程式子產品，并被存儲在資料庫中。以後使用者要完成該功能，隻要在程式中直接調用該存儲過程即可，而無需再編寫重複的程式代碼。存儲過程可由資料庫程式設計方面的專門人員建立，并可獨立于程式源代碼而進行修改和擴充。

(2)

使用存儲過程可以提高執行效率。當客戶程式需要通路伺服器上的資料時，一般要經過5個步驟：

 ● 查詢語句被發送到伺服器；

 ●

伺服器編譯T-SQL語句；

 ● 優化産生查詢執行計劃；

 ● 資料庫引擎執行查詢；

執行結果發回客戶程式。

如果執行存儲在用戶端本地的T-SQL程式，那麼每次執行該程式時，對于程式中的每一條語句都要經過以上5個步驟。而存儲過程在建立時就被編譯和優化，當存儲過程第一次被執行時，SQL

Server為其産生查詢計劃并将其儲存在記憶體中，這樣以後在調用該存儲過程時就不必再進行編譯，即以上5個步驟中的第2步和第3步就被省略了，這能大大改善系統的性能。

(3)

減少網絡流量。一個需要數百行T-SQL代碼的操作，如果将其建立成存儲過程，那麼使用一條調用存儲過程的語句就可完成該操作。這樣就可避免在網絡上發送數百行代碼，進而減少了網絡負荷。

(4)

可作為安全機制使用。管理者可以不授予使用者通路存儲過程中涉及的表的權限，而隻授予執行存儲過程的權限。這樣，既可以保證使用者通過存儲過程操縱資料庫中的資料，又可以保證使用者不能直接通路存儲過程中涉及的表。使用者通過存儲過程來通路表，所能進行的操作是有限制的，進而保證了表中資料的安全性。

2．存儲過程的類型

(1) 系統存儲過程

在SQL

Server中的許多管理工作是通過執行系統存儲過程來完成的。系統存儲過程建立和儲存在master資料庫中，都以sp_為名稱的字首。系統存儲過程是SQL

Server系統自帶的，具有執行系統存儲過程權限的使用者，可在master資料庫之外直接調用。一般情況下，系統存儲過程執行成功傳回0值，若有錯誤發生傳回非0值。

擴充存儲過程

擴充存儲過程是以動态連結庫(dll)形式存在的外部程式。SQL

Server自身帶了大量的擴充存儲過程安裝在master資料庫中，擴充存儲過程與普通存儲過程執行方法相同。

若擴充存儲過程的字首為sp_，則該擴充存儲過程在master資料庫之外也可直接調用；否則，必須在擴充存儲過程前面加上“master.dbo.”字首。開發人員可以使用其他程式設計語言來建立擴充存儲過程，編寫好擴充存儲過程後，可由sysadmin伺服器角色的成員在

SQL Server 中注冊該擴充存儲過程，然後授予其他使用者執行該過程的權限。擴充存儲過程隻能添加到 master 資料庫中，利用擴充存儲過程可以擴充SQL

Server的功能。

(3) 使用者存儲過程

使用者存儲過程是由使用者根據實際問題的需要所建立的存儲過程。固定伺服器角色sysadmin

的成員可根據實際需要在master資料庫中建立使用者存儲過程，若使用sp_做存儲過程的字首，則該存儲過程在任何位置均可直接調用，否則，必須在該存儲過程前面加上“master.dbo.”字首。對于在使用者資料庫中建立的存儲過程，最好不要使用sp_作為其名稱的字首，否則如果該存儲過程與系統存儲過程同名，則該存儲過程永遠不會被執行。并且若在該使用者資料庫之外調用該存儲過程，也必須在存儲過程名的前面加上“使用者資料庫名.所有者名.”字首才能找着、執行該存儲過程。