一句喜、 什么是存儲(chǔ)過(guò)程
系統(tǒng)存儲(chǔ)過(guò)程是系統(tǒng)創(chuàng)建的存儲(chǔ)過(guò)程建瘫，目的在于能夠方便的從系統(tǒng)表中查詢(xún)信息或完成與更新數(shù)據(jù)庫(kù)表相關(guān)的管理任務(wù)或其他的系統(tǒng)管理任務(wù)洁桌。系統(tǒng)存儲(chǔ)過(guò)程主要存儲(chǔ)在master數(shù)據(jù)庫(kù)中媚朦，以“sp”下劃線開(kāi)頭的存儲(chǔ)過(guò)程氧敢。盡管這些系統(tǒng)存儲(chǔ)過(guò)程在master數(shù)據(jù)庫(kù)中，但我們?cè)谄渌麛?shù)據(jù)庫(kù)還是可以調(diào)用系統(tǒng)存儲(chǔ)過(guò)程询张。有一些系統(tǒng)存儲(chǔ)過(guò)程會(huì)在創(chuàng)建新的數(shù)據(jù)庫(kù)的時(shí)候被自動(dòng)創(chuàng)建在當(dāng)前數(shù)據(jù)庫(kù)中福稳。
二、 存儲(chǔ)過(guò)程運(yùn)行流程
存儲(chǔ)過(guò)程是由一些SQL語(yǔ)句和控制語(yǔ)句組成的被封裝起來(lái)的過(guò)程瑞侮，它駐留在數(shù)據(jù)庫(kù)中，可以被客戶應(yīng)用程序調(diào)用鼓拧，也可以從另一個(gè)過(guò)程或觸發(fā)器調(diào)用半火。它的參數(shù)可以被傳遞和返回。與應(yīng)用程序中的函數(shù)過(guò)程類(lèi)似季俩，存儲(chǔ)過(guò)程可以通過(guò)名字來(lái)調(diào)用钮糖，而且它們同樣有輸入?yún)?shù)和輸出參數(shù)。
根據(jù)返回值類(lèi)型的不同酌住，我們可以將存儲(chǔ)過(guò)程分為三類(lèi)：
- 返回記錄集的存儲(chǔ)過(guò)程的執(zhí)行結(jié)果是一個(gè)記錄集店归，典型的例子是從數(shù)據(jù)庫(kù)中檢索出符合某一個(gè)或幾個(gè)條件的記錄；
- 返回?cái)?shù)值的存儲(chǔ)過(guò)程執(zhí)行完以后返回一個(gè)值酪我，例如在數(shù)據(jù)庫(kù)中執(zhí)行一個(gè)有返回值的函數(shù)或命令;
- 行為存儲(chǔ)過(guò)程僅僅是用來(lái)實(shí)現(xiàn)數(shù)據(jù)庫(kù)的某個(gè)功能消痛，而沒(méi)有返回值，例如在數(shù)據(jù)庫(kù)中的更新和刪除操作都哭。
個(gè)人認(rèn)為秩伞，存儲(chǔ)過(guò)程說(shuō)白了就是一堆 SQL 的合并逞带。中間加了點(diǎn)邏輯控制。
但是存儲(chǔ)過(guò)程處理比較復(fù)雜的業(yè)務(wù)時(shí)比較實(shí)用纱新。比如說(shuō)展氓，
一個(gè)復(fù)雜的數(shù)據(jù)操作。如果你在前臺(tái)處理的話脸爱∮龉可能會(huì)涉及到多次數(shù)據(jù)庫(kù)連接。但如果你用存儲(chǔ)過(guò)程的話簿废。就只有一次空入。從響應(yīng)時(shí)間上來(lái)說(shuō)有優(yōu)勢(shì)。
也就是說(shuō)存儲(chǔ)過(guò)程可以給我們帶來(lái)運(yùn)行效率提高的好處捏鱼。
另外执庐，程序容易出現(xiàn) BUG 不穩(wěn)定，而存儲(chǔ)過(guò)程导梆，只要數(shù)據(jù)庫(kù)不出現(xiàn)問(wèn)題轨淌，基本上是不會(huì)出現(xiàn)什么問(wèn)題的。也就是說(shuō)從安全上講看尼，使用了存儲(chǔ)過(guò)程的系統(tǒng)更加穩(wěn)定递鹉。
那么問(wèn)題來(lái)了，什么時(shí)候才可以用存儲(chǔ)藏斩？對(duì)于數(shù)據(jù)量不是很大以及業(yè)務(wù)處理不是很復(fù)雜的小項(xiàng)目就無(wú)需要了么躏结？
答：錯(cuò)。存儲(chǔ)過(guò)程不僅僅適用于大型項(xiàng)目狰域，對(duì)于中小型項(xiàng)目媳拴，使用存儲(chǔ)過(guò)程也是非常有必要的。
其威力和優(yōu)勢(shì)主要體現(xiàn)在：
- 存儲(chǔ)過(guò)程只在創(chuàng)造時(shí)進(jìn)行編譯兆览，以后每次執(zhí)行存儲(chǔ)過(guò)程都不需再重新編譯屈溉，而一般 SQL 語(yǔ)句每執(zhí)行一次就編譯一次,所以使用存儲(chǔ)過(guò)程可提高數(shù)據(jù)庫(kù)執(zhí)行速度。
- 當(dāng)對(duì)數(shù)據(jù)庫(kù)進(jìn)行復(fù)雜操作時(shí)(如對(duì)多個(gè)表進(jìn)行 Update,Insert,Query,Delete 時(shí)）抬探，可將此復(fù)雜操作用存儲(chǔ)過(guò)程封裝起來(lái)與數(shù)據(jù)庫(kù)提供的事務(wù)處理結(jié)合一起使用子巾。這些操作，如果用程序來(lái)完成小压，就變成了一條條的 SQL 語(yǔ)句线梗，可能要多次連接數(shù)據(jù)庫(kù)。而換成存儲(chǔ)怠益，只需要連接一次數(shù)據(jù)庫(kù)就可以了仪搔。
- 存儲(chǔ)過(guò)程可以重復(fù)使用,可減少數(shù)據(jù)庫(kù)開(kāi)發(fā)人員的工作量。
- 安全性高,可設(shè)定只有某此用戶才具有對(duì)指定存儲(chǔ)過(guò)程的使用權(quán)溉痢。
- 減少網(wǎng)絡(luò)通信量僻造。調(diào)用一個(gè)行數(shù)不多的存儲(chǔ)過(guò)程與直接調(diào)用SQL語(yǔ)句的網(wǎng)絡(luò)通信量可能不會(huì)有很大的差別憋他，可是如果存儲(chǔ)過(guò)程包含上百行SQL語(yǔ)句，那么其性能絕對(duì)比一條一條的調(diào)用SQL語(yǔ)句要高得多髓削。
- 執(zhí)行速度更快竹挡。有兩個(gè)原因：首先，在存儲(chǔ)過(guò)程創(chuàng)建的時(shí)候立膛，數(shù)據(jù)庫(kù)已經(jīng)對(duì)其進(jìn)行了一次解析和優(yōu)化揪罕。其次，存儲(chǔ)過(guò)程一旦執(zhí)行宝泵，在內(nèi)存中就會(huì)保留一份這個(gè)存儲(chǔ)過(guò)程好啰，這樣下次再執(zhí)行同樣的存儲(chǔ)過(guò)程時(shí)，可以從內(nèi)存中直接調(diào)用儿奶。
- 更強(qiáng)的適應(yīng)性：由于存儲(chǔ)過(guò)程對(duì)數(shù)據(jù)庫(kù)的訪問(wèn)是通過(guò)存儲(chǔ)過(guò)程來(lái)進(jìn)行的框往，因此數(shù)據(jù)庫(kù)開(kāi)發(fā)人員可以在不改動(dòng)存儲(chǔ)過(guò)程接口的情況下對(duì)數(shù)據(jù)庫(kù)進(jìn)行任何改動(dòng)，而這些改動(dòng)不會(huì)對(duì)應(yīng)用程序造成影響闯捎。
- 布式工作：應(yīng)用程序和數(shù)據(jù)庫(kù)的編碼工作可以分別獨(dú)立進(jìn)行椰弊，而不會(huì)相互壓制。
存儲(chǔ)過(guò)程的使用瓤鼻，好像一直是一個(gè)爭(zhēng)論秉版。
我不傾向于盡可能使用存儲(chǔ)過(guò)程，是這么認(rèn)為的：
- 運(yùn)行速度： 大多數(shù)高級(jí)的數(shù)據(jù)庫(kù)系統(tǒng)都有statement cache的茬祷，所以編譯sql的花費(fèi)沒(méi)什么影響清焕。但是執(zhí)行存儲(chǔ)過(guò)程要比直接執(zhí)行sql花費(fèi)更多（檢查權(quán)限等），所以對(duì)于很簡(jiǎn)單的sql祭犯，存儲(chǔ)過(guò)程沒(méi)有什么優(yōu)勢(shì)秸妥。
- 網(wǎng)絡(luò)負(fù)荷：如果在存儲(chǔ)過(guò)程中沒(méi)有多次數(shù)據(jù)交互，那么實(shí)際上網(wǎng)絡(luò)傳輸量和直接sql是一樣的沃粗。
- 團(tuán)隊(duì)開(kāi)發(fā)：很遺憾筛峭，比起成熟的IDE，沒(méi)有什么很好存儲(chǔ)過(guò)程的IDE工具來(lái)支持陪每，也就是說(shuō)，這些必須手工完成镰吵。
- 安全機(jī)制：對(duì)于傳統(tǒng)的C/S結(jié)構(gòu)檩禾，連接數(shù)據(jù)庫(kù)的用戶可以不同，所以安全機(jī)制有用疤祭；但是在web的三層架構(gòu)中盼产，數(shù)據(jù)庫(kù)用戶不是給用戶用的，所以基本上勺馆，只有一個(gè)用戶戏售，擁有所有權(quán)限（最多還有一個(gè)開(kāi)發(fā)用戶）侨核。這個(gè)時(shí)候，安全機(jī)制有點(diǎn)多余灌灾。
- 用戶滿意：實(shí)際上這個(gè)只是要將訪問(wèn)數(shù)據(jù)庫(kù)的接口統(tǒng)一搓译，是用存儲(chǔ)過(guò)程，還是EJB锋喜，沒(méi)太大關(guān)系些己，也就是說(shuō)，在三層結(jié)構(gòu)中嘿般，單獨(dú)設(shè)計(jì)出一個(gè)數(shù)據(jù)訪問(wèn)層段标，同樣能實(shí)現(xiàn)這個(gè)目標(biāo)。
- 開(kāi)發(fā)調(diào)試：一樣由于IDE的問(wèn)題炉奴，存儲(chǔ)過(guò)程的開(kāi)發(fā)調(diào)試要比一般程序困難（老版本DB2還只能用C寫(xiě)存儲(chǔ)過(guò)程逼庞，更是一個(gè)災(zāi)難）。
- 移植性：算了瞻赶，這個(gè)不用提赛糟，反正一般的應(yīng)用總是綁定某個(gè)數(shù)據(jù)庫(kù)的，不然就無(wú)法靠?jī)?yōu)化數(shù)據(jù)庫(kù)訪問(wèn)來(lái)提高性能了共耍。
- 維護(hù)性：的確虑灰，存儲(chǔ)過(guò)程有些時(shí)候比程序容易維護(hù)，這是因?yàn)榭梢詫?shí)時(shí)更新DB端的存儲(chǔ)過(guò)程痹兜，但是在3層結(jié)構(gòu)下穆咐，更新server端的數(shù)據(jù)訪問(wèn)層一樣能實(shí)現(xiàn)這個(gè)目標(biāo)，可惜現(xiàn)在很多平臺(tái)不支持實(shí)時(shí)更新而已字旭。
三对湃、存儲(chǔ)過(guò)程示例
1.常用系統(tǒng)存儲(chǔ)過(guò)程有
xec sp_databases; --查看數(shù)據(jù)庫(kù)
exec sp_tables; --查看表
exec sp_columns student;--查看列
exec sp_helpIndex student;--查看索引
exec sp_helpConstraint student;--約束
exec sp_stored_procedures;
exec sp_helptext 'sp_stored_procedures';--查看存儲(chǔ)過(guò)程創(chuàng)建、定義語(yǔ)句
exec sp_rename student, stuInfo;--修改表遗淳、索引拍柒、列的名稱(chēng)
exec sp_renamedb myTempDB, myDB;--更改數(shù)據(jù)庫(kù)名稱(chēng)
exec sp_defaultdb 'master', 'myDB';--更改登錄名的默認(rèn)數(shù)據(jù)庫(kù)
exec sp_helpdb;--數(shù)據(jù)庫(kù)幫助，查詢(xún)數(shù)據(jù)庫(kù)信息
exec sp_helpdb master;
系統(tǒng)存儲(chǔ)過(guò)程示例:
--表重命名
exec sp_rename 'stu', 'stud';
select * from stud;
--列重命名
exec sp_rename 'stud.name', 'sName', 'column';
exec sp_help 'stud';
--重命名索引
exec sp_rename N'student.idx_cid', N'idx_cidd', N'index';
exec sp_help 'student';
--查詢(xún)所有存儲(chǔ)過(guò)程
select * from sys.objects where type = 'P';
select * from sys.objects where type_desc like '%pro%' and name like 'sp%';
2.用戶自定義存儲(chǔ)過(guò)程
create proc | procedure pro_name
[{@參數(shù)數(shù)據(jù)類(lèi)型} [=默認(rèn)值] [output],
{@參數(shù)數(shù)據(jù)類(lèi)型} [=默認(rèn)值] [output],
....
]
as
SQL_statements
3.創(chuàng)建不帶參數(shù)存儲(chǔ)過(guò)程
--創(chuàng)建存儲(chǔ)過(guò)程
if (exists (select * from sys.objects where name = 'proc_get_student'))
drop proc proc_get_student
go
create proc proc_get_student
as
select * from student;
--調(diào)用屈暗、執(zhí)行存儲(chǔ)過(guò)程
exec proc_get_student;
4.修改存儲(chǔ)過(guò)程
--修改存儲(chǔ)過(guò)程
alter proc proc_get_student
as
select * from student;
5.帶參存儲(chǔ)過(guò)程
-帶參存儲(chǔ)過(guò)程
if (object_id('proc_find_stu', 'P') is not null)
drop proc proc_find_stu
go
create proc proc_find_stu(@startId int, @endId int)
as
select * from student where id between @startId and @endId
go
--調(diào)用拆讯、執(zhí)行存儲(chǔ)過(guò)程
exec proc_find_stu 2, 4;
6.帶通配符參數(shù)存儲(chǔ)過(guò)程
通配符，在參數(shù)值賦值時(shí)养叛，加上相應(yīng)的通配符
-帶通配符參數(shù)存儲(chǔ)過(guò)程
if (object_id('proc_findStudentByName', 'P') is not null)
drop proc proc_findStudentByName
go
create proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')--默認(rèn)值
as
select * from student where name like @name and name like @nextName;
go
--調(diào)用种呐、執(zhí)行存儲(chǔ)過(guò)程
exec proc_findStudentByName;
exec proc_findStudentByName '%o%', 't%';
7.帶輸出參數(shù)存儲(chǔ)過(guò)程
if (object_id('proc_getStudentRecord', 'P') is not null)
drop proc proc_getStudentRecord
go
create proc proc_getStudentRecord(
@id int, --默認(rèn)輸入?yún)?shù)
@name varchar(20) out, --輸出參數(shù)
@age varchar(20) output--輸入輸出參數(shù)
)
as
select @name = name, @age = age from student where id = @id and sex = @age;
go
--調(diào)用、執(zhí)行存儲(chǔ)過(guò)程
declare @id int,
@name varchar(20),
@temp varchar(20);
set @id = 7;
set @temp = 1;
exec proc_getStudentRecord @id, @name out, @temp output;
select @name, @temp;
print @name + '#' + @temp;
8.不緩存存儲(chǔ)過(guò)程
存儲(chǔ)過(guò)程弃甥，每次執(zhí)行都進(jìn)行重新編譯
--WITH RECOMPILE 不緩存
if (object_id('proc_temp', 'P') is not null)
drop proc proc_temp
go
create proc proc_temp
with recompile
as
select * from student;
go
--調(diào)用爽室、執(zhí)行存儲(chǔ)過(guò)程
exec proc_temp;
9.加密存儲(chǔ)過(guò)程
加密后，不能查看和修改源腳本
--加密WITH ENCRYPTION
if (object_id('proc_temp_encryption', 'P') is not null)
drop proc proc_temp_encryption
go
create proc proc_temp_encryption
with encryption --加密
as
select * from student;
go
--調(diào)用淆攻、執(zhí)行存儲(chǔ)過(guò)程
exec proc_temp_encryption;
exec sp_helptext 'proc_temp';
exec sp_helptext 'proc_temp_encryption';
效果阔墩，無(wú)法查看腳本或者導(dǎo)出創(chuàng)建腳本
圖不對(duì)應(yīng)上面存儲(chǔ)過(guò)程名稱(chēng)嘿架，但效果一樣，有一個(gè)小鎖頭
10.帶游標(biāo)參數(shù)存儲(chǔ)過(guò)程
if (object_id('proc_cursor', 'P') is not null)
drop proc proc_cursor
go
create proc proc_cursor
@cur cursor varying output
as
set @cur = cursor forward_only static for
select id, name, age from student;
open @cur;
go
--調(diào)用
declare @exec_cur cursor;
declare @id int,
@name varchar(20),
@age int;
exec proc_cursor @cur = @exec_cur output;--調(diào)用存儲(chǔ)過(guò)程
fetch next from @exec_cur into @id, @name, @age;
while (@@fetch_status = 0)
begin
fetch next from @exec_cur into @id, @name, @age;
print 'id: ' + convert(varchar, @id) + ', name: ' + @name + ', age: ' + convert(char, @age);
end
close @exec_cur;
deallocate @exec_cur;--刪除游標(biāo)
11.分頁(yè)存儲(chǔ)過(guò)程
---存儲(chǔ)過(guò)程啸箫、row_number完成分頁(yè)
if (object_id('pro_page', 'P') is not null)
drop proc pro_page
go
create proc pro_page
@startIndex int,
@endIndex int
as
select count(*) from product
;
select * from (
select row_number() over(order by pid) as rowId, * from product
) temp
where temp.rowId between @startIndex and @endIndex
go
--drop proc pro_page
exec pro_page 1, 4
--
--分頁(yè)存儲(chǔ)過(guò)程
if (object_id('pro_stu', 'P') is not null)
drop proc pro_stu
go
create procedure pro_stu(
@pageIndex int,
@pageSize int
)
as
declare @startRow int, @endRow int
set @startRow = (@pageIndex - 1) * @pageSize +1
set @endRow = @startRow + @pageSize -1
select * from (
select *, row_number() over (order by id asc) as number from student
) t
where t.number between @startRow and @endRow;
exec pro_stu 2, 2;
