SQL SERVER儲存過程用法詳解

2022-02-24 13:01:31

1、不帶引數的儲存過程，獲取MyStudentInfo表的所有記錄
2、建立帶輸入引數的儲存過程
3、建立帶預設值的輸入引數的儲存過程
4、建立帶輸出引數的儲存過程（根據輸入的ID返回年級ID）
5、建立帶返回值的儲存過程
6、建立帶變數的儲存過程
7、建立帶輸入輸出引數的儲存過程
8、建立分頁儲存過程

分頁儲存過程2

9、返回多個結果集

10、儲存過程中建立變數、賦值變數、建立表變數和臨時表

11、執行動態SQL

Transact-SQL中的儲存過程，非常類似於C#語言中的方法，可以重複呼叫。當儲存過程執行一次後，可以將語句儲存到快取中，這樣下次執行的時候直接使用快取中的語句。這樣就可以提高儲存過程的效能。

一、儲存過程的概念

儲存過程Procedure是一組為了完成特定功能的SQL語句集合，經過編譯後儲存在伺服器端的資料庫中，經過第一次編譯後再次呼叫不需要再次編譯，使用者通過指定儲存過程的名稱並給出引數來執行，利用儲存過程可以加速SQL語句的執行。

自定義儲存過程，由使用者建立並能完成某一特定功能的儲存過程，但是它與函數不同，儲存過程的返回值只是指明執行是否成功。

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

由於儲存過程在建立時即在資料庫伺服器上進行了編譯並儲存在資料庫中，所以儲存過程執行要比單個的SQL語句塊要快。同時由於在呼叫時只需用提供儲存過程名和必要的引數資訊，所以在一定程度上也可以減少網路流量、簡單網路負擔。

儲存過程的優點

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

儲存過程建立後可以在程式中被多次呼叫執行，而不必重新編寫該儲存過程的SQL語句。而且資料庫專業人員可以隨時對儲存過程進行修改，但對應用程式原始碼卻毫無影響，從而極大的提高了程式的可移植性。

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

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

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

對於同一個針對資料庫物件的操作，如果這一操作所涉及到的T-SQL語句被組織成一儲存過程，那麼當在客戶機上呼叫該儲存過程時，網路中傳遞的只是該呼叫語句，否則將會是多條SQL語句。從而減輕了網路流量，降低了網路負載。

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

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

二、儲存過程的分類

1、系統儲存過程

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

2、臨時儲存過程

分為兩種儲存過程：

一是本地臨時儲存過程，以井字號(#)作為其名稱的第一個字元，則該儲存過程將成為一個存放在tempdb資料庫中的本地臨時儲存過程，且只有建立它的使用者才能執行它;

二是全域性臨時儲存過程，以兩個井字號(##)號開始，則該儲存過程將成為一個儲存在tempdb資料庫中的全域性臨時儲存過程，全域性臨時儲存過程一旦建立，以後連線到伺服器的任意使用者都可以執行它，而且不需要特定的許可權。

3、使用者自定義儲存過程

建立儲存過程語法：

CREATE PROC [ EDURE ] procedure_name [ ; number ]
    [ { @parameter data_type }
        [ VARYING ] [ = default ] [ OUTPUT ]
    ] [ ,...n ]
[ WITH
    { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS
[ begin ]
    T-SQL 語句
[ 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表的所有記錄

-- 建立無引數的儲存過程，獲取MyStudentInfo表的所有記錄
create procedure Proc_GetInfo
as
begin
  select * from MyStudentInfo
end

執行儲存過程

-- 執行儲存過程
exec Proc_GetInfo

輸出結果

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
 go

執行儲存過程

 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
 go

執行儲存過程

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
go

執行結果

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
go

執行結果

執行儲存過程，插入不存在的學號

exec Proc_Return @Id=15,@Name='小李',@Age=21,@Gender='2',@Phone='13981257512',@Address='太原',@GradeId=4,@Score=75
go

執行結果

（)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
 go

執行結果

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
go

執行儲存過程

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)
go

執行結果

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
go

執行儲存過程

exec Proc_Page 1,4
go

執行結果

分頁儲存過程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
go

執行儲存過程

exec Proc_P 2,4
go

執行結果

9、返回多個結果集

-- 返回多個結果集
create procedure proc_get
@Stuid int,
@Gradeid int
as
begin
   select * from MyStudentInfo where id=@Stuid
   select * from GradeInfo where id=@Gradeid
end
go

執行儲存過程

-- 執行儲存過程
exec proc_get @Stuid=2,@GradeId=4

執行結果

10、儲存過程中建立變數、賦值變數、建立表變數和臨時表

--建立名為 GetStuCou_Ext 的返回多個結果集的儲存過程
create procedure GetStuCou_Ext
@StuNo    nvarchar(64),
@Height nvarchar(32)
as
begin
    declare @Var nvarchar(10)    --定義變數

    set @Var='123'        --賦值變數

    --定義表變數
    declare @StuTab table
    (
        ID     int not null primary key,
        StuNo    nvarchar(50) unique,
        Name varchar(50),
        Sex varchar(10),
        Height varchar(10)
    )
    --表變數只能在定義的時候新增約束

    --定義臨時表
    create table #Tab
    (
        ID     int not null primary key,
        StuNo    nvarchar(50),
        Name varchar(50),
        Sex varchar(10),
        Height varchar(10)
    )

    alter table #Tab add constraint S_UNIQUE unique(StuNo)

    --臨時表可以在之後新增約束

    if(@StuNo is not null and @StuNo <> '')
    begin
        insert into @StuTab(ID,StuNo,Name,Sex,Height)    --把資料插入表變數
        select S_Id,S_StuNo,S_Name,S_Sex,S_Height
        from Student
        where S_StuNo=@StuNo

        insert into #Tab(ID,StuNo,Name,Sex,Height)    --把資料插入臨時表
        select S_Id,S_StuNo,S_Name,S_Sex,S_Height
        from Student
        where S_StuNo=@StuNo
    end

    if(@Height is not null and @Height <> '')
    begin
        insert into @StuTab(ID,StuNo,Name,Sex,Height)    --把資料插入表變數
        select S_Id,S_StuNo,S_Name,S_Sex,S_Height
        from Student
        where S_Height=@Height

        insert into #Tab(ID,StuNo,Name,Sex,Height)    --把資料插入臨時表
        select S_Id,S_StuNo,S_Name,S_Sex,S_Height
        from Student
        where S_Height=@Height
    end

    SELECT * FROM @StuTab
    select * from #Tab
end

--執行名為 GetStuCou_DSS 的返回多個結果集的儲存過程
execute GetStuCou_Ext '005','185'

11、執行動態SQL

在儲存過程中可以執行動態的SQL

create proc GetStus
@StuNo nvarchar(500)
as
begin
    declare @Sql nvarchar(3000)

    if(@StuNo is not null and @StuNo <> '')
    begin
        set @Sql=' select * from Student where S_StuNo in ('+@StuNo+') '
    end

    exec (@Sql)    --執行動態 sql
end

exec GetStus '003,005,009'        --執行儲存過程 GetStus

到此這篇關於SQL SERVER儲存過程用法詳解的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支援it145.com。