SQL2000存儲過程的基礎(chǔ) 收藏
存儲過程的概念
SQL Server提供了一種方法，它可以將一些固定的操作集中起來由SQL Server數(shù)據(jù)庫服務(wù)器來完成，以實現(xiàn)某個任務(wù)，這種方法就是存儲過程。
存儲過程是SQL語句和可選控制流語句的預(yù)編譯集合，存儲在數(shù)據(jù)庫中，可由應(yīng)用程序通過一個調(diào)用執(zhí)行，而且允許用戶聲明變量、有條件執(zhí)行以及其他強大的編程功能。
在SQL Server中存儲過程分為兩類：即系統(tǒng)提供的存儲過程和用戶自定義的存儲過程。
可以出于任何使用SQL語句的目的來使用存儲過程，它具有以下優(yōu)點：
可以在單個存儲過程中執(zhí)行一系列SQL語句。
可以從自己的存儲過程內(nèi)引用其他存儲過程，這可以簡化一系列復(fù)雜語句。
存儲過程在創(chuàng)建時即在服務(wù)器上進(jìn)行編譯，所以執(zhí)行起來比單個SQL語句快，而且減少網(wǎng)絡(luò)通信的負(fù)擔(dān)。
安全性更高。
創(chuàng)建存儲過程
在SQL Server中，可以使用三種方法創(chuàng)建存儲過程 ：
①使用創(chuàng)建存儲過程向?qū)?chuàng)建存儲過程。
②利用SQL Server 企業(yè)管理器創(chuàng)建存儲過程。
③使用Transact-SQL語句中的CREATE PROCEDURE命令創(chuàng)建存儲過程。
下面介紹使用Transact-SQL語句中的CREATE PROCEDURE命令創(chuàng)建存儲過程
創(chuàng)建存儲過程前，應(yīng)該考慮下列幾個事項：
①不能將 CREATE PROCEDURE 語句與其它 SQL 語句組合到單個批處理中。
②存儲過程可以嵌套使用，嵌套的最大深度不能超過32層。
③創(chuàng)建存儲過程的權(quán)限默認(rèn)屬于數(shù)據(jù)庫所有者，該所有者可將此權(quán)限授予其他用戶。
④存儲過程是數(shù)據(jù)庫對象，其名稱必須遵守標(biāo)識符規(guī)則。
⑤只能在當(dāng)前數(shù)據(jù)庫中創(chuàng)建存儲過程。
⑥ 一個存儲過程的最大尺寸為128M。
使用CREATE PROCEDURE創(chuàng)建存儲過程的語法形式如下：
QUOTE:
CREATE PROC[EDURE]procedure_name[;number][;number]
[{@parameter data_type}
[VARYING][=default][OUTPUT]
][,...n] WITH
{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
[FOR REPLICATION]
AS sql_statement [ ...n ]
用CREATE PROCEDURE創(chuàng)建存儲過程的語法參數(shù)的意義如下：
procedure_name：用于指定要創(chuàng)建的存儲過程的名稱。
number：該參數(shù)是可選的整數(shù)，它用來對同名的存儲過程分組，以便用一條 DROP PROCEDURE 語句即可將同組的過程一起除去。
@parameter：過程中的參數(shù)。在 CREATE PROCEDURE 語句中可以聲明一個或多個參數(shù)。
data_type：用于指定參數(shù)的數(shù)據(jù)類型。
VARYING：用于指定作為輸出OUTPUT參數(shù)支持的結(jié)果集。
Default：用于指定參數(shù)的默認(rèn)值。
OUTPUT：表明該參數(shù)是一個返回參數(shù)。
例如：下面創(chuàng)建一個 簡單的存儲過程productinfo，用于檢索產(chǎn)品信息。
USE Northwind
if exists(select name from sysobjects
where name='productinfo' and type = 'p')
drop procedure productinfo
GO
create procedure productinfo
as
select * from products
GO
通過下述sql語句執(zhí)行該存儲過程：execute productinfo
即可檢索到產(chǎn)品信息。
執(zhí)行存儲過程
直接執(zhí)行存儲過程可以使用EXECUTE命令來執(zhí)行，其語法形式如下：
[[EXEC[UTE]]
{ [@return_status=]
{procedure_name[;number]|@procedure_name_var} [[@parameter=]{value|@variable[OUTPUT]|[DEFAULT]}
[,...n]
[ WITH RECOMPILE ]
使用 EXECUTE 命令傳遞單個參數(shù)，它執(zhí)行 showind 存儲過程，以 titles 為參數(shù)值。showind 存儲過程需要參數(shù) (@tabname)，它是一個表的名稱。其程序清單如下：
EXEC showind titles
當(dāng)然，在執(zhí)行過程中變量可以顯式命名：
EXEC showind @tabname = titles
如果這是 isql 腳本或批處理中第一個語句，則 EXEC 語句可以省略：
showind titles或者showind @tabname = titles
下面的例子使用了默認(rèn)參數(shù)
USE Northwind
GO
CREATE PROCEDURE insert_Products_1
( @SupplierID_2 int,
@CategoryID_3 int,
@ProductName_1 nvarchar(40)='無')
AS INSERT INTO Products
(ProductName,SupplierID,CategoryID)
VALUES
(@ProductName_1,@SupplierID_2,@CategoryID_3)
GO
exec insert_Products_1 1,1
Select * from Products where SupplierID=1 and CategoryID=1
GO
下面的例子使用了返回參數(shù)
USE Northwind
GO
CREATE PROCEDURE query_products
( @SupplierID_1 int,
@ProductName_2 nvarchar(40) output)
AS
select @ProductName_2 = ProductName from products
where SupplierID = @SupplierID_1
執(zhí)行該存儲過程來查詢SupplierID為1的產(chǎn)品名：
declare @product nvarchar(40)
exec query_products 1,@product output
select '產(chǎn)品名'= @product
go
查看存儲過程
存儲過程被創(chuàng)建之后，它的名字就存儲在系統(tǒng)表sysobjects中，它的源代碼存放在系統(tǒng)表syscomments中?？梢允褂檬褂闷髽I(yè)管理器或系統(tǒng)存儲過程來查看用戶創(chuàng)建的存儲過程。
使用企業(yè)管理器查看用戶創(chuàng)建的存儲過程
在企業(yè)管理器中，打開指定的服務(wù)器和數(shù)據(jù)庫項，選擇要創(chuàng)建存儲過程的數(shù)據(jù)庫，單擊存儲過程文件夾，此時在右邊的頁框中顯示該數(shù)據(jù)庫的所有存儲過程。用右鍵單擊要查看的存儲過程，從彈出的快捷菜單中選擇屬性選項，此時便可以看到存儲過程的源代碼。
使用系統(tǒng)存儲過程來查看用戶創(chuàng)建的存儲過程
可供使用的系統(tǒng)存儲過程及其語法形式如下：
sp_help：用于顯示存儲過程的參數(shù)及其數(shù)據(jù)類型
sp_help [[@objname=] name]
參數(shù)name為要查看的存儲過程的名稱。
sp_helptext：用于顯示存儲過程的源代碼
sp_helptext [[@objname=] name]
參數(shù)name為要查看的存儲過程的名稱。
sp_depends：用于顯示和存儲過程相關(guān)的數(shù)據(jù)庫對象
sp_depends [@objname=]’object’
參數(shù)object為要查看依賴關(guān)系的存儲過程的名稱。
sp_stored_procedures：用于返回當(dāng)前數(shù)據(jù)庫中的存儲過程列表
修改存儲過程
存儲過程可以根據(jù)用戶的要求或者基表定義的改變而改變。使用ALTER PROCEDURE語句可以更改先前通過執(zhí)行 CREATE PROCEDURE 語句創(chuàng)建的過程，但不會更改權(quán)限，也不影響相關(guān)的存儲過程或觸發(fā)器。其語法形式如下：
ALTERPROC[EDURE]procedure_name[;number]
[{@parameterdata_type}
[VARYING][=default][OUTPUT]][,...n] [WITH
{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]
[FOR REPLICATION]
AS
sql_statement [ ...n ]
重命名和刪除存儲過程
1. 重命名存儲過程
修改存儲過程的名稱可以使用系統(tǒng)存儲過程sp_rename，其語法形式如下：
sp_rename 原存儲過程名稱，新存儲過程名稱
另外，通過企業(yè)管理器也可以修改存儲過程的名稱。
刪除存儲過程
刪除存儲過程可以使用DROP命令，DROP命令可以將一個或者多個存儲過程或者存儲過程組從當(dāng)前數(shù)據(jù)庫中刪除，其語法形式如下：
drop procedure {procedure} [,…n]
當(dāng)然，利用企業(yè)管理器也可以很方便地刪除存儲過程。
存儲過程的重新編譯
在我們使用了一次存儲過程后，可能會因為某些原因，必須向表中新增加數(shù)據(jù)列或者為表新添加索引，從而改變了數(shù)據(jù)庫的邏輯結(jié)構(gòu)。這時，需要對存儲過程進(jìn)行重新編譯，SQL Server提供三種重新編譯存儲過程的方法 ：
1、在建立存儲過程時設(shè)定重新編譯
語法格式：CREATE PROCEDURE procedure_name WITH RECOMPILE AS sql_statement
2、在執(zhí)行存儲過程時設(shè)定重編譯
語法格式： EXECUTE procedure_name WITH RECOMPILE
3、通過使用系統(tǒng)存儲過程設(shè)定重編譯
語法格式為： EXEC sp_recompile OBJECT
系統(tǒng)存儲過程與擴展存儲過程
1.系統(tǒng)存儲過程
系統(tǒng)存儲過程存儲在master數(shù)據(jù)庫中，并以sp_為前綴，主要用來從系統(tǒng)表中獲取信息，為系統(tǒng)管理員管理SQL Server提供幫助，為用戶查看數(shù)據(jù)庫對象提供方便。比如用來查看數(shù)據(jù)庫對象信息的系統(tǒng)存儲過程sp_help、顯示存儲過程和其它對象的文本的存儲過程sp_helptext等。
2.擴展存儲過程：
擴展存儲過程以xp_為前綴，它是關(guān)系數(shù)據(jù)庫引擎的開放式數(shù)據(jù)服務(wù)層的一部分，其可以使用戶在動態(tài)鏈接庫(DLL)文件所包含的函數(shù)中實現(xiàn)邏輯，從而擴展了Transact-SQL的功能，并且可以象調(diào)用Transact-SQL過程那樣從Transact-SQL語句調(diào)用這些函數(shù)。
例: 利用擴展存儲過程xp_cmdshell為一個操作系統(tǒng)外殼執(zhí)行指定命令串，并作為文本返回任何輸出。
執(zhí)行代碼：
use master
exec xp_cmdshell 'dir *.exe'
執(zhí)行結(jié)果返回系統(tǒng)目錄下的文件內(nèi)容文本信息。
最后給大家舉一個例子：
QUOTE:
IF exists (select * from SysObjects where name='more_than_total' and type='p')
drop procedure more_than_total
go
CREATE PROCEDURE More_Than_Total
@total money = 0
AS
Declare @amount smallint
BEGIN
select distinct
P.productName,
S.contactName,
P.UnitPrice
from Products P inner join [order Details] O
on p.productID=o.productID inner join suppliers s
on p.supplierID=s.SupplierID
where O.productID in
(select productID
from [order Details]
group by productId
having sum(quantity*unitprice)>@total
)
發(fā)表于 @ 2009年10月09日 14:41:00 | 評論( 0 ) | 編輯| 舉報| 收藏
舊一篇:SQL2005中的事務(wù)與鎖定（二） | 新一篇:SQL2008不允許保存更改的表結(jié)構(gòu)問題解決方法
相關(guān)文章
sql server存儲過程基礎(chǔ)存儲過程-CREATE PROCEDURE記錄-存儲過程基礎(chǔ)（一）語法SQL SERVER 存儲過程學(xué)習(xí)筆記SQL Server存儲過程SQL Server2000存儲過程基礎(chǔ)存儲過程(二) 存儲過程語法存儲過程（三）存儲過程的修改與刪除發(fā)表評論 “評論王爭奪賽”第3期活動開始啦！ 表 情： 評論內(nèi)容： 用 戶 名： 登錄 注冊 匿名評論 qaz20472049驗 證 碼： 重新獲得驗證碼 Copyright © xiaowei_001
Powered by CSDN Blog
