SQLServer - 預存程序基本語法

oracle的建表sql轉成sqlserver的建表sql時的注意點 :
1.所有的comment語句需要刪除。
2.clob類型轉換為text類型。
3.blob型別轉換為image型別。
4.number型別轉換為int，number(16,2)等轉換為decimal(16,2)，number(18)轉換為bigint。
5.default sysdate改為default getDate()。
6.to_date('2009-12-18','yyyy-mm-dd')改為cast('2009-12-18'  as   datetime)

SQLSERVER:
變數的宣告:
宣告變數時必須在變數前加@符號
DECLARE @I INT

變數的賦值:
變數賦值時變數前必須加上set
SET @I = 30

宣告多個變數:
DECLARE @s varchar(10),@a INT

if語句:

Java程式碼 

if ..  
begin  
  ...  
end  
else if ..  
begin  
  ...  
end  
else  
begin  
  ...  
end

 Example:

Sql程式碼 

DECLARE @d INT  
set @d = 1  
IF @d = 1 BEGIN  
   PRINT '正确'  
END  
ELSE BEGIN  
   PRINT '错误'  
END

多條件選擇語句：＜ Java程式碼 

declare @today int  
declare @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 '值错误'  
end  
print @week

 Example:

Java程式碼 

WHILE 条件 BEGIN    
执行语句  
END

定義遊標：

Sql程式碼 

DECLARE @i INT  
SET @i = 1  
WHILE @i<1000000 BEGIN  
set @i=@i+1  
END

Sql程式碼 

DECLARE @cur1 CURSOR FOR SELECT .........  
  
OPEN @cur1  
FETCH NEXT FROM @cur1 INTO 变量  
WHILE(@@FETCH_STATUS=0)  
BEGIN  
处理.....  
FETCH NEXT FROM @cur1 INTO 变量  
END  
CLOSE @cur1  
DEALLOCATE @cur1

臨時表：

-- SelectTOTOTO 資料不會回傳給客戶端，這一點和普通的Select 不同。 新表格的欄位具有和 Select 的輸出欄位相關聯（相同）的名字和資料類型。

        select * into NewTable

            from Uname

-- Insert INTO ABC Select 的欄位Username複製到表ABC

        Insert INTO ABC Select Username FROM Uname

-- 創建臨時表
        Create TABLE #temp(
            UID int identity(1, 1) PRIMARY K         Pwd varchar(50),
            Age smallint,
          
--開啟臨時表
        Select * from #temp

 

 1、局部臨時表（#開頭）只對目前連線有效，當連線中斷時自動刪除。
2、全域臨時表（##開頭）對其它連接也有效，在當前連接和其他訪問過它的連接都斷開時自動刪除。
3、不管局部臨時表還是全域臨時表，只要連接有存取權限，都可以用drop table #Tmp(或drop table ##Tmp)來明確刪除臨時表。

臨時表對執行效率應該影響不大，只要不是太過份，相反可以提高效率特別是連接查詢的地方，只要你的資料庫臨時表空間足夠
遊標多，會嚴重執行效率，能免則免！

其他：

--有輸入參數的預存程序--

create proc GetComment

(@commentid int)

as

select * from Commentid int)

as

與輸出參數的預存程序--

create proc GetCommentCount

@newsid int,

@count int output

as

select @count

--傳回單一值的函數--

create function MyFunction

(@newsid int)

returns int

as

begincount where NewsID=@newsid

return @count

end

 

--呼叫方法--

declare @count int

exec @count=MyFunction 2值。為表格的函數--

Create function GetFunctionTable

(@newsid int)

returns table

as

return

(selectlect

as

return

(selectlect

as

🎇表的函數的呼叫--

select * from GetFunctionTable(2)

 

 

---------------------------- -------------------------------------------------- -------------------------------------------------- ---

SQLServer 預存程序中不拼接SQL字串實作多條件查詢

　以前拼接的寫法
　　set @sql=' select * from table where 1=1 '
　　if (@addDate is not null) 
　　 set @sql = @sql+' and notdate @name '' and is not null) 
　　 set @sql = @sql+ ' and name = ' + @name + ' '
　　exec(@sql)
下面是不採用拼接的解字串實作多條件查詢的解題字串實作多條件查詢方案
　　第一種寫法是 感覺代碼有些冗餘
　　if (@addDate is not null) and (@name '') 
　　 select * from table　 ( addDate is not null) and (@name ='') 
　　 select * from table where addDate = @addDate 
　　else if(@addDate is null) and (@name 　　else if(@addDate is null) and (@name = '') 
　　select * from table 
　　第二種寫法是 wh
　〕　se) = @name or @name = '') 
　　第三種寫法是 
　　SELECT * FROM table where 
　　addDate = CASE @addDate IS name END

 

-------------------------------------------- -------------------------------------------------- -------------------------------------

 

SQLSERVER預存程序基本語法

 

一、定義變數

--簡單賦值    

declare @a  int    

set @a=5     

🠎print @a  

declare @user1 nvarchar(50)     

select @user1= '張三'    

print @user1     

declare @user2 nvarchar(50)     

print @user2     

--使用update語句賦值    

declare @user3 nvarchar(50)     

update ST_User  set @user3 =  Name where ID=1   

--建立臨時表1     

create table #DU_User1     

(     

      [ID] [ int ]   NOT  NULL ,     

      [Login] [nvarchar](50)  NOT NULL ,     

  (4)  NOT NULL ,     

      [ Name ] [nvarchar](5)  NOT NULL ,    ,     

      [State] [nvarchar](8)  NOT NULL    

);

--插入一記記錄    

insert into #DU_User1 (ID,Oid,[Login],Rtx, Name ,[ Password ],State)  values (100, '臨時' , '321' , '特殊' );     

--從ST_User查詢數據，填入新產生的臨時表   

select * her 

--查詢與兩個臨時表    

select *  from #DU_User2  where ID

drop table #DU_User2    

 

- -建立臨時表    

CREATE TABLE #t     

(     

      id] [ int ]  NOT NULL ,     

     [Login] [nvarchar](50)  NOT NULL ,     

     [Rtx] [nvarchar](4)  NOT NULL ,     

     [ Name  word ] [nvarchar]( max )  NULL ,     

     [State] [nvarchar](8 )  NOT NULL ,     

)     

--將查詢結果集（多重資料）插入臨時資料表

--不能這樣插入    

--select * into # t from dbo.ST_User     

--新增一列，為int型自增長子段    

alter table # add 全球唯一標誌    

alter table #t  add [myid1] uniqueidentifier  NOT NULL default (newid())     

🠎 table #t    

--增加自增長列    

--無主鍵時：     

select IDENTITY( int ,1,1) as ID,  Name ,[Login],[ Password ] 頎#t     

--有主鍵時：     

select ( select SUM (1)  from ST_User  where IDdeclare @t  table    

(     

     id int not null ,     

     msg nvarchar(50)  null    

)     🠎🠎insert ) 1.

insert into @t  values (2, '2' )     

select *  from @t

 三、循環

--while循環計算1到100的和    

declare @a  int    

set @ sum =0     

while @abegin    

     set @ sum +=@a     

 

print @ sum    

四、條件語句

--if,else條件分支    

if(1+1 =2)     

begin    

     print  '對'    

end    

print  '錯誤'    

end    

--when then條件分支    

declare @today  int    

declare @today  int    

declare @today  int  week nvarchar(3)     

set @today=3     

set @week= case    

    when @today=2  then '星期二'    

     when @today=3  then '星期三'    

     when @today=4  then '星期四'    

      then '星期六'    

     when @today=7  then '星期日'    

     else '價值錯誤'    

end    

print @week    

 

五、遊標

declare 

declare @Login  varchar (50)     

--定義一個遊標    

declare user_cur  cursor for select ID,Oid,[Login]  from ST_User     

--開啟遊標    

while @@fetch_status=0     

begin    

--讀遊標    

    

     print @ID     

     --print @Login     

end    

close user_cur     

--摧毀遊標    

deallocate user_cur    

六、觸發器

o

　sere進行觸發update 操作後的資料 

　　Deleted 

　　存放進行delete 和update操作前的資料

--建立觸發器    

Create trigger User_OnUpdate      

     

As      

     declare @msg nvarchar(50)     

   姓名從「' + Deleted. Name + N '」修改為「' + Inserted. Name +  '」' fromInserted,Deleted     

(MSG) values (@msg)     

--刪除觸發器    

drop trigger User_OnUpdate    

七、儲存過程

--

     @a  int ,     

     @b  int ,     

   sum int output    

AS    

BEGIN    

     set @ sum 回值預存程序    

CREATE PROCEDURE PR_Sum2     

     @a  int ,   

AS    

BEGIN    

     Return @a+@b     

END  

declare @mysum  int    

execute PR_Sum 1,2,@mysum  output    

print @mysum     

--執行預存程序取得Return型回值    

declare @mysum2  int    

, 15555ute  

 

   

八、自訂函數

函數的分類：

　　　　1）標量值函數

　　　　2）表值函數

　　　　　　㟎　　b:多語句表值函數

　　　　3）系統函數

 

--新建標量值函數    

create function FUNC_Sum1     

(     

    

)     

returns int    

as    

begin   

--新內嵌表值函數    

create function FUNC_UserTab_1     

(     

   table    

as    

return ( select *  from ST_User  where ID

-新語句表值函數    

create function FUNC_UserTab_2     

(     

  s @t  table    

(     

     [ID] [ int ]  NOT NULL , ]  NOT NULL ,     

     [Login] [nvarchar](50)  NOT NULL ,     

     [ ] [nvarchar](5)  NOT NULL ,     

     [ Password ] [nvarchar]( max )  NULL ,     

     [State] [nvarchar](8)  NOT NULL    

)    insert into @t  select *  from ST_User  where ID

     return   

     return   

--調用表值函數    

select *  from dbo.FUNC_UserTab_1(15)     

--調用標量值函數🠎     

--調用標量值函數🠎 FUNC_Sum1(100,50)     

print @s     

--刪除標量值函數    

drop function FUNC_Sum1    

談談

　　2 . 限制頗多，包括

　　　　不能使用output參數；

　　　　　　不能使用output參數；

　　　　　〜㟎

　　　　不能update，delete，資料庫表;

　　3. 必須return 一個標量值或表變量

　　自訂函數一般用在復用度高，功能簡單單一，爭對性強的地方。

二、預存程序

　　1. 不能回傳表變數

　　2. 限制少，可以執行資料庫表的操作，可以傳回資料集

　　3. 可以執行對資料庫表的操作，可以回傳資料集

　　3. 可以省略一個標量預存程序一般用在實現複雜的功能，資料操縱方面。

 

---------------------------------------------- -------------------------------------------------- -----------------------------------

SqlServer預存程序--實例

實例1：只傳回單一記錄集的預存程序。

　　表銀行存款表（bankMoney）的內容如下

要求1：查詢表bankMoney的內容的預存程序

create procedure sp_query_bankMoney的內容的預存程序

create procedure sp_query_bankMoney的內容的預存程序

集

註*  在使用過程中只要把T-Sql中的SQL語句替換為預存程序名，就可以了很方便吧！

實例2（傳遞參數給預存程序）：

加入一筆記錄到表bankMoney，並查詢此表中userID= Zhangsan的所有存款的總金額。

Create proc insert_bank @param1 char(10),@param2 varchar(20),@param3 varchar(20),@param4 int,@param5 int output

with encryption ---------加密🜎as

insert into bankMoney (id,userID,sex,Money)

Values(@param1,@param2,@param3, @param4)select @param5=sum(Money) from bankMoney where userID='Zhangsan'

SQLgo查詢分析器中執行此預存程序的方法是：

declare @total_price int

exec insert_bank '004','Zhangsan','男',100,@total_price output

print '總餘額為'+convert(varchar,@ total_price)
go

在這裡再囉嗦一下儲存過程的3種傳回值（方便正在看這個例子的朋友不用再去查看語法內容）:

1.以Return傳回整數

2.以output格式傳回參數

3.Recordset

傳回值的區別:

output和return都可在批次程序中用變數接收,而recordset則傳回到執行批次的客戶端。

實例3：使用帶有複雜 SELECT 語句的簡單過程

　　下面的預存程序從四個表的聯接中返回所有作者（提供了姓名）、出版的書籍以及出版社。該預存程​​序不使用任何參數。

　　USE pubs
IF EXISTS (SELECT name FROM sysobjects
         WHERE name = 'au_info_all' AND type = 'P.S. info_all
AS
SELECT au_lname, au_fname, title, pub_name
   FROM authors a INNER JOIN titleauthor ta
      ON a.au_id = ta.au_id INNER JOIN titles t

      ON t.title_id = ta.title_id INNER

　　au_info_all 預存程序可以透過以下方法執行：

　　EXECUTE au_info_all
-- Or
EXEC au_info_all

『

實例4：使用帶有參數的簡單製程

　　CREATE PROCEDURE au_info

   @lastname varchar(40),

   @firstname varchar(20)

ASAS
SELECT au_lNERname, comfido​​ion cador _tle​​p. author ta
      ON a.au_id = ta.au_id INNER JOIN titles t
      ON t.title_id = ta.title_id INNER JOIN publishers p
      ON t.pub_id = p.pub_id
      ON t.pub_id = p.pub_id au_lname = @lastname
GO

　　au_info 預存程序可以透過以下方法執行：

　　EXECUTE au_info 'Dull', 'Ann'
-- Or

EXECUTE au_info @lastname = 'Dull', @firstname = 'Ann'

-- lastname = 'Dull'

-- Or

EXEC au_info 'Dull', 'Ann'
-- Or
EXEC au_info @lastname = 'Dull', @firstname = 'Ann'
-- Or
EXEC au_info @firstname = 'Ann'
-- Or
EXEC au_info @firstname = '----------------
EXEC Ann', @lastname = 'Dull'

　　如果過程是批次中的第一個語句，則可使用：

　　au_info 'Dull', 'Ann'

-- Or

au_info @lastname = 'Dull' , @firstname = 'Ann'

-- Or
au_info @firstname = 'Ann', @lastname = 'Dull'

 

 實例5：使用帶有通配符參數的簡單過程

 實例5：使用帶有通配符參數的簡單過程

info_au_au lastname varchar(30) = 'D%',

@firstname varchar(18) = '%'
AS
SELECT au_lname, au_fname, title, pub_name
FROM authors a INNER JOIN tiauthorta🜎a. INNER JOIN titles t
   ON t.title_id = ta.title_id INNER JOIN publishers p
   ON t.pub_id = p.pub_id
WHERE au_LIfname LIKE @firstname🎠　au_info2 預存程序可以用多種組合執行。下面只列出了部分組合：

　　EXECUTE au_info2
-- Or
EXECUTE au_info2 'Wh%'
-- Or

EXECUTE au_info2 @firstname = 'A%'

--o

EXECUTE au_info2 @firstname = 'A%'

--Es
--ECK_au] OE]n'
-- Or
EXECUTE au_info2 'Hunter', 'Sheryl'
-- Or
EXECUTE au_info2 'H%', 'S%'

　　= 'proc2'


   

存儲過程，其中@case作為執行update的選擇依據，用if...else實現執行時根據傳入的參數執行不同的修改. 

--下面是if…else的存儲過程： 

if exists (select 1 from sysobjects where name = 'Student' and type ='u' )

. e name = 'spUpdateStudent' and type ='p' )

drop proc spUpdateStudent

go

create table Student
(
fName nvarchar (10),
fAge 👎
)
go

insert into Student values ('X.X.Y ' , 28, 'Tesing' , 888888)
go

create proc spUpdateStudent
(
@fCase int ,

@fName nvarchar

@fCase int ,
@fName nvarchar ,Dinvarchar (50),
@fTel  int 
)
as 
update Student
set fAge = @fAge, -- 傳1,2,3 都要更新fAge 不需要用case 
fDiqu = (case 之一)、
fTel  = (case when @fCase = 3 then @fTel else fTel end )
where fName = @fName
select @fCase = 1,
@ fName = N'X.X.Y' ,
@fAge = 80,
@fDiqu = N'Update' ,
@fTel  = 1010101

-- StuAge 及Diqu
execmUppate
'X.X.Y' ,
@fAge = 80,
@fDiqu = N'Update' ,
@fTel  = 1010101

--全改
exec spUpUpdateStud.
@fAge = 80,
@fDiqu = N'Update' ,
@fTel  = 1010101