熱點推薦:
您现在的位置: 電腦知識網 >> 操作系統 >> Windows系統管理 >> 正文

設計存儲過程

2022-06-13   來源: Windows系統管理 

  幾乎任何可寫成批處理的 TransactSQL 代碼都可用於創建存儲過程
  
  存儲過程的設計規則
  存儲過程的設計規則包括
  
  CREATE PROCEDURE 定義本身可包括除下列 CREATE 語句以外的任何數量和類型的 SQL 語句存儲過程中的任意地方都不能使用下列語句
   
  可在存儲過程中創建其它數據庫對象可以引用在同一存儲過程中創建的對象前提是在創建對象後再引用對象
  
  
  可以在存儲過程內引用臨時表
  
  
  如果在存儲過程內創建本地臨時表則該臨時表僅為該存儲過程而存在退出該存儲過程後臨時表即會消失
  
  
  如果執行調用其它存儲過程的存儲過程那麼被調用存儲過程可以訪問由第一個存儲過程創建的包括臨時表在內的所有對象
  
  
  如果執行在遠程 Microsoft® SQL Server&#; 實例上進行更改的遠程存儲過程則不能回滾這些更改遠程存儲過程不參與事務處理
  
  
  存儲過程中參數的最大數目為
  
  
  存儲過程中局部變量的最大數目僅受可用內存的限制
  
  
  根據可用內存的不同存儲過程的最大大小可達 MB
  有關創建存儲過程的規則的更多信息請參見 CREATE PROCEDURE
  
  限定存儲過程內的名稱
  在存儲過程內部如果用於諸如 SELECT 或 INSERT 這樣的語句的對象名沒有限定用戶那麼用戶將默認為該存儲過程的所有者在存儲過程內部如果創建存儲過程的用戶沒有限定 SELECTINSERTUPDATE 或 DELETE 語句中引用的表名那麼通過該存儲過程對這些表進行的訪問將默認地受到該過程的創建者權限的限制
  
  如果有其他用戶要使用存儲過程則用於語句 ALTER TABLECREATE TABLEDROP TABLETRUNCATE TABLECREATE INDEXDROP INDEXUPDATE STATISTICS 和 DBCC 的對象名必須用該對象所有者的名稱限定例如Mary 擁有表 marytab如果她希望其他用戶能夠執行使用該表的存儲過程必須在該表用於上述某一條語句時對其表名進行限定
  
  此規則是必需的因為運行存儲過程時將解析對象的名稱如果未限定 marytab而 John 試圖執行該過程SQL Server 將查找 John 所擁有的名為 marytab 的表
  
  加密過程定義
  如果要創建存儲過程並且希望確保其他用戶無法查看該過程的定義那麼可以使用 WITH ENCRYPTION 子句這樣過程定義將以不可讀的形式存儲
  
  存儲過程一旦加密其定義即無法解密任何人(包括存儲過程的所有者或系統管理員)都將無法查看存儲過程定義
  
  SET 語句選項
  當 ODBC 應用程序與 SQL Server 連接時服務器將自動設置會話的下列選項
  
  SET QUOTED_IDENTIFIER ON
  
  
  SET TEXTSIZE
  
  
  SET ANSI_DEFAULTS ON
  
  
  SET CURSOR_CLOSE_ON_COMMIT OFF
  
  
  SET IMPLICIT_TRANSACTIONS OFF
  這些設置將提高 ODBC 應用程序的可移植性由於基於 DBLibrary 的應用程序通常不設置這些選項所以應在上述所列 SET 選項打開和關閉的情況下都對存儲過程進行測試這樣可確保存儲過程始終能正確工作而不管特定的連接在喚醒調用該存儲過程時可能設置的選項需要特別設置其中一個選項的存儲過程應在開始該存儲過程時發出一條 SET 語句此 SET 語句將只對該存儲過程的執行保持有效當該存儲過程結束時將恢復原設置
  
  示例
  A 創建使用參數的存儲過程
  下例創建一個在 pubs 數據庫中很有用的存儲過程給出一個作者的姓和名該存儲過程將顯示該作者的每本書的標題和出版商
  
  CREATE PROC au_info @lastname varchar() @firstname varchar()
  AS
  SELECT au_lname au_fname title pub_name
  FROM authors INNER JOIN titleauthor ON authorsau_id = titleauthorau_id
  JOIN titles ON titleauthortitle_id = titlestitle_id
  JOIN publishers ON titlespub_id = publisherspub_id
  WHERE au_fname = @firstname
  AND au_lname = @lastname
  GO
  
  將出現一條說明該命令未返回任何數據也未返回任何行的消息這表示已創建該存儲過程
  
  現在執行 au_info 存儲過程
  
  EXECUTE au_info Ringer Anne
  GO
  
  下面是結果集
   
  
  ( row(s) affected)
  
  B 創建使用參數默認值的存儲過程
  下例創建一個存儲過程 pub_info該存儲過程顯示作為參數給出的出版商所出版的某本書的作者姓名如果未提供出版商的名稱該存儲過程將顯示由 Algodata Infosystems 出版的書籍的作者
  
  CREATE PROC pub_info @pubname varchar() = Algodata Infosystems
  AS
  SELECT au_lname au_fname pub_name
  FROM authors a INNER JOIN titleauthor ta ON aau_id = taau_id
  JOIN titles t ON tatitle_id = ttitle_id
  JOIN publishers p ON tpub_id = ppub_id
  WHERE @pubname = ppub_name
  
  執行未指定參數的 pub_info
  
  EXECUTE pub_info
  GO
  
  下面是結果集
   
  ( row(s) affected)
  
  C 執行用顯式值替代參數默認值的存儲過程
  在下例中存儲過程 showind 的 @table 參數默認值是 titles
  
  CREATE PROC showind @table varchar() = titles
  AS
  SELECT TABLE_NAME = sysobjectsname
  INDEX_NAME = sysindexesname INDEX_ID = indid
  FROM sysindexes INNER JOIN sysobjects ON sysobjectsid = sysindexesid
  WHERE sysobjectsname = @table
  
  列標題(例如TABLE_NAME)可使結果更具可讀性下面是該存儲過程顯示的 authors 表的情況
  
  EXECUTE showind authors
  GO
  
   
  ( row(s) affected)
  
  如果用戶未提供值則 SQL Server 將使用默認表 titles
  
  EXECUTE showind
  GO
  
  下面是結果集
   
  ( row(s) affected)
  
  D 使用參數默認值 NULL 創建存儲過程
  參數默認值可以是 NULL 值在這種情況下如果未提供參數則 SQL Server 將根據存儲過程的其它語句執行存儲過程不會顯示錯誤信息
  
  過程定義還可指定當不給出參數時要采取的其它某種措施例如
  
  CREATE PROC showind @table varchar() = NULL
  AS IF @table IS NULL
  PRINT Give a table name
  ELSE
  SELECT TABLE_NAME = sysobjectsname
  INDEX_NAME = sysindexesname INDEX_ID = indid
  FROM sysindexes INNER JOIN sysobjects
  ON sysobjectsid = sysindexesid
  WHERE sysobjectsname = @table
  
  E 使用包含通配符的參數默認值創建存儲過程
  如果存儲過程將參數用於 LIKE 關鍵字那麼默認值可包括通配符(%_[] 和 [^])例如可將 showind 修改為當不提供參數時顯示有關系統表的信息
  
  CREATE PROC showind @table varchar() = sys%
  AS SELECT TABLE_NAME = sysobjectsname
  INDEX_NAME = sysindexesname INDEX_ID = indid
  FROM sysindexes INNER JOIN sysobjects
  ON sysobjectsid = sysindexesid
  WHERE sysobjectsname LIKE @table
  
  在存儲過程 au_info 的下列變化形式中兩個參數都有帶通配符的默認值
  
  CREATE PROC au_info @lastname varchar() = D%
  @firstname varchar() = %
  AS
  SELECT au_lname au_fname title pub_name
  FROM authors INNER JOIN titleauthor ON authorsau_id = titleauthorau_id
  JOIN titles ON titleauthortitle_id = titlestitle_id
  JOIN publishers ON titlespub_id = publisherspub_id
  WHERE au_fname LIKE @firstname
  AND au_lname LIKE @lastname
  
  如果執行 au_info 時不指定參數將顯示姓以字母 D 開頭的所有作者
  
  EXECUTE au_info
  GO
  
  下面是結果集
   
  ( row(s) affected)
  
  下例在兩個參數的默認值已定義的情況下省略了第二個參數因此可找到姓為 Ringer 的所有作者的書和出版商
  
  EXECUTE au_info Ringer
  GO
  
  ( row(s) affected)

From:http://tw.wingwit.com/Article/os/xtgl/201311/9335.html
    推薦文章
    Copyright © 2005-2022 電腦知識網 Computer Knowledge   All rights reserved.