深刻剖析SQL Server 存儲進程。本站提示廣大學習愛好者：（深刻剖析SQL Server 存儲進程）文章只能為提供參考，不一定能成為您想要的結果。以下是深刻剖析SQL Server 存儲進程正文
Transact-SQL中的存儲進程，異常相似於Java說話中的辦法，它可以反復挪用。當存儲進程履行一次後，可以將語句緩存中，如許下次履行的時刻直接應用緩存中的語句。如許便可以進步存儲進程的機能。
Ø 存儲進程的概念
存儲進程Procedure是一組為了完成特定功效的SQL語句聚集，經編譯後存儲在數據庫中，用戶經由過程指定存儲進程的稱號並給出參數來履行。
存儲進程中可以包括邏輯掌握語句和數據把持語句，它可以接收參數、輸入參數、前往單個或多個成果集和前往值。
因為存儲進程在創立時即在數據庫辦事器長進行了編譯並存儲在數據庫中，所以存儲進程運轉要比單個的SQL語句塊要快。同時因為在挪用時只需用供給存儲進程名和需要的參數信息，所以在必定水平上也能夠削減收集流量、簡略收集累贅。
1、 存儲進程的長處
A、 存儲進程許可尺度組件式編程
存儲進程創立後可以在法式中被屢次挪用履行，而不用從新編寫該存儲進程的SQL語句。並且數據庫專業人員可以隨時對存儲進程停止修正，但對運用法式源代碼卻毫無影響，從而極年夜的進步了法式的可移植性。
B、 存儲進程可以或許完成較快的履行速度
假如某一操作包括年夜量的T-SQL語句代碼，分離被屢次履行，那末存儲進程要比批處置的履行速度快很多。由於存儲進程是預編譯的，在初次運轉一個存儲進程時，查詢優化器對其停止剖析、優化，並給出終究被存在體系表中的存儲籌劃。而批處置的T-SQL語句每次運轉都須要預編譯和優化，所以速度就要慢一些。
C、 存儲進程加重收集流量
關於統一個針對數據庫對象的操作，假如這一操作所觸及到的T-SQL語句被組織成一存儲進程，那末當在客戶機上挪用該存儲進程時，收集中傳遞的只是該挪用語句，不然將會是多條SQL語句。從而加重了收集流量，下降了收集負載。
D、 存儲進程可被作為一種平安機制來充足應用
體系治理員可以對履行的某一個存儲進程停止權限限制，從而可以或許完成對某些數據拜訪的限制，防止非受權用戶對數據的拜訪，包管數據的平安。
Ø 體系存儲進程
體系存儲進程是體系創立的存儲進程，目標在於可以或許便利的從體系表中查詢信息或完成與更新數據庫表相干的治理義務或其他的體系治理義務。體系存儲進程重要存儲在master數據庫中，以“sp”下劃線開首的存儲進程。雖然這些體系存儲進程在master數據庫中，但我們在其他數據庫照樣可以挪用體系存儲進程。有一些體系存儲進程會在創立新的數據庫的時刻被主動創立在以後數據庫中。
經常使用體系存儲進程有：
exec sp_databases; --檢查數據庫
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';--檢查存儲進程創立、界說語句
exec sp_rename student, stuInfo;--修正表、索引、列的稱號
exec sp_renamedb myTempDB, myDB;--更改數據庫稱號
exec sp_defaultdb 'master', 'myDB';--更改登錄名的默許數據庫
exec sp_helpdb;--數據庫贊助，查詢數據庫信息
exec sp_helpdb master;
體系存儲進程示例：
--表重定名
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';
--查詢一切存儲進程
select * from sys.objects where type = 'P'; select * from sys.objects where type_desc like '%pro%' and name like 'sp%';
Ø 用戶自界說存儲進程
1、 創立語法
create proc | procedure pro_name
[{@參數數據類型} [=默許值] [output],
{@參數數據類型} [=默許值] [output],
....
]
as
SQL_statements
2、 創立不帶參數存儲進程
--創立存儲進程
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;
--挪用、履行存儲進程
exec proc_get_student;
3、 修正存儲進程
--修正存儲進程
alter proc proc_get_student as select * from student;
4、 帶參存儲進程
--帶參存儲進程
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
exec proc_find_stu 2, 4;
5、 帶通配符參數存儲進程
--帶通配符參數存儲進程
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) = '%')
as
select * from student where name like @name and name like @nextName;
go
exec proc_findStudentByName;
exec proc_findStudentByName '%o%', 't%';
6、 帶輸入參數存儲進程
if (object_id('proc_getStudentRecord', 'P') is not null)
drop proc proc_getStudentRecord
go
create proc proc_getStudentRecord(
@id int, --默許輸出參數
@name varchar(20) out, --輸入參數
@age varchar(20) output--輸出輸入參數
)
as
select @name = name, @age = age from student where id = @id and sex = @age;
go
--
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;
7、 不緩存存儲進程
--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
exec proc_temp;
8、 加密存儲進程
--加密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
exec proc_temp_encryption;
exec sp_helptext 'proc_temp';
exec sp_helptext 'proc_temp_encryption';
9、 帶游標參數存儲進程
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
--挪用
declare @exec_cur cursor;
declare @id int,
@name varchar(20),
@age int;
exec proc_cursor @cur = @exec_cur output;--挪用存儲進程
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;--刪除游標
10、 分頁存儲進程
---存儲進程、row_number完成份頁
if (object_id('pro_page', 'P') is not null)
drop proc proc_cursor
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
--
--分頁存儲進程
if (object_id('pro_page', '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;
Ø Raiserror
Raiserror前往用戶界說的毛病信息，可以指定嚴重級別，設置體系變量記載所產生的毛病。
語法以下：
Raiserror({msg_id | msg_str | @local_variable}
{, severity, state}
[,argument[,…n]]
[with option[,…n]]
)
# msg_id:在sysmessages體系表中指定的用戶界說毛病信息
# msg_str:用戶界說的信息，信息最年夜長度在2047個字符。
# severity：用戶界說與該新聞聯系關系的嚴重級別。當應用msg_id激發應用sp_addmessage創立的用戶界說新聞時，raiserror上指定嚴重性將籠罩sp_addmessage中界說的嚴重性。
任何用戶可以指定0-18直接的嚴重級別。只要sysadmin固定辦事器腳色經常使用或具有alter trace權限的用戶能力指定19-25直接的嚴重級別。19-25之間的平安級別須要應用with log選項。
# state：介於1至127直接的任何整數。State默許值是1。
raiserror('is error', 16, 1);
select * from sys.messages;
--應用sysmessages中界說的新聞
raiserror(33003, 16, 1);
raiserror(33006, 16, 1);
以上所述就是本文的全體內容了，願望年夜家可以或許愛好。
