[DB] Stored Procedure On SQL Server

老實說，我很少寫 Stored Procedure，上次寫也已經是2013年的事了。

最近要寫一張報表，因為表格每一個欄位計算複雜，所以不得不使用到 Stored Procedure 事先計算好，報表負責呈現就好，才不必等太久。

這次 DB 是 SQL Server，想說參考之前 Oracle 的 Stored Procedure 文章看看應該就行了，沒想到差異不小@@，就基本的跑迴圈來說就完成不同了。

Google 了一下大都用 Cursor，但是普遍說效能不好不建議，加上語法不好理解(對我來說...)，然後找到以下的方法，覺得很適合我，便記錄下來當參考。

declare @Count

--將要跑迴圈的資料抓出來放到#TempTable
Select Product_ID into #TempTable From Product 

--計算 #TempTable 資料筆數，要用在跑迴圈
Select @Count = count(Product_ID) from #TempTable 

--開始跑迴圈
while(@Count > 0)
begin
    Select top 1 @Product_ID=Product_ID From #TempTable 

    --start do something 

    --將處理好的資料從 #TempTable 刪除
    Delete from #TempTable where Product_ID=@Product_ID   

    --重新計算 #TempTable 資料筆數，要用在跑迴圈
    Select @Count = count(Product_ID) from #TempTable
end

參考來源：遜砲賴的爆肝筆記

[DB] 指定欄位某些值優先排序

某欄位裡有值 0~9，使用 Order by 排序不是由小到大(Asc)，就是由大到小(Desc)。

今日遇到需求是 5 要排第一個，其他值由小到大排序。依照上面的觀念，馬上就跟他說不可能。

回頭想想，這種需求在某些情況下確實真的會發生，於是 Google 看看，沒想到 SQL 真的辦的到，而且不需要用到內建的 function，特此記錄一下。

P.S：以下範例不是唯一的方法，當然還有其他方法也能做到。


下圖是基本的由小到大排序(Asc)：

指定欄位某個值排在第一個，如下圖 5 優先排在第一個，其他預設排序(Asc)：(註：case 後面的 else 只要比前面的數字大即可。)

指定多個值也行，如下圖 5 優先排在第一個，2 排在第二個，其他預設排序(Asc)：

如果 case 後面的 else 數字比前面的數字小，那就是排在最後面，如下圖的 0：

參考來源：趕知識

[DB] SQL Server Express 如何自動備份

SQL Server 的備份通常都是設定維護計劃來達到自動備份，但 Express 沒有維護計劃，因此微軟有教學如何使用排程來自動備份，完整教學請看這裡。

下面是我將這教學改成適合自己用的，很簡單明暸程式又短，提供出來給大家參考：

(1) 建立 .sql 指定好要備份的 DB，假設檔名是 BackUpDB.sql ：

DECLARE @DBName varchar(20)
Declare @FileName Varchar(50)
Declare @Folder Varchar(50)
Declare @DateTimes Varchar(20)

--要備份的資料庫名稱
SET @DBName='NorthWind'

--儲存備份檔的路徑
SET @Folder='D:\DBbackUp\'

--定義備份檔名稱，後面我加上年月日yyyymmdd
SET @DateTimes = Convert(varchar(20), GETDATE(), 112)
SET @FileName=@Folder + @DBName + '_' + @DateTimes + '.BAK'

--執行備份
BackUp Database @DBName To Disk=@FileName

(2) 建立一個批次檔，用來指定排程去執行 BackUpDB.sql ，假設檔名是 BackUpDB.bat：
sqlcmd -S 192.168.192.10\SQLEXPRESS -E -i D:\Temp\BackUpDB.sql
exit

(3) 再來就是將 BackUpDB.bat 設定到排程去執行即可。


參考來源：Microsoft

[DB] 如何用 IP 來連接 SQL Server Express

SQL Server 剛安裝好時，預設 TCP/IP 通訊協定是關閉的，如果想用 SQL Server Management Studio 以 IP 的方式連接 SQL Server 會發生以下錯誤訊息：

Google 後大部份都教你去開啟 TCP/IP 、指定 Port 成 1433，最後重啟 SQL Server Service即可(範例)。

但發垷 Express 版本還需要做個動作才行，就是要啟動 SQL Server Browser，原因可參考這裡。

這點總是會忘記，特別記錄下來，必竟通常只有在測試時才會用到 Express 版本。



參考連結：StackOverflow、Microsoft

[DB] SQL Server 還原 DB 後 ReLink 帳號權限

SQL Server 的備份/還原 DB 功能很方便，因為還原後的帳號權限設定都還在，不需要再次設定，只是如果帳號也是重建的，那因為帳號的 ID 已經不同了，必須下指令 ReLink 這些帳號才行，指令如下：

EXEC sp_change_users_login 'Update_One', '你的帳號', '你的帳號'

參考來源：esri

[DB] SQL Server 附加資料庫會存取被拒的原因

自從接觸 SQL Server 至今，在更換主機時，附加資料庫偶而會遇到存取被拒，解決方法都是將權限開到最大，或是改用備份/還原的方式，但這方式成功後還需要手動將 DB 權限 Link 回來，方法記錄在這篇裡。

靜下心來 Google 找出真因如下：

如果是用 Windows 驗證方式來登入並缷離 DB，DB 實體檔案會將 MSSQL$SQLEXPRESS 的權限刪掉，但保留 Administrators 群組權限。

反之，如果是用 SQL 驗證方式來登入並缷離 DB，DB 實體檔案會將 Administrators 的權限刪掉，但保留 MSSQL$SQLEXPRESS 群組權限。

所以結論是你用哪種驗證方式缷離了 DB，就要用哪種方式來附加，就可以成功了。

參考連結：Rock的SQL筆記

[DB] 使用 SQL 將多筆資料轉成一筆 XML 資料

在 Master、Detail 資料結構中，如果要在 Master 清單中同時也顯示出 Detail 的資料，通常做法是先抓出 Master 資料，跑迴圈再查詢 Detail 抓出資料，也就是要分兩次 SQL 來進行。

如果只想用一個 SQL 就同時抓出 Master 和 Detail 資料，就會想用子查詢的做法，而當 Master 對應的 Detail 資料回傳不只一筆時，就會出現如下錯誤：

這時可以使用 FOR XML Path 來解決這個問題，它可以將子查詢 Detail 回傳的多筆資料轉成一筆 XML 格式，如此就沒有回傳不只一筆資料的問題了：

以上這個範例中，Path('') 是指不加入任何的 XML tag，單純將欄位組合成字串就好，預設不加東西的結果是會將資料加上 <row> tag 的：

P.S：Path 只是其中一種用法，詳細可參考Microsoft MSDN。



參考來源：黃昏的甘蔗

[DB] 圖示表示 SQL 的 JOIN 概念

INNER JOIN

select 
 * 
from 
 Table1 t1
inner join 
 Table2 t2 
on 
 t1.kol1 = t2.kol1

LEFT (OUTER) JOIN

select 
 * 
from 
 Table1 t1
left join 
 Table2 t2 
on 
 t1.kol1 = t2.kol1

RIGHT (OUTER) JOIN

select 
 * 
from 
 Table1 t1
right join 
 Table2 t2 
on 
 t1.kol1 = t2.kol1

FULL (OUTER) JOIN

select 
 * 
from 
 Table1 t1
full join
 Table2 t2 
on 
 t1.kol1 = t2.kol1

LEFT (OUTER) JOIN, WHERE IS NULL

select 
 * 
from 
 Table1 t1
right join 
 Table2 t2 
on 
 t1.kol1 = t2.kol1
where 
 t2.kol1 IS NULL

FULL (OUTER) JOIN, WHERE IS NULL

select 
 * 
from 
 Table1 t1
right join 
 Table2 t2 
on 
 t1.kol1 = t2.kol1
where 
 t1.kol1 is NULL or t2.kol1 IS NULL

參考來源：yazilimmutfagi

[DB] Stored Procedure On Oracle

簡單整理一下Oracle的Stored Procedure(SP)的基本寫法~
P.S：C#如何呼叫SP可以參考此篇。

下面是SP的主要架構(SP大小寫沒有差別)：
此SP命名為SP1。
sYEAR和sNAME是呼叫SP時要輸入的參數(sNAME有預設值TIM)，sRETURN_VALUE是SP執行結束後會輸出的參數。

create or replace 
PROCEDURE SP1 (sYEAR NUMBER, sNAME VARCHAR2 DEFAULT 'TIM', sRETURN_VALUE OUT VARCHAR2) 
AS
  -- 定義變數區
BEGIN
  -- 主要執行區
END SP1;

定義變數區的範例：

--NUMBER
nYEAR NUMBER;

--INTEGER
iINTEGER PLS_INTEGER := 1;

--VARCHAR要指定好長度
sADDRESS VARCHAR2(20); 

--變數為 Not Null時，須指定預設值
sNOT_NULL VARCHAR2(4) NOT NULL := 'test';

主要執行區的範例：

--IF的用法
IF nYEAR IS NULL THEN
 BEGIN
  nYEAR := '10';
  sADDRESS := '台灣省台中市';
 END;
ELSE
 BEGIN
  nYEAR := '99';
  sADDRESS := '台灣省台北市';
 END;
END IF;

--FOR迴圈的用法：用SELECT指令找出資料集，再用LOOP來跑迴圈
FOR row_TEST IN (SELECT YEAR,ADDRESS from tblTEST)
LOOP
  -- row_TEST代表每一個RECORD，用下面的方法很方便的就能取到讓RECORD的某個欄位值
  sRETURN_VALUE := row_TEST.YEAR;
END LOOP;

--單純用SELECT取一個值：存放值的變數要先定義於定義變數區
SELECT ADDRESS INTO sADDRESS FROM tblTEST WHERE YEAR='99';

--呼叫另一個SP，並傳入一個參數過去.
--P.S：如果此SP_OTHER不想包含於SP1同一個Transaction，那可以在SP_OTHER的定義變數區最後加上PRAGMA AUTONOMOUS_TRANSACTION;
SP_OTHER(sYEAR);

--印一個資訊出來，方便用於測試時
dbms_output.put_line(sYEAR);

Transaction的範例(寫在主要執行區最下面)：

--commit
COMMIT;

--發生Exception時就RollBack
EXCEPTION
WHEN OTHERS
THEN 
 sRETURN_VALUE := '執行失敗，請洽系統管理員';

 --也可在此再做些產生Exception時的動作，例如寫Log檔

 --RollBack
 ROLLBACK;

FUNCTION的寫法跟SP很像：
此function命名為Fun1。
sNAME是呼叫function時要輸入的參數，執行結束後會回傳一個VARCHAR。

create or replace 
FUNCTION Fun1 (sNAME VARCHAR2) RETURN VARCHAR2
AS
  -- 定義變數區
BEGIN
  -- 主要執行區
  ...
  ...
  RETURN '執行成功';
END;

由於早期Oracle沒有內建可以分割字串的功能，因此必須自己動手來做分割字串。在此分享一個在StackOverflow找到的SPLIT字串的function，只要餵入",a,b,c,d"，就會分別回傳abcd，使用範例如下：

create or replace 
PROCEDURE TEST
AS
    i PLS_INTEGER := 1;
BEGIN
 LOOP
  -- 注意！輸入的字串開頭要有一個逗號
  str := SPLIT(',A,B,C,D', i, ',');
  EXIT WHEN str IS NULL;
  dbms_output.put_line(str);
  i := i + 1;
 END LOOP;
END;

--執行結果：
--A
--B
--C
--D

參考來源：StackOverflow

[DB] 用timestamp當成Oracle的自動編號流水號

Oracle並沒有像SQL Server一樣方便設定不重覆的流水號欄位，必須建立Sequence才行。

如果你只是要一個不重覆的欄位來當Key值就好，並不需要有規則，這時就可以用內定參數current_timestamp來用。

current_timestamp可以產生系統目前的時間，格式類似2012/09/11 14:20:22.9877460，因為它取到的值很小，所以不怕會重覆。

建立方式很簡單，先新增一個欄位，欄位型態選用TIMESTAMP，以及設定成不可Null，然後在Default Value填入current_timestamp即完成。

如果你想在已存在內容的Table加上這種欄位，那你要先將此Table的內容匯出備份起來，再把原有的內容都刪掉，之後如上述方法新增好欄位後再匯入備份的資料，此欄位在匯入時就會自動產生不同的current_timestamp並填入了，而且不會重覆。


以上方式是用Oracle 10g 搭配PL/SQL管理工具。

[DB]Oracle / MySQL / SQLServer 常見function區別

1.類型轉換

--Oracle
select to_number('123') from dual; --123; 
select to_char(33) from dual;  --33;
select to_date('2004-11-27','yyyy/mm/dd') from dual;--2004-11-27

--Mysql
select cast('123' as signed integer); --123 
select cast(33 as char(2));  --33；
select to_days('2000-01-01');  --730485

--SqlServer 
select cast('123' as decimal(30,2)); --123.00
select cast(33 as char(2));  --33；
select convert(varchar(12) , getdate(), 120)

2.四捨五入

--Oracle
select round(12.86*10)/10 from dual;    --12.9

--Mysql
select format(12.89,1);   --12.9 

--SqlServer
select round(12.89,1);   --12.9 

3.日期時間

--Oracle
select sysdate from dual;  --日期時間 

--Mysql
select sysdate();   --日期時間 
select current_date();   --日期

--SqlServer
select getdate();   --日期時間
select datediff(day,'2010-01-01',cast(getdate() as varchar(10)));--日期相差天數

4.Decode

--Oracle 
select decode(sign(12),1,1,0,0,-1) from dual;--1 

--Mysql/SqlServer 
select case when sign(12)=1 then 1 when sign(12)=0 then 0 else -1 end;--1

5.判斷null

--Oracle
select nvl(1,0) from dual;  --1 

--Mysql
select ifnull(1,0);   --1 

--SqlServer
select isnull(1,0);   --1 

6.字串相接

--Oracle
select '1'||'2' from dual;  --12
select concat('1','2');   --12

--Mysql
select concat('1','2');   --12

--SqlServer
select '1'+'2';    --12

7.筆數限制

--Oracle
select 1 from dual where rownum <= 10;

--Mysql
select 1 from dual limit 10;

--SqlServer
select top 10 1

8.字串截取

--Oracle
select substr('12345',1,3) from dual;

--Mysql/SqlServer
select substring('12345',1,3);

9.把多行轉換成一合併列

--Oracle
select wm_concat(列名) from dual; --多行記錄轉換成一列之間用,分割

--Mysql/SqlServer
select group_concat(列名);

10.利用SELECT結果來CREATE TABLE

--Oracle
CREATE TABLE dept_bak AS SELECT * FROM dept;

--Mysql/SqlServer
SELECT * INOT t1 FROM titles

來源：藍色的博客

[DB]一些常見的SQL語法效能建議

1.對查詢進行優化，應儘量避免全表掃描，首先應考慮在 where 及 order by 涉及的列上建立索引。

2.應儘量避免在 where 子句中對欄位進行 null 值判斷，否則將導致引擎放棄使用索引而進行全表掃描，如：

select id from t where num is null
--可以在num上設置預設值0，確保表中num列沒有null值，然後這樣查詢：
select id from t where num=0

3.應儘量避免在 where 子句中使用!=或<>操作符，否則將使引擎放棄使用索引而進行全表掃描。

4.應儘量避免在 where 子句中使用 or 來連接條件，否則將導致引擎放棄使用索引而進行全表掃描，如：

select id from t where num=10 or num=20
--可以這樣查詢：
select id from t where num=10
union all
select id from t where num=20

5.in 和 not in 也要慎用，否則會導致全表掃描，如：

select id from t where num in (1,2,3)
--對於連續的數值，能用 between 就不要用 in ：
select id from t where num between 1 and 3

6.下面的查詢也將導致全表掃描：

select id from t where name like 'abc'

若要提高效率，可以考慮全文檢索。

7.應儘量避免在 where 子句中對欄位進行運算式操作，這將導致引擎放棄使用索引而進行全表掃描。如：

select id from t where num/2=100
--應改為:
select id from t where num=100*2

8.應儘量避免在 where 子句中對欄位進行函數操作，這將導致引擎放棄使用索引而進行全表掃描。如：

select id from t where substring(name,1,3)='abc' --name以abc開頭的id
select id from t where datediff(day,createdate,'2005-11-30')=0--‘2005-11-30’生成的id
--應改為:
select id from t where name like 'abc'
select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'

9.不要在 where 子句中的“=”左邊進行函數、算術運算或其他運算式運算，否則系統將可能無法正確使用索引。

10.在使用索引欄位作為條件時，如果該索引是複合索引，那麼必須使用到該索引中的第一個欄位作為條件時才能保證系統使用該索引，否則該索引將不會被使用，並且應盡可能的讓欄位順序與索引順序相一致。

11.很多時候用 exists 代替 in 是一個好的選擇：

select num from a where num in(select num from b)
--用下面的語句替換：
select num from a where exists(select 1 from b where num=a.num)

12.並不是所有索引對查詢都有效，SQL是根據表中資料來進行查詢優化的，當索引列有大量資料重複時，SQL查詢可能不會去利用索引，如一表中有欄位sex，male、female幾乎各一半，那麼即使在sex上建了索引也對查詢效率起不了作用。

13.索引並不是越多越好，索引固然可以提高相應的 select 的效率，但同時也降低了 insert 及 update 的效率，因為 insert 或 update 時有可能會重建索引，所以怎樣建索引需要慎重考慮，視具體情況而定。一個表的索引數最好不要超過6個，若太多則應考慮一些不常使用到的列上建的索引是否有必要。

14.應盡可能的避免更新 clustered 索引資料列，因為 clustered 索引資料列的順序就是表記錄的物理存儲順序，一旦該列值改變將導致整個表記錄的順序的調整，會耗費相當大的資源。若應用系統需要頻繁更新 clustered 索引資料列，那麼需要考慮是否應將該索引建為 clustered 索引。

15.儘量使用數字型欄位，若只含數值資訊的欄位儘量不要設計為字元型，這會降低查詢和連接的性能，並會增加存儲開銷。這是因為引擎在處理查詢和連接時會逐個比較字串中每一個字元，而對於數字型而言只需要比較一次就夠了。

16.盡可能的使用 varchar/nvarchar 代替 char/nchar ，因為首先變長欄位儲存空間小，可以節省儲存空間，其次對於查詢來說，在一個相對較小的欄位內搜索效率顯然要高些。

17.盡量不要用 select * from t ，用具體的欄位列表代替“*”，不要返回用不到的任何欄位。

18.HAVING 條件是在抓出所需資料後才做的過濾，跟一開始就寫在 WHERE 條件中是很像的：

select subject, count(subject) from tblA GROUP BY subject HAVING subject != 'Tim' AND subject != 'Lin'
--應改為:
select subject, count(subject) from tblA WHERE subject != 'Tim' AND subject != 'Lin' GROUP BY subject

19.減少子查詢數量，也能提升效能：

select name from tblA where salary = (select MAX(salary) FROM tblB) and age = (select MAX(age) FROM tblB)
--應改為:
select name from tblA where (salary, age) = (select MAX (salary), MAX (age) from tblB)

來源：Avan_Lau的專欄