第11章存儲過程與觸發(fā)器

1、第 11 章過程與觸發(fā)器本章主要介紹 SQL Server 2005 中過程的概念、類型、常用系統(tǒng)過程以及如何創(chuàng)建過程；另外介紹了觸發(fā)器的類型以及創(chuàng)建和使用。11.1過程過程是由 Transact-SQL 命令編寫的過程，這個過程經(jīng)編譯和優(yōu)化后務(wù)器中，使用時只要調(diào)用即可。在數(shù)據(jù)庫服11.1.1過程的功能及優(yōu)勢Microsoft SQL Server 中的過程與其他編程語言中的過程類似，可以實現(xiàn)以下功能。ØØØ接受輸入?yún)?shù)并以輸出參數(shù)的格式向調(diào)用過程或批處理返回多個值。包含用于在數(shù)據(jù)庫中執(zhí)行操作(包括調(diào)用其他過程)的編程語句。向調(diào)用過程或批處理返回狀態(tài)值，以指明或失

2、敗(以及失敗的)?？梢允褂?Transact-SQL 語言的 EXECUTE 命令運行過程。過程與函數(shù)不同，過程不返回取代其名稱的值，也不能直接在表達(dá)式中使用。在 SQL Server 中使用過程而不使用在客戶端計算機(jī)本地的 Transact-SQL 程序有以下優(yōu)勢。ØØ過程已在服務(wù)器。過程具有安全特性(例如權(quán)限)和所，且具有可以附加到過程的證書。用戶可以被授予權(quán)限來執(zhí)行過程而不必直接對過程中的對象具限。ØSQL InjectionØ過程可以加強(qiáng)應(yīng)用程序的安全性。參數(shù)化。過程有助于保護(hù)應(yīng)用程序不受過程模塊化程序設(shè)計。過程一旦創(chuàng)建，以后即可在程序中調(diào)用任意

3、多次。這樣可以改進(jìn)應(yīng)用程序的可維護(hù)性，并應(yīng)用程序統(tǒng)一數(shù)據(jù)庫。Ø過程可以減少網(wǎng)絡(luò)通信流量。一個需要數(shù)百行 Transact-SQL 代碼的操作可以通過一條執(zhí)行過程代碼的語句來執(zhí)行，而不需要在網(wǎng)絡(luò)中數(shù)百行代碼。11.1.2過程類型Microsoft SQL Server 2005 中有多種可用的1. 用戶定義的過程過程。用戶定義的過程是指封裝了可重用代碼的模塊或例程。過程可以接受輸入?yún)?shù)、向客戶端返回表格或標(biāo)量結(jié)果和消息、調(diào)用數(shù)據(jù)定義語言(DDL)和數(shù)據(jù)操作語言(DML)令，然后返回輸出參數(shù)。在 SQL Server 2005 中，用戶定義的或 CLR 兩種類型。過程有 Transact

4、-SQL·2·數(shù)據(jù)庫應(yīng)用技術(shù)(1)Transact-SQLTransact-SQL過程過程是指保存的 Transact-SQL 語句集合，可以接受和返回用戶提供的參數(shù)。過程中可能包含根據(jù)客戶端應(yīng)用程序提供的信息在一個或多個表中新行所需過程也可能從數(shù)據(jù)庫向客戶端應(yīng)用程序返回數(shù)據(jù)。例如，電子商務(wù) Web 應(yīng)用的語句。程序可以使用(2)CLR CLR過程實現(xiàn)根據(jù)聯(lián)機(jī)用戶指定的搜索條件返回有關(guān)特定過程的信息。過程是指對 Microsoft .NET Framework 公共語言運行時 CLR 方法的，可以接受和返回用戶提供的參數(shù)。它們在.NET Framework 程序集中是作為類

5、的公共靜態(tài)方法實現(xiàn)的。2. 擴(kuò)展過程擴(kuò)展過程是指 Microsoft SQL Server 的實例可以動態(tài)加載和運行的 DLL，使用編程語言(例如 C 語言)創(chuàng)建的外部例程。擴(kuò)展過程直接在 SQL Server 的實例的地址空間中運行，可以使用 SQL Server 擴(kuò)展過程 API 完成編程。3. 系統(tǒng)過程SQL Server 2005 中的許多管理活動都是通過一種特殊的過程執(zhí)行的，這種過程稱為系統(tǒng)過程。例如，sys.sp_changedbowner 就是一個系統(tǒng)過程。從物理意義上過程在源數(shù)據(jù)庫中，并且?guī)в?sp_前綴。從邏輯意義上講，系統(tǒng)講，系統(tǒng)過程出現(xiàn)在每個系統(tǒng)定義數(shù)據(jù)庫和用戶定義數(shù)據(jù)庫

6、的 sys 構(gòu)架中。在 SQL Server 2005 中，可將GRANT、DENY 和 REVOKE 權(quán)限應(yīng)用于系統(tǒng)過程。SQL Server 支持在 SQL Server 和外部程序之間提供一個接口，以實現(xiàn)各種維護(hù)活動的系統(tǒng)過程。這些擴(kuò)展程序使用 xp_前綴。11.1.3 常用系統(tǒng)過程下面介紹幾種常用的系統(tǒng)過程。1.sp_help報告有關(guān)數(shù)據(jù)庫對象(sys.sysobjects 兼容視圖中列出的所有對象)、用戶定義數(shù)據(jù)類型或SQL Server 2005 提供的數(shù)據(jù)類型的信息。語法格式如下。sp_help objname = 'name' sp_help 過程僅在當(dāng)前數(shù)據(jù)庫

7、中查找對象。如果未指定 name，則 sp_help 將列出當(dāng)前數(shù)據(jù)庫中所有對象的對象名稱、所有者和對象類型。sp_helptrigger 提供有關(guān)觸發(fā)器的信息。 objname = 'name'：sysobjects 類型或 systypes 表中任何用戶定義數(shù)據(jù)類型的某個對象的名稱。name 的數(shù)據(jù)類型為 nvarchar(776)，默認(rèn)值為 NULL。不能接受數(shù)據(jù)庫名稱。返回代碼值為 0()或 1(失敗)。返回的結(jié)果集取決于 name 是否已指定、何時指定以及屬于何種數(shù)據(jù)庫對象。該系統(tǒng)過程的使用權(quán)限為具有 public的成員。l如果執(zhí)行不帶參數(shù)的 sp_help，則返回當(dāng)

8、前數(shù)據(jù)庫中現(xiàn)有的所有類型對象的匯總信息，即 Name(對象名稱)、所有者(對象所有者)及 Object_type(對象類型)。第 11 章過程與觸發(fā)器·3·【例 11.1】返回有關(guān)所有對象的信息。以下示例將列出有關(guān) teaching 數(shù)據(jù)庫中每個對象的信息。USE teaching GOEXEC sp_help GO執(zhí)行結(jié)果如圖 11.1 所示。圖11.1teaching 數(shù)據(jù)庫中每個對象的信息l如果 name 是 SQL Server 數(shù)據(jù)類型或用戶定義數(shù)據(jù)類型，則 sp_help 將返回結(jié)果集包括 Type_name(數(shù)據(jù)類型名稱)、Storage_type(SQL S

9、erver 類型名稱)、長度(數(shù)據(jù)類型的物理長度(以字節(jié)為Nullable(指示是否)、Prec(精度(數(shù)字總位數(shù))、小數(shù)位數(shù)(小數(shù)點右邊的數(shù)字位數(shù))、NULL 值：“是”或“否”)、Default_name(綁定到此類型的默認(rèn)值的名稱)、Rule_name(綁定到此類型的規(guī)則的名稱)、排序規(guī)則(數(shù)據(jù)類型的排序規(guī)則)。【例 11.2】返回數(shù)據(jù)類型信息。以下示例將列出有關(guān) teaching 數(shù)據(jù)庫中 smallint 數(shù)據(jù)類型的信息。USE teaching GOEXEC sp_help smallint GO執(zhí)行結(jié)果如圖 11.2 所示。·4·數(shù)據(jù)庫應(yīng)用技術(shù)圖11.2 te

10、aching 數(shù)據(jù)庫中smallint數(shù)據(jù)類型的信息l如果 name 是數(shù)據(jù)庫對象而不是數(shù)據(jù)類型，則 sp_help 將根據(jù)指定的數(shù)據(jù)庫對象的類型返回結(jié)果集， 同時還包括 Name( 表名) 、所有者( 表所有者) 、Type( 表類型) 及Created_datetime(表的創(chuàng)建日期)。根據(jù)指定的數(shù)據(jù)庫對象，sp_help 將返回其他結(jié)果集。如果 name 是系統(tǒng)表、用戶表或視圖，則 sp_help 將返回結(jié)果集，結(jié)果集包括的內(nèi)容及含義見表 11.1 至表 11.8。但是，為視圖返回說明數(shù)據(jù)文件在文件組中位置的結(jié)果集。² 返回的有關(guān)列對象的結(jié)果集表11.1 有關(guān)列對象的結(jié)果集列名

11、數(shù)據(jù)類型說明Column_namenvarchar(128)列名Typenvarchar(128)列數(shù)據(jù)類型Computedvarchar(35)指示是否計算列中的值：“是”或“否”Lengthint以字節(jié)為的列長度Precchar(5)列精度Scalechar(5)列小數(shù)位數(shù)Nullablevarchar(35)指示是否列中包含NULL 值：“是”或“否”TrimTrailingBlanksvarchar(35)剪裁尾隨空格，返回Yes 或 NoFixedLenNullInSourcevarchar(35)僅為保持向后兼容性Collationsysname列的排序規(guī)則，對于非字符數(shù)據(jù)類性為N

12、ULL²標(biāo)識列返回的結(jié)果集表11.2標(biāo)識列返回的結(jié)果集列名數(shù)據(jù)類型說明Identitynvarchar(128)其數(shù)據(jù)類型被為標(biāo)識的列名Seednumeric標(biāo)識列的起始值Incrementnumeric用于此列中的值的增量第 11 章過程與觸發(fā)器·5·登錄名(如 sqlrepl)試圖在表中數(shù)據(jù)時，Not For Replicationint不強(qiáng)制使用IDENTITY 屬性：1 = True；0 = False²各列返回的結(jié)果集表11.3各列返回的結(jié)果集列名數(shù)據(jù)類型說明RowGuidColsysname全局唯一標(biāo)識符列的名稱²文件組返回的結(jié)果集

13、表11.4文件組返回的結(jié)果集列名數(shù)據(jù)類型說明數(shù)據(jù)所在的文件組：主要文件組、次要文件組或事務(wù)日志文件組Data_located_on_filegroupnvarchar(128)²索引返回的結(jié)果集表11.5索引返回的結(jié)果集列名數(shù)據(jù)類型說明index_namesysname索引名index_descriptionvarchar(210)索引的說明index_keysnvarchar(2078)要生成索引的列的列名²約束返回的結(jié)果集表11.6約束返回的結(jié)果集列名數(shù)據(jù)類型說明constrain_typenvarchar(146)約束的類型constraint_namenvarcha

14、r(128)約束的名稱指示DELETE 操作是：No Action、CASCADE還是N/A。僅適用于FOREIGN KEY 約束delete_actionnvarchar(9)指示UPDATE 操作是：No Action、Cascade 還是N/A。僅適用于FOREIGN KEY 約束update_actionnvarchar(9)指示是否啟用約束：Enabled、Disabled 或N/A。僅適用于CHECK 和 FOREIGN KEY 約束status_enabledvarchar(8)指示約束是否用于。僅適用于 CHECK 和FOREIGN KEY 約束Status_for_repli

15、cationvarchar(19)約束的列的名稱。對于默認(rèn)值和規(guī)則而言， 則為定義默認(rèn)值或規(guī)則的文本constrain_keysnvarchar(2078)²執(zhí)行的對象返回的結(jié)果集·6·數(shù)據(jù)庫應(yīng)用技術(shù)表11.7執(zhí)行的對象返回的結(jié)果集列名數(shù)據(jù)類型說明Table is referenced by viewsnvarchar(516)標(biāo)識表的其他數(shù)據(jù)庫對象²過程、函數(shù)或擴(kuò)展過程返回的結(jié)果集表11.8過程、函數(shù)或擴(kuò)展過程返回的結(jié)果集列名數(shù)據(jù)類型說明Parameter_namenvarchar(128)過程參數(shù)名Typenvarchar(128)過程參數(shù)的數(shù)據(jù)類型

16、Lengthsmallint最大物理長度(以字節(jié)為)Precint精度，即數(shù)字總位數(shù)Scaleint小數(shù)點右邊的數(shù)字位數(shù)Param_ordersmallint參數(shù)的順序Parameter_namenvarchar(128)過程參數(shù)名【例 11.3】返回有關(guān)單個對象的信息。以下示例將顯示有關(guān) student 表的信息。USE teaching GOEXEC sp_help 'student' GO執(zhí)行結(jié)果如圖 11.3 所示。第 11 章過程與觸發(fā)器·7·圖11.3 teaching數(shù)據(jù)庫中student表的信息2.sp_helpdb報告有關(guān)指定數(shù)據(jù)庫或所有數(shù)

17、據(jù)庫的信息。語法格式如下。sp_helpdb dbname= 'name' dbname = 'name'：要報告其信息的數(shù)據(jù)庫的名稱。name 的數(shù)據(jù)類型為 sysname， 沒有默認(rèn)值。如果未指定 name，則 sp_helpdb 將報告 sys.databases 目錄視圖中所有數(shù)據(jù)庫的信息。返回代碼值為 0()或 1(失敗)。sp_helpdb過程結(jié)果集如表 11.9 所示。表11.9sp_helpdb過程結(jié)果集列名數(shù)據(jù)類型說明namesysname數(shù)據(jù)庫名稱db_sizenvarchar(13)數(shù)據(jù)庫總計大小ownersysname數(shù)據(jù)庫所有者，例如

18、sadbidsmallint數(shù)據(jù)庫IDcreatednvarchar(11)數(shù)據(jù)庫創(chuàng)建的日期以逗號分隔的值列表，這些值是當(dāng)前在數(shù)據(jù)庫上設(shè)置的數(shù)據(jù)庫選項的值只有啟用布爾值選項時，才將這些選項列出。非布爾statusnvarchar(600)·8·數(shù)據(jù)庫應(yīng)用技術(shù)選項及其對應(yīng)值以 option_name=value 的形式列出compatibility_leveltinyint數(shù)據(jù)庫兼容級別：60、65、70、80 或 90結(jié)果集中的 status 列報告數(shù)據(jù)庫中已設(shè)置為 ON 的選項，并非所有的數(shù)據(jù)庫選項都由status 列報告。若要查看當(dāng)前數(shù)據(jù)庫選項設(shè)置的完整列表，可以使用

19、sys.databases 目錄視圖。如果指定 name，便會有顯示指定數(shù)據(jù)庫的文件分配的結(jié)果集，如表 11.10 所示。表11.10 指定name的結(jié)果集列名數(shù)據(jù)類型說明namenchar(128)邏輯文件名fileidsmallint文件IDfilenamenchar(260)操作系統(tǒng)文件名(物理文件名稱)文件所屬的文件組NULL = 文件為日志文件，它決不是文件組的一部分filegroupnvarchar(128)sizenvarchar(18)文件大小(MB)文件大小可達(dá)到的最大值。此字段中的 UNLIMITED值表示文件可以一直增長到磁盤已滿maxsizenvarchar(18)文件

20、的增量，表示每次需要新的空間時給文件增加的空間大小growthnvarchar(18)文件用法，對于數(shù)據(jù)文件，該值為'data only'，對于日志文件，該值為'log only'usagevarchar(9)該系統(tǒng)過程使用權(quán)限為，當(dāng)指定單個數(shù)據(jù)庫時，需要具有數(shù)據(jù)庫中的 public成員。當(dāng)沒有指定數(shù)據(jù)庫時，需要具有 master 數(shù)據(jù)庫中的 public成員。如果無法數(shù)據(jù)庫，那么 sp_helpdb 將顯示相應(yīng)的錯誤消息?！纠?11.4】返回有關(guān)單個數(shù)據(jù)庫的信息。以下示例顯示有關(guān) teaching 數(shù)據(jù)庫的信息。EXEC sp_helpdb ' tea

21、ching ' GO執(zhí)行結(jié)果如圖 11.4 所示。圖11.4 teaching數(shù)據(jù)庫信息【例 11.5】返回有關(guān)所有數(shù)據(jù)庫的信息。以下示例顯示運行在 SQL Server 服務(wù)器上所第 11 章過程與觸發(fā)器·9·有數(shù)據(jù)庫的信息。EXEC sp_helpdb GO執(zhí)行結(jié)果如圖 11.5 所示。圖11.5所有數(shù)據(jù)庫信息3.sp_helpfile返回與當(dāng)前數(shù)據(jù)庫關(guān)聯(lián)的文件的物理名稱及屬性。使用此器或從服務(wù)器分離的文件名。語法格式如下。sp_helpfile filename = 'name' 過程可以確定附加到服務(wù) filename = 'name

22、'：是當(dāng)前數(shù)據(jù)庫中任意文件的邏輯名稱。name 的數(shù)據(jù)類型為sysname，默認(rèn)值為 NULL。如果未指定 name，則返回當(dāng)前數(shù)據(jù)庫中所有文件的屬性。返回代碼值為 0()或 1(失敗)。sp_helpfile過程結(jié)果集如表 11.11 所示。表11.11 sp_helpfile過程結(jié)果集列名數(shù)據(jù)類型說明namesysname邏輯文件名fileidsmallint文件的數(shù)字標(biāo)識符。如果指定了name，則不返回該標(biāo)識符filenamenchar(260)物理文件名文件所屬的文件組NULL = 文件為日志文件，它決不是文件組的一部分filegroupsysnamesizenvarchar(

23、15)文件大小(MB)文件大小可達(dá)到的最大值，此字段中的 UNLIMITED 值表示文件可以一直增長到磁盤充滿maxsizenvarchar(15)文件的增量，表示每次需要新空間時為文件增加的空間大小growthnvarchar(15)0 = 文件的大小是固定的，增長對于數(shù)據(jù)文件，該值為'data only'，而對于日志文件，該值為'log only'usagevarchar(9)過程使用權(quán)限為要求具有 public該系統(tǒng)的成員?！纠?11.6】以下示例返回有關(guān) teaching 中的文件的信息。USE teaching·10·數(shù)據(jù)庫應(yīng)用技術(shù)

24、GOEXEC sp_helpfile GO執(zhí)行結(jié)果如圖 11.6 所示。圖11.6 teaching數(shù)據(jù)庫文件信息4.sp_rename在當(dāng)前數(shù)據(jù)庫中更改用戶創(chuàng)建對象的名稱。此對象可以是表、索引、列、別名數(shù)據(jù)類型或 Microsoft .NET Framework 公共語言運行時(CLR)用戶定義類型。語法格式如下。sp_rename objname = 'object_name' , newname = 'new_name' , objtype = 'object_type' 只能更改當(dāng)前數(shù)據(jù)庫中的對象名稱或數(shù)據(jù)類型名稱。大多數(shù)系統(tǒng)數(shù)據(jù)類型和系

25、統(tǒng)對象的名稱都不能更改。每當(dāng)重命名 PRIMARY KEY 或 UNIQUE 約束時，sp_rename 都會自動重命名關(guān)聯(lián)的索引。如果重命名的索引與 PRIMARY KEY 約束關(guān)聯(lián)，則 sp_rename 也會自動重命名該PRIMARY KEY 約束。更改對象名的任一部分都可能破壞和過程。建議不要使用此語句來重命名過程、觸發(fā)器、用戶定義函數(shù)或視圖，而是刪除該對象，然后使用新名稱重新創(chuàng)建該對象。重命名諸如表或列等對象將自動重命名對該對象的。必須手動修改已重命名對象的任何對象。例如，如果重命名表列，并且觸發(fā)器中發(fā)器以反映新的列名。了該列，則手動必須修改觸l objname = 'obj

26、ect_name'：用戶對象或數(shù)據(jù)類型的當(dāng)前限定或非限定的名稱。如果要重命名的對象是表中的列，則 object_name 的格式必須是 table.column。如果要重命名的對象是索引，則 object_name 的格式必須是 table.index。只有在指定了合法的對象時才使用引號。如果提供了完全限定名稱，包括數(shù)據(jù)庫名稱，則該數(shù)據(jù)庫名稱必須是當(dāng)前數(shù)據(jù)庫的名稱。object_name 的數(shù)據(jù)類型為 nvarchar(776)，無默認(rèn)值。l newname = 'new_name'：指定對象的新名稱。new_name 必須是名稱的一部分，并且必須遵循標(biāo)識符的規(guī)則。ne

27、w_name 的數(shù)據(jù)類型為 sysname，無默認(rèn)值。注意：觸發(fā)器名稱不能以 # 或 # 開頭。l objtype = 'object_type'：指定要重命名的對象類型。object_type 的數(shù)據(jù)類型為第 11 章過程與觸發(fā)器·11·varchar(13)，默認(rèn)值為 NULL，可取如表 11.12 所示的值之一。表11.12 object_type的數(shù)據(jù)類型值值說明COLUMN要重命名的列DATABASE用戶定義數(shù)據(jù)庫。重命名數(shù)據(jù)庫時需要此對象類型INDEX用戶定義索引在sys.objects 中跟蹤的類型的項目。例如，OBJECT 可用于重命名約束(

28、CHECK 、FOREIGN KEY 、PRIMARY/UNIQUE KEY)、用戶表和規(guī)則等對象OBJECT通過執(zhí)行CREATE TYPE 或sp_addtype 添加別名數(shù)據(jù)類型或CLR 用戶定義類型USERDATATYPE返回代碼值為 0()或非零數(shù)字(失敗)。該系統(tǒng)過程的使用權(quán)限為，若要重命名對象、列或索引，則需要對該對象具有ALTER 權(quán)限。若要重命名用戶類型，則需要對該類型具有 CONTROL 權(quán)限。若要重命名數(shù)據(jù)庫，需要具備 sysadmin 或dbcreator 固定服務(wù)器的成員?！纠?11.7】重命名表。以下示例將 student 表重命名為 stu。USE teaching

29、GOEXEC sp_rename 'student', 'stu' GO【例 11.8】重命名列。以下示例將 student 表中的 SNO 重命名為 SID。USE teaching GOEXEC sp_rename 'student.SNO', 'SID', 'COLUMN' GO【例 11.9】重命名索引。以下示例將 PK_student 索引重命名為 PK_stu。USE teaching GOEXEC sp_rename 'student.PK_student', 'PK_stu&

30、#39;, 'INDEX' GO5.sp_renamedb更改數(shù)據(jù)庫的名稱。語法格式如下。sp_renamedb dbname = 'old_name' , newname = 'new_name'l dbname = 'old_name'：數(shù)據(jù)庫的當(dāng)前名稱。old_name 的數(shù)據(jù)類型為 sysname，無默認(rèn)值。l newname = 'new_name'：數(shù)據(jù)庫的新名稱。new_name 必須遵循有關(guān)標(biāo)識符的規(guī)則。new_name 的數(shù)據(jù)類型為 sysname，無默認(rèn)值。返回代碼值為 0()或非零數(shù)字(失敗)

31、。該系統(tǒng)過程的使用權(quán)限為，具有 sysadmin 或 dbcreator 固定服務(wù)器的成員資格。·12·數(shù)據(jù)庫應(yīng)用技術(shù)【例11.10】以下示例先創(chuàng)建Accounting 數(shù)據(jù)庫，然后將該數(shù)據(jù)庫的名稱更改為Financial， 再sys.databases 目錄視圖以確認(rèn)數(shù)據(jù)庫的新名稱。USE master GOCREATE DATABASE Accounting GOEXEC sp_renamedb N'Accounting', N'Financial' GOSELECT name, database_id, modified_date FR

32、OM sys.databasesWHERE name = N'Financial' GO6.sp_databases列出駐留在 SQL Server 2005 Database Engine 實例中的數(shù)據(jù)庫或可以通過數(shù)據(jù)庫網(wǎng)關(guān)訪問的數(shù)據(jù)庫。語法格式如下。sp_databases所返回的數(shù)據(jù)庫名稱可以作為 USE 語句的參數(shù)，用來更改當(dāng)前數(shù)據(jù)庫上下文。返回代碼值為無。sp_databases過程結(jié)果集如表 11.13 所示。表11.13 sp_databases過程結(jié)果集列名數(shù)據(jù)類型說明數(shù)據(jù)庫的名稱。在數(shù)據(jù)庫引擎中，此列表示存儲在sys.databases 目錄視圖中的數(shù)據(jù)庫名稱

33、DATABASE_NAMEsysnameDATABASE_SIZEint數(shù)據(jù)庫的大小(以 KB 計)REMARKSvarchar(254)對于數(shù)據(jù)庫引擎，此字段始終返回NULL該系統(tǒng)過程的使用權(quán)限為：需要對架構(gòu)的 SELECT 權(quán)限?！纠?11.11】以下示例顯示如何執(zhí)行 sp_databases。EXEC sp_databasesGO執(zhí)行結(jié)果如圖 11.7 所示。第 11 章過程與觸發(fā)器·13·圖11.7 駐留實例中的數(shù)據(jù)庫信息7.sp_tables返回可在當(dāng)前環(huán)境中象。語法格式如下。的對象列表。這些對象是可以在 FROM 子句中出現(xiàn)的任何對sp_tables table

34、_name = 'name' , table_owner = 'owner' , table_qualifier = 'qualifier' , table_type = "'type'" , fUsePattern = 'fUsePattern' table_name = 'name' ： 用來返回目錄信息的表。 name 的數(shù)據(jù)類型為lnvarchar(384)，默認(rèn)值為 NULL。支持通配符模式匹配。l table_owner = 'owner'：用于返回目

35、錄信息的表的所有者。owner 的數(shù)據(jù)類型為 nvarchar(384)，默認(rèn)值為 NULL。支持通配符模式匹配。如果未指定所有者，則遵循基礎(chǔ)DBMS 的默認(rèn)表可見性規(guī)則。在 SQL Server 中，如果當(dāng)前用戶擁有一個具有指定名稱的表，則返回該表的列。如果未指定所有者，且當(dāng)前用戶未擁有指定名稱的表，則該過程查找由數(shù)據(jù)庫所有者擁有的具有指定名稱的表。如果有，則返回該表的列。l table_qualifier = 'qualifier'：表限定符的名稱。qualifier 的數(shù)據(jù)類型為 sysname，默認(rèn)值為 NULL。在 SQL Server 中，此列表示數(shù)據(jù)庫名稱。l ,

36、 table_type = "'type'" ：由逗號分隔的值列表，該列表提供有關(guān)所有指定的表的類型信息。這些類型包括 TABLE、SYSTEMTABLE 和 VIEW。type 的數(shù)據(jù)類型為varchar(100)，默認(rèn)值為 NULL。每個表類型都必須用單引號括起來，整個參數(shù)必須引號括起來。表類型必須大寫。如果 SET QUOTED_IDENTIFIER 為 ON，則每個單引號必須換成雙引號，整個參數(shù)必須用單引號括起來。l fUsePattern = 'fUsePattern'：確定下劃線 (_)、百分號 (%) 和方括號( 或 )是否解釋

37、為通配符。有效值為 0(模式匹配為關(guān)閉狀態(tài))和1(模式匹配為打開狀態(tài))。fUsePattern的數(shù)據(jù)類型為 bit，默認(rèn)值為 1。返回代碼值為無。sp_tables過程結(jié)果集如表 11.14 所示。·14·數(shù)據(jù)庫應(yīng)用技術(shù)表11.14sp_tables過程結(jié)果集列名數(shù)據(jù)類型說明表限定符名稱，在 SQL Server 中，此列表示數(shù)據(jù)庫名稱。該字段可以為NULLTABLE_QUALIFIERsysname表所有者名稱，在 SQL Server 中，此列表示創(chuàng)建該表的數(shù)據(jù)庫用戶的名稱。該字段始終返回值TABLE_OWNERsysnameTABLE_NAMEsysname表名，該字

38、段始終返回值TABLE_TYPEvarchar(32)表、系統(tǒng)表或視圖REMARKSvarchar(254)SQL Server 不為此列返回值過程的使用權(quán)限為：需要具有對架構(gòu)的 SELECT 權(quán)限。該系統(tǒng)【例 11.12】返回可在 master 數(shù)據(jù)庫中USE master EXEC sp_tables GO執(zhí)行結(jié)果如圖 11.8 所示。的對象列表。圖11.8master數(shù)據(jù)庫中可的對象列表【例 11.13】返回有關(guān) teaching 中的表的信息。以下示例返回有關(guān) teaching 數(shù)據(jù)庫中的dbo 所擁有的表的信息。USE teaching GOEXEC sp_tables table_

39、name = '%', table_owner = 'dbo',table_qualifier = 'teaching'GO執(zhí)行結(jié)果如圖 11.9 所示。第 11 章過程與觸發(fā)器·15·圖11.9 teaching數(shù)據(jù)庫dbo所擁有的表信息8.sp_columns返回當(dāng)前環(huán)境中可的指定表或視圖的列信息。語法格式如下。sp_columns table_name = object , table_owner = owner table_name = object：用于返回目錄信息的表或視圖的名稱。object 的數(shù)據(jù)類型l為 nv

40、archar(384)，沒有默認(rèn)值。支持通配符模式匹配。l table_owner = owner：用于返回目錄信息的表或視圖的對象所有者。owner 的數(shù)據(jù)類型為 nvarchar(384)，默認(rèn)值是 NULL。支持通配符模式匹配。如果未指定 owner，則應(yīng)用基礎(chǔ) DBMS 的默認(rèn)表或視圖可見性規(guī)則。返回代碼值為無。sp_columns過程結(jié)果集如表 11.15 所示。表11.15 sp_columns過程結(jié)果集列名數(shù)據(jù)類型說明TABLE_QUALIFIERsysname表或視圖限定符的名稱，該字段可以為NULLTABLE_OWNERsysname表或視圖所有者的名稱，該字段始終返回值TA

41、BLE_NAMEsysname表或視圖的名稱，該字段始終返回值所返回的 TABLE_NAME 中每列的列名。該字段始終返回值COLUMN_NAMEsysnameODBC 數(shù)據(jù)類型的整數(shù)代碼。如果該數(shù)據(jù)類型無法到 ODBC 類型，則為 NULL。本機(jī)數(shù)據(jù)類型名稱在 TYPE_NAME 列中返回DATA_TYPEsmallint表示數(shù)據(jù)類型的字符串，基礎(chǔ) DBMS 提供此數(shù)據(jù)類型的名稱TYPE_NAMEsysnamePRECISIONint有效數(shù)字位數(shù)，PRECISION 列的返回值以 10 為基數(shù)LENGTHint數(shù)據(jù)的傳輸大小SCALEsmallint小數(shù)點后的數(shù)字位數(shù)RADIXsmallin

42、t數(shù)值數(shù)據(jù)類型的基數(shù)指定為空性1 = 可以為 NULLNULLABLEsmallint·16·數(shù)據(jù)庫應(yīng)用技術(shù)0 = NOT NULLREMARKSvarchar(254)該字段總是返回NULLCOLUMN_DEFnvarchar(4000)列的默認(rèn)值SQL 數(shù)據(jù)類型出現(xiàn)在說明符的TYPE 字段中時的值。該列與 DATA_TYPE 列相同，datetime 和 SQL-92 interval 數(shù)據(jù)類型除外。該列始終返回值SQL_DATA_TYPEsmallintdatetime 及 SQL-92 interval 數(shù)據(jù)類型的子類型代碼。對于其他數(shù)據(jù)類型，該列返回NULLSQL

43、_DATETIME_SUBsmallint字符或整數(shù)數(shù)據(jù)類型的列的最大長度(字節(jié))，對于所有其他數(shù)據(jù)類型，該列返回NULLCHAR_OCTET_LENGTHint列在表中的序號位置。表中的第一列為 1。此列始終返回值ORDINAL_POSITIONint表中列的為空性。根據(jù) ISO 規(guī)則確定為空性。符合ISO SQL 的DBMS 無法返回空字符串。YES = 列可以包含NULL。NO = 列不能包含NULL。如果不知道為空性，該列則返回零長度字符串該列的返回值與NULLABLE 列的返回值不同IS_NULLABLEvarchar(254)SS_DATA_TYPEtinyint擴(kuò)展過程使用的 S

44、QL Server 數(shù)據(jù)類型該系統(tǒng)過程的使用權(quán)限為：需要具有對架構(gòu)的 SELECT 權(quán)限。【例 11.14】以下示例返回指定表 course 的列信息。USE teaching GOEXEC sp_columns table_name = 'course', table_owner = 'dbo'執(zhí)行結(jié)果如圖 11.10 所示。圖11.10 course數(shù)據(jù)表的列信息11.1.4 設(shè)計過程幾乎所有可以寫成批處理的 Transact-SQL 代碼都可以用來創(chuàng)建過程。1.過程的設(shè)計規(guī)則過程的設(shè)計規(guī)則包括以下內(nèi)容。CREATE PROCEDURE 定義自身可以包括任意

45、數(shù)量和類型的 Transact-SQL 語句，Ø但表 11.16 所示的語句除外。不能在過程的任何位置使用這些語句。第 11 章過程與觸發(fā)器·17·表11.16不能在過程中使用的語句CREATE AGGREGATECREATE RULECREATE DEFAULTCREATE SCHEMACREATE 或ALTER FUNCTIONCREATE 或ALTER TRIGGERCREATE 或ALTER PROCEDURECREATE 或ALTER VIEWUSE database_nameØ其他數(shù)據(jù)庫對象均可在時已經(jīng)創(chuàng)建了該對象即可。過程中創(chuàng)建?？梢栽谕?/p>

46、過程中創(chuàng)建的對象，只要ØØ可以在如果在過程內(nèi)臨時表。過程內(nèi)創(chuàng)建本地臨時表，則臨時表僅為該過程而存在；該過程后，臨時表將消失。Ø如果執(zhí)行的過程將調(diào)用另一個過程，則被調(diào)用的過程可以由第一個過程創(chuàng)建的所有對象，包括臨時表在內(nèi)。Ø如果執(zhí)行對Microsoft SQL Server 2005 實例進(jìn)行更改的過程不參與事務(wù)處理。過程，則不能回滾這些更改。過程中的參數(shù)的最大數(shù)目為 2 100。過程中的局部變量的最大數(shù)目僅受可用內(nèi)存的限制。ØØØ2.根據(jù)可用內(nèi)存的不同，過程最大可達(dá) 128MB。限定過程內(nèi)的名稱在過程內(nèi)，如果用于語句(例如

47、SELECT 或 INSERT)的對象名沒有限定架構(gòu)，則架構(gòu)將默認(rèn)為該過程的架構(gòu)。在過程內(nèi)，如果創(chuàng)建該過程的用戶沒有限定SELECT、INSERT、UPDATE 或 DELETE 語句中的表名或視圖名，則默認(rèn)情況下，通過該過程對這些表進(jìn)行的將受到該過程創(chuàng)建者的權(quán)限的限制。如果有其他用戶要使用過程，則用于所有數(shù)據(jù)定義語言(DDL)語句(例如 CREATE、ALTER 或 DROP 語句，DBCC 語句，EXECUTE 和動態(tài) SQL 語句)的對象名應(yīng)該用該對象架構(gòu)的名稱來限定。為這些對象指定架構(gòu)名稱可確保名稱為同一對象，而不管過程的調(diào)用方是誰。如果沒有指定架構(gòu)名稱，SQL Server 將首先嘗

48、試使用調(diào)用方的默認(rèn)架構(gòu)或用戶在 EXECUTE AS 子句中指定的架構(gòu)來3.加密過程定義對象名稱，然后嘗試使用 dbo 架構(gòu)。如果要創(chuàng)建過程，并且希望確保其他用戶無法查看該過程的定義，則可以使用WITH ENCRYPTION 子句。這樣，過程定義將以不可讀的形式過程一旦被加密，其定義將無法，任何人(包括該理員)都將無法查看該過程的定義。過程的所有者或系統(tǒng)管11.1.5 實現(xiàn)過程1.創(chuàng)建用戶定義的過程(1)使用 Transact-SQL 命令創(chuàng)建過程·18·數(shù)據(jù)庫應(yīng)用技術(shù)l創(chuàng)建過程前，需考慮下列事項。ØØCREATE PROCEDURE 語句不能與其他 S

49、QL 語句在單個批處理中組合使用。要創(chuàng)建過程，必須具有數(shù)據(jù)庫的 CREATE PROCEDURE 權(quán)限，還必須具有對架構(gòu)(在其下創(chuàng)建過程)的 ALTER 權(quán)限。ØØ過程是架構(gòu)作用域內(nèi)的對象，名稱必須遵守標(biāo)識符規(guī)則。只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建過程。l創(chuàng)建過程時，應(yīng)指定下列事項。ØØØØ所有輸入?yún)?shù)和向調(diào)用過程或批處理返回的輸出參數(shù)。執(zhí)行數(shù)據(jù)庫操作(包括調(diào)用其他過程)的編程語句。返回至調(diào)用過程或批處理以表明或失敗(以及失敗)的狀態(tài)值。捕獲和處理潛在的錯誤所需的任何錯誤處理語句。可以使用 Transact-SQL 命令 CREATE PROCE

50、DURE 創(chuàng)建PROCEDURE 命令的語法格式如下。過程。 CREATECREATE PROC | PROCEDURE schema_name. procedure_name parameter type_schema_name. data_type = default OUT PUT ,.n WITH < ENCRYPTION > AS <sql_statement> ; .n ;<sql_statement> := BEGIN statements END 各選項含義如下。llschema_name：過程所屬架構(gòu)的名稱。procedure_name：新

51、過程的名稱。過程名稱必須遵循有關(guān)標(biāo)識符的規(guī)則，并且在架構(gòu)中必須唯一。sp_前綴是 SQL Server 用來指定系統(tǒng)過程的，不要以 sp_為前綴創(chuàng)建任何過程。如果用戶定義始終執(zhí)行系統(tǒng)過程與系統(tǒng)過程。過程名稱相同，則該過程將永不執(zhí)行，取而代之的是可在 procedure_name 前面使用一個數(shù)字符號 (#) (#procedure_name) 來創(chuàng)建局部臨時過程，使用兩個數(shù)字符號 (#procedure_name) 來創(chuàng)建全局臨時過程。過程或全局臨時過程的完整名稱(包括 #)不能超過 128 個字符。局部臨時過程的完整名稱(包括 #)不能超過 116 個字符。lparameter：過程中的參數(shù)

52、。在 CREATE PROCEDURE 語句中可以一個或多個參數(shù)。除非定義了參數(shù)的默認(rèn)值或者將參數(shù)設(shè)置為等于另一個參數(shù)，否則用戶必須在調(diào)用的參數(shù)提供值。過程最多可以有 2 100 個參數(shù)。過程時為每個通過使用符號作為第一個字符來指定參數(shù)名稱。參數(shù)名稱必須符合有關(guān)標(biāo)識符的規(guī) 則。每個過程的參數(shù)僅用于該過程本身，其他過程中可以使用相同的參數(shù)名稱。默認(rèn)情況下，參數(shù)只能代替常量表達(dá)式，而不能用于代替表名、列名或其他數(shù)據(jù)庫對象的名稱。第 11 章過程與觸發(fā)器·19·l type_schema_name. data_type：參數(shù)以及所屬架構(gòu)的數(shù)據(jù)類型。除 table 之外的其他所有數(shù)

53、據(jù)類型均可以用作 Transact-SQL過程的參數(shù)。如果未指定 type_schema_name，則 SQL Server 2005 Database Engine 將按以下順序type_name。ØØØSQL Server 系統(tǒng)數(shù)據(jù)類型。當(dāng)前數(shù)據(jù)庫中當(dāng)前用戶的默認(rèn)架構(gòu)。當(dāng)前數(shù)據(jù)庫中的 dbo 架構(gòu)。ldefault：參數(shù)的默認(rèn)值。如果定義了 default 值，則無需指定此參數(shù)的值即可執(zhí)行過程。默認(rèn)值必須是常量或 NULL。如果過程使用帶 LIKE 關(guān)鍵字的參數(shù)，則可包含下列通配符：%、_、 和 。只有 CLR過程的默認(rèn)值在 sys.parameters.de

54、fault 列中。對于 Transact-SQL過程參數(shù)，該列將為 NULL。lOUTPUT：指示參數(shù)是輸出參數(shù)。此選項的值可以返回給調(diào)用 EXECUTE 的語句。使用OUTPUT 參數(shù)將值返回給過程的調(diào)用方。除非是 CLR過程，否則 text、ntext 和image參數(shù)不能用作 OUTPUT 參數(shù)。使用 OUTPUT 關(guān)鍵字的輸出參數(shù)可以為游標(biāo)占位符，CLR 存儲過程除外。lWITH <ENCRYPTION>：指示 SQL Server 將 CREATE PROCEDURE 語句的原始文本轉(zhuǎn)換為模糊格式。模糊代碼的輸出在 SQL Server 2005 的任何目錄視圖中都不能直接顯示。對系統(tǒng)表或數(shù)據(jù)庫文件沒有過程無效。權(quán)限的用戶不能檢索模糊文本。該選項對于 CLRl<sql_statement&