[T-SQL] T-SQL 基本介紹_筆記

變數形式

變數的形式主要分為兩大類

區域變數：使用者自訂的變數。

區域變數以@開頭。

如：@n

全域變數：屬於系統變數，以@@開頭。

例如：@@ERROR

變數宣告

變數的宣告方式，是在變數前方加上Declare單字，後方加上要指定給該變數的資料型態。

DECLARE @n int

Declare @cname varchar(10)

註：T-SQL語言中，語法的大小寫並沒有差別。

變數的設定與顯示

設定變數有以下兩種方法：

select @n = 100
set @a = 120

若要顯示變數的內容，則是如下方：

select @n

將select的結果放入變數

/*將A01的姓名放入變數中*/
declare @cname varchar(20)
select @cname = cname
from userinfo
where uid = 'A01'

IF_判斷

IF @i > 10
BEGIN
// 判斷式成立
END
ELSE
BEGIN
// 判斷式不成立
END

註：如果 Begin End 中間只有單行程式碼，Begin End可省略不打。

IF @i > 10
// 判斷式成立
ELSE
// 判斷式不成立

CASE_多個條件判斷

CASE語法主要是針對多個條件判斷需求所使用，但原則上多個條件判斷也可使用if...else。

declare @cname varchar(50)
select @cname = cname from userinfo where uid = 'A03'

print CASE @cname
when '王力宏' then '姓王'
when '周杰倫' then '姓周'
else '不知姓什麼'

End

WHILE_迴圈

Declare @i int --迴圈要執行幾次的變數

Set @i = 0 --設初始值為零
while @i < 10 --當變數i小於10
BEGIN
if @i = 5 --當變數i等於5
begin
set@i=7  --設變數為7
continue  --以下直接不做，跳回到while

end
if @i = 9  --當變數等於九時
break       --程式停止直接跳離

print @i  --顯示當下的變數值

set @i = @i + 1  --變數每一次的增加量
END

註：Continue & Break 只用在 WHILE 迴圈。

GOTO_流程控制

程式會直接跳到GOTO所指定的標籤位置，中間跳過的區域全都不執行。

結構複雜的程式碼非常不建議使用，因為容易造成流程混亂！

如需進行流程控制，建議使用IF條件判斷較佳。

GOTO Label 
/*很多行程式碼*/
Label:
/*很多行程式碼*/

ERROR HANDLER_錯誤控制

每一個SQL Command 執行完, 系統都會將錯誤編號放到@@ERROR 變數中, 如果沒有錯誤發生, @@ERROR 等於 0。

begin transaction
update userinfo set cname = '朱小妹' where uid = 'A04'  /*假設這行程式碼成功執行*/
insert into userinfo values ('A01', '沈月')  /*假設這行程式碼有錯誤造成失敗*/

if @@error != 0  /* 變數為1 大於零*/
rollback   /*全部指令rollback, 資料並無更新與新增*/
else     commit

**ERROR變數只存放離它最近的執行結果，因此若上方兩行update&insert的程式碼對調，結果將不同！

begin transaction
insert into userinfo values ('A01', '沈月')  /*假設這行程式碼有錯誤造成失敗*/
update userinfo set cname = '朱小妹' where uid = 'A04'  /*假設這行程式碼成功執行*/

if @@error != 0 /* 變數為零*/
rollback
else commit /*程式commit, A04資料更新成功*/

同場加映：如何修正上方的問題？

begin transaction
declare @myerror int = 0

insert into userinfo values ('A01', '沈月')
set @myerror = @myerror + @@ERROR
update userinfo set cname = '朱小妹' where uid = 'A04'
set @myerror = @myerror + @@ERROR

if @@error != 0
rollback
else
commit

@@ROWCOUNT_影響的資料筆數

紀錄SQL Command執行後所影響的資料筆數。

update userinfo set pwd = NULL
print @@rowcount
/*若成功更新10筆資料，則@@rowcount等於10*/

同場加映：建立防止資料被全部刪除的Trigger

CREATE TRIGGER tr_userinfo_all ON userinfo FOR UPDATE,insert, delete
AS
if @@ROWCOUNT > 1
begin rollback
END

STORED PROCEDURE_預存程序

將T-SQL程式碼儲存在資料庫中並給予一個名字，需要時再以程式碼呼叫執行。
資料庫內建的預存程序以sp_開頭命名。
例如sp_databases(查詢目前有幾個資料庫)、sp_tables(查詢目前有幾個資料表)

執行預存程序

EXEC sp_database

建立預存程序

語法:

CREATE PROCEDURE p_userinfo
AS

範例

CREATE PROCEDURE p_userinfo
AS
select * from userinfo

有參數的預存程序

輸入UID，傳回UID的姓名。

/* 設定兩個參數, @uid為輸入的參數, @cname指定為output參數*/
CREATE PROCEDURE p_getCnameByUID
@uid NAVARCHAR(50)
@cname NVARCHAR(50) OUTPUT  --呼叫完的結果會從cname回傳
AS
select @cname = cname
from userinfo
where uid = @uid

執行有參數的預存程序

透過宣告一個新變數，使其透過新變數回傳結果。

BEGIN
DECLARE @s NVARCHAR(50)  /*先宣告一個變數S, 讓結果可以存放跟傳出*/
EXEC p_getCnameByUID 'A01', @s OUTPUT
/*第一個參數把'A01'傳進去, 第二個參數@s把結果回傳(因為是結果所以一定要是個變數)*/
/*執行參數的後方一定要加上OUTPUT*/
print @s
END

FUNCTION_函數

語法:

CREATE FUNCTION f_name(parameter list)

RETURNS (return type) AS
BEGIN


END

範例:

CREATE FUNCTION f_add  (@v1 FLOAT, @v2 FLOAT)
RETURNS FLOAT
AS
BEGIN
RETURN @v1 + @v2
END
/*這個Function含有兩個變數v1,v2，資料型態皆為float，設定回傳值也是float。程式碼從begin開始，回傳兩參數相加的值。*/

預存程序與函數的差異: 預存程序沒有RETURN

預存程序透過變數或裡面的select回傳結果，但函數則是透過RETURN回傳結果。

函數程式碼需放在BEGIN& END中間，但預存程序沒有。

BY THE WAY

SP&Function實務上不常用，因為可在外界用其他程式語言寫，且TSQL只限定在SQL使用，換其他的資料庫如Oracle等資料庫則無法使用。 (資料可搬過去但是SP/F無法)

業界徵人說要T-SQL但是好像不多? 

且真正懂TSQL的可能不多，所以老師說今天上完要說自己會了哦XD

函數呼叫

select dbo.f_add(4,3)

函數的傳回值型別

純量型別：

1. 例如 int, char, varchar,...etc.

2. 呼叫方式 select f1(fee) from bill [放在select後面]

資料表型別：

1. 傳回recordset，型態為table

2. 呼叫方式：select * from f1() [因為它是資料表所以select時要放在from後面!]

行內資料集函數_Inline Table-valued Function

AS內只有一行，因此沒有 BEGIN END
與 View 的差別在於可以輸入參數

範例：

CREATE FUNCTION f1(@lastname varchar(4))
RETURNS TABLE
AS 
RETURN (
select *
from userinfo 
where cname like @lastname + '%'
)

多敘述資料集函數_Multi-statement Table-valued Function

範例：

CREATE FUNCTION f2() RETURNS @t TABLE
(
a nvarchar(20),
b nvarchar(20)
) 
AS  
BEGIN 
insert @t select uid, cname from userinfo where cname like '王%'
insert @t select 'C01', '丁小雨'
return
END

**Table前多了一個變數，變數名字可以隨便取。
**可以直接把某一個查詢結果insert到這個變數裡面。(手動填了一個資料到create表格內)
**可以客製化這個表格內的內容
**’C01’,’丁小雨’insert進入的，沒有在資料庫內。(類似union all)

CURSOR_資料指標

對每一筆資料作最細微的控制，可以單獨處理每一筆資料。
需要一筆一筆處理資料時可以用。

例如：將阿拉伯數字轉成大寫國字
1 -> 壹元
203 -> 貳佰零叁元

解法....

建立、開啟、關閉CURSOR

DECLARE c CURSOR FOR 
select * from bill
OPEN c
…
CLOSE c
DEALLOCATE c

4 steps
1.建立cursor：cursor一定跟查詢有關。 宣告一個變數(投影片是c)資料型態是cursor, for 某一個 SQL Command (指向某一個查詢結果)
2.開啟 cursor: open c (cursor像是一個指標，指向title標題列的位置。)
3.開始使用
4.關閉 cursor: close c (記憶體才會釋放)
5.移除cursor所占用的記憶體: deallocate c
FETCH

Fetch: 取到查詢出來的每一筆資料
Fetch c into @fee (指標移動往下一筆，並把資料放到@fee裡。每執行fetch一次就會往下一筆移動。)
用while迴圈偵測是否有沒有資料
Fetch_status 為零 代表有資料
當指到沒有資料時，fetch_status就不會為零。
**除非原本的SQL Command無法處理一筆一筆資料，再用cursor。
範例：列出每一筆電話費用

BEGIN
declare c cursor for select fee from bill
declare @fee int
open c
fetch c into @fee
while (@@fetch_status = 0)
begin
print @fee
fetch c into @fee
end
close c
deallocate c
END

同場加映：透過Cursoru異動資料

例子：將沒有密碼的新會員指定一個預設密碼

BEGIN
declare c cursor for select uid from userinfo where pwd is null for update
declare @pwd nvarchar(50)
open c
fetch c
while (@@fetch_status = 0)
begin
set @pwd = right(convert(varchar, rand()), 4) --亂數生成的密碼
update userinfo set pwd = @pwd where current of c --update目前c指向的資料
fetch c
end
close c
deallocate c
END

/*for update_修改透過cursor所指向的資料。*/

另外一個做法

**也可以用cursor+fetch做。先把要改的uid抓出來，再針對他執行修改PWD。

Declare c cursor for select uid  from userinfo where pwd is null
Declare @uid nvarchar(50)
Open c
Fetch c
While(@@fetch_status = 0)
Begin
Update userinfo set pwd = right(conver(varchar, rand(), 4)where uid = @uid
Fetch c
End
Close c
Deaalocate c
End