欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

SQL?SERVER存儲過程用法詳解

 更新時間：2022年02月24日 11:15:25   作者：.NET開發(fā)菜鳥  
本文詳細講解了SQL?SERVER存儲過程用法，文中通過示例代碼介紹的非常詳細。對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下

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

一、存儲過程的概念

存儲過程Procedure是一組為了完成特定功能的SQL語句集合，經過編譯后存儲在服務器端的數據庫中，經過第一次編譯后再次調用不需要再次編譯，用戶通過指定存儲過程的名稱并給出參數來執(zhí)行，利用存儲過程可以加速SQL語句的執(zhí)行。

自定義存儲過程，由用戶創(chuàng)建并能完成某一特定功能的存儲過程，但是它與函數不同，存儲過程的返回值只是指明執(zhí)行是否成功。

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

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

存儲過程的優(yōu)點

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

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

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

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

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

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

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

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

二、存儲過程的分類

1、系統(tǒng)存儲過程

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

2、臨時存儲過程

分為兩種存儲過程：

一是本地臨時存儲過程，以井字號(#)作為其名稱的第一個字符，則該存儲過程將成為一個存放在tempdb數據庫中的本地臨時存儲過程，且只有創(chuàng)建它的用戶才能執(zhí)行它;

二是全局臨時存儲過程，以兩個井字號(##)號開始，則該存儲過程將成為一個存儲在tempdb數據庫中的全局臨時存儲過程，全局臨時存儲過程一旦創(chuàng)建，以后連接到服務器的任意用戶都可以執(zhí)行它，而且不需要特定的權限。

3、用戶自定義存儲過程

創(chuàng)建存儲過程語法：

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]是用來指定該參數是輸入參數還是輸出參數，默認是輸入參數。

二、存儲過程的用法

創(chuàng)建測試表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

執(zhí)行結果如下圖所示

創(chuàng)建表

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表的所有記錄

-- 創(chuàng)建無參數的存儲過程，獲取MyStudentInfo表的所有記錄
create procedure Proc_GetInfo
as
begin
  select * from MyStudentInfo
end

執(zhí)行存儲過程

-- 執(zhí)行存儲過程
exec Proc_GetInfo

輸出結果

2、創(chuàng)建帶輸入參數的存儲過程

 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

執(zhí)行存儲過程

 exec Proc_InsertData @Id=11,@Name='小李',@Age=21,@Gender='2',@Phone='13981257512',@Address='太原',@GradeId=4,@Score=62
 GO

執(zhí)行結果

3、創(chuàng)建帶默認值的輸入參數的存儲過程

 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

執(zhí)行存儲過程

exec Proc_InsertDefault @Id=12,@Name='小阮',@Age=24,@Phone='13832757512',@GradeId=4,@Score=70
go

4、創(chuàng)建帶輸出參數的存儲過程（根據輸入的ID返回年級ID）

在創(chuàng)建存儲過程時，可以用關鍵字OUTPUT來創(chuàng)建一個輸出參數，另外，調用時也必須給出OUTPUT關鍵字

 create proc Proc_Out
 @Id int, -- 默認為輸入參數
 @gradeId int out  -- 輸出參數
 as
 begin
  select @gradeId=GradeId from MyStudentInfo where id=@id
 end

執(zhí)行存儲過程，按照存儲過程中定義的參數順序

 declare @GradeIdOut int
 exec Proc_Out 10,@GradeIdOut out
 print @GradeIdOut
 GO

執(zhí)行結果

執(zhí)行存儲過程，指定參數名稱進行賦值，參數順序可以與定義時的參數順序不一致

DECLARE @Sid int
EXEC Proc_Out @gradeId=@Sid OUT,@Id=10
PRINT @Sid
go

執(zhí)行結果

5、創(chuàng)建帶返回值的存儲過程

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

執(zhí)行存儲過程，該學號已經存在

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

執(zhí)行結果

執(zhí)行存儲過程，插入不存在的學號

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

執(zhí)行結果

（)return 語句，return語句可以從過程、批處理或語句塊中退出，不執(zhí)行其后繼語句

 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

執(zhí)行結果

6、創(chuàng)建帶變量的存儲過程

/*在存儲過程可以定義變量，包括全局變量（@@變量名）和局部變量（@變量名）。
用于保存存儲過程中的臨時結果。
編寫存儲過程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

執(zhí)行結果

7、創(chuàng)建帶輸入輸出參數的存儲過程

 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

執(zhí)行存儲過程

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

執(zhí)行結果

8、創(chuàng)建分頁存儲過程

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

執(zhí)行存儲過程

exec Proc_Page 1,4
go

執(zhí)行結果

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

執(zhí)行存儲過程

exec Proc_P 2,4
go

執(zhí)行結果

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

執(zhí)行存儲過程

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

執(zhí)行結果

10、存儲過程中創(chuàng)建變量、賦值變量、創(chuàng)建表變量和臨時表

--創(chuàng)建名為 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

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

11、執(zhí)行動態(tài)SQL

在存儲過程中可以執(zhí)行動態(tài)的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)    --執(zhí)行動態(tài) sql
end

exec GetStus '003,005,009'        --執(zhí)行存儲過程 GetStus

到此這篇關于SQL SERVER存儲過程用法詳解的文章就介紹到這了。希望對大家的學習有所幫助，也希望大家多多支持腳本之家。

相關文章

  • SQLServer清理日志文件方法案例詳解

    SQLServer清理日志文件方法案例詳解

    這篇文章主要介紹了SQLServer清理日志文件方法案例詳解,本篇文章通過簡要的案例,講解了該項技術的了解與使用,以下就是詳細內容,需要的朋友可以參考下
    2021-09-09
  • SQL?Server內存機制詳解

    SQL?Server內存機制詳解

    本文詳細講解了SQL?Server的內存機制，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
    2022-04-04
  • 在SQL查詢中使用LIKE來代替IN查詢的方法

    在SQL查詢中使用LIKE來代替IN查詢的方法

    在SQL查詢中根據已知ID的集合來查詢結果我們通常會用到IN，直接在IN后面給出ID的集合或是在IN后面跟一個子查詢。
    2011-08-08
  • SQL Server統(tǒng)計信息更新時采樣百分比對數據預估準確性的影響詳解

    SQL Server統(tǒng)計信息更新時采樣百分比對數據預估準確性的影響詳解

    這篇文章主要給大家介紹了關于SQL Server統(tǒng)計信息更新時采樣百分比對數據預估準確性影響的相關資料，文中通過圖文介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧
    2018-09-09
  • CREATE FUNCTION sqlserver用戶定義函數

    CREATE FUNCTION sqlserver用戶定義函數

    創(chuàng)建用戶定義函數，它是返回值的已保存的 Transact-SQL 例程。用戶定義函數不能用于執(zhí)行一組修改全局數據庫狀態(tài)的操作。與系統(tǒng)函數一樣，用戶定義函數可以從查詢中喚醒調用。也可以像存儲過程一樣，通過 EXECUTE 語句執(zhí)行
    2012-07-07
  • 一條SQL語句修改多表多字段的信息的具體實現

    一條SQL語句修改多表多字段的信息的具體實現

    修改兩張及以上表的時候，總得需要用幾次語句才修改，萬一其中一條沒修改上，又沒事務機制的話，處理很麻煩，下面為大家介紹下使用一條SQL語句修改多表多字段的信息
    2014-01-01
  • Navicat 連接SQLServer數據庫(圖文步驟)

    Navicat 連接SQLServer數據庫(圖文步驟)

    這篇文章主要介紹了Navicat 連接SQLServer數據庫(圖文步驟)，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
    2021-04-04
  • SQL Server中的SELECT會阻塞SELECT嗎

    SQL Server中的SELECT會阻塞SELECT嗎

    這篇文章主要給大家介紹了SQL Server中的SELECT會阻塞SELECT的相關資料，文中通過示例代碼介紹的非常詳細，需要的朋友可以參考借鑒，下面隨著小編來一起學習學習吧
    2018-12-12
  • SQLServer 附加數據庫后出現只讀或失敗的解決方法

    SQLServer 附加數據庫后出現只讀或失敗的解決方法

    如果你在附加SQL數據庫，出現只讀或失敗的情況，來看下本文的解決方案吧。
    2010-03-03
  • 解決sql server 數據庫,sa用戶被鎖定的問題

    解決sql server 數據庫,sa用戶被鎖定的問題

    這篇文章主要介紹了解決sql server 數據庫,sa用戶被鎖定的問題，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教
    2021-06-06

最新評論