SqlServer儲存過程的建立與使用

學習中的苦與樂發表於2021-03-30
SQLServer儲存過程

什麼是儲存過程？

T-SQL中的儲存過程，非常類似於net語言中的方法，它可以重複呼叫。當儲存過程執行一次後，可以將語句快取中，這樣下次執行的時候直接使用快取中的語句。

這樣就可以提高儲存過程的效能。

  1.  儲存過程Procedure是一組為了完成特定功能的SQL語句集合，經編譯後儲存在資料庫中，使用者通過指定儲存過程的名稱並給出引數來執行。
  2.  儲存過程中可以包含邏輯控制語句和資料操縱語句，它可以接受引數、輸出引數、返回單個或多個結果集以及返回值。
  3.  由於儲存過程在建立時即在資料庫伺服器上進行了編譯並儲存在資料庫中，所以儲存過程執行要比單個的SQL語句塊要快。
  4.  同時由於在呼叫時只需用提供儲存過程名和必要的引數資訊，所以在一定程度上也可以減少網路流量、簡單網路負擔。

 

 

 

儲存過程的優點

1、儲存過程允許標準元件式程式設計

儲存過程建立後可以在程式中被多次呼叫執行，而不必重新編寫該儲存過程的SQL語句。

而且資料庫專業人員可以隨時對儲存過程進行修改，但對應用程式原始碼卻毫無影響，從而極大的提高了程式的可移植性。

2、儲存過程能夠實現較快的執行速度

如果某一操作包含大量的T-SQL語句程式碼，分別被多次執行，那麼儲存過程要比批處理的執行速度快得多。

因為儲存過程是預編譯的，在首次執行一個儲存過程 時，查詢優化器對其進行分析、優化，並給出最終被存在系統表中的儲存計劃。

而批處理的T-SQL語句每次執行都需要預編譯和優化，所以速度就要慢一些。

3、儲存過程減輕網路流量

對於同一個針對資料庫物件的操作，如果這一操作所涉及到的T-SQL語句被組織成一儲存過程，

那麼當在客戶機上呼叫該儲存過程時，網路中傳遞的只是該呼叫語句，否則將會是多條SQL語句。

從而減輕了網路流量，降低了網路負載。

4、儲存過程可被作為一種安全機制來充分利用

系統管理員可以對執行的某一個儲存過程進行許可權限制，從而能夠實現對某些資料訪問的限制，避免非授權使用者對資料的訪問，保證資料的安全。

 

 

 儲存過程的缺點

1、執行速度

對於很簡單的sql，儲存過程執行速度沒有什麼優勢。 

2、程式碼可讀性差，不易於維護

儲存過程的開發除錯要比一般程式困難（老版本DB2還只能用C寫儲存過程，更是一個災難）。

程式碼可讀性差，不易於難維護。

3、可移植性差

由於儲存過程將應用程式繫結到SQLServer，因此使用儲存過程封裝業務邏輯將限制應用程式的可移植性。

如果應用程式的可移植性在您的環境中非常重要，則將業務邏輯封裝在不特定於RDBMS的中間層中可能是一個更佳的選擇。

 

儲存過程的基本語法

變數的宣告：
宣告變數時必須在變數前加@符號
declare @num int

變數的賦值：
變數賦值時變數前必須加set
set @num= 30

宣告多個變數：
declare @name varchar(10),@num int

if語句的使用：

declare @d int
set @d = 1
IF @d = 1
BEGIN
    PRINT '正確' 
END 
ELSE BEGIN 
    PRINT '錯誤' 
END

 

多條件選擇語句：

declare @today int
declare @week nvarchar(3)
set @today=3
set @week= case
     when @today=1 then '星期一'
     when @today=2 then '星期二'
     when @today=3 then '星期三'
     when @today=4 then '星期四'
     when @today=5 then '星期五'
     when @today=6 then '星期六'
     when @today=7 then '星期日'
     else '值錯誤'
end
print @week

 

迴圈語句：

DECLARE @i INT
SET @i = 1
WHILE @i<1000000 BEGIN
set @i=@i+1
END

 

定義遊標：

DECLARE @cur1 CURSOR FOR SELECT .........

OPEN @cur1
FETCH NEXT FROM @cur1 INTO 變數
WHILE(@@FETCH_STATUS=0)
BEGIN
處理.....
FETCH NEXT FROM @cur1 INTO 變數
END
CLOSE @cur1
DEALLOCATE @cur1

儲存過程的分類

1、系統儲存過程

系統儲存過程是系統建立的儲存過程，目的在於能夠方便的從系統表中查詢資訊或完成與更新資料庫表相關的管理任務或其他的系統管理任務。

系統儲存過程主要存 儲在master資料庫中，以“sp”下劃線開頭的儲存過程。

儘管這些系統儲存過程在master資料庫中，但我們在其他資料庫還是可以呼叫系統儲存過 程。

有一些系統儲存過程會在建立新的資料庫的時候被自動建立在當前資料庫中。

1.1、系統儲存過程sql示例

--表重新命名
exec sp_rename 'stu', '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%';

2、自定義儲存過程

所謂自定義儲存過程，是指為了完成某一段特定的功能需求，在使用者資料庫中利用t-sql自行編輯的語句集合，在使用者自定義的過程中可以有輸入引數，返回的輸出引數及返回至客戶端的資訊與結果 。

如果在儲存過程名稱前加了“##”符號，表示建立的儲存過程是臨時的全域性性的；

如果前面的為“#”符號，表示所建立的儲存過程是臨時的區域性的，該儲存過程只能在建立它的會話中使用。

以上兩種儲存過程建立後都存放在tempdb資料庫中。

使用者自定義儲存過程還可以細分為t-sql語言儲存過程和CLR儲存過程。CLR儲存過程是指利用.NET框架公共語言編輯的儲存過程，既可以接受使用者提供的引數又可以返回儲存過程的執行結果，通常用作某個類的公共靜態方法。

 2.1、建立不帶引數儲存過程

--建立一個返回結果集的儲存過程（proc或者procedure均可）
if (object_id('proc_get_student', 'P') is not null)--判斷儲存過程是否存在 另外一種 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或者execute均可)
exec proc_get_student;

2.2、修改儲存過程

--修改儲存過程
alter proc proc_get_student
as
select * from student;  --修改後的SQL語句

--呼叫執行儲存過程，得到返回集(exec或者execute均可)
exec proc_get_student;

2.3、帶引數儲存過程

--建立一個返回結果集的儲存過程（proc或者procedure均可）
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

--呼叫執行儲存過程，2,4為引數
exec proc_find_stu 2, 4;

2.4、帶萬用字元引數儲存過程

--建立一個返回結果集的儲存過程（proc或者procedure均可）
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%';

2.5、帶輸出引數儲存過程

--建立一個返回結果集的儲存過程（proc或者procedure均可）
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;

3、擴充套件儲存過程

通常以“xp_”為字首標識，在sql server系統外通過執行動態連結庫，即DLL檔案，來實現的功能，該儲存過程經常使用API介面進行編輯，可以載入到sql server例項的地址空間裡試試執行。

在sql server常見的擴充套件儲存過程有：

  • xp_enumgroups 指定WINDOWS本地組列表在WINDOWS域中定義的全域性組表
  • xp_findnextmsg 接受輸入的郵件ID號，返回輸出的郵件ID號
  • xp_grantlogin     給使用者分配對sql server2012系統的許可權
  • xp_logevent    把使用者自定義訊息輸入到sql server日誌檔案或WINDOWS系統事件檢視器中
  • xp_loginconfig 顯示sql server 2012例項執行時登陸的安全配置

 

好了，我們就介紹到這裡吧，

拜拜，我們下次見。

 

歡迎關注訂閱我的微信公眾平臺【熊澤有話說】，更多好玩易學知識等你來取
作者：熊澤-學習中的苦與樂
公眾號：熊澤有話說
出處： https://www.cnblogs.com/xiongze520/p/14595601.html
創作不易，任何人或團體、機構全部轉載或者部分轉載、摘錄，請在文章明顯位置註明作者和原文連結。  

 

相關文章