前言
最近由於公司oracle主機更新
DBA盯長運算的sql盯得特別緊
於是幾個sql server上的古蹟就被挖出來了
也發現了幾個有趣的現象跟大家分享一下
最開始收到DBA傳來的sql是長這樣的
(雖然我覺得table schema算不上機密，但還是改編了一下)
select * from tableA where FLAG<>'Y' and NAME<> 'xxx'
這個tableA裡面包含了日期欄位
每天都有資料進帳，也就是說他是一個不算小的龐大table
看到這句sql不由得驚呼「怎麼會這樣子寫SQL!?」
追查了一下sql server上面的寫法後發現
在sql server上是這麼寫的
insert temptable
SELECT * FROM OPENQUERY(oracle_con, 'select * from tableA') AS a
WHERE FLAG <> 'Y' AND NAME <> 'xxx' AND ID IS NOT NULL
and SDATE>@sdate
這樣的一句指令
在我本來的認知中他去了oracle應該就只有select * from tableA這段而已
有趣的他卻帶了兩個where條件過去，這有點顛覆了我對openquery的認知
不過更有趣的是，最後一個條件是帶參數過去不不意外
但為什麼ID IS NOT NULL這條件也過不去呢?
恩，雖然有趣但這神秘的規則就待以後再慢慢研究了
今天主要想提的是openquery的寫法
正確來說openquery該執行的應該是
select * from tableA' WHERE FLAG <> 'Y' AND NAME <> 'xxx' AND ID IS NOT NULL
and SDATE>@sdate
這樣的完整句子才能避免不必要的資源浪費並縮短執行時間
我們來改造一下這段
首先是openquery帶參數的部分
第一段
先來組sql字串吧
DECLARE @TSQL nvarchar(4000)
SELECT @TSQL = 'SELECT * FROM OPENQUERY(oracle_con, ''select * from tableA WHERE FLAG <> ''''Y'''' AND NAME <> ''''xxx'''' AND ID IS NOT NULL
and SDATE>'''''+@sdate+''''' '') AS a '
接著執行
EXEC sp_executesql @TSQL
就有資料了~~~
咦?不對，我們是要insert到temptable裡我要怎麼把executesql裡面的東西拿出來呢!?
傻瓜，包進去就對了
DECLARE @TSQL nvarchar(4000)
SELECT @TSQL = ' insert temptable
SELECT *
FROM OPENQUERY(oracle_con, ''select * from tableA WHERE FLAG <>
''''Y'''' AND NAME <> ''''xxx'''' AND ID IS NOT NULL
and SDATE>'+@sdate+' '') AS a'
EXEC sp_executesql @TSQL
稍微說一下因為在nvarchar裡，一個'要變兩個'
然後openquery裡面的因為是字串裡的字串，所以兩個'要再變成四個'
總之就是很多個點
這裡隱藏了一個亮點
「為什麼你的SDATE沒有點!!!不對他是oracle的話你就算點了也沒用」
恩......不瞞您說.....SDATE這欄位.....是個NUMBER.......
如果真的要帶日期的參數.....Convert這麼好用自己轉一轉再用字串去帶吧!!
