SQL 語法 WITH - 將 SQL 的查詢當一個臨時的 VIEW 來使用

Oracle 有提供非常有用的 SQL 語法，
要介紹的就是 WITH 的函數，主要的功用就是將 SQL 查詢包進來，類似 VIEW 的功能，
這樣要進行 Update 或是比較複雜的查詢就可以用到。

語法： WITH 臨時的名稱 AS (SQL 查詢語法)

直接看範例，查詢所有製程工單未完工的製程序，所以就要把大於或等於有 WIP 量的製程序都列出來。
with ecm_wip as(
select ecm01 ecm01x,min(ecm03) ecm03x from ecm_file
where (ecm301+ecm302+ecm303-ecm311-ecm312-ecm313-ecm314-ecm316 <> 0 or ecm301+ecm302+ecm303 = 0)
group by ecm01)
select ecm01,ecm03,ecm301,ecm302,ecm303,ecm311,ecm312,ecm313,ecm314,ecm316,ecm315 from ecm_file,ecm_wip
where ecm01 = ecm01x
and ecm03 >= ecm03x
order by ecm01,ecm03

將有 WIP 量不為 0 或是還沒有發料的工單找出來取最小的製程序製作成一個臨時的 View，
然後再 JOIN 進來取等於或大於以後的製程序，這樣是不是就可以簡化許多了。

有了這個 WITH 的語法，我們寫 SQL 就可以將要查詢的資料先各別寫出來，然後再用 WITH 一一的拼裝起來，
最後再全部 JOIN 在一起就可以了，
我們也不需要為了專屬的 SQL 查詢，寫了許多共用的 VIEW 出來，也變的不容易閱讀。

當資料量越來越大時，不佳的查詢 SQL 語句就會影響系統的效能，好用的函數也是可以多加利用，
有複雜的 SQL 語句時記得要查看是否有 Full Scan Table 的情況。

Oracle 分析統計函數 - OVER 累加、LAG 上一筆、LEAD 下一筆

Oracle 的資料庫在使用率之所以翌立不搖，就是因為穩定、效能高、維護容易，
再來就是提供 200 多種的相關 SQL 語法。
參考 Mastering Oracle SQL and SQL Plus 的書籍，就有提到進階的 Oracle SQL 指令。

分析統計的指令 OVER 說明如下：
SELECT 統計函數(欄位) OVER (window spec) FROM table
其實可以把 OVER 當作同一個條件下的子查詢，並且有 Current Row 的概念。
整個 table 的資料在某些條件下篩選的資料就是 window，也就是我們所下 where 條件出來的資料。

統計函數就不多說明了，一般就是 SUM、AVERAGE、MIN、MAX…

windows-spec 指令的語法就是： partition by 欄位 + order by 欄位 + range-spec
1. partiton by：就是區分成多個區段做分析運算
2. order by：要先跟 Database 說依什麼方式的順序來計算，所以就要在此區段來定義
3. range-spec：要計算的資料範圍，下面會再做說明

範例要累加料件庫存的數量：
依料件不同各別累加，累加的順序為 img01,img02,img03,img04
select img01,img02,img03,img04,img10,sum(img10) over (partition by img01 order by img01,img02,img03,img04) from img_file
where img10 > 0
order by img01,img02,img03,img04

range-spec 說明：

RANGE + BETWEEN 開始 AND 結束

RANGE + UNBOUNDED PRECEDING

ROW + BETWEEN 開始 AND 結束

ROW + UNBOUNDED PRECEDING

BETWEEN…AND…：開始或結束，可以用 CURRENT ROW(目前)、PRECEDING(往前)、FOLLOWING(往後)

上面的範例再加上資料的範圍，結果會是相同的，依料件不同各別累加，累加的順序為 img01,img02,img03,img04
select img01,img02,img03,img04,img10,sum(img10) over (partition by img01 order by img01,img02,img03,img04 range unbounded preceding) from img_file
where img10 > 0
order by img01,img02,img03,img04
或是
select img01,img02,img03,img04,img10,sum(img10) over (partition by img01 order by img01,img02,img03,img04 row between unbounded preceding and current row) from img_file
where img10 > 0
order by img01,img02,img03,img04

範例加總往前1筆到往後1筆的數量：

select img01,img02,img10,

sum(img10) over (partition by img01 order by img01,img02 rows between 1 preceding and 1 following) 

from img_file

where img10 > 0

order by img01,img02

要注意，當用 BETWEEN…AND…超過 partition by 的運算範圍的時候，partition by 就不會有作用。

當然也可以做到是統計數字是往下累加(遞增)的，還是往上累加(遞減)的方式。

統計函數還有提供 LAG 上一筆、LEAD 下一筆，想要比較上一筆或下一筆的資料就可以做資料的判斷。
範例為帶出上一筆的庫存數量：
select img01,img02,img10,lag(img10) over (partition by img01 order by img01,img02)
from img_file
where img10 > 0
order by img01,img02

如果在 SQL 想要能夠抓取上一筆的欄位或是下一筆的欄位資料，也是可以用 OVER 的方式來達到。

Oracle 發送 e-mail 的功能

有些時候能夠定期由 Oracle 來寄送一些資料庫的狀況和資訊或是建立 Alert 機制，
對於 DBA 來說工作就可以輕鬆不少，也可以避免一些預期上的問題。
或是有一些報表或是執行的結果也可以透過這個方式，將  SQL 結果寄送到相關人員的 e-mail 信箱。

Oracle 提供 e-mail 傳送的功能 UTL_SMTP 來發送郵件，
只要放到 Oracle 排程裡就會定期發送 e-mail 給相關的人員，
使用此功能必須要以 sysdba 的角色來登入才能使用 (用 sys 的帳號) 。

指令看了就知道作用了，就不詳細介紹。

範例：發送 Oracle Tablespace 可用容量的 e-mail 。

declare
 --宣告
  l_mail_conn   UTL_SMTP.connection;
  l_boundary    VARCHAR2(50) := '----=*#abc1234321cba#*=';
BEGIN
 --定義郵件主機，不能用 IP 只能用 host name，可以在 /etc/hosts 增加
  l_mail_conn := UTL_SMTP.open_connection('mailserver', '25');
  UTL_SMTP.helo(l_mail_conn, 'mailserver');

  --寄件者
  UTL_SMTP.mail(l_mail_conn, '4shiun@gmail.com');

  --多個收件者
  UTL_SMTP.rcpt(l_mail_conn, '4shiun@gmail.com');

  UTL_SMTP.open_data(l_mail_conn);

  --以 HTML 方式來傳送，定義收件者和寄件者名稱
  UTL_SMTP.write_data(l_mail_conn, 'Date: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'To: ' || 'Adam' || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'From: ' || 'Adam' || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Subject: ' || 'Tablespace Information' || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Reply-To: ' || 'Adam' || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'MIME-Version: 1.0' || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Content-Type: multipart/alternative; boundary="' || l_boundary || '"' || UTL_TCP.crlf || UTL_TCP.crlf);

  UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || UTL_TCP.crlf);
  UTL_SMTP.write_data(l_mail_conn, 'Content-Type: text/html; charset="iso-8859-1"' || UTL_TCP.crlf || UTL_TCP.crlf);

  --把 Tablespace 的資料用 Table 來顯示
  UTL_SMTP.write_data(l_mail_conn, '<table border=1 width=800px>');
  UTL_SMTP.write_data(l_mail_conn, '<TR align=center><TD>tablespace_name</TD><TD>free</TD><TD>used</TD><TD>total</TD><TD>used_percent</TD><TD>free_percent</TD></TR>');
  for i in(select a.tablespace_name,to_char(b.free,'fm999,999,999,999') free,to_char(a.total-b.free,'fm999,999,999,999') used,to_char(a.total,'fm999,999,999,999') total,to_char(((a.total - b.free)/a.total)*100,'999.99')||'%' used_percent,to_char((b.free/a.total)*100,'999.99')||'%' free_percent from (select tablespace_name,sum(bytes) total from dba_data_files group  by tablespace_name)a,(select tablespace_name,sum(bytes) free from dba_free_space group by tablespace_name) b where a.tablespace_name= b.tablespace_name order by 1)
      loop
      UTL_SMTP.write_data(l_mail_conn, '<TR align=right><TD align=left>'||i.tablespace_name||'</TD><TD>'||i.free||'</TD><TD>'||i.used||'</TD><TD>'||i.total||'</TD><TD>'||i.used_percent||'</TD><TD>'||i.free_percent||'</TD></TR>');
      end loop;
  UTL_SMTP.write_data(l_mail_conn, '</table>');
  UTL_SMTP.write_data(l_mail_conn, UTL_TCP.crlf || UTL_TCP.crlf);

  UTL_SMTP.write_data(l_mail_conn, '--' || l_boundary || '--' || UTL_TCP.crlf);
  UTL_SMTP.close_data(l_mail_conn);

  UTL_SMTP.quit(l_mail_conn);
END;

發送 e-mail 當然也可以把 Blob 欄位的資料當成附件的方式來傳送。

要注意的是，如果 mail server 有認證才能發送 e-mail 的話，就需要再加上帳號和密碼，

發送的 e-mail 如果是中文的話，HTML 的文字編碼也需要修改。

建議是把此 SQL 指令寫成 PROCEDURE 的方式方便執行。

建立 Oracle 觸發 Trigger 來 Debug

有時候系統會到遇到靈異事件，
經常發生還容易查，用 r.d2+ 就大概可以 debug 出來問題在那，
偶發情況然後又只要重新再做一次又正常了，不知道到底是時間差、還是資料的問題，或是程式的 bug。
系統又沒有記錄的話，使用者刪除或修改資料是不會承認的，都是朝系統的問題來處理。

只好建立 Trigger 來記錄資料的異常歷史記錄了，看到底是誰搞的鬼。
在 TIPTOP 有提供 log_file 和 aooq040 提供 Trigger log 的查詢，但是 Trigger 要自已在 Oracle 建立。

Trigger 的語法：
CREATE OR REPLACE TRIGGER 觸發名稱
BEFORE INSERT OR UPDATE OR DELETE OF 觸發的欄位 ON 觸發的表
AFTER INSERT OR UPDATE OR DELETE OF 觸發的欄位 ON 觸發的表
FOR EACH ROW
記錄異動的資料
END;

bmd_file 異動資料的範例，把異動的欄位記錄在 log_file：
create or replace trigger bmd_file_log
--只記錄4 個欄位的異動
after insert or update or delete of bmd01,bmd02,bmd03,bmd04,bmd08 on bmd_file
for each row
declare l_col varchar2(3);
        l_user varchar2(6);
        l_type varchar2(20);
        l_cnt  number;
begin
l_col :='';
--先找有沒有資料，當執行失敗時 trigger 就會中止，所以要先找是否有資料，SID 是唯一值，SESSION有可能重複。
select count(*) into l_cnt from v$session,gbq_file where process=gbq01 and sid = userenv('sid') and process not like '%:%';
if l_cnt > 0 then
--把TIPTOP使用者和程式代號找出來
   select gbq03,gbq04 into l_user,l_type from v$session,gbq_file where process=gbq01 and audsid = userenv('sessionid') and process not like '%:%';
else
--把非TIPTOP的使用者和程式找出來
   l_user:=sys_context('userenv','os_user'); l_type:=substr(sys_context('userenv','module'),1,10);
end if ;
--新增
if inserting then
   l_col :='ins';
   insert into log_file values('bmd_file',:new.bmd01,:new.bmd02,:new.bmd03,:new.bmd04,:new.bmd08,l_col,sysdate,l_user,l_type,'','');
end if;
--修改就記錄修改前和修改後
if updating then
   l_col :='upd';
   insert into log_file values('bmd_file',:old.bmd01,:old.bmd02,:old.bmd03,:old.bmd04,:old.bmd08,l_col||'-old',sysdate,l_user,l_type,'','');
   insert into log_file values('bmd_file',:new.bmd01,:new.bmd02,:new.bmd03,:new.bmd04,:new.bmd08,l_col||'-new',sysdate,l_user,l_type,'','');
end if;
--刪除
if deleting then
   l_col :='del';
   insert into log_file values('bmd_file',:old.bmd01,:old.bmd02,:old.bmd03,:old.bmd04,:old.bmd08,l_col,sysdate,l_user,l_type,'','');
end if;
end;

要刪除 TRIGGER ，就用 drop trigger 名稱，就可以刪除了。

SQL 語法將群組的資料全部列出來

我們都知道 GROUP 可以用 AVG、SUM、COUNT、MIN、MAX…等，

可以做數字、日期或是字串的運算。

但是如果群組不想要運算，要合起來全部都列出來呢 ?

通常我們就用程式跑迴圈的方式來將資料連接成一個字串。

我們知道 Oracle 有一個函數叫 concat 也就是將二個字串連接在一起，

這時候也可以用在 group 囉~~

介紹一個函數 wm_concat ，但重複資料不會排除，所以再加上 distinct 就更完美了。
欄位內容會以逗號來分隔。
不想用逗號來分隔的話，就只能用 replace 的方式來取代了。

範例：顯示部門內的所有員工資料。

SELECT gen03,wm_concat(gen01) FROM gen_file

GROUP BY gen03

ORDER BY 1

要注意，wm_concat 的欄位是以 CLOB 格式來呈現的，
所以如果有建立 view 或是直接在 p_query 使用的話，記得要轉成 VARCHAR 的格式。
原因就是 p_query 所有欄位都是依照 gaq_file.gaq03 欄位來宣告的。
用 CAST 將 CLOB 改為 VARCHAR 的格式。
範例：

SELECT gen03,cast(wm_concat(gen01) as varchar(255)) FROM gen_file

GROUP BY gen03

ORDER BY 1

另一個需注意就是 wm_concat 的資料是不能排序，
所以有可能資料所列出來的順序會不一樣。必需改為先 wm_concat 合併再 GROUP來處理。
範例：

SELECT gen03,max(cast(gen01 as varchar(255))) FROM (
SELECT gen03,wm_concat(gen01) OVER (PARTITION BY gen03 ORDER BY gen03,gen01) gen01 FROM gen_file
)
GROUP BY gen03

常用的 PL/SQL 函數備忘

常用的 PL/SQL 函數備忘，以免日後要用到的時候要再花時間查。
後續有常用的 SQL 函數的話會再補充。

ABS(數值) ：取絕對值

CIEL(數值) ：無條件進位

FLOOR(數值) ：無條件捨去

MOD(數值1,數值2) ：取餘數

POWER(數值1,數值2) ：次方

ROUND(數值,小數位) ：四捨五入

SIGN(數值) ：判斷正負值，正 = 1 ，負 = -1，零 = 0

SQRT(數值) ：平方根

LPAD(字串,長度,字元) ：向左補字元

RPAD(字串,長度,字元) ：向右補字元

LTRIM(字串,字元) ：去除最左邊的連續字元或空白

RTRIM(字串,字元) ：去除最右邊的連續字元或空白

LOWER(字串) ：轉小寫

UPPER(字串) ：轉大寫

REPLACE(字串1,字串2,字串3) ：取代字串1裡面有內容是字串2，改為字串3

SUBSTR(字串,開始,第幾個) ：取字串的部份內容

INSTR(字串1,字串2) ：找出字串1裡面有字串2的第一次出現位置

GREATEST(數值1,數值2) ：取二數的最大值，有時候還蠻好用的

LEAST(數值1,數值2) ：取二數的最小值，有時候還蠻好用的

比對二個 Table 的資料, 並進行資料更新

系統做資料同步，新的資料就用 Insert ，原本的資料就用 Update 指令，
當然也可以全部資料 delete 掉再全部重新 Insert 進去，
從 Oracle 10g 的版本開始就提供了一個 Marge 的指令，可以解決很多資料比對上的問題。
Merge 指令的缺點就是，只能二個 Table 進行資料異動。
優點是使用 Update 更新時要注意找不到資料會更新為 Null 的情況，用 Merge 可以避免。

例如：TableA 要依據 TableB 的欄位更新過來，一般都會寫：
            update TableA set (ColumnA2,ColumnA3) =
             (select ColumnB2,ColumnB3 from TableB where ColumnB1 = ColumnA1)
            但是如果要再增加判斷 TableB 的 ColumnB2 不是空的才更新，這樣要改成：
            update TableA set (ColumnA2,ColumnA3) =
             (select nvl(ColumnB2,ColumnA1),ColumnB3 from TableB where ColumnB1 = ColumnA1)

有了 Merge 的指令就可以比較有直覺的 SQL 語法了。
merge into TableA a  a using TableB b  on (a.ColumnA1= b.ColumnB1)
when matched then
   update set a.ColumnA2= b.ColumnB2, a.ColumnA3 = b.ColumnB3 where b.ColumnB2 is not null
when not matched then
   insert (ColumnA1,ColumnA2,ColumnA3) values (ColumnB1,ColumnB2,ColumnB3)

Marge 指令要如何變化不彷就研究試試看吧~~

SQL進行全文搜索,找出所有Table相關字的欄位出來

應該有很多人感到非常的不便就是找尋資料庫的所有 Table ，可能是在某個欄位的資料。
TIPTOP 標準不含 synonym 的話也有 3千多個 Table ，總不能一個一個去 SELECT 吧~
另外 5.X 版就開始有所謂的營運中心，也就是 Table 欄位會多了 PLANT、LEGAL 欄位，
也有許多非 PLANT、LEGAL 的欄位記錄著營運中心的資料。
所以複製到測試資料庫時，當測試資料進行"還原"時，會有可能造成還原到正式的資料庫。
EX：測試資料庫在 AP刪除時，會還原正式資料庫的 rvv20 欄位，變成未匹配。

遇到此狀況時測試資料庫就需要好好的"檢視"一番..........

以下是將資料庫進行全文的檢索 SQL 語法。

create table xxxx as select imk01,imk02,imk09 from imk_file where rownum = 0;   --先建一個暫存 xxx 的 Table 存放搜尋出來的值

declare
    str varchar2(1000);
    num number;
    begin
    for i in(select column_name,table_name from user_tab_cols where data_type in('CHAR','VARCHAR','VARCHAR2'))       -- 列出此 OWNER 的所有欄位出來
    loop
      str:='insert into xxxx select '''||i.table_name||''','''||i.column_name||''',count(*) from '||i.table_name||' where '||i.column_name||'='||'''DS1''';              -- 將查詢有DS1的欄位筆數寫入暫存 xxx Table
      execute immediate str ;                                   -- 轉換並執行
      commit;
   end loop;
   end;

接下來只要執行以下的查詢 SQL 指令，就可以知道有多少筆資料存在那些 Table 裡面了。
再進行整批的資料更新，將營運中心改為測試資料庫的營運中心。

select 'update '||imk01||' set '||imk02||'= ''DS1'';',imk09 from xxxx
where imk09 <> 0
order by imk09

隨著資料庫越來越大，當然要把資料量大的 Table 獨立出來更新，不然會造成資料庫 IO 滿載，系統會變很慢，以下是用迴圈的方式更新資料，減少資料大批量更新。

declare nums number;
begin
for i in(select distinct tlf01 from TLF_FILE)
loop
   update TLF_FILE set TLF20 = 'DS1' where TLF01 = i.tlf01;
commit;
end loop;
end;

這樣完全不會影響到正式資料庫的測試資料就完成囉~

如何查詢 TIPTOP 系統的所有欄位的內容和定義

我們在維護 TIPTOP 程式的時候, 會發現 SQL 指令要查詢那一個欄位.
用 p_zta 實在是不方便, 工作效率也不佳.

TIPTOP 在資料庫有紀錄欄位定義的 Table ,
以後只要查詢這一個 Table 就好啦~ 就可以快速查到你要的欄位定義和內容了.

範例參考 :
select * from ds.gaq_file
where gaq02 = '0'   --語言別
and gaq01 like '%ima%'   --Table 的代號
order by 1

是不是就方便許多了呢?

如何用一個 SQL 來查 BOM -- 遞迴查詢

常常在設計連結資料庫程式的時候, 階層式架構的 Table 大致上都是母子在同一行的樣式來設計，                

資料庫 Table 是容易設計,不過呢? 要把所有的階層展開時,程式就不容易寫,且執行效能也低落,
其實 SQL 是可以做遞迴查詢的,不但不用寫程式一直 WHILE 跑迴圈 , 而且執行效率更快.

以下是 TIPTOP 5.x 的多階 BOM 的 Oracle 範例：

select level,bmb02,bmb01,bmb03,bmb04,bmb05 from bmb_file,bma_file,ima_file
where (bmb05 > sysdate or bmb05 is null)
  and (bmb04 < sysdate or bmb04 is null)
  and bma01 = bmb01 and bmaacti = 'Y'
  and bma01 = ima01
connect by  bmb01 = prior bmb03 and (bmb05 > sysdate or bmb05 is null) and (bmb04 < sysdate or bmb04 is null) and bmaacti = 'Y'
start with ima01 = '料件編號' and (bmb05 > sysdate or bmb05 is null) and (bmb04 < sysdate or bmb04 is null) and bmaacti = 'Y'

抓取最新的 BOM , 有生失效日的判斷 , 重點是還有階層數喔~~
可以試試看.

再提供一些相關的指令：
LEVEL：階數
SYS_CONNECT_BY_PATH(欄位,符號)：可以列出整個路徑出來，符號用 -> 會比較清楚
CONNECT_BY_ISLEAF：可以判斷是不是最底層
CONNECT_BY_ROOT(欄位)：顯示最上層的資料欄位
CONNECT_BY_ISCYCLE：判斷是否有迴圈
NOCYCLE：加在 CONNECT BY 後面有迴圈的就不繼續往下，可配合 CONNECT_BY_ISCYCLE 使用

查出所有 TIPTOP 使用者的所有程式權限清單

公司可能有規定需要定期去檢視 TIPTOP 系統權限是否設定正確.
因此提供 SQL 指令把所有人員的 TIPTOP 系統權限清單列示出來,
就方便各主管去檢視囉~~

參考以下指令 :
select zx01,zx02,zx03,zx04,'','',zxw04,gaz03,zxw05 from zx_file,zxw_file,gaz_file
where zx01 = zxw01
and zxw03 = 2
and zxw04 = gaz01(+)
and (gaz02 = 0 or gaz02 is null)
union
select zx01,zx02,zx03,zx04,zy01,zw02,zy02,gaz03,zy03 from zx_file,zxw_file,zw_file,zy_file,gaz_file
where zx01 = zxw01
and zxw03 = 1
and zxw04 = zy01
and zxw04 = zw01
and zy02 = gaz01(+)
and (gaz02 = 0 or gaz02 is null)
order by 1,5