iT邦幫忙

DAY 3
4

哇咧~夠了(Oracle SQL)系列 第 3

(續)單一SQL好,還是PL/SQL好?

基本上,我是比較推崇單一SQL的,但很大的因素,是因為自以為這種作法才能顯現自己厲害的地方!
這就跟愛用組合語言,C語言來寫核心程式、開發程式的人,對用高階語言開發的人....
有那麼點想考倒看的人的味道,我就是要讓你看不懂啦~
事實上,我們這類資料庫應用系統開發的人,在純粹語言開發程式的人眼哩,可能真的連個屁都不是...
別生氣,我還真的看過,把Power Builder的form,自己寫insert into.., delete from ..., update ...,
例如我說: 在Oracle D2k Form裡面自己寫On-Inser Trigger 的 Insert into 合理嗎?
如果On-Insert 裡面卻是寫Update, 甚至Delete呢?

我說合理,如果你不認同,甚至會覺得我是笨蛋的人、或亂寫一通誤人子弟,那我不能怪你,
只能說你是規規矩矩寫程式的人,你受的是正規教育。就同我上一篇寫的,A,B 兩個Table為何不Join!
不能接受的話,其實,我後面的文,你也是會繼續接受不了吧!

話說回來,這篇文,不是要挑起口水戰?到底哪種人比較會寫程式!純粹是分享SQL的變化運用。

舉例:(不討論效能、最佳化、索引等等)
我有個表格emp_sal,紀錄了每個員工每個月的薪水
例如: 5個員工,12個月薪水,共60筆紀錄。
老闆說,我要看到矩陣的格式統計表
左邊縱軸: 員工
右邊橫軸:月份(共12個月)+加總

  1. 一般Matrix Report 派標準作法:
    用1~3個子查詢(一個產生員工明細、另一個產生月份、中間一個是完整資料),架出一個Matrix Report。
    我會說 1~3 個字查詢是因為,看個人功力?以及是否受到的是哪種教育而定。
    我是Report V2.5 的教科書教的,剛開始是用3個子查詢去架出Matrix。
    這種報表改起來很OOXX。

  2. 一般Pl/Sql 派作法:
    開個表格(橫軸式員工+12個月分+小計),再寫一段PL/SQL,依照格式去寫到表格內,
    然後用一般條列式 Report 輸出,相信絕大部分的報表工程師都是走這條方案。

  3. 我的作法,單一SQL & 一般條列式 Report 輸出。

以下只是舉例,年月也是要設變動的,才能因應實際變化:
Select Emp_Name
, sum(decode(period_name, 201201, sal, 0)) y201201
, sum(decode(period_name, 201202, sal, 0)) y201202
, sum(decode(period_name, 201203, sal, 0)) y201203
, sum(decode(period_name, 201204, sal, 0)) y201204
, sum(decode(period_name, 201205, sal, 0)) y201205
, sum(decode(period_name, 201206, sal, 0)) y201206
, sum(decode(period_name, 201207, sal, 0)) y201207
, sum(decode(period_name, 201208, sal, 0)) y201208
, sum(decode(period_name, 201209, sal, 0)) y201209
, sum(decode(period_name, 201210, sal, 0)) y201210
, sum(decode(period_name, 201211, sal, 0)) y201211
, sum(decode(period_name, 201212, sal, 0)) y201212
From Emp_Sal
group by emp_name;

個人加總/平均等,在報表處理即可。以下可能斷行不好讀,莫怪!
ENAME Y201201 Y201202 Y201203 Y201204 Y201205 Y201206 Y201207 Y201208 Y201209 Y201210 Y201211 Y201212
Anson 22000 22000 22000 22000 22000 22000 22000 22000 22000 22000 22000 22000
Steven 50000 50000 50000 60000 60000 60000 60000 62000 62000 62000 62000 62000
Vicky 40000 40000 40000 40000 40000 40000 40000 40000 50000 50000 50000 50000
Scott 30000 30000 30000 30000 30000 30000 30000 31000 31000 31000 31000 31000
Webb 55000 55000 55000 55000 55000 55000 65000 65000 65000 65000 65000 65000
未必是好方法,但是SQL的可讀性會比PL/SQL好,但加上年月的參數條件變數,可能就未必了。
好不好,看個人喜好啦。

其實,多年的觀察+承接前人維護工作後,
我是寧可寫成看得懂的PL/SQL方式,來開發報表、程式,也不太願意用單一SQL來炫耀,
因為最終不是自己去承接別人的看不懂的程式!就是讓後人罵你這位太厲害的讓人受苦的前輩。
所以,為了造福後人,還是多寫一些可讀性高的程式、多製作完整的SOP吧!

既然是要花1HR跑完的報表,那麼,跟2HR跑完的報表比起來,其實對USER而言,差別真的不大,
User是不會乖乖的一直按"重新查詢"來確認報表跑完沒有的(這是Oracle ERP的環境用語)。
肯定是忙完手上的事,再來回頭看報表結果。

另外,一種是10秒跑完,跟2分鐘跑完的情況,反而我看到過2分鐘組的User把牙籤、牙線、立可白、有重量的小東西,直接給擺在鍵盤上壓住固定的按鍵(一直讓鍵盤送出"重新查詢")!
你說DBA希望你開發哪種報表?他絕對不希望User一直執行Execute-Query。

我遇到過User跟我要求,某些報表不要太快可以跑完的例子耶!
因為他希望用下班後時間去拋,等隔天再看結果!
因為跑太快,主管會要求他做到全部完成再下班,
啥!跑的慢反而是User希望的!驚
料想不到吧!


上一篇
單一SQL好,還是PL/SQL好?最好是再多問一句:哪一種寫法比較Smart!
下一篇
Oracle 日期之我轉、我轉、我轉..轉..轉......(2013/09/19)
系列文
哇咧~夠了(Oracle SQL)28
0
aboutit234
iT邦新手 4 級 ‧ 2013-09-18 12:36:34

讚筆記

月半車甫 iT邦研究生 3 級 ‧ 2013-09-18 14:10:10 檢舉

感謝支持。

0
player
iT邦大師 1 級 ‧ 2013-09-18 13:41:24

關於您的第3段所列出的SQL
應該有更精簡的寫法
PL/SQL應該與T-SQL一樣有支援 pivot 相關語法吧

月半車甫 iT邦研究生 3 級 ‧ 2013-09-18 14:08:51 檢舉

謝謝分享~

我這麼寫,主要是因為Oracle DB持續改版,很多函數、很多的語法也不斷被開發出來,
對我們這些老SQL技術人,很難有機會去發現新DB版本的新功能的。

或許Oracle 也如你所說,有pivot語法可用。
就好像,
以前我們展BOM表,如果不知道Nest Loop Join,也只能乖乖地寫PL/SQL迴圈去跑結果。
寫完才發現,Oracle EBS 本來就有標準Bom Explode Package可用。
但是,台灣很多企業是不肯花錢簽MA的,MIS很多是資源管道缺缺的土法煉鋼者。

這些人除了Google大神,要想自己從茫茫大海般的EBS資料庫,去找出可用的標準Package,
有時候.....真的很難。

不管用新技術、舊方法,結果同樣是對的,
只是對老技術人來說,Nest Loop這個新名詞,可能等同火星文....
給個看得懂的語言,有時候也能起到柳暗花明又一村的效果。

如果可以,您也可以分享實際pivot語法案例! 感恩。

0
holmes2136
iT邦新手 3 級 ‧ 2013-09-18 15:09:38
<pre class="c" name="code">
CREATE TABLE yourtable
	([CompID] varchar(20), [Salary] int, [month] varchar(20))
;
	
INSERT INTO yourtable
	([CompID], [Salary], [month])
VALUES
	('1', 2000, 'January'),
	('2', 3000, 'Feburary'),
	('3', 4000, 'March'),
	('1', 5000, 'March'),
	('2', 2000, 'March'),
	('3', 3000, 'March'),
	('1', 1000, 'Feburary'),
	('1', 2000, 'Feburary'),
    ('1', 1000, 'January')
;

SELECT CompID,January,Feburary,March
FROM
(
  SELECT CompID,Salary,month
  FROM yourtable
) d
pivot
(
  SUM(Salary)
  FOR month IN (January, Feburary, March)
) piv;

COMPID JANUARY FEBURARY MARCH
1 3000 3000 5000
2 (null) 3000 2000
3 (null) (null) 7000

月半車甫 iT邦研究生 3 級 ‧ 2013-09-18 15:36:40 檢舉

讚啦~喜歡

月半車甫 iT邦研究生 3 級 ‧ 2013-09-18 15:44:53 檢舉

但,說真的,如我所說:剛剛Google一下,
https://www.google.com.tw/#q=oracle+pivot
網路文章,聽說Oracle 11g 已支援PIVOT樞紐表的應用了!

對應Oracle EBS,那會是Oracle R12版本囉!

這就是我想表述的,除非是大企業的MIS,
不然恐怕很多MIS接觸到的還是Oracle DB 10以下版本,甚至7,8,9。
Pivot 和Unpivot - Oracle,真的是外星文,我的DB不認識它哩!

但還是感謝您的分享。

player iT邦大師 1 級 ‧ 2013-09-18 16:08:53 檢舉

讚

Oracle 的 Pivot 和Unpivot 語法
我也沒用過 (上一個與 Oracle PL/SQL 相關的工作隔好幾年了)
我只用過 MS SQL Server 的T-SQL語法的 Pivot 和Unpivot 語法
不過我想應該不會差太多

0
一級屠豬士
iT邦大師 1 級 ‧ 2013-09-18 16:15:03

發表的時候,選擇"程式碼",會排的比較好看.

月半車甫 iT邦研究生 3 級 ‧ 2013-09-18 17:57:08 檢舉

這個建議很讚! 下個POST文來試試,感恩,中秋節快樂~

我要留言

立即登入留言