《數(shù)據(jù)庫原理與應(yīng)用》09.存儲過程的創(chuàng)建和使用

《《數(shù)據(jù)庫原理與應(yīng)用》09.存儲過程的創(chuàng)建和使用》由會員分享，可在線閱讀，更多相關(guān)《《數(shù)據(jù)庫原理與應(yīng)用》09.存儲過程的創(chuàng)建和使用（39頁珍藏版）》請在裝配圖網(wǎng)上搜索。

1、 數(shù) 據(jù) 庫 原 理 與 應(yīng) 用 第 九 講 存 儲 過 程 的 創(chuàng) 建 和 使 用 1、 創(chuàng) 建 存 儲 過 程2、 執(zhí) 行 存 儲 過 程3、 查 看 和 修 改 存 儲 過 程4、 重 命 名 和 刪 除 存 儲 過 程 本 講 主 要 內(nèi) 容 存 儲 過 程 的 概 念SQL Server提 供 了 一 種 方 法 ， 它 可 以 將 一 些 固 定 的操 作 集 中 起 來 由 SQL Server數(shù) 據(jù) 庫 服 務(wù) 器 來 完 成 ， 以 實現(xiàn) 某 個 任 務(wù) ， 這 種 方 法 就 是 存 儲 過 程 。 類 似 于 DOS一 下的 批 處 理 。 存 儲 過 程 是 SQL語

2、句 和 可 選 控 制 流 程 語 句 的預(yù) 編 譯 集 合 。 是 一 種 封 裝 重 復(fù) 任 務(wù) 操 作 的 方 法 ， 以 一 個名 稱 存 儲 ， 作 為 一 個 單 元 處 理 。 存 儲 過 程 屬 于 服 務(wù) 器 方軟 件 ， 可 立 即 訪 問 數(shù) 據(jù) 庫在 SQL Server中 存 儲 過 程 分 為 兩 類 ： 即 系 統(tǒng) 提 供 的存 儲 過 程 和 用 戶 自 定 義 的 存 儲 過 程 。 存 儲 過 程 存 儲 在 數(shù) 據(jù) 庫 內(nèi) ， 可 由 應(yīng) 用 程 序 通 過 一 個調(diào) 用 來 執(zhí) 行 ， 而 且 充 許 用 戶 聲 明 變 量 。 同 時 ， 存 儲 過

3、程可 以 接 收 和 輸 出 參 數(shù) 、 返 回 執(zhí) 行 存 儲 過 程 的 狀 態(tài) 值 ， 也可 以 嵌 套 調(diào) 用 。注 意 :存 儲 過 程 與 函 數(shù) 不 同 ， 因 為 存 儲 過 程 并 不 返 回取 代 其 名 稱 的 值 ， 也 不 能 直 接 在 表 達(dá) 式 中 使 用 。 在 SQL Server中 存 儲 過 程 分 為 兩 類 :系 統(tǒng) 提 供 的 存 儲 過 程 和 用 戶 存儲 過 程 。 系 統(tǒng) 過 程 主 要 存 儲 在 Master數(shù) 據(jù) 庫 中 ， 并 以 SP_為 前 綴 ， 并 且系 統(tǒng) 存 儲 過 程 主 要 是 從 系 統(tǒng) 表 中 獲 取 信 息 ，

4、 從 而 為 系 統(tǒng) 管 理 員 管 理 SQL Server提 供 支 持 。 通 過 系 統(tǒng) 存 儲 過 程 ， SQLServer中 的 許 多 管 理 性 或 信 息 性 的 活 動 :如 了 解 數(shù) 據(jù) 庫 對 象 、 數(shù) 據(jù) 庫 信 息都 可 以 被 順 利 有 效 地 完 成 。 盡 管 這 些 系 統(tǒng) 存 儲 過 程 被 放 在 master數(shù) 據(jù) 庫中 ， 但 是 仍 可 以 在 其 它 數(shù) 據(jù) 庫 中 對 其 進(jìn) 行 調(diào) 用 ， 在 調(diào) 用 時 不 必 在 存 儲 過程 名 前 加 上 數(shù) 據(jù) 庫 名 ， 而 且 當(dāng) 創(chuàng) 建 一 個 新 數(shù) 據(jù) 庫 時 ， 一 些 系 統(tǒng)

5、存 儲 過 程會 在 新 數(shù) 據(jù) 庫 中 被 自 動 創(chuàng) 建 。 用 戶 自 定 義 存 儲 過 程 是 由 用 戶 創(chuàng) 建 并 能 完成 某 一 特 定 功 能 (如 查 詢 用 戶 所 需 數(shù) 據(jù) 信 息 )的 存 儲 過 程 。 存 儲 過 程 時 存 放 在 SQL Server中 的 特 別 快 的 數(shù) 據(jù) 庫 對 象 ， 當(dāng) 首 次 運行 存 儲 過 程 時 ， 它 按 以 下 方 式 進(jìn) 行 :1、 該 過 程 被 劃 分 成 部 件 片 斷 。2、 檢 查 引 用 數(shù) 據(jù) 庫 中 其 它 對 蒙 (表 、 視 圖 等 )的 部 件 ， 確 保 引 用 的 對象 是 存 在 的

6、， 這 也 被 稱 為 分 解 。3、 一 旦 分 解 完 成 ， 該 過 程 的 名 字 將 存 放 倒 sysobjects表 中 ， 而 創(chuàng)建 存 儲 過 程 的 代 碼 存 放 在 syscomments表 中4、 然 后 編 譯 ， 并 且 ， 編 譯 過 程 中 將 創(chuàng) 建 如 何 運 行 查 詢 的 藍(lán) 本 。 該藍(lán) 本 通 稱 稱 為 常 規(guī) 計 劃 或 查 詢 樹 ， 查 詢 樹 存 放 在 sysProcetlures表 中 。5、 存 儲 過 程 首 次 運 行 時 ， 讀 出 查 詢 計 劃 并 完 全 編 譯 成 過 程 計 劃 ，然 后 運 行 。 這 樣 ， 節(jié)

7、約 了 每 次 運 行 存 成 過 程 的 語 法 檢 查 、 分 解 和 編 譯 查 詢 樹 的 時 間 。 當(dāng) 利 用 SQL Server創(chuàng) 建 一 個 應(yīng) 用 程 序 時 ， T-SQL是 一 種 主要 的 編 程 語 言 。 若 運 用 T一 SQL來 進(jìn) 行 編 程 有 兩 種 方 法 :其 一 是 在 本 地 存 儲 T-SQL程 序 并 創(chuàng) 建 應(yīng) 用 程 序 ， 向 SQL-Server發(fā) 送 命 令 米 對 結(jié) 果 進(jìn) 行 T-SQL編 寫 的 程 序 作 為 存 儲過 程 其 二 是 可 以 把 部 分 用 在 SQL Server中 ， 并 創(chuàng) 建 應(yīng) 用 程序 來 調(diào)

8、 用 存 儲 過 程 ， 對 數(shù) 據(jù) 結(jié) 果 進(jìn) 行 處 理 。 存 儲 過 程 能 夠通 過 接 收 參 數(shù) 向 調(diào) 用 者 返 回 結(jié) 果 集 ， 結(jié) 果 集 的 格 式 由 調(diào) 用者 確 定 ;返 回 狀 態(tài) 值 給 調(diào) 用 者 ， 指 明 調(diào) 用 是 成 功 或 是 失 敗 :包 括 針 對 數(shù) 據(jù) 庫 的 操 作 語 句 ， 并 且 可 以 在 一 個 存 儲 過 程 中調(diào) 用 另 一 存 儲 過 程 。我 們 通 常 更 偏 愛 于 使 用 第 二 種 方 法 ， 即 在 SQL Server中 使用 存 儲 過 程 ， 而 不 是 在 客 戶 計 算 機 上 調(diào) 用 T一 QL編

9、 寫 的 一段 程 序 原 因 在 于 存 儲 過 程 具 有 以 下 優(yōu) 點 : 存 儲 過 程 的 優(yōu) 點可 用 存 儲 過 程 封 裝 事 務(wù) 規(guī) 則 。 一 旦 封 裝 完 成 ， 這 些 規(guī)則 就 可 用 于 多 個 應(yīng) 用 ， 從 而 有 一 個 一 致 的 數(shù) 據(jù) 接 口 ，因 此 ， 只 需 改 變 過 程 的 功 能 ， 只 需 在 一 個 地 方 對 其 進(jìn)行 修 改 ， 而 不 必 對 每 個 應(yīng) 用 都 進(jìn) 行 修 改 。 存 儲 過 程 允 許 標(biāo) 準(zhǔn) 組 件 式 編 程 :存 儲 過 程 在 被 創(chuàng)建 以 后 ， 可 以 在 程 序 中 被 多 次 調(diào) 用 而 不

10、必 重 新 編寫 該 存 儲 過 程 的 SQL語 句 ;而 且 數(shù) 據(jù) 庫 專 業(yè) 人 員可 隨 時 對 存 儲 過 程 進(jìn) 行 修 改 ， 但 對 應(yīng) 用 程 序 源 代碼 毫 無 影 響 ， 因 為 應(yīng) 用 程 序 源 代 碼 只 包 含 存 儲 過程 的 調(diào) 用 語 句 ， 從 而 極 大 地 提 高 了 程 序 的 可 移 植性 。 存 儲 過 程 能 夠 實 現(xiàn) 較 快 的 執(zhí) 行 速 度 :如 果 某 一 操 作 包 含 大量 的 T SQL代 碼 或 分 別 被 多 次 執(zhí) 行 ， 那 么 存 儲 過 程 要 比批 處 理 的 執(zhí) 行 速 度 快 很 多 。 因 為 存 儲 過

11、 程 是 預(yù) 編 譯 的 ， 在首 次 運 行 一 個 存 儲 過 程 時 ， 查 詢 優(yōu) 化 器 對 其 進(jìn) 行 分 析 優(yōu) 化 ，并 給 出 最 終 被 存 在 系 統(tǒng) 表 中 的 執(zhí) 行 計 劃 ;而 批 處 理 的 T-SQL語 句 在 每 次 運 行 時 都 要 進(jìn) 行 編 譯 和 優(yōu) 化 ， 因 此 速 度 相對 要 慢 一 些 。 存 儲 過 程 能 夠 減 少 網(wǎng) 絡(luò) 流 量 :對 于 同 一 個 針 對 數(shù) 據(jù) 數(shù) 據(jù) 庫對 象 的 操 作 (如 查 詢 修 改 )， 如 果 這 一 操 作 所 涉 及 到 的 T-SQL語 句 被 組 織 成 一 存 儲 過 程 ， 那 么

12、 當(dāng) 在 客 戶 計 算 機 上調(diào) 用 該 存 儲 過 程 時 ， 網(wǎng) 絡(luò) 中 傳 送 的 只 是 該 調(diào) 用 語 句 ;否 則將 是 多 條 SQL語 句 ， 從 而 大 大 增 加 了 網(wǎng) 絡(luò) 流 量 ， 降 低 網(wǎng)絡(luò) 負(fù) 載 。 存 儲 過 程 可 被 作 為 一 種 安 全 機 制 來 充 分 利 用 :系 統(tǒng) 管 理 員通 過 對 執(zhí) 行 某 一 存 儲 過 程 的 權(quán) 限 進(jìn) 行 限 制 ， 從 而 能 夠 實現(xiàn) 對 相 應(yīng) 的 數(shù) 據(jù) 訪 問 權(quán) 限 的 限 制 ， 避 免 非 授 權(quán) 用 戶 對 數(shù)據(jù) 的 訪 問 ， 保 證 數(shù) 據(jù) 的 安 全 。 創(chuàng) 建 存 儲 過 程 的

13、規(guī) 則幾 乎 任 何 可 以 寫 成 批 處 理 的 T一 SQL代 碼 都 可 用 于 創(chuàng) 建存 儲 過 程 ， 但 是 在 設(shè) 計 存 儲 過 程 時 ， 需 要 遵 循 下 列 規(guī) 則 :名 字 必 須 符 合 SQL Server命 名 規(guī) 則 。引 用 的 對 象 必 須 在 創(chuàng) 建 存 儲 過 程 前 就 存 在不 能 在 單 個 存 儲 過 程 中 創(chuàng) 建 后 去 掉 或 再 創(chuàng) 建 同 名 的 對 象 。存 儲 過 程 最 后 能 有 255各 參 數(shù) 。 創(chuàng) 建 存 儲 過 程 的 規(guī) 則再 自 己 的 存 儲 過 程 中 可 以 引 用 臨 時 表 ， 局 部 臨 時 表

14、再 過 程 結(jié) 束 時 將會 消 失 。再 存 儲 過 程 中 不 能 有 如 下 的 SQL創(chuàng) 建 語 句 :Create Default、 Create Procedure、 Create Rule、 Create Trigger、 Create View.可 在 過 程 中 嵌 套 過 程 。創(chuàng) 建 存 儲 過 程 的 文 本 不 能 超 過 64K字 節(jié) ， 以 為 SQL存 放 再syscomments表 中 。若 在 存 儲 過 程 中 使 用 了 Select *， 而 底 層 表 中 加 入 了 新 的 列 ， 新 的 列 再 過 程 運 行 時 無 法 顯 示 。 1 創(chuàng) 建

15、 存 儲 過 程 在 SQL Server中 ， 可 以 使 用 三 種 方 法 創(chuàng) 建 存儲 過 程 ： 使 用 創(chuàng) 建 存 儲 過 程 向 導(dǎo) 創(chuàng) 建 存 儲 過 程 。 利 用 SQL Server 企 業(yè) 管 理 器 創(chuàng) 建 存 儲 過 程 。 使 用 Transact-SQL語 句 中 的 CREATE PROCEDURE命 令 創(chuàng) 建 存 儲 過 程 。 需 要 確 定 存 儲 過 程 的 三 個 組 成 部 分 ： 所 有 的 輸 入 參 數(shù) 以 及 傳 給 調(diào) 用 者 的 輸 出 參 數(shù) 。 被 執(zhí) 行 的 針 對 數(shù) 據(jù) 庫 的 操 作 語 句 ， 包 括 調(diào) 用 其它 存

16、儲 過 程 的 語 句 。 返 回 給 調(diào) 用 者 的 狀 態(tài) 值 ， 以 指 明 調(diào) 用 是 成 功 還是 失 敗 。 1. 使 用 創(chuàng) 建 存 儲 過 程 向 導(dǎo) 創(chuàng) 建 存 儲 過 程 在 企 業(yè) 管 理 器 中 ， 選 擇 工 具 菜 單 中 的 向 導(dǎo)選 項 ， 選 擇 “ 創(chuàng) 建 存 儲 過 程 向 導(dǎo) ” 根 據(jù) 提 示 可完 成 創(chuàng) 建 存 儲 過 程 。 2. 使 用 SQL Server 企 業(yè) 管 理 器 創(chuàng) 建 存 儲 過 程 （ ） 在 SQL Server企 業(yè) 管 理 器 中 ， 選 擇 指 定 的 服務(wù) 器 和 數(shù) 據(jù) 庫 ， 用 右 鍵 單 擊 要 創(chuàng) 建 存

17、 儲 過 程 的 數(shù) 據(jù) 庫 ，在 彈 出 的 快 捷 菜 單 中 選 擇 “ 新 建 ” 選 項 ， 再 選 擇 下 一 級菜 單 中 的 “ 存 儲 過 程 ” 選 項（ ） 在 文 本 框 中 可 以 輸 入 創(chuàng) 建 存 儲 過 程 的 T_SQL語 句 ， 單 擊 “ 檢 查 語 法 ” ， 則 可 以 檢 查 語 法 是 否 正 確 ；單 擊 “ 確 定 ” 按 鈕 ， 即 可 保 存 該 存 儲 過 程 。 如 果 要 設(shè) 置權(quán) 限 ， 單 擊 “ 權(quán) 限 ” 按 鈕 。 3. 使 用 Transact-SQL語 句 創(chuàng) 建 存 儲 過 程創(chuàng) 建 存 儲 過 程 前 ， 應(yīng) 該 考

18、 慮 下 列 幾 個 事 項 ： 不 能 將 CREATE PROCEDURE 語 句 與 其 它 SQL 語 句 組合 到 單 個 批 處 理 中 。 創(chuàng) 建 存 儲 過 程 的 權(quán) 限 默 認(rèn) 屬 于 數(shù) 據(jù) 庫 所 有 者 ， 該 所 有者 可 將 此 權(quán) 限 授 予 其 他 用 戶 。 存 儲 過 程 是 數(shù) 據(jù) 庫 對 象 ， 其 名 稱 必 須 遵 守 標(biāo) 識 符 規(guī) 則 。 只 能 在 當(dāng) 前 數(shù) 據(jù) 庫 中 創(chuàng) 建 存 儲 過 程 。 一 個 存 儲 過 程 的 最 大 尺 寸 為 128M。 CREATE PROCEDURE的 語 法 形 式 ： CREATEPROCEDUR

19、Eprocedure_name;number parameterdata_type VARYING=defaultOUTPUT,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION FORREPLICATION AS sql_statement .n CREATE PROCEDURE的 語 法 參 數(shù) 的 意 義 ： procedure_name： 用 于 指 定 要 創(chuàng) 建 的 存 儲 過 程 的 名 稱 。 number： 該 參 數(shù) 是 可 選 的 整 數(shù) ， 它 用 來 對 同 名 的 存 儲過 程 分 組 ， 以 便 用 一 條 DROP

20、 PROCEDURE 語 句 即 可將 同 組 的 過 程 一 起 除 去 。 parameter： 過 程 中 的 參 數(shù) 。 在 CREATE PROCEDURE 語 句 中 可 以 聲 明 一 個 或 多 個 參 數(shù) 。 data_type： 用 于 指 定 參 數(shù) 的 數(shù) 據(jù) 類 型 。 VARYING： 用 于 指 定 作 為 輸 出 OUTPUT參 數(shù) 支 持 的 結(jié)果 集 。 Default： 用 于 指 定 參 數(shù) 的 默 認(rèn) 值 。 OUTPUT： 表 明 該 參 數(shù) 是 一 個 返 回 參 數(shù) 。 RECOMPILE： 表 明 SQL Server 不 會 保 存 該 存

21、儲 過 程的 執(zhí) 行 計 劃 。ENCRYPTION ： 表 示 SQL Server 加 密 了 syscomments 表 ， 該 表 的 text字 段 是 包 含 CREATE PROCEDURE 語 句 的 存 儲 過 程 文 本 。 FOR REPLICATION： 用 于 指 定 不 能 在 訂 閱 服 務(wù) 器 上執(zhí) 行 為 復(fù) 制 創(chuàng) 建 的 存 儲 過 程 。 AS： 用 于 指 定 該 存 儲 過 程 要 執(zhí) 行 的 操 作 。sql_statement： 是 存 儲 過 程 中 要 包 含 的 任 意 數(shù) 目 和 類型 的 Transact-SQL 語 句 。 2 執(zhí) 行

22、 存 儲 過 程 直 接 執(zhí) 行 存 儲 過 程 可 以 使 用 EXECUTE命 令 來執(zhí) 行 ， 其 語 法 形 式 如 下 ：EXECUTE return_status= procedure_name;number|procedure_name_var parameter=value|variableOUTPUT|DEFAULT ,.n WITH RECOMPILE 如 果 執(zhí) 行 存 儲 過 程 的 語 句 是 批 中 的 第 一 個 語 句 ， 可 以 省 略execute關(guān) 鍵 字 。return status為 整 型 局 部 變 量 ， 用 于 保 存 存 儲 過 程 的 返回

23、 值 ;procedure_name指 定 執(zhí) 行 的 存 儲 過 程 的 名稱 ;number用 來 指 定 該 存 儲 過 程 與 其 它 同 名 存 儲 過 程同 組 時 的 標(biāo) 識 號 。parameter:在 創(chuàng) 建 過 程 時 定 義 的 過 程 參 數(shù) 。 調(diào) 用 者 向存 儲 過 程 所 傳 遞 的 參 數(shù) 值 由 value參 數(shù) 或 variable變 量提 供 ， 或 者 使 用 default關(guān) 鍵 字 指 定 使 用 該 參 數(shù) 的 默 認(rèn) 值 。output參 數(shù) 說 明 指 定 參 數(shù) 為 返 回 參 數(shù) 。 注 意 :如 果 按 paramete=value|v

24、ariable的 形 式 為 存 儲過 程 提 供 參 數(shù) ， 可 以 不 考 慮 創(chuàng) 建 存 儲 過 程 時 的 參 數(shù) 順 序 ，但 是 必 須 以 這 種 方 式 提 供 該 存 儲 過 程 的 全 部 參 數(shù) 。 如 果 不以 這 種 方 式 提 供 參 數(shù) ， 則 必 須 按 照 創(chuàng) 建 存 儲 過 程 時 參 數(shù) 的順 序 提 供 參 數(shù) 。With Recompile指 定 在 實 行 存 儲 過 程 時 重 新 編 譯 執(zhí) 行 計劃 。 例 ：Create procedure GetAvgname varchar(10),avg int outputAs DeclareErro

25、rSave intSetErrorSave=0Select avgp=AVG(項 目 )From project as p INNER JOIN pmanager as pmon p.負(fù) 責(zé) 人 ID=Pm.負(fù) 責(zé) 人 ID Where pm.姓 名 =nameif(Error 0) SetErrorSave=ErrorReturnErrorSavego 例 ： 使 用 EXECUTE 命 令 傳 遞 單 個 參 數(shù) ， 它 執(zhí) 行 showind 存儲 過 程 ， 以 titles 為 參 數(shù) 值 。 showind 存 儲 過 程 需 要 參 數(shù) (tabname)， 它 是 一 個 表

26、的 名 稱 。 其 程 序 清 單 如 下 ：EXEC showind titles當(dāng) 然 ， 在 執(zhí) 行 過 程 中 變 量 可 以 顯 式 命 名 ：EXEC showind tabname = titles如 果 這 是 isql 腳 本 或 批 處 理 中 第 一 個 語 句 ， 則 EXEC 語 句可 以 省 略 ： showind titles或 者showind tabname = titles 3 查 看 和 修 改 存 儲 過 程 查 看 存 儲 過 程 存 儲 過 程 被 創(chuàng) 建 之 后 ， 它 的 名 字 就 存 儲 在 系 統(tǒng) 表sysobjects 中 ， 它 的 源

27、 代 碼 存 放 在 系 統(tǒng) 表syscomments中 。 可 以 使 用 使 用 企 業(yè) 管 理 器 或 系 統(tǒng) 存儲 過 程 來 查 看 用 戶 創(chuàng) 建 的 存 儲 過 程 。 （ 1） 使 用 企 業(yè) 管 理 器 查 看 用 戶 創(chuàng) 建 的 存 儲 過 程 在 企 業(yè) 管 理 器 中 ， 打 開 指 定 的 服 務(wù) 器 和 數(shù)據(jù) 庫 項 ， 選 擇 要 創(chuàng) 建 存 儲 過 程 的 數(shù) 據(jù) 庫 ， 單 擊 存儲 過 程 文 件 夾 ， 此 時 在 右 邊 的 頁 框 中 顯 示 該 數(shù) 據(jù)庫 的 所 有 存 儲 過 程 。 用 右 鍵 單 擊 要 查 看 的 存 儲 過程 ， 從 彈 出

28、 的 快 捷 菜 單 中 選 擇 屬 性 選 項 ， 此 時 便可 以 看 到 存 儲 過 程 的 源 代 碼 。 （ 2） 使 用 系 統(tǒng) 存 儲 過 程 來 查 看 用 戶 創(chuàng) 建 的 存 儲 過 程 可 供 使 用 的 系 統(tǒng) 存 儲 過 程 及 其 語 法 形 式 如 下 ：sp_help： 用 于 顯 示 存 儲 過 程 的 參 數(shù) 及 其 數(shù) 據(jù) 類 型sp_help objname= name參 數(shù) name為 要 查 看 的 存 儲 過 程 的 名 稱 。 sp_helptext： 用 于 顯 示 存 儲 過 程 的 源 代 碼 sp_helptext objname= nam

29、e參 數(shù) name為 要 查 看 的 存 儲 過 程 的 名 稱 。sp_depends： 用 于 顯 示 和 存 儲 過 程 相 關(guān) 的 數(shù) 據(jù) 庫 對 象 sp_depends objname=object參 數(shù) object為 要 查 看 依 賴 關(guān) 系 的 存 儲 過 程 的 名 稱 。sp_stored_procedures： 用 于 返 回 當(dāng) 前 數(shù) 據(jù) 庫 中 的 存 儲 過 程 列 表 2. 修 改 存 儲 過 程存 儲 過 程 可 以 根 據(jù) 用 戶 的 要 求 或 者 基 表 定 義 的 改 變 而 改 變 。 使 用ALTER PROCEDURE語 句 可 以 更 改

30、先 前 通 過 執(zhí) 行 CREATE PROCEDURE 語 句 創(chuàng) 建 的 過 程 ， 但 不 會 更 改 權(quán) 限 ， 也 不 影 響 相 關(guān) 的存 儲 過 程 或 觸 發(fā) 器 。 其 語 法 形 式 如 下 ：ALTERPROCEDUREprocedure_name;number parameterdata_type VARYING=defaultOUTPUT,.n WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATION AS sql_statement .n 4 重 命 名 和 刪 除 存 儲 過 程1. 重 命 名 存

31、儲 過 程修 改 存 儲 過 程 的 名 稱 可 以 使 用 系 統(tǒng) 存 儲 過 程sp_rename， 其 語 法 形 式 如 下 ：sp_rename 原 存 儲 過 程 名 稱 ， 新 存 儲 過 程名 稱 另 外 ， 通 過 企 業(yè) 管 理 器 也 可 以 修 改 存 儲 過 程的 名 稱 。 2. 刪 除 存 儲 過 程刪 除 存 儲 過 程 可 以 使 用 DROP命 令 ， DROP命 令可 以 將 一 個 或 者 多 個 存 儲 過 程 或 者 存 儲 過 程 組 從當(dāng) 前 數(shù) 據(jù) 庫 中 刪 除 ， 其 語 法 形 式 如 下 ：drop procedure procedur

32、e ,n當(dāng) 然 ， 利 用 企 業(yè) 管 理 器 也 可 以 很 方 便 地 刪 除 存 儲過 程 。 在 存 儲 過 程 中 使 用 參 數(shù)例 :下 面 的 存 儲 過 程 有 5個 傳 入 參 數(shù) ， 求 其 平 均 值 ， 而 后 將平 均 值 賦 給 output。Create Procedure scoresscore1 smallint,score2 smallint,score3 smallint,score4 smallint,score5 smallint,myAvg smallint OutputAs selectmyAvg=(scarel+scare2+scare3+sca

33、re4+s care5)/5 要 調(diào) 用 myAvg的 值 ， 一 首 先 要 定 義 一 個 變 量 ， 然后 才 能 運 行 該 程 序 。Declare AvgScore smallintExec scores 113,9,8,8,113,AvgScnre OutputSelect The Average Score is:， AvgScnrego 在 將 值 傳 遞 給 存 儲 過 程 時 ， 可 以 按 位 置 順 序 傳 入 (稱 為 按 位 置 傳入 )， 也 可 按 引 用 轉(zhuǎn) 參 數(shù) ， 即 用 參 數(shù) 名 稱 值 的 方 式 傳 入 參 數(shù) 。 當(dāng) 按引 用 傳 入 參 數(shù)

34、 時 ， 可 按 任 何 次 序 傳 入 參 數(shù) 。例 :declare Avgscore smallintExec scoresscore1=10,score3=9,score2=8,score4=8, score5=10, myAvg=AvgScore OutputSelect The Average score is, AvgScoreGo注 意 :如 果 開 始 時 己 經(jīng) 按 引 用 傳 入 ， 則 整 個 過 程 調(diào) 用 必 須 按 引用 傳 入 ， 不 能 在 存 儲 過 程 調(diào) 用 中 在 按 位 置 傳 入 和 按 參 數(shù) 傳 入 二 者之 間 掉 換 。 也 可 用 ret

35、urn關(guān) 鍵 字 來 將 信 息 返 回 給 調(diào) 用 過 程 。這 將 會 直 接 將 一 個 變 量 返 回 給 調(diào) 用 過 程 ， 而 無 需要 求 有 存 儲 過 程 定 義 及 調(diào) 用 過 程 都 要 有 的 output聲 明 。 例 :create proc MyReturnt1 smallint,t2 smallint,retval smallintAs selectretval=t1+t2Return retval創(chuàng) 建 該 過 程 后 ， 可 輸 入 以 下 內(nèi) 容 對 其 調(diào) 用 :DeclaremyReturnValue smallintExec myReturnValu

36、e=myReturn 9,9,0SelectThe return value is， myReturnValue With Recompile可 以 在 Create Procedure語 句 或 Exec procedure語 句 后 增 加 With Recompile語 句 ，其 位 置 將 影 響 存 儲 過 程 的 處 理 和 運 行 方 式 。用 With Recompile創(chuàng) 建 過 程 :在 Create Procedure中 使 用 With Recompile后 ， 執(zhí) 行 劃將 不 被 存 入 Catch.每 次 運 行 時 都 要 重 新 編 譯 整 個過 程 ， 這 氣 標(biāo) 準(zhǔn) 查 詢 的 處 理 方 式 很 相 似 ， 這 種 方式 在 存 儲 過 程 帶 有 使 常 規(guī) 執(zhí) 行 效 率 較 低 的 參 數(shù) 時是 很 有 幫 助 的 ， 通 過 每 次 重 新 編 譯 ， 過 程 可 針 對新 參 數(shù) 進(jìn) 行 優(yōu) 化 執(zhí) 行 。