第十八章

編寫基本Stored Procedures

•Stored Procedure

•Stored Procedure是SQL語句和控制流語句命名集合

•Stored Procedure的好處

–簡化重複的任務

–運行速度更快比相同的陳述交互輸入

–減少網絡流量

–他們進入資料庫之前可以捕捉用戶的錯誤

–建立執行的一致性

–促進模組化應用程序開發

–幫助提供安全性

–可以執行複雜的應用rules和defaults •Stored Procedures的類型

•用戶定義的Stored Procedures

–用戶定義程序必須明確要求

•觸發

–用戶定義程序，自動執行一個給定表當數據被修改

•系統程序

–Sybase提供程序，讀取或修改一個或多個系統表

•擴展Stored Procedures

–Sybase提供程序，進行呼叫外部操作系統•交互執行

•當批次陳述執行交互，會發生下列動作

–創建一個查詢樹。這是一個批次的分析版本，並包含額外在批次處理的內部信息批次和引用的對象

–查詢樹被轉換成一個查詢計劃。查詢計劃是排列來進行批次所需步驟，包括這些信息為使用哪個索引，加入執行首次，等等

•查詢計劃位於被稱為過程緩存的記憶體部分

•創建Stored Procedure

•當創建Stored Procedure一個，出現以下行動

–原來的Transact-SQL語句存儲在syscomments中的系統表

–解析Stored Procedure和資料庫物件的引用得以解決

–查詢樹的Stored Procedure創建並存儲在另一個系統表中，sysprocedures

•Stored Procedure的執行

•執行Stored Procedure時，會發生下列動作

–Adaptive Server將檢查過程緩存以查看是否進一步是目前尚未被使用的查詢計劃。如果有，Adaptive Server使用的Stored Procedure的查詢計劃

–如果沒有查詢計劃的過程高速緩存中的Stored Procedure（或如果有一個計劃，但它正由另一個用戶使用），然後Adaptive Server的查詢樹存儲在sysprocedures，並創建另一個查詢計劃

•性能方面的好處

•更迅速地執行批次因為編譯的存儲程序

–預存程序已經被解析

–在存儲過程中物件引用已經解決

–Adaptive Server並不需要建立一個查詢樹它可以使用一個在sysprocedures

–如果在過程緩存中存在的程序未使用查詢計劃時，Adaptive Server並不需要創建一個查詢計劃

•創建、刪除和執行Stored Procedure

•創建語法

create proc procedure_name

as

statements

return

•刪除語法

drop proc procedure_name

•執行語法

[ exec | execute ] procedure_name

•變數

•Stored Procedure可以創建和使用本地變數

–變數只存在於Stored Procedure的持續時間

–變數不能用於其他進程

•有效和無效的聲明

•Stored Procedure可以

–選取和修改數據

–創建臨時和永久表

–調用其他Stored Procedure

–在其自己的數據庫和其他數據庫中的參考物件

•Stored Procedure不能執行這些陳述

–use database

–create view

–create default

–create rule

–create procedure

–create trgger

•Stored Procedures 和權限

•為了讓其他人使用你的stored procedure，你必須給予他們的權限

•語法

grant execute

on procedure_name

to user_list

•對於Stored Procedures的系統過程

•sp_depends { table_name | procedure_name }

–當給定一個表，列出了參考該表在同一數據庫中的所有對象（包括程序）

–當給定一個過程，列出了在同一個數據庫的過程中引用的所有的資料表

•sp_help procedure_name

–顯示有關指定的程序的信息

•sp_helptext procedure_name

–顯示用於創建指定的程序文本

•sp_rename old_proc_name, new_proc_name

–改變一個程序的名稱

•輸入參數

•輸入參數是一個局部變數來存儲過程，可以接收來自EXEC procedure語句的值

•創建輸入參數

create prcedure procedure_name

( [ parameter_name datatype [ = default_value ]

, parameter_name datatype [ = default_value ] ] )

as

statements

return

•參數傳遞

傳遞值的參數的兩種方法

–經由參數的位置

•語法

•[ exec | execute ] procedure_name value [, value… ]

•參數必須傳遞按照它們在CREATE PROCEDURE語句中出現的順序相同

•這種方法更容易出錯，傳遞按名稱是推薦的方法

–經由參數名稱

•語法

[ exec | execute ] procedure_name parameter_name = value [ , parameter_name = value ]

•exec語句中的參數名稱必須符合在CREATE PROCEDURE語句中的參數名稱

•可以在任何順序傳遞參數

•預設值

•默認值的值是指，指派一個參數且該參數沒有值，讓exec語句接收

•輸入參數：常見錯誤

•傳遞的參數是參數的數據類型不相容

•在同一聲明中，參數是以傳遞位置參數之後已傳遞按名稱

–雖然不建議這樣做，它有可能通過兩種方法混合，然而，之後一個參數傳遞的名字，隨後在該聲明中的所有參數必須由名稱傳遞

•一個或多個參數缺少

–缺少的參數，可以通過輸入參數使用默認值，克服

•參數傳遞的位置順序錯誤

•返回參數

•返回參數是局部變量的存儲過程，可以發送一個值EXEC procedure語句

•創建語法

create procedure procedure_name

( parameter_name datatype output

[ , parameter_name datatype output… ] )

as

statements

return

•使用語法

[ exec | execute ] procedure_name variable output

•返回參數自動返回結果集

•返回參數可以傳遞名稱或位置

–建議的名字傳遞