第7章

數(shù)據(jù)庫編程基礎游標與SQL的宿主使用存儲過程觸發(fā)器及其用途動態(tài)SQL7.1游標與SQL的宿主使用SQL宿主使用面臨的問題嵌入識別與預編譯數(shù)據(jù)通訊區(qū)與主變量游標7.1.1SQL宿主使用面臨的問題嵌入識別問題：宿主語言的編譯程序不能識別SQL語句，所以首要的問題就是要解決如何區(qū)分宿主語言的語句和SQL語句；宿主語言與SQL語言的數(shù)據(jù)交互問題：SQL語句的查詢結果必須能夠交給宿主語言處理，宿主語言的數(shù)據(jù)也要能夠交給SQL語句使用；宿主語言的單記錄與SQL的多記錄的問題：宿主語言一般一次處理一條記錄，而SQL常常處理的是記錄（元組）的集合，這個矛盾必須解決。7.1.2嵌入識別與預編譯為了區(qū)分宿主語言和SQL語句，為SQL語句加一個特殊的前綴，在用宿主語言的編譯系統(tǒng)編譯源程序之前，首先由預編譯系統(tǒng)將SQL語句轉(zhuǎn)換為宿主語言的合法函數(shù)調(diào)用。常用前綴格式是：EXECSQL比如：

EXECSQLINSERTINTO倉儲.倉庫 VALUES('WH1','北京',500)嵌入了SQL的應用程序的執(zhí)行過程

7.1.3數(shù)據(jù)通訊區(qū)與主變量為了解決程序變量和數(shù)據(jù)庫的交互，一般需要說明一個數(shù)據(jù)通訊區(qū)，即在程序的前部都要有如下語句：

INCLUDESQLCA

注：SQLCA是SQL與宿主語言的通訊區(qū)，它類似于結構變量，各個分量分別反映SQL語句的各種執(zhí)行狀態(tài)，如：sqlca.sqlcode主（Host）變量負責SQL語句和宿主語言語句數(shù)據(jù)交換的是主變量，這種變量既可以用在SQL語句中，又可以用在宿主語言語句中。主變量的說明格式BEGINDECLARESECTION…主變量說明…ENDDECLARESECTION主變量說明的例子：EXECSQLBEGINDECLARESECTION；charwhnumb[5]charcity[12]intwh_areaEXECSQLENDDECLARESECTION；使用主變量的例子：UPDATE倉庫SET面積=:wh_areaWHERE倉庫號=:whnumb注：主變量用在宿主語言語句中時，和一般程序變量的使用方法是一樣的。7.1.4游標（Cursor）DECLARECURSOROPENFETCHCLOSEDEALLOCATE

為了解決宿主語言一次只能處理一條記錄，而SQL語言一次處理多條記錄的矛盾，引入了游標的概念。相關的語句有：定義游標基本格式

DECLARE<游標名>CURSORFOR<SELECT-查詢>游標可以看作是一個臨時存儲或臨時文件，它的內(nèi)容就是SELECT語句的查詢結果。打開游標

OPEN<游標名>從游標中讀記錄

基本格式是

FETCH<游標名>[INTO<主變量1>,<主變量2>…]

這里的游標必須是已經(jīng)說明并打開了的，INTO后的主變量要與在DECLARECURSOR中SELECT的字段相對應。關閉游標

CLOSE<游標名>游標被關閉后如果需要還可以用OPEN語句打開。

釋放游標

DEALLOCATE<游標名>DEALLOCATE命令釋放和刪除與游標有關的所有數(shù)據(jù)結構和定義。

游標概念歸納與程序設計語言中的文件相對照：DECLARECURSOR相當于說明了一個文件OPEN相當于打開文件FETCH相當于讀一條記錄CLOSE相當于關閉文件DEALLOCATE語句相當于刪除文件。

使用游標的一個C語言程序段

…EXECSQLBEGINDECLARESECTIONcharwhnumb[5]charcity[12]

intwh_areaEXECSQLENDDECLARESECTION…EXECSQLDECLAREc1CURSORFORSELECT倉庫號,城市,面積FROM倉庫WHERE面積=:wh_areaEXECSQLOPENc1while(SQLCA.sqlcode<>100){EXECSQLFETCHc1INTO:whnumb,:city,:wh_area…}EXECSQLCLOSEc1…在T-SQL中使用游標使用全局變量@@FETCH_STATUS來判斷FETCH語句對游標的操作狀態(tài)。取值如下：

0：FETCH語句成功；-1：FETCH語句失敗或行不在結果集中；-2：提取的行不存在。循環(huán)語句WHILE@@FETCH_STATUS=0在T-SQL中用游標列出所有倉庫記錄DECLARE@whnoCHAR(6),@cityCHAR(10),@areaINTDECLARE

wh_cursorCURSORFOR

SELECT倉庫號,城市,面積

FROM倉儲.倉庫OPEN

wh_cursorPRINT'--------倉庫列表--------'PRINT‘倉庫號

所在城市

倉庫面積’FETCH

FROM

wh_cursorINTO@whno,@city,@areaWHILE@@FETCH_STATUS=0BEGIN PRINT@whno+''+@city+STR(@area,4)

FETCHFROM

wh_cursorINTO@whno,@city,@areaENDCLOSE

wh_cursorDEALLOCATE

wh_cursor

游標可以嵌套參見P182例7-3注意@@FETCH_STATUS的使用7.1.6利用游標進行刪除和更新操作使用游標的UPDATE命令的格式是

UPDATE<表名>SET<列名>={<表達式>|NULL}[,n…]WHERECURRENTOF<游標名>使用游標的DELETE命令的格式是

DELETEFROM<表名>WHERECURRENTOF<游標名>

利用WHERECURRENTOF<游標名>進行的修改或刪除只影響表的當前行。注意：不是對游標中的記錄進行修改或刪除。使用游標進行更新的例子

DECLARE@salarynumeric(8,2)DECLAREsal_cursorCURSORFORSELECT工資FROM基礎.職工OPENsal_cursorFETCHFROMsal_cursorINTO@salaryWHILE@@FETCH_STATUS=0BEGINIF@salary<1300BEGIN UPDATE基礎.職工SET工資=1300

WHERECURRENTOFsal_cursor

PRINT'修改了一個職工的工資'ENDFETCHFROMsal_cursorINTO@salaryENDCLOSEsal_cursorDEALLOCATEsal_cursor7.2存儲過程存儲過程（StoredProcedure）源于客戶/服務器數(shù)據(jù)庫體系結構，它與傳統(tǒng)數(shù)據(jù)庫結構的一個很重要的區(qū)別是，在傳統(tǒng)的數(shù)據(jù)庫中只存放數(shù)據(jù)，所有的應用程序都在用戶端，都與用戶實際運行的應用程序捆綁在一起；而在客戶/服務器結構的數(shù)據(jù)庫中，在數(shù)據(jù)庫中還可以存放程序，因為這種程序以數(shù)據(jù)庫對象的形式存儲在數(shù)據(jù)庫中，所以稱為存儲過程。

7.2.1基本概念事先編好、經(jīng)過預編譯、存儲在數(shù)據(jù)庫中的SQL語句的集合，用戶在需要的時候可以調(diào)用這些存儲過程完成數(shù)據(jù)查詢或操作。

在數(shù)據(jù)庫服務器上運行SQLServer自身預定義了一些用于管理系統(tǒng)和數(shù)據(jù)庫的存儲過程——系統(tǒng)存儲過程。如：sp_databases用戶可以建立自己的存儲過程客戶端應用（不使用存儲過程）客戶端應用（使用存儲過程）.Starttransaction.INSERTdataUPDATEdataDELETEdataEndtransaction..Starttransaction.CallStoredprocedureEndtransaction.DBMSServerDBMSServerProcedure:INSERTdataUPDATEdataDELETEdata(a)(b)

不使用存儲過程時，所有的數(shù)據(jù)處理都在客戶端完成；而使用存儲過程時，可以使數(shù)據(jù)處理在服務器端完成。存儲過程的作用可以避免在網(wǎng)絡上傳輸大量無用的信息或原始數(shù)據(jù)，只需要傳輸調(diào)用存儲過程的指令和數(shù)據(jù)庫服務器返回的處理結果

把完成某一數(shù)據(jù)庫處理的功能設計為存儲過程，則可以在各個程序中反復調(diào)用，從而減輕程序的編寫工作量

可以利用存儲過程間接實現(xiàn)一些安全控制功能7.2.2創(chuàng)建和執(zhí)行存儲過程@parameter：給出參數(shù)名（注意需要使用@做前綴）data_type：指出參數(shù)的數(shù)據(jù)類型VARYING：指定作為輸出參數(shù)支持的結果集，該參數(shù)由存儲過程動態(tài)構造，其內(nèi)容可能發(fā)生改變，僅適用于cursor（游標）類型的參數(shù)。=default：給出參數(shù)的默認值，該值必須是常量或NULL，如果過程中使用了帶LIKE關鍵字的參數(shù)，則可包含通配符%、_、[]和[^]。OUT|OUTPUT：指示參數(shù)是輸出參數(shù)。sql_statement：存儲過程所要執(zhí)行的SQL語句，它可以是一組SQL語句，可以包含流程控制語句等。創(chuàng)建存儲過程的基本格式:CREATEPROCEDURE[schema_name.]procedure_name[@parameterdata_type[VARYING][=

default][OUT|OUTPUT],…]ASsql_statement

說明創(chuàng)建存儲過程通常是在數(shù)據(jù)庫設計和開發(fā)階段完成的；存儲過程可以嵌套，即在一個存儲過程中可以調(diào)用另外一個存儲過程；存儲過程一般用來完成數(shù)據(jù)查詢和數(shù)據(jù)處理操作；在存儲過程中不可以使用創(chuàng)建數(shù)據(jù)庫對象的語句（如CREATETABLE等各種CREATE語句）。

執(zhí)行存儲過程

@return_status：用于存放存儲過程返回的狀態(tài)。這個變量在執(zhí)行存儲過程前必須說明過。procedure_name：要執(zhí)行或調(diào)用的存儲過程名。@parameter：存儲過程中定義的參數(shù)。value

：傳遞給存儲過程的參數(shù)值。如果參數(shù)名稱沒有指定，參數(shù)值必須以在存儲過程中定義的順序提供。如果在存儲過程中定義了默認值，則可以不必指定參數(shù)。@variable

：用來存儲參數(shù)或返回參數(shù)的變量。OUTPUT：說明是輸出參數(shù)，用于從存儲過程返回值。使用游標變量作為參數(shù)時必須使用該關鍵字。

EXECUTE[@return_status=][schema_name.]procedure_name

[@parameter

=]{value|@variable[OUTPUT]}[,...n]7.2.3存儲過程的修改和刪除修改存儲過程ALTERPROCEDURE，它的格式與CREATEPROCEDURE命令的格式類似。刪除存儲過程DROPPROCEDURE[schema_name.]procedure_name存儲過程應用舉例例7-5.使用簡單過程（不使用任何參數(shù)）查詢所有職工信息（姓名、倉庫號、工資、所在城市）例7-6.使用帶有參數(shù)的簡單過程

返回指定城市的職工信息例7-7.使用帶有參數(shù)和返回值的簡單過程

查詢指定倉庫訂單金額大于指定值的訂單數(shù)，查詢結果通過RETURN語句返回存儲過程應用舉例例7-8.使用帶有通配符參數(shù)的簡單過程

返回指定的一些供應商的信息（提供供應商名稱中的關鍵字）例7-9.使用

OUTPUT參數(shù)指定供應商所經(jīng)手訂購單的數(shù)量和平均金額，一個參數(shù)傳入指定供應商名，一個參數(shù)傳出計算的訂單數(shù)量，計算的平均金額用RETURN語句返回例7-10.使用

cursor數(shù)據(jù)類型的參數(shù)

在存儲過程中使用游標獲得有關存儲過程的信息

存儲過程的定義可以通過當前數(shù)據(jù)庫的sys.sql_modules和sys.objects系統(tǒng)視圖查詢到

SELECTdefinitionFROMsys.sql_modulesJOINsys.objectsONsys.sql_modules.object_id=sys.objects.object_idWHERETYPE='P'在數(shù)據(jù)庫設計階段設計存儲過程使用存儲過程也是數(shù)據(jù)庫設計階段要考慮的內(nèi)容之一，根據(jù)業(yè)務需求和開發(fā)者的經(jīng)驗可以靈活使用存儲過程。使用存儲過程，可以：提高系統(tǒng)的開發(fā)效率和運行效率使數(shù)據(jù)查詢和操作更簡單使數(shù)據(jù)更安全數(shù)據(jù)庫設計者可以在物理數(shù)據(jù)模型設計階段進行存儲過程的設計。在PowerDesingner的物理數(shù)據(jù)模型（PDM）設計階段可以設計存儲過程。7.2.7用戶定義函數(shù)SQLServer還可以定義用戶函數(shù)，用戶函數(shù)和存儲過程的基本思想是一樣的，只是定義和使用方法有一些區(qū)別。例7-11用函數(shù)實現(xiàn)例7-7存儲過程的功能。CREATEFUNCTION訂貨.udfGetOrderNum(@whnochar(6),@summoney)RETURNSintWITHEXECUTEASCALLERASBEGIN DECLARE@countint SELECT@count=COUNT(*)FROM訂貨.訂購單 WHERE金額>=@sumAND經(jīng)手人IN (SELECT職工號FROM基礎.職工WHERE倉庫號=@whno) RETURN@countEND調(diào)用udfGetOrderNum函數(shù)： PRINT'WH1倉庫金額在1000以上的訂單數(shù)是:'+ STR(訂貨.udfGetOrderNum('WH1',1000))7.3觸發(fā)器及其用途觸發(fā)器可以看作是一類特殊的存儲過程，它在滿足某個特定條件時自動觸發(fā)執(zhí)行。觸發(fā)器和存儲過程同是提高數(shù)據(jù)庫服務器性能的有力工具。7.3.1基本概念觸發(fā)器是一種程序或是一種過程，它和存儲過程一樣是事先設計好存儲在數(shù)據(jù)庫中的，與存儲過程不同的是觸發(fā)器不需要專門調(diào)用或執(zhí)行，觸發(fā)器是在某個特定條件發(fā)生時自動觸發(fā)執(zhí)行的。分為DML(數(shù)據(jù)操作語言)觸發(fā)器、DDL(數(shù)據(jù)定義語言)觸發(fā)器和LOGIN觸發(fā)器DML觸發(fā)器在執(zhí)行數(shù)據(jù)操作語言（Update、Insert和Delete）時觸發(fā)DDL觸發(fā)器在執(zhí)行數(shù)據(jù)定義語言時觸發(fā)LOGIN觸發(fā)器在有用戶登錄時觸發(fā)DML觸發(fā)器DML觸發(fā)器也是一個數(shù)據(jù)庫對象，但DML觸發(fā)器依附于表（或視圖）。DML觸發(fā)器分為插入觸發(fā)器、刪除觸發(fā)器和更新觸發(fā)器三類觸發(fā)器的三個要素：定義觸發(fā)器的表（或視圖）激活觸發(fā)器的數(shù)據(jù)操作語句采取的動作建立觸發(fā)器

ON指出定義觸發(fā)器的表或視圖

FORINSERT、UPDATE或DELETE指出觸發(fā)事件sql_statement：觸發(fā)器所采取的操作CREATETRIGGER[schema_name.]trigger_nameON{table|view}FOR{[INSERT][,][UPDATE][,][DELETE]}ASsql_statement

一個簡單的建立觸發(fā)器的例子CREATETRIGGERwh_triggerON倉儲.倉庫FORINSERTASPRINT'插入了一個倉庫元組'7.3.3deleted表和inserted表當DML觸發(fā)器激活時系統(tǒng)會自動產(chǎn)生兩個特殊的臨時表：deleted表和inserted表當發(fā)生INSERT操作時新插入的記錄也存儲在inserted表當發(fā)生DELETE操作時被刪除的記錄也存儲在deleted表當發(fā)生UPDATE操作時修改前的舊記錄也存儲在deleted表、修改后的新記錄也存儲在inserted表deleted表和inserted表的作用？可以使用deleted表和inserted表判斷正在操作的記錄是否符合要求，從而檢查錯誤并采取相應的措施找出數(shù)據(jù)修改前、后表的狀態(tài)差異，并基于該差異采取相應的措施可以擴展表之間的參照完整性deleted表和inserted表只在觸發(fā)器內(nèi)可用，一旦觸發(fā)器完成任務，這兩個系統(tǒng)產(chǎn)生的臨時表將自動刪除觸發(fā)器應用舉例

例7-13：使用包含提醒消息的DML觸發(fā)器CREATETRIGGERreminder1ON倉儲.庫存FORUPDATEASDECLARE@amountintSELECT@amount=數(shù)量FROMinsertedIF@amount<5 RAISERROR('庫存數(shù)量已經(jīng)小于5！',16,10)執(zhí)行如下UPDATE語句會發(fā)生什么？

UPDATE倉儲.庫存SET數(shù)量=數(shù)量-3 WHERE倉庫號='WH1'AND器件號='P1'則會在客戶端顯示如下信息：

消息50000，級別16，狀態(tài)10，過程reminder1，第7行

庫存數(shù)量已經(jīng)小于5！觸發(fā)器應用舉例由于CHECK約束只能定義列級或表級約束，表間的任何約束（在本例中是業(yè)務規(guī)則）都必須通過定義觸發(fā)器來實現(xiàn)。例7-14：使用DML觸發(fā)器實現(xiàn)表之間強制業(yè)務規(guī)則編程實現(xiàn)業(yè)務規(guī)定“如果本地有供應商則訂購單不許發(fā)往異地”可以在訂購單上定義一個Insert和Update觸發(fā)器，當插入新的訂購單記錄、或修改供貨方屬性時，如果供貨方屬性不為NULL則做如下處理：通過經(jīng)手人屬性確定所參照職工記錄；通過職工記錄的倉庫號屬性確定所參照倉庫記錄及其城市屬性值；通過供貨方屬性確定所參照供應商記錄及其地址屬性值；通過倉庫的城市屬性值和供應商的地址屬性值判斷它們是否屬于一個城市，如果不是則再檢查有無同城的供應商，如果有則拒絕對訂購單的插入或更新操作。代碼見P195查看導致觸發(fā)器觸發(fā)的事件

通過查詢sys.triggers和sys.trigger_events系統(tǒng)目錄表可以查詢導致觸發(fā)器觸發(fā)的事件

例7-15：查詢是哪個Transact-SQL語言事件導致觸發(fā)了觸發(fā)器IsSameCity。SELECTTE.*FROMsys.trigger_eventsASTEJOINsys.triggersASTONT.object_id=TE.object_idWHERET.name='IsSameCity'7.3.6在數(shù)據(jù)庫設計階段設計觸發(fā)器DML觸發(fā)器常常用于強制業(yè)務規(guī)則、數(shù)據(jù)完整性和一些提示服務，如下一些場合可以使用DML觸發(fā)器：當在某些表上發(fā)生數(shù)據(jù)操作時可以及時在線提示或發(fā)送短信、電子郵件給用戶，以引起用戶的關注。觸發(fā)器可以通過級聯(lián)的方式對相關的表進行修改。例如，對父表的修改，可以引起對子孫表的一系列修改，從而保證數(shù)據(jù)的一致性和完整性（簡單的可以通過參照完整性的級聯(lián)功能完成）。觸發(fā)器可以禁止或撤消違反數(shù)據(jù)完整性的修改（一般可以用參照完整性約束完成）。觸發(fā)器可以強制定義比CHECK約束更加復雜的約束，特別是跨表的約束則只能使用觸發(fā)器來實現(xiàn)。7.4動態(tài)SQL有時候在編程序時SQL語句或語句的參數(shù)和格式不能確定，應用程序只能在執(zhí)行時才知道需要什么樣的SQL語句，即必須在應用程序執(zhí)行時動態(tài)建立SQL語句。7.4.1動態(tài)SQL語句的劃分沒有參數(shù)、沒有返回結果的SQL語句，這類語句主要是建立數(shù)據(jù)庫對象的語句，如動態(tài)生成的CREATETABLE語句；有參數(shù)、但沒有返回結果的SQL語句，這類語句主要是完成數(shù)據(jù)庫操作的語句，如動態(tài)生成的INSERT、UPDATE和DELETE語句；有參數(shù)、有返回結果的SQL語句，這類語句主要是對數(shù)據(jù)庫進行動態(tài)查詢的語句，也稱作動態(tài)游標（DYNAMICCURSOR）語句。7.4.2動態(tài)定義功能一般格式是：

EXECUTEIMMEDIATESQLStatement

其中SQLStatement是構成合法SQL語句的字符串（一般應該是變量）。7.4.3動態(tài)操作功能這種格式的動態(tài)SQL語句實際包含了