SQL Server建立預存程序——動態SQL

標籤：文法   伺服器端   nvarchar   沒有   name   pre   列表   tle   添加   

預存程序(stored procedure)是一組為了完成特定功能的SQL語句集合，經編譯後儲存在伺服器端的資料庫中，利用預存程序可以加速SQL語句的執行。

自訂預存程序，由使用者建立並能完成某一特定功能的預存程序，預存程序既可以有參數又有傳回值，但是它與函數不同，預存程序的傳回值只是指明執行是否成功，

預存程序並不能像函數那樣被直接調用，只能利用 execute 來執行預存程序。

優點：

1、提高應用程式的通用性和可移植性：預存程序建立後，可以在程式中被多次調用，而不必重新編寫該預存程序的SQL語句。並且資料庫專業人員可以隨時對預存程序進行

修改，且對程式原始碼沒有影響，這樣就極大的提高了程式的可移植性。

2、可以提高SQL的速度，預存程序是編譯過的，如果某一個操作包含大量的SQL代碼或分別被執行多次，那麼使用預存程序比直接使用單條SQL語句執行速度快的多。

3、減輕伺服器的負擔：當使用者的操作是針對資料庫物件的操作時，如果使用單條調用的方式，那麼網路上還必須傳輸大量的SQL語句，如果使用預存程序，

則直接發送過程的調用命令即可，降低了網路的負擔。

 

文法：

 1 CREATE PROC [ EDURE ] procedure_name [ ; number ] 2     [ { @parameter data_type } 3         [ VARYING ] [ = default ] [ OUTPUT ] 4     ] [ ,...n ] 5 [ WITH 6     { RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] 7 [ FOR REPLICATION ] 8 AS  9 [ begin ]10     T-SQL 陳述式11 [ end ]

無參數預存程序：

 1 --建立名為 GetStuCou 的無參數預存程序 2 create procedure GetStuCou 3 as 4 begin 5     select *  6     from Student s 7     left join Course c on s.C_S_Id=c.C_Id 8 end 9 10 --執行名為 GetStuCou 的無參數預存程序11 execute GetStuCou

有傳回值的預存程序：

 1 --建立名為 GetStuCou_Re 的有傳回值的預存程序 2 create procedure GetStuCou_Re 3 as 4 begin 5     insert into Course(C_Name) values(‘HTML5‘) 6     return SCOPE_IDENTITY();        -- 返回為當前表插入資料最後產生的標識值。 7 end 8  9 --執行名為 GetStuCou 的有傳回值的預存程序10 execute GetStuCou_Re

有輸入參數的預存程序：

 1 --建立名為 GetStuCou_In 的有輸入參數的預存程序 2 create procedure GetStuCou_In 3 @StuNo    nvarchar(64)=‘001‘        --設定預設值 4 as 5 begin 6     select * from Student where [email protected] 7 end 8  9 --執行名為 GetStuCou_In 的有輸入參數的預存程序（不傳參數，即使用預設值）10 execute GetStuCou_In11 12 --執行名為 GetStuCou_In 的有輸入參數的預存程序（傳入參數）13 execute GetStuCou_In ‘005‘

有輸入、輸出參數的預存程序：

 1 --建立名為 GetStuCou_Out 的有輸入參數和輸出參數的預存程序 2 create procedure GetStuCou_Out 3 @StuNo    nvarchar(64), 4 @Height nvarchar(32) output 5 as 6 begin 7     if(@StuNo is not null and @StuNo <> ‘‘) 8     begin 9         select @Height=S_Height 10         from Student 11         where [email protected]12     end13     else14     begin15         set @Height=‘185‘16     end17 end18 19 --執行名為 GetStuCou_Out 的有輸入參數和輸出參數的預存程序20 execute GetStuCou_Out ‘005‘,null

有輸入、輸出參數和結果集的預存程序：

 1 --建立名為 GetStuCou_DS 的有輸入參數、輸出參數和結果集的預存程序 2 create procedure GetStuCou_DS 3 @StuNo    nvarchar(64), 4 @Height nvarchar(32) output 5 as 6 begin 7     if(@StuNo is not null and @StuNo <> ‘‘) 8     begin 9         select @Height=S_Height 10         from Student 11         where [email protected]12     end13     else14     begin15         set @Height=‘185‘16     end17 18     select s.S_Id,s.S_StuNo,s.S_Name,s.S_Sex,s.S_Height,s.S_BirthDate,c.C_Id,c.C_Name19     from Student s20     left join Course c on s.C_S_Id=c.C_Id21     where [email protected]22 end23 24 --執行名為 GetStuCou_DS 的有輸入參數、輸出參數和結果集的預存程序25 execute GetStuCou_DS ‘005‘,null

返回多個結果集的預存程序：

 1 --建立名為 GetStuCou_DSS 的返回多個結果集的預存程序 2 create procedure GetStuCou_DSS 3 @StuNo    nvarchar(64), 4 @Height nvarchar(32) 5 as 6 begin 7     if(@StuNo is not null and @StuNo <> ‘‘) 8     begin 9         select *10         from Student 11         where [email protected]12     end13 14     if(@Height is not null and @Height <> ‘‘)15     begin16         select * 17         from Student18         where [email protected]19     end20 end21 22 --執行名為 GetStuCou_DSS 的返回多個結果集的預存程序23 execute GetStuCou_DSS ‘005‘,‘185‘

預存程序裡面不僅可以進行查詢，還可以進行各種增刪改操作。其實儲存就是由很多 T-SQL 陳述式組成的代碼塊。

 

預存程序中建立變數、賦值變數、建立表變數和暫存資料表：

 1 --建立名為 GetStuCou_Ext 的返回多個結果集的預存程序 2 create procedure GetStuCou_Ext 3 @StuNo    nvarchar(64), 4 @Height nvarchar(32) 5 as 6 begin 7     declare @Var nvarchar(10)    --定義變數 8  9     set @Var=‘123‘        --賦值變數10 11     --定義表變數12     declare @StuTab table 13     (14         ID     int not null primary key,15         StuNo    nvarchar(50) unique,16         Name varchar(50),17         Sex varchar(10),18         Height varchar(10)19     )20     --表變數只能在定義的時候添加約束21 22     --定義暫存資料表23     create table #Tab24     (25         ID     int not null primary key,26         StuNo    nvarchar(50),27         Name varchar(50),28         Sex varchar(10),29         Height varchar(10)30     )31 32     alter table #Tab add constraint S_UNIQUE unique(StuNo)33 34     --暫存資料表可以在之後添加約束35     36     if(@StuNo is not null and @StuNo <> ‘‘)37     begin38         insert into @StuTab(ID,StuNo,Name,Sex,Height)    --把資料插入表變數39         select S_Id,S_StuNo,S_Name,S_Sex,S_Height40         from Student 41         where [email protected]42 43         insert into #Tab(ID,StuNo,Name,Sex,Height)    --把資料插入暫存資料表44         select S_Id,S_StuNo,S_Name,S_Sex,S_Height45         from Student 46         where [email protected]47     end48 49     if(@Height is not null and @Height <> ‘‘)50     begin51         insert into @StuTab(ID,StuNo,Name,Sex,Height)    --把資料插入表變數52         select S_Id,S_StuNo,S_Name,S_Sex,S_Height53         from Student54         where [email protected]55 56         insert into #Tab(ID,StuNo,Name,Sex,Height)    --把資料插入暫存資料表57         select S_Id,S_StuNo,S_Name,S_Sex,S_Height58         from Student59         where [email protected]60     end61 62     SELECT * FROM @StuTab63     select * from #Tab64 end65 66 --執行名為 GetStuCou_DSS 的返回多個結果集的預存程序67 execute GetStuCou_Ext ‘005‘,‘185‘

預存程序動態執行 SQL 陳述式：

以上可以看出我們傳入的參數（學號）是單個的，那麼如果一次性傳入多個學號呢（使用逗號隔開，即 ‘005,006,007‘ ），這就需要用到動態拼接並執行 sql 語句。

 1 create proc GetStus 2 @StuNo nvarchar(500) 3 as 4 begin 5     declare @Sql nvarchar(3000) 6  7     if(@StuNo is not null and @StuNo <> ‘‘) 8     begin 9         set @Sql=‘ select * from Student where S_StuNo in (‘[email protected]+‘) ‘10     end11 12     exec (@Sql)    --執行動態 sql 13 end14 15 exec GetStus ‘003,005,009‘        --執行預存程序 GetStus

現在還有一個問題，我想要執行動態 sql 的時候並返回變數值，比如我現在需要執行動態 sql 的時候返回課程 ID ,然後根據課程 ID 查詢課程。

使用系統預存程序 sp_executesql 動態 sql 給變數賦值。

修改預存程序之後：

 1 ALTER proc [dbo].[GetStus] 2 @StuNo nvarchar(500) 3 as 4 begin 5     declare @Sql nvarchar(3000) 6     declare @C_Id int 7     declare @Cou int 8  9     if(@StuNo is not null and @StuNo <> ‘‘)10     begin11         set @Sql=‘ select @CId=C_S_Id,@cou=count(1) from Student where S_StuNo in (‘[email protected]+‘) group by C_S_Id ‘12     end13 14     exec sp_executesql @Sql,N‘@CId int output,@cou int output‘,@CId = @C_Id output,@cou = @Cou output;15 16     select @C_Id,@Cou    --查看返回變數的值17 18     select * from Course where [email protected]_Id 19 end20 21 exec GetStus ‘005‘        --執行預存程序 GetStus

PS：sp_executesql 要求動態 Sql 和動態 Sql 參數列表必須是 NVARCHAR 類型。

動態Sql的參數列表與外部提供值的參數列表順序必需一致，例如：N‘@CId int output,@cou int output‘,@CId = @C_Id output,@cou = @Cou output;，@CId 對應 @C_Id，@cou 對應 @Cou。

動態SQl的參數列表與外部提供參數的參數列表參數名可以同名，如果不一樣，需顯示註明，例如：N‘@CId int output,@cou int output‘,@CId = @C_Id output,@cou = @Cou output;，即 @CId = @C_Id 和 @cou = @Cou 。

SQL Server建立預存程序——動態SQL