當前位置：首頁 > 人文社科 > 生活经验 >内容正文

生活经验

SER SERVER存储过程

發布時間：2023/11/27 生活经验 28 豆豆

生活随笔收集整理的這篇文章主要介紹了 SER SERVER存储过程小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

Transact-SQL中的存儲過程，非常類似于C#語言中的方法，可以重復調用。當存儲過程執行一次后，可以將語句存儲到緩存中，這樣下次執行的時候直接使用緩存中的語句。這樣就可以提高存儲過程的性能。

一、存儲過程的概念

??? 存儲過程Procedure是一組為了完成特定功能的SQL語句集合，存儲在數據庫中，經過第一次編譯后再次調用不需要再次編譯，用戶通過指定存儲過程的名稱并給出參數來執行。

??? 存儲過程中可以包含邏輯控制語句和數據操縱語句，它可以接受參數、輸出參數、返回單個或多個結果集以及返回值。

??? 由于存儲過程在創建時即在數據庫服務器上進行了編譯并存儲在數據庫中，所以存儲過程運行要比單個的SQL語句塊要快。同時由于在調用時只需用提供存儲過程名和必要的參數信息，所以在一定程度上也可以減少網絡流量、簡單網絡負擔。

? 1、存儲過程的優點

??????? A、存儲過程允許標準組件式編程

??????? 存儲過程創建后可以在程序中被多次調用執行，而不必重新編寫該存儲過程的SQL語句。而且數據庫專業人員可以隨時對存儲過程進行修改，但對應用程序源代碼卻毫無影響，從而極大的提高了程序的可移植性。

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

??????? 如果某一操作包含大量的T-SQL語句代碼，分別被多次執行，那么存儲過程要比批處理的執行速度快得多。因為存儲過程是預編譯的，在首次運行一個存儲過程時，查詢優化器對其進行分析、優化，并給出最終被存在系統表中的存儲計劃。而批處理的T-SQL語句每次運行都需要預編譯和優化，所以速度就要慢一些。

??????? C、存儲過程減輕網絡流量

??????? 對于同一個針對數據庫對象的操作，如果這一操作所涉及到的T-SQL語句被組織成一存儲過程，那么當在客戶機上調用該存儲過程時，網絡中傳遞的只是該調用語句，否則將會是多條SQL語句。從而減輕了網絡流量，降低了網絡負載。

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

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

二、存儲過程的分類

1、系統存儲過程

以sp_開頭,用來進行系統的各項設定.取得信息.相關管理工作。系統存儲過程是系統創建的存儲過程，目的在于能夠方便的從系統表中查詢信息或完成與更新數據庫表相關的管理任務或其他的系統管理任務。系統存儲過程主要存儲在master數據庫中，以“sp”下劃線開頭的存儲過程。盡管這些系統存儲過程在master數據庫中，但我們在其他數據庫還是可以調用系統存儲過程。有一些系統存儲過程會在創建新的數據庫的時候被自動創建在當前數據庫中。

2、臨時存儲過程分為兩種存儲過程：一是本地臨時存儲過程，以井字號(#)作為其名稱的第一個字符，則該存儲過程將成為一個存放在tempdb數據庫中的本地臨時存儲過程，且只有創建它的用戶才能執行它; 二是全局臨時存儲過程，以兩個井字號(##)號開始，則該存儲過程將成為一個存儲在tempdb數據庫中的全局臨時存儲過程，全局臨時存儲過程一旦創建，以后連接到服務器的任意用戶都可以執行它，而且不需要特定的權限。 3、用戶自定義存儲過程創建存儲過程語法： create proc | procedure pro_name
??? [{@參數名稱參數數據類型} [=默認值] [output],
???? {@參數名稱參數數據類型} [=默認值] [output],
???? ....
??? ]
as begin
??? SQL_statements end 每個參數名前要有一個“@”符號,每一個存儲過程的參數僅為該程序內部使用,參數的類型除了IMAGE外，其他SQL Server所支持的數據類型都可使用。 [OUTPUT]是用來指定該參數是輸入參數還是輸出參數，默認是輸入參數。

--創建測試表MyStudentInfo
CREATE table MyStudentInfo
(
? Id int not null primary key,
? Name varchar(16),
? Age int,
? Gender varchar(2),
? Phone varchar(16),
? Address varchar(50),
? GradeId int,
? Score int
)

--聯合插入多條數據
INSERT INTO MyStudentInfo
SELECT 1,'張三',20,'1','15801258912','上海',1,90 UNION
SELECT 2,'李四',22,'1','12345678901','北京',1,84 UNION
SELECT 3,'王五',16,'1','13976891234','天津',2,35 UNION
SELECT 4,'趙六',19,'1','18676891234','重慶',3,56 UNION
SELECT 5,'小紅',21,'2','17776891234','廣州',4,82 UNION
SELECT 6,'小王',25,'2','13176891234','深圳',5,54 UNION
SELECT 7,'小劉',18,'2','13374591234','南京',6,69 UNION
SELECT 8,'小張',16,'1','13974596734','長沙',6,58 UNION
SELECT 9,'小羅',27,'1','13175122786','武漢',7,40 UNION
SELECT 10,'小袁',21,'2','17715872346','石家莊',8,34

CREATE table GradeInfo
(
? Id int not null primary key,
? GradeName varchar(16)
)

--聯合插入多條數據
INSERT INTO GradeInfo
SELECT 1,'.NET' UNION
SELECT 2,'Android' UNION
SELECT 3,'PHP' UNION
SELECT 4,'UI' UNION
SELECT 5,'HTML5' UNION
SELECT 6,'JAVA' UNION
SELECT 9,'HADOOP' UNION
SELECT 10,'大數據'

?--1、不帶參數的存儲過程，獲取MyStudentInfo表的所有記錄
?CREATE proc Proc_GetInfo
?as
?select * from MyStudentInfo

?--執行存儲過程
?exec Proc_GetInfo
?go

--2、創建帶輸入參數的存儲過程
?CREATE proc Proc_InsertData
?@Id int,
?@Name varchar(16),
?@Age int,
?@Gender varchar(2),
?@Phone varchar(16),
?@Address varchar(50),
?@GradeId int,
?@Score int
?as
?begin
? insert INTO MyStudentInfo VALUES(@Id,@Name,@Age,@Gender,@Phone,@Address,@Gender,@Score)
?end

?--執行存儲過程
?exec Proc_InsertData @Id=11,@Name='小李',@Age=21,@Gender='2',@Phone='13981257512',@Address='太原',@GradeId=4,@Score=62
?GO

--3、創建帶默認值的輸入參數的存儲過程
? CREATE proc Proc_InsertDefault
?@Id int,
?@Name varchar(16),
?@Age int,
?@Gender varchar(2)='1',
?@Phone varchar(16),
?@Address varchar(50)='沈陽',
?@GradeId int,
?@Score int
?as
?begin
? insert INTO MyStudentInfo VALUES(@Id,@Name,@Age,@Gender,@Phone,@Address,@Gender,@Score)
?end

?--執行存儲過程
?exec Proc_InsertDefault @Id=12,@Name='小阮',@Age=24,@Phone='13832757512',@GradeId=4,@Score=70
?GO

?--4、創建帶輸出參數的存儲過程（根據輸入的ID返回年級ID）
?--在創建存儲過程時，可以用關鍵字OUTPUT來創建一個輸出參數，另外，調用時也必須給出OUTPUT關鍵字
?create proc Proc_Out
?@Id int,
?@gradeId int out
?as
?begin
? select @gradeId=GradeId from MyStudentInfo where id=@id
?end

?--執行存儲過程
?declare @GradeIdOut int
?exec Proc_Out 10,@GradeIdOut out
?print @GradeIdOut
?GO

--執行存儲過程
DECLARE @Sid int
EXEC Proc_Out @gradeId=@Sid OUT,@Id=10
PRINT @Sid

--5、創建帶返回值的存儲過程
?--print語句可以將用戶定義的消息返回給客戶端
?--編寫一個存儲過程，在插入學生數據前，先判斷學號是否存在，如果存在，
?--輸出“要插入的學生的學號已經存在”；否則，插入學生數據，返回“恭喜，數據插入成功”
?create proc Proc_Return
?@Id int,
?@Name varchar(16),
?@Age int,
?@Gender varchar(2),
?@Phone varchar(16),
?@Address varchar(50),
?@GradeId int,
?@Score int
?as
?begin
?? IF exists (SELECT * FROM MyStudentInfo WHERE Id=@Id)
?????? begin
???????? print '要插入的學生的學號已經存在'
????? ROLLBACK
??? end
?? else
?????? begin
????? insert INTO MyStudentInfo VALUES(@Id,@Name,@Age,@Gender,@Phone,@Address,@GradeId,@Score)
?? print '恭喜，數據插入成功'
??? end
?end
?GO

?--執行存儲過程
?exec Proc_Return @Id=10,@Name='小李',@Age=21,@Gender='2',@Phone='13981257512',@Address='太原',@GradeId=4,@Score=75

?--執行存儲過程
?exec Proc_Return @Id=12,@Name='小李',@Age=21,@Gender='2',@Phone='13981257512',@Address='太原',@GradeId=4,@Score=75

?--（)return 語句，return語句可以從過程、批處理或語句塊中退出，不執行其后繼語句
?CREATE proc Proc_Delete
?@name varchar(16)
?as
? begin
??? delete FROM MyStudentInfo where Name=@name
?return @@rowcount
? end

? --調用存儲過程
? declare @Del_Row int
? exec @Del_Row= Proc_Delete @name='小劉'
? select @Del_Row 刪除的行
? GO

?--6、創建帶變量的存儲過程
? /*在存儲過程可以定義變量，包括全局變量（@@變量名）和局部變量（@變量名）。
? 用于保存存儲過程中的臨時結果。
? 編寫存儲過程Proc_Var，根據輸入的學生學號，計算該學生的平均成績。
? 根據該生平局成績與全體學生平均成績的關系，返回相應信息*/
?create proc Proc_Var
?@id int,
?@ResStr varchar(16) out
?as
?begin
? --聲明變量
? declare @curAvg decimal(18,2)
? declare @totalAvg decimal(18,2)
? select @totalAvg=AVG(Score) from MyStudentInfo
? select @curAvg=AVG(Score) from MyStudentInfo where Id=@id
? IF @curAvg>@totalAvg
???? set @ResStr='高于平均分'
? else
???? set @ResStr='低于平均分'
? print '總平均分為:'+convert(varchar(18),@totalAvg)
? print '該生平均分為:'+convert(varchar(18),@curAvg)
? print @ResStr
?end

?--調用存儲過程
?declare @resstring varchar(20)
?exec Proc_Var 6,@resstring out

?--7、創建帶輸入輸出參數的存儲過程
?create proc Proc_OutPut
?@Id int,--默認輸入參數
?@Score int out,--輸出參數
?@Age int output --輸入輸出參數
?as
?? begin
???? select @Score=Score,@Age=Age from MyStudentInfo where Id=@Id and Age=@Age
?? end

--執行存儲過程
declare @Sid int,@SScore int,@SAge int
set @Sid=8
set @SAge=16
exec Proc_OutPut @Sid,@SScore out,@SAge output
print '成績:'+convert(varchar(8),@SScore)+',年齡:'+convert(varchar(8),@SAge)

--8、創建分頁存儲過程
create proc Proc_Page
@StartIndex int,
@EndIndex int
as
? begin
?? select COUNT(*) from MyStudentInfo
?? select * from (
????? SELECT ROW_NUMBER() OVER(ORDER BY Id) AS rowid,* FROM MyStudentInfo ) temp
?? where temp.rowid between @StartIndex AND @EndIndex
? end

? --執行存儲過程
? exec Proc_Page 1,4

? --分頁存儲過程2
? create proc Proc_P
? @PageIndex int,
? @PageSize int
? as
??? begin
????? 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 rowid ,* FROM MyStudentInfo ) temp
?? where temp.rowid between @StartRow AND @EndRow
?end

--執行存儲過程
exec Proc_P 2,4

轉載于:https://www.cnblogs.com/dotnet261010/p/5921211.html

總結

以上是生活随笔為你收集整理的SER SERVER存储过程的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。