SQLSERVER預存程序基本文法

標籤：style   blog   color   os   io   使用   ar   for   資料   

一、定義變數--簡單賦值 declare @a intset @a=5 print @a   --使用select語句賦值 declare @user1 nvarchar(50) select @user1=‘張三‘print @user1 declare @user2 nvarchar(50) select @user2 = Name from ST_User where ID=1 print @user2   --使用update語句賦值 declare @user3 nvarchar(50) update ST_User set @user3 = Name where ID=1 print @user3 二、表、暫存資料表、表變數--建立暫存資料表1 create table #DU_User1 (      [ID] [int]  NOT NULL,      [Oid] [int] NOT NULL,      [Login] [nvarchar](50) NOT NULL,      [Rtx] [nvarchar](4) NOT NULL,      [Name] [nvarchar](5) NOT NULL,      [Password] [nvarchar](max) NULL,      [State] [nvarchar](8) NOT NULL); --向暫存資料表1插入一條記錄 insert into #DU_User1 (ID,Oid,[Login],Rtx,Name,[Password],State) values (100,2,‘LS‘,‘0000‘,‘臨時‘,‘321‘,‘特殊‘);   --從ST_User查詢資料，填充至新產生的暫存資料表 select * into #DU_User2 from ST_User where ID<8   --查詢並聯合兩暫存資料表 select * from #DU_User2 where ID<3 union select * from #DU_User1   --刪除兩暫存資料表 drop table #DU_User1 drop table #DU_User2 --建立暫存資料表 CREATE TABLE #t (     [ID] [int] NOT NULL,     [Oid] [int] NOT NULL,     [Login] [nvarchar](50) NOT NULL,     [Rtx] [nvarchar](4) NOT NULL,     [Name] [nvarchar](5) NOT NULL,     [Password] [nvarchar](max) NULL,     [State] [nvarchar](8) NOT NULL, )   --將查詢結果集（多條資料）插入暫存資料表 insert into #t select * from ST_User --不能這樣插入 --select * into #t from dbo.ST_User   --添加一列，為int型自增長子段 alter table #t add [myid] int NOT NULL IDENTITY(1,1) --添加一列，預設填充全球唯一標識 alter table #t add [myid1] uniqueidentifier NOT NULL default(newid())   select * from #t drop table #t--給查詢結果集增加自增長列   --無主鍵時： select IDENTITY(int,1,1)as ID, Name,[Login],[Password] into #t from ST_User select * from #t   --有主鍵時： select (select SUM(1) from ST_User where ID<= a.ID) as myID,* from ST_User a order by myID--定義表變數 declare @t table(     id int not null,     msg nvarchar(50) null) insert into @t values(1,‘1‘) insert into @t values(2,‘2‘) select * from @t 三、迴圈--while迴圈計算1到100的和 declare @a intdeclare @sum intset @a=1 set @sum=0 while @a<=100 begin    set @sum+=@a     set @a+=1 endprint @sum四、條件陳述式--if,else條件分支 if(1+1=2) begin    print ‘對‘endelsebegin    print ‘錯‘end  --when then條件分支 declare @today intdeclare @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 ‘值錯誤‘endprint @week 五、遊標declare @ID intdeclare @Oid intdeclare @Login varchar(50)   --定義一個遊標 declare user_cur cursor for select ID,Oid,[Login] from ST_User --開啟遊標 open user_cur while @@fetch_status=0 begin--讀取遊標     fetch next from user_cur into @ID,@Oid,@Login     print @ID     --print @Login endclose user_cur --摧毀遊標 deallocate user_cur六、觸發器　　觸發器中的暫存資料表：　　Inserted 　　存放進行insert和update 操作後的資料 　　Deleted 　　存放進行delete 和update操作前的資料--建立觸發器 Create trigger User_OnUpdate      On ST_User      for Update As     declare @msg nvarchar(50)     --@msg記錄修改情況     select @msg = N‘姓名從“‘ + Deleted.Name + N‘”修改為“‘ + Inserted.Name + ‘”‘ from Inserted,Deleted     --插入日誌表     insert into [LOG](MSG)values(@msg)       --刪除觸發器 drop trigger User_OnUpdate七、預存程序--建立帶output參數的預存程序 CREATE PROCEDURE PR_Sum     @a int,     @b int,     @sum int outputASBEGIN    set @[email protected]+@b END  --建立Return傳回值預存程序 CREATE PROCEDURE PR_Sum2     @a int,     @b intASBEGIN    Return @a+@b END      --執行預存程序擷取output型傳回值 declare @mysum intexecute PR_Sum 1,2,@mysum outputprint @mysum   --執行預存程序擷取Return型傳回值 declare @mysum2 intexecute @mysum2= PR_Sum2 1,2 print @mysum2   八、自訂函數　　函數的分類：　　　　1）純量值函式　　　　2）資料表值函式　　　　　　　　a:內聯資料表值函式　　　　　　　　b:多語句資料表值函式　　　　3）系統函數　　--建立純量值函式 create function FUNC_Sum1 (     @a int,     @b int) returns intasbegin    return @a+@b end  --建立內聯資料表值函式 create function FUNC_UserTab_1 (     @myId int) returns tableasreturn (select * from ST_User where ID<@myId)   --建立多語句資料表值函式 create function FUNC_UserTab_2 (     @myId int) returns @t table(     [ID] [int] NOT NULL,     [Oid] [int] NOT NULL,     [Login] [nvarchar](50) NOT NULL,     [Rtx] [nvarchar](4) NOT NULL,     [Name] [nvarchar](5) NOT NULL,     [Password] [nvarchar](max) NULL,     [State] [nvarchar](8) NOT NULL) asbegin    insert into @t select * from ST_User where ID<@myId     returnend  --調用資料表值函式 select * from dbo.FUNC_UserTab_1(15) --調用純量值函式 declare @s intset @s=dbo.FUNC_Sum1(100,50) print @s   --刪除純量值函式 drop function FUNC_Sum1談談自訂函數與預存程序的區別：一、自訂函數：　　1. 可以返回表變數　　2. 限制頗多，包括　　　　不能使用output參數；　　　　不能用暫存資料表；　　　　函數內部的操作不能影響到外部環境；　　　　不能通過select返回結果集；　　　　不能update，delete，資料庫表；　　3. 必須return 一個標量值或表變數　　自訂函數一般用在複用度高，功能簡單單一，爭對性強的地方。二、預存程序　　1. 不能返回表變數　　2. 限制少，可以執行對資料庫表的操作，可以返回資料集　　3. 可以return一個標量值，也可以省略return　　　預存程序一般用在實現複雜的功能，資料操縱方面。

 

SQLSERVER預存程序基本文法