2009年1月31日 星期六

在 Windows 中編輯 Linux Shell Script 的問題

最近開始練習 Linux shell script，想說用 Windows 上的編輯器來編輯

卻發現程式一直都無法正確執行

後來網路上找到資料，原來是 Windows 與 Linux 的換行字元不同所導致.......

這時候就必須要修改一下編輯器的設定了!

以 MadEdit 為例：


將換行字元改為 Unix，程式就可以正常執行啦! 


參考資料
張貼者： 於 沒有留言:
標籤： ,

2009年1月23日 星期五

[TCP/IP Illustrated] Dynamic Routing Protocols

Dynamic Routing

相鄰的 router 之間互相交換網段資訊、連線資訊 ... 等資訊，稱為 dynamic routing，而在 router 上處理這一類工作的 process，就稱為 routing daemon。

routing protocol  還可以根據是否在同一個 AS(Autonomous System) 中來分類，分別是：
  • IGP (Interior Gateway Protocol)
    包含 RIP(Routing Information Protocol)、EIGRP、OSPF(Open Shortest Path First Protocol)、IS-IS ... 等等。

  • EGP (Exterior Gateway Protocol)
    包含 EGP、BGP(Border Gateway Protocol) ... 等等。




Routing Information Protocol

RIP message 是包在 UDP datagram 中傳遞的，以下是傳遞 RIP 訊息的 UDP datagram 格式：


接著是 RIP message 本身的格式：


其中 RIP message 有幾個部份需要注意的：
  • command
    1 = request
    2 = reply

  • version
    是版本不同而不同，RIP = 1，RIPv2 = 2。

  • address family
    此欄位的值永遠為 2，而其他框起來的部份(共 20 bytes)則共同組成一個 IP address 與 metric 資訊。

一個 RIP 訊息最多就僅能帶 25 個 IP address 與 metric 資訊。

運作方式

RIP 所使用的是 UDP port 520，運作的流程如下：
  1. Initialization
    首先會偵測 router 上的介面是否啟用，接著會從啟用的介面送出 request 封包，請求其他 router 將其完整的 routing table 資訊送過來。
    而 request 封包中的 command = 1，address family = 0，metric = 16。

  2. Request received
    router 接收到 request 的封包後，回傳 routing table 資訊給請求者；或是根據封包中所請求的位址給予相對應的 routing 資訊。

  3. Response received
    請求者收到回應，更新 routing table。

  4. Regular routing updates
    接著 router 每 30 秒會將自己的 routing table 資訊送給相鄰的 router。

  5. Triggered updates
    當設定有改變時，就可以發送訊息給鄰近的 router 進行修改。

每個 router 都有其 timeout 的預設值，以三分鐘為例，就表示有 6 次 router advertisement message 沒有收到，因此此時會將 metric 設定為 16，表示此 router 已經不存在，將其刪除。

但沒辦法避免的，使用 RIP 的確會發生 routing table 更新延遲而發生網路不夠穩定的情況。

Metric 的意義

metric 在 RIP 是用來紀錄封包傳遞時，會經過的 router 次數(hop count)，而直接連接網路介面的 hop count 為 1，以下用圖來說明：
若是 R1 要到 N2，metric = 1
若是 R1 要到 N3，metric = 2
若是 R2 要到 N2，metric = 1
若是 R2 要到 N1，metric = 2

RIP 的問題所在

從上面運作的原理，其實就可以大概推測出一些問題的端倪了，大概有以下幾點：
  1. hop count 最大是 15，這到 internet 上就不是那麼適用了。

  2. RIP 無法處理 subnet 的定址資訊。

  3. 不論是 router 故障或是從故障中復原，要讓網路上的其他 router 知道以更新 routing table，需要花過多的時間。




RIP version 2

基本上 RIPv2 並沒有改變原先 RIP 運作的模式，而是將原本在 RIP message 中沒有用到的欄位拿來使用而已，以下是 RIPv2 message 的格式：
不同於 RIP 的欄位說明如下：
  • routing domain
    辨識此封包屬於哪個 routing daemon(儲存 process number) 之用，因此表示可以同時有多個 routing daemon 同時處理 RIPv2 的封包。

  • route tag
    用來支援 EGP 之用，儲存 AS(Autonomous System) number。

  • subnet mask
    對應上面的 IP address，以支援 subnet 的處理。

另外要提的是，RIPv2 還支援了簡單的認證功能，只要 address family 設定為 0xFFFFF，且 route tag 的值為 2，往下的 16 個 bits 就是儲存明文密碼的地方。

不僅如此，RIPv2 還支援 multicast，如此一來就不用使用 broadcast 來減少網路流量了!



OSPF (Open Shortest Path First)

相較於 RIP，OSPF 是較新的 routing protocol，克服了 RIP 的許多限制，其中 OSPF Ver.2 定義在 RFC 1247 中。

RIP 屬於距離向量(distance-vector)協定，而 OSPF 屬於鏈路狀態(link-state)協定；差別在於 link-state protocol 中，router 之間不互相交換距離(這裡指的是 hop count)資訊，而是主動偵測周遭 router 的連線狀態(包含實際的距離、頻寬 ... 等資訊)，接著將蒐集到的資訊傳送給同一個 AS 中的所有 router，每個 router 蒐集來自各方的資訊後，再將這些資訊組合而成 routing table。

其中最重要的是，當網路上有 router 發生問題而無法提供正常服務時，比起 RIP，使用 OSPF 可以大幅降低收斂(亦即重新發送訊息以組合成可以正常提供服務的 routing table)的時間。

之前提到 RIP 是使用 UDP port 520，而 OSPF 則是直接使用 IP 協定。

由於 OSPF 屬於 link-state protocol，因此也較 RIP 多了以下的優點：
  1. 可以針對不同的路徑計算出 IP type-of-service，因此在 routing table 中可以針對同一個目的地有多個選擇。

  2. 每個網路介面都會給予一個值，其計算的基準在於 throughput、round-trip time、可靠度 .... 等等。每個 IP type-of-service 都可以賦予一個值。

  3. 當到目的地的封包有多條路徑可以選擇，且每條路徑成本相同時，OSPF 會自動將流量分散從不同的路徑傳遞，以求達到負載平衡的效果。

  4. 點對點之間的連線網路介面不需要 IP address。

  5. router 之間訊息的傳輸使用 multicast 而非 broadcast，減少整體的網路流量。

而現在 routing protocol 幾乎都改用 OSPF 囉!



BGP (Border Gateway Protocol)

BGP 是 EGP 的一種，用來在 internet 中讓 AS 之間交換 routing 資訊之用，目前 BGP Ver. 4 的詳細規則可以查詢 RFC 1771。

不同 AS 之間會透過 BGP 傳遞包含 AS 的完整 routing 路徑表資訊，藉由組合這些 routing 資訊來形成一個完整的 internet routing 資訊，並避免掉可能發生的 routing loop。

不同於 RIP(UDP port 520) 與 OSPF(IP)，BGP 系統間的溝通是透過 TCP 協定來交換 routing table 的資訊；此外，BGP 還會定期送出 keepalive 的訊息，用來確定周遭的 AS 是否可以正常提供 routing 的服務，以便當 AS 有問題時，可以縮短收斂的時間。

而 BGP 的設計上，還可以讓 AS 的管理員自訂設定規則，亦即 BGP 可以允許轉換為 policy-based routing，因此也讓管理者有更大的空間可以發揮與利用。



CIDR (Classless Interdomain Routing)

CIDR 應該是不能算是 protocol，而是一種技術(或是功能)。

怎麼說呢? 這是由於 B class 的網段過於龐大，256 * 256 台主機，如果不切割網段會造成可怕的 broadcast storm，因此解決方式就是切成 C class。

但是切成 C class 卻衍生出了另一個問題：每個 network 之間都必須要有 router。

這可是相當麻煩且耗費成本的事情阿，而且 routing table 的數量也會一下子暴增很多；因此 CIDR 就是用來解決這個問題所衍生出來的技術。

而若是僅按照 Class A B C 去切割網路，其實問題是相當多的，例如：
  1. 浪費 IP address
    使用 A class、B class 的組織不見得用的到這麼多 IP；使用 C class 的組織有可能 IP 數量不夠用；多切割一個網段就必須要多浪費兩個 IP(Network ID & Broadcast)。

  2. 無法符合實際需求
    大部分組織可能會遇到 C class 不夠用，B class 卻太多的情況，僅切成 class A B C 並不符合實際的需求。

CIDR 使用 7~32 可變換長度的 Network ID 來取代原本 Class A B C 長度為的 8 16 24 的 Network ID，因此 host 範圍可以從 32 ~ 約 50000 個!

從上面的敘述可以瞭解到，其實 CIDR 的功能就是「將多個小網段組成一個較大的網段」，如此一來 router 所維護的 routing table entry 就可以大大地減少，也因此可以提昇 router 運作的效能。



參考資料

張貼者： 於 沒有留言:
標籤：

2009年1月22日 星期四

[Oracle] Trigger - 「:NEW」的使用

今天試寫一支 trigger，目的是要讓每一列的 action 欄位更新為 1 時，自動塞入日期與時間的資料，第一次用到「:new」，因此記錄一下。

以下是程式碼：

create or replace trigger tri_fill_datetime_to_message
--只有在更新資料後才需要加入日期時間資訊
before update
on  mytable
for each row
begin
--當 action 更新的值為 1 時，在執行以下動作
if :new.action = 1 then
  --設定新資料的日期時間資訊
  :new.log_date := to_char(sysdate, 'YYYYMMDD');
  :new.log_time := to_char(sysdate, 'HH24MISS');
  :new.log_datetime := sysdate;
end if;
end tri_fill_datetime_to_message;


參考資料
張貼者： 於 沒有留言:
標籤： ,

2009年1月19日 星期一

[TCP/IP Illustrated] IP Routing

簡介

IP 在 TCP/IP protocol suit 中是非常重要的一個協定，有多重要呢? 以下用一張圖來簡單說明 IP 所負責的事情：

在圖中的 routing table 是最常被 IP 存取的部份，可能每秒數百次；而 routing daemon 則是在背景執行的程序，大約半分鐘會更新 routing table 的資訊一次，而更新的資訊可能來自鄰近的 router，或甚至是 ICMP redirect 的封包都可以用來作為更新 routing table 的依據。


Routing 原則

基本上，routing 的規則如下：
  1. 搜尋有無符合的 host

  2. 搜尋有無符合的 network

  3. 搜尋 default gateway (預設閘道)

以上的 routing 工作是由 IP 負責處理；而上面的規則都是去搜尋 routing table 中的資訊，而這些資訊(也稱為 routing policy)都是由 routing daemon 所負責提供。

routing table 長什麼樣呢? 以下截圖說明：

其中兩條被框起來的規則(Flags 的部份有大寫「G」)為 gateway，而 Flags 說明如下：
  • U：表示此規則路徑是正常運作的

  • G：將封包導向 gateway

  • H：將封包導向特定的主機

  • D：表示此規則是由 ICMP Redirect 訊息所新增的

  • M：表示此規則是由 ICMP Redirect 訊息所修改的



關於 routing 時會發生的錯誤種類

ICMP Host & Network Unreachable Errors

當 router 收到 IP datagram 卻無法將其繼續傳送時，就會發生此錯誤，通常此種錯誤出現的原因為網路設定上的錯誤，或是硬體線路沒接好。

ICMP Redirect Errors

此錯誤訊息是由 router 所發，目的是用來告知發送 IP datagram 的主機，應該要將封包送到另外一個 router 上。

以下用圖片來說明一下發生 ICMP Redirect Error 的情境：

  1. 其中 host 將 IP datagram 送給 R1，因為 R1 是 host 的 default gateway。

  2. R1 收到封包，檢查 routing table 規則後，發現其實 R2 才是正確的路徑，因此將封包導向 R2。

  3. 除了將封包導向外，R1 也額外發送了 ICMP Redirect Error 的訊息給 host，通知它要修改自己本身的 routing table。

由這個功能可以瞭解到，若是在複雜一點的 topology 中，要每個 host 都清楚知道封包 routing 的路徑是很難的，但有了這功能後，就可以僅設定 default gateway，接著送封包後就會由 router 通知來改變自身的 routing table 了。

【備註】
  1. 此種錯誤訊息僅有 router 能發送，host 是不能發送的。

  2. router 僅能送出 host redirect 而不是 network redirect，理由是因為 router 要掌握 topology 所有的網段資訊不是很容易，而且也有可能覆蓋原本 host 的 routing table 中不需要修改的設定。



ICMP Router Discovery Messages

此種訊息是用來讓 host 動態取得 routing 資訊用，透過發送此訊息(solicitation)，周遭收到此訊息的 router 便會進行回應(advertisement)；此外，router 還會定期的廣播 router advertisement，讓 host 可以即時更新 routing table 中的資訊。

從上面的運作方式可以瞭解到：
ICMP Router Discovery Message = ICMP Router Solicitation Message +
ICMP Router Advertisement Message
接著是兩種 message 的格式：

ICMP Router Solicitation Message
ICMP Router Advertisement Message

Router 在做哪些事情?

在 routing 資訊的傳送中，router 做了哪些事情呢?

router 會固定每一段時間(約 450~600 秒不等)都發送 advertisement 訊息，若是 router 中有網路介面準備要關閉停用時，會發送一個 lifetime = 0 的 advertisement 訊息通知周圍的 host 與 router 此網路介面將要失效，必須要更新 routing table 中的資訊。

若是同時有多台 router 在同一個網段中，preference level 的部份就是要由網路管理者來決定了，才有辦法讓 routing policy 有先後順序之分。

Host 在做哪些事情?

host 就跟 router 相反了，開機時送出 router solicitation 訊息，以便接收 router 的回應來更新 routing table；當然，也會隨時監聽來自 router 的 advertisement 訊息，以便取得 routing 的最新資訊。

一般來說，router advertisement 訊息每 10 分鐘就會發送一次，而 lifetime 為 30 分鐘，只要在 30 分鐘之內有收到 router advertisement，就可以一直保持著有 routing 資訊了!
張貼者： 於 沒有留言:
標籤：

2009年1月11日 星期日

[TCP/IP Illustrated] Traceroute

運作原理簡介

基本上 traceroute 是使用 ICMP 協定，並透過 IP header 中的 TTL 的值每經過一個 router 會遞減的特性，來取得封包傳遞的路徑。

詳細的運作原理說明如下：(以下 client 為執行 traceroute 的主機，server 則為目的地)
  1. client 發送 TTL 為 1 的封包，destination 指定為 server。

  2. 當第一個 router 收到後會將 TTL 減一，此時 TTL 為 0，因此 router 回送 ICMP time exceeded 訊息，如此一來，client 就可以知道封包傳遞路徑中的第一個 router 是哪一個。

  3. 接著發送 TTL 為 2 的封包，同樣的可以接收到來自第二個 router 所回傳的 ICMP time exceeded 訊息。

  4. 一直到封包真正到達 server 時，此時 server 會回應 ICMP port unreachable 訊息，為什麼呢? 原因是因為 client 所發送的封包為 UDP datagram，並指定一個很大的數字(> 30000)，通常不會有 server 會在這麼大的 port number 上提供服務，因此會回傳 ICMP port unreachable 的訊息。

  5. client 收到來自 server 的 ICMP port unreachable 訊息，就完整拼湊出封包傳遞路徑了!


tracertroute 實測

在 Windows 中的程式名稱為 tracert，在 Linux 則為 tracepath (也有 tracertroute，不過要另外裝套件)，以下是在 Windows 使用的範例圖：

若用 tcpdump 偵測封包往來情況，結果如下：

從上面的實測可以得知，在封包傳遞路徑上的節點，都是由 ICMP time exceeded 與 udp port unreachable 來決定的，以下為 ICMP time exceeded 封包的格式：
從圖中可以看出，ICMP time exceed 封包的 type 值為 11，而 time exceeded error 又會在兩種情況下發生，分別是在傳送(trasmit，code = 0)以及封包重組(reassembly，code = 1)時。


IP Source Routing Option

這個功能剛好跟 ping 的 record route option 相反，是將 router 的 IP 清單填到封包中，讓封包可以按照清單上的路徑傳遞，有分為 strict/loose source routing，封包格式如下：

不過這功能似乎不常用，可能高階的網管有可能用到吧......以後學到高階的時候再來研究這個好了。


參考資料

張貼者： 於 沒有留言:
標籤：

[Linux Command] T 開頭

tcpdump
張貼者： 於 沒有留言:
標籤：

2009年1月10日 星期六

[TCP/IP Illustrated] Ping

簡介

ping 通常是我們用來驗證主機之間的通訊是否有連通的第一個手段。然而隨著網路技術越趨進步與複雜，光用 ping 已經不足以判斷兩台主機之間是否可以互相進行通訊了，但由於 ping 已經是一個歷史悠久的網路工具程式，因此還是很值得拿來探討一下。


ping 是如何運作的 ?

ping 的實作是使用 ICMP echo/reply 來達成的，其中發送 echo 的主機稱為 client，回應 reply 的主機稱為 server，而由於 ping 都已經實作在各 OS 的 kernel 中，因此也不必為了啟用這個功能而安裝特定的軟體或是服務。

以下是 ICMP echo/reply 的封包格式圖：
封包的欄位說明：
  • type
    echo 為 8；reply 為 0。

  • code
    echo/reply 皆為 0。

  • identifier
    此欄位儲存 ping client 的 process number，如此一來當收到 reply 後，就可以知道由哪一個 process 來處理。

  • sequence number
    紀錄 ping client 所送出的第幾個封包，從 0 開始依次遞增。


以下是從 Linux server ping 到 google 的範例：


IP Record Route Option

使用 ping 時可以透過「-R」參數將 record route 選項開啟，如此一來就可以知道封包所經過的路徑與節點，但由於目前大部分主機都關閉對此功能的支援，因此使用這個參數都不會有相關的回應。

不過雖然 ping 支援紀錄路徑的功能，但還是有最多僅能紀錄 9 組 ip 的限制(即使來回經過的相同節點都要包含)，在目前網路環境下，實用性似乎不足。

不過以下還是放一下 IP record route 封包存放資料的格式：


IP Timestamp Option

這個就跟 record route option 蠻類似的，只是紀錄的是封包到達每個 hop 的 timstamp 而非 ip address，封包格式如下圖：
張貼者： 於 沒有留言:
標籤：

2009年1月9日 星期五

[Linux] 透過指令直接設定 IP、Netmask、Gateway

今天在 VirtualBox 上安裝 Ubuntu Server，但由於網路環境中沒有 DHCP，因此要進到 /etc/network/interfaces 去修改成 static；但麻煩的是，沒有安裝 vim 套件前，實在是沒辦法編輯(不曉得預設是哪種編輯器....用起來很彆扭)，當然也就沒辦法修改 IP。

那........怎麼辦呢? 我要改 IP 跟 Gateway 阿!

找了一下資料，發現可以透過指令方式直接指定：(設定範例)
root> ifconfig eth0 192.168.1.1 netmask 255.255.255.0
root> route add default gw 192.168.1.254

這樣就成功啦!


參考資料

張貼者： 於 沒有留言:
標籤：

2008年12月25日 星期四

[Oracle] 多國語系的處理

前言

Oracle 對於多國語系的支援是相當強大的，可以隨時根據需求改變使用不同的語系、時間格式、貨幣格式、文字編碼、設數字表示法 … 等等。

在 Oracle 中是透過一群參數來控制多國語系的設定，這一群參數稱為「NLS(Native Language Support) parameter」。

檢視並運用 NSL paramter

NSL parameter 可以透過 Oracle 所提供的 SQL Developer 工具進行檢視，選擇「Report –> Data Dictionary Reports –> About Your Database –> National Language Support Parameters」，畫面如下：

另外，可以透過以下語法，暫時修改目前連線 session 的語系環境：

ALTER SESSION SET parameter-name = ”value”;

以下以範例作說明： 

--修改語言
ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN';

但需要注意的是，透過 ALTER SESSION 所修改的參數，在重新連線後都會重新變回原本的預設值。


建立支援多國語系的環境

以下分段說明如何自訂多國語系的環境：

設定語言(Language)與地區(Territory)參數

在此部份的參數與影響的部份為：

1、NLS_LANGUAGE

  • server 訊息顯示時所用的語言

  • 日期與其縮寫的表示方式

  • 資料排序時的依據(不同的語系會根據不同的字元標準進行排序)

以下為 NLS_LANGUAGE 的使用範例： 

--修改語言
ALTER SESSION SET NLS_LANGUAGE = 'TRADITIONAL CHINESE';
--顯示 : 23-12月-08  
SELECT SYSDATE FROM DUAL;

--修改語言
ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN';
--顯示 : 23-DEC-08 
SELECT SYSDATE FROM DUAL;


2、NLS_TERRITORY

  • 日期格式

  • 十進位字元與分隔符號

  • 本地貨幣符號

  • ISO 貨幣符號

以下為 NLS_TERRITORY 的使用範例：

--結果如下：
-- NT$24,000.00 
-- NT$17,000.00 
-- NT$17,000.00
SELECT TO_CHAR(salary, 'L99G999D99') salary
FROM employees
WHERE employee_id IN (100, 101, 102);

--改變 territory 為德國
ALTER SESSION SET NLS_TERRITORY = 'GERMANY'

--結果如下：
-- €24.000,00  
-- €17.000,00  
-- €17.000,00
SELECT TO_CHAR(salary, 'L99G999D99') salary
FROM employees
WHERE employee_id IN (100, 101, 102);


設定日期(Date)與時間(Time)參數

不同的地區都會有自己地區表示時間的格式，但如果不適合用，當然要修改預設值也是沒有問題的，關於日期與時間的 NLS parameter，有以下幾個：

1、NLS_DATE_FORMAT

除了 NLS_TERRITORY 參數可以改變預設的日期格式外，若要進行客製化的話，就必須透過 NLS_DATE_FORMAT 這個參數了，以下用範例說明： (官方文件說明) 

--執行結果 : 24.12.08
SELECT SYSDATE FROM DUAL;

--改變日期格式
ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY';

--執行結果 : 12/24/2008
SELECT SYSDATE FROM DUAL;

--改變日期格式
ALTER SESSION SET NLS_DATE_FORMAT = '"DATE: "MM/DD/YYYY';

--執行結果 : DATE: 12/24/2008
SELECT SYSDATE FROM DUAL;


2、NLS_DATE_LANGUAGE

透過此參數可以改變顯示日期的語言，不過僅會在使用 TO_CHAR 與 TO_DATE 兩個 function 時有效，以下是範例：(官方文件說明)

Created with colorer-take5 library. Type 'sql'

--執行結果: ���期三:24 12月 2008 
SELECT TO_CHAR(SYSDATE, 'Day:Dd Month yyyy') FROM DUAL;

--改變表示 DATE 語言
ALTER SESSION SET NLS_DATE_LANGUAGE = 'FRENCH';

--執行結果: Mercredi:24 Decembre  2008
SELECT TO_CHAR(SYSDATE, 'Day:Dd Month yyyy') FROM DUAL;

--改變表示 DATE 語言
ALTER SESSION SET NLS_DATE_LANGUAGE = 'AMERICAN';

--執行結果: Wednesday:24 December  2008
SELECT TO_CHAR(SYSDATE, 'Day:Dd Month yyyy') FROM DUAL;


3、NLS_TIMESTAMP_FORMAT & NLS_TIMESTAMP_TZ_FORMAT

這兩個其實是一起的，只是一個會改變 select 出來的 timestamp 顯示(NLS_TIMESTAMP_FORMAT)，一個會改變由 TO_CHAR() function 所呈現的 timestamp 顯示(NLS_TIMESTAMP_TZ_FORMAT)。

以下為範例：(官方文件說明)

--執行結果: 24-12月-08 02.22.38.284000000 下午 ASIA/TAIPEI
SELECT CURRENT_TIMESTAMP FROM DUAL;

--改變 timestamp 顯示格式
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH:MI:SS:FF TZH:TZM';

--執行結果: 2008-12-24 02:22:38:334000000 +08:00
SELECT CURRENT_TIMESTAMP FROM DUAL;


數字表示法的設定

數字的部份，可以透過「NLS_NUMERIC_CHARACTERS」參數設定千、百萬的分隔字元，以及表示小數點的分隔字元；以下用範例說明：

--執行結果: 4,000.00
SELECT TO_CHAR(4000, '9G999D99') FROM DUAL;

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ',.';

--執行結果: 4.000,00
SELECT TO_CHAR(4000, '9G999D99') FROM DUAL;


貨幣格式的設定

貨幣格式的部份可由以下三個參數來設定：

  1. NLS_CURRENCY

  2. NLS_ISO_CURRENCY

  3. NLS_DUAL_CURRENCY

以下用範例說明使用方式：

--執行結果:
-- TWD009.000,00 
-- TWD006.000,00 
-- TWD004.800,00 
-- TWD004.800,00 
-- TWD004.200,00
SELECT TO_CHAR(salary, 'C099G999D99') salary FROM employees where department_id = 60;

--將貨幣表示更改為法國
ALTER SESSION SET NLS_ISO_CURRENCY = 'FRENCH';

--執行結果:
-- EUR009.000,00 
-- EUR006.000,00 
-- EUR004.800,00 
-- EUR004.800,00 
-- EUR004.200,00
SELECT TO_CHAR(salary, 'C099G999D99') salary FROM employees where department_id = 60;


排序與搜尋方式的設定

不同的語言在資料排序上不盡然相同，有些以字母順序為依據，有些以發音為依據、有些以單字的字母數量為依據；而 Oracle 當然也支援根據不同的情況進行改變。

而影響排序搜尋的 NLS parameter 有兩個，分別是「NLS_SORT」與「NLS_COMP」 以下有兩個參數會影響到系統的運作：

以下用範例來說明： 

--修改排序方式
ALTER SESSION SET NLS_SORT = 'BINARY';

--執行結果:
-- Cabrio                    
-- Cambrault                 
-- Cambrault                 
-- Chen                      
-- Chung                     
-- Colmenares
SELECT last_name FROM employees WHERE last_name LIKE 'C%'
ORDER BY last_name;

--修改排序方式
ALTER SESSION SET NLS_SORT = 'spanish_m';

--執行結果:
-- Cabrio                    
-- Cambrault                 
-- Cambrault                 
-- Colmenares                
-- Chen                      
-- Chung
SELECT last_name FROM employees WHERE last_name LIKE 'C%'
ORDER BY last_name;

此外，若是搜尋時大小寫有別，可以透過類似「NLS_SORT = ‘BINARY_CI’」(Case-Insensitive) 的方式進行設定。


設定資料長度的判斷基準

由於 Oracle 支援多國語言，其中英文是屬於 single-byte 的字元集，其他許多國家的語言則屬於 multi-byte 的字元集；而在 Oracle 中預設是以 byte 為基準，但實際上有時候還是必須以 char 為基準才是比較正確的。

因此在 Oracle 中可以透過以下語法進行設定： 

--將計算基準改為 char
ALTER SESSION SET NLS_LENGTH_SEMANTICS = 'CHAR';

--將計算基準改為 byte
ALTER SESSION SET NLS_LENGTH_SEMANTICS = 'BYTE';


設計支援多語系的應用程式

官方網站中有文件說明，指導開發者如何開發 unicode 的應用程式

要儲存 unicode 的資料到 Oracle 有兩種方式：

  1. 建立一個 unicode 資料庫。(指定資料庫使用的編碼)

  2. 指定特定欄位支援 multi-bytes 以儲存 unicode 的資料，在 Oracle 為 NCHAR 資料型態。 (基本上，設定 NCHAR 型態的話，不論資料庫使用的編碼為何，都可以儲存 unicode 的資料)


使用 Unicode 資料型態

在 Oracle 有兩種儲存 unicode 資料的型態，分別為：

  1. NCHAR

  2. NVARCHAR2

首先說明 NCHAR 使用方式，宣告方式如下：

CREATE TABLE table1 (column1 NCHAR(30));


而 NCHAR 的使用長度上有兩個限制：

  1. 當字元編碼為 UTF-8 時，NCHAR 最多可以儲存 2000 的字元；若是改為 AL16UTF16，就僅能儲存 1000 個字元。

  2. 實際儲存長度最大為 32768 bytes。

根據使用者的宣告，資料長度若不足宣告的欄位長度，則會補足空白字元。


另外一種資料型態則為 NVARCHAR2，宣告方式如下：

CREATE TABLE tables (column2 NVARCHAR2(2000));

而 NVARCHAR2 在使用長度上也是有兩個限制：

  1. 當字元編碼為 UTF-8 時，NCHAR 最多可以儲存 4000 的字元；若是改為 AL16UTF16，就僅能儲存 2000 個字元。

  2. 實際儲存長度最大為 32768 bytes。


使用與處理 Unicode 字串

在 SQL 處理中，若要指定使用 unicode 字串，有以下幾個方式：

  1. 在單引號旁邊加上大寫「N」。

  2. 使用 NCHR(n) 函式，可以將字元轉為 unicode 編碼，提高程式的可攜帶性。

  3. 使用 UNISTR(‘string’) 函式，將字串轉換為 unicode 編碼。


NCHAR 的轉換

在開發多國語系的應用程式時，要考慮的部份除了 server 的編碼外，還包含 client 的編碼，當兩者編碼不同時，資料在傳輸時可能就會因為轉換而造成資料不正確，即使使用 N(‘string’) 的方式也是相同。

此時可以在 DB client 的地方增加一個名稱為「ORA_NCHAR_LITERAL_REPLACE」的環境變數，並將值設定為 TRUE，如此一來使用 N(‘string’) 時，系統就會自動轉換為 unicode 來傳輸並儲存囉!


在 Function 中使用 NLS parameter

在 Oracle 中有些 function 會因為語系、地區的不同而造成使用效果也相對不同(例如：TO_CHAR'、TO_DATE、TO_NUMBER、NLS_UPPER、NLS_LOWER、NLS_INITCAP、NLSSORT … 等等)，因此若是有特殊需求的話，可以直接將 NLS parameter 指定在 function 中，讓操作環境暫時改變。

為了讓 function 開發時可以容易些，有時候必須暫時轉變語系進行處理，否則程式光是要考慮語系的部份可能就太過於複雜了，以下用個範例說明：

Created with colorer-take5 library. Type 'sql'

--這樣搜尋是不被允許的，因為日期格式並非如此表示
SELECT last_name FROM employees WHERE hire_date > '01-Jan-1999';

--所以改成美國的表示法
ALTER SESSION SET NLS_DATE_LANGUAGE = 'American';

--如此一來，不論原本的語系為何，都可以正確顯示搜尋結果囉!
SELECT last_name FROM employees WHERE hire_date > '01-Jan-1999';


透過在 function 中指定有個好處，就是不會改變該連線 session 時的 NLS 設定，而只有在該 DML 操作時有效而已，以下用個範例說明： 

--直接在 TO_CHAR 函式中設定 NLS parameter
SELECT last_name
FROM employees
WHERE hire_date > TO_DATE('01-Jan-1999', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = American');

因此若是要避免修改到 NLS 設定而影響其他操作，就將 NLS parameter 放進 function 中。

而有哪些 function 可以接受 NLS parameter ? 而這些 function 可以接受的 NLS parameter 又是哪些呢?

以下為列表：
  • TO_DATE
    NLS_DATE_LANGUAGE, NLS_CALENDAR

  • TO_NUMBER
    NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_ISO_CURRENCY, NLS_DUAL_CURRENCY

  • TO_CHAR
    NLS_DATE_LANGUAGE, NLS_NUMERIC_CHARACTERS, NLS_CURRENCY,NLS_ISO_CURRENCY, NLS_DUAL_CURRENCY, NLS_CALENDAR

  • TO_NCHAR
    NLS_SORT

  • NLS_UPPER
    NLS_SORT

  • NLS_LOWER
    NLS_SORT

  • NLS_INITCAP
    NLS_SORT

  • NLSSORT
    NLS_SORT

接著為使用範例：

-- 21/JUIN /1999      
-- 13/JANV./2000      
-- 17/SEPT./1987
SELECT TO_CHAR(hire_date, 'DD/MON/YYYY', 'NLS_DATE_LANGUAGE = French') "Hire Date" FROM employees;

-- 25/12月/2008
SELECT TO_CHAR(SYSDATE, 'DD/MON/YYYY', 'NLS_DATE_LANGUAGE=''Traditional Chinese'' ') "System Date" FROM DUAL;

-- 13.000,00
SELECT TO_CHAR(13000, '99G999D99', 'NLS_NUMERIC_CHARACTERS = '',.''') "13K" FROM DUAL;

-- 2.600,00 
-- 2.600,00 
-- 4.400,00
SELECT TO_CHAR(salary, '99G999D99', 'NLS_NUMERIC_CHARACTERS='',.'' NLS_CURRENCY=''EUR''') salary FROM employees;
                                                                                  -- ABEL               
-- ANDE               
-- ATKINSON
SELECT NLS_UPPER(last_name, 'NLS_SORT = Swiss') "Last Name" FROM employees;

-- Abel                      
-- Ande                      
-- Atkinson                  
-- Austin
SELECT last_name FROM employees ORDER BY NLSSORT(last_name, 'NLS_SORT = German');

為了符合多國語系，在開發應用程式時，許多細節就必須注意到，未來才不需要因為語系或是編碼的問題傷透腦筋囉!

張貼者： 於 沒有留言:
標籤： ,

2008年12月23日 星期二

[Oracle] Trigger

何謂觸發(Trigger) ?

觸發(Triggers)是當 Table 或是 View 因為 Insert、Delete、Update動作發生時自動觸發的程序(或是因為特定事件的發生，例如：服務啟動)。可以用來描述商業邏輯。跟 stored procedure 很類似，差別在於它不是用來直接呼叫的。

通常 trigger 被用來作為以下幾項用途：

  1. 資料完整性的檢查

  2. 稽核與日誌紀錄功能

  3. 執行複雜的商業邏輯運算與處理

  4. 衍生欄位資料的計算與產生

  5. 讓 table 資料有限制的被修改

 

使用語法

首先是 trigger 的使用語法： (詳細語法可參考官方文件)

從語法可以看出，整個 trigger 可以分為四個部份：

  1. trigger name
    在同一個 schema 中，trigger name 必須是唯一的。

  2. triggering statement
    指定讓 trigger 啟動的事件，這些事情可能包含 DML(for table)、DDL(for schema object) 或是資料庫服務啟動、關閉、甚至是發生錯誤時。

  3. trigger restriction
    額外設定的限制，可讓 trigger 在特定情況下(當 restriction = TRUE 時)才啟動。

  4. triggered action
    trigger 所要執行的程式內容。

 

觸發(Trigger)的種類

在 Oracle 中，trigger 大致可分為五類：

  1. statement trigger (詳細用法可參考官方文件)
    此種 trigger 跟 DML 操作(例如：DELETE、INSERT、UPDATE)有關聯，但每一次的 DML 操作僅會觸發一次 trigger。
    雖然設定在 DML 操作上的 trigger 在 DML 操作發生時僅會出發一次，但可以同時設定多個 trigger 在同一個 DML 操作上，而且還可以透過「FOLLOW」、「PRECEDES」… 等關鍵字來決定多個 trigger 觸發的先後順序。

  2. row trigger (詳細用法可參考官方文件)
    當 table 中的每一列資料發生 INSERT、UPDATE 或是 DELETE 時，此種 trigger 就會觸發。大致跟 statement trigger 運作方式相同，但 row trigger 是針對「每一列」資料進行觸發；因此假設一個 DML 同時影響很多筆資料時，statement trigger 僅會出發一次，但 row trigger 卻會觸發很多次。

  3. INSTEAD OF (詳細用法可參考官方文件)
    可以用在當使用者針對 VIEW 或是衍生欄位進行操作時，可透過 trigger 將資料進行正確的資料處理。

  4. user event trigger
    用於 DDL(例如：CREATE、ALTER、DROP、USER LOGON/LOGOFF … 等等) 以及特定的 DML 操作(分析、統計、稽核、使用者權限管理)上。

  5. system event trigger
    當資料庫啟動、關閉、或是發生錯誤的事件發生時，會進行觸發。

 

Trigger 發生的時間點

trigger 發生的時間點也必須要注意，因為在 trigger statement 中，有「BEFORE」與「AFTER」兩種可供使用，表示 trigger 觸發的時間點位於「事件發生前」或是「事件發生後」。

但也不是每個事件都有前後可以設定，例如：STARTUP、SUSPEND、LOGON … 等事件，就僅有 AFTER 可以用；而 SHUTDOWN、LOGOFF … 等事件就僅有 BEFORE 可以用。

 

Trigger 的設計原則與限制

雖然 trigger 看似很好用，可以作很多的管理與控制，但最好還是注意以下幾點原則與限制：

  1. 過度使用 trigger 或許會產生複雜的依賴關係，後續的維護變得很困難。

  2. 確定當某個特定工作執行後，所有相關與相依的工作都有被一一執行。

  3. 避免將 recursive trigger 的發生，否則記憶體裝再多都沒用。

  4. 避免 trigger 的工作會觸發另一個 trigger 而造成連鎖效應，以免造成非預期的情況發生，或是對資料庫效能造成影響。

  5. 不要用 trigger 作一些資料庫已經能做的工作，例如：參考完整性的檢查。

  6. 確定「BEFORE」、「AFTER」的使用有符合規則與需求。

  7. trigger 的程式碼不宜過多(Oracle 限制不能超過 32 Kb)，若是程式碼太多，或許可以考慮寫成 stored procedure 來處理。

  8. 確定 trigger 中運作的工作符合資料庫與商業邏輯規則，若是要針對特定人物、特定團體或是特定 application 客製化工作需求，別用 trigger，寫在 application 裡面吧!

  9. 在 trigger 中無法使用 COMMIT、ROLLBACK、SAVEPOINT … 等關鍵字，因為 DDL 操作已經隱含 COMMIT 在裡面了。

 

建立 Trigger

Statement Trigger

statement trigger 是與 DML 的操作(Insert、Update、Delete)有關，以下用範例說明：

 

Row Trigger

與 statement trigger 不同的是，DML 對於每個資料列的操作都會觸發 row trigger 動作，但在 trigger 寫法上，僅有一點點不同而已：

 

INSTEAD OF Trigger

instead of trigger 通常用於發生使用者對 view 進行操作時，可以針對使用者傳入的資料進行額外的處理，以下是範例：

 

LOGON & LOGOFF Trigger

通常此種 trigger 是用於紀錄使用者登入登出 schema object 時紀錄 log 之用，以下為範例：

 

停用 & 啟用 Trigger

trigger 建立完成後，也可以暫時停用的，之後也還是可以繼續啟用，語法範例如下：

 

錯誤排除

由於 trigger 建立時會經過 compile 的動作，若是錯誤發生後，想要瞭解錯誤的詳細描述進行排除，或是想要瞭解 trigger 之間有哪些相依的關係，就必須透過 Oracle 中 Data Dictionary 的幫忙! 詳細的 Data Dictionary 資訊可以參考官方網站文件。

以下用範例作說明：

當然範例中僅使用到了 USER_ERRORS 與 ALL_DEPENDENCIES 兩個 system view，還有很多其他的 system tables & views 可作為它用，就之後再來慢慢探討了!

張貼者： 於 沒有留言:
標籤：

2008年12月19日 星期五

[Oracle] PL/SQL 例外處理

前言

現在的程式語言中，幾乎都提供了例外處理的功能，當然 PL/SQL 也不能例外，除了提供例外處理的功能外，還可以讓使用者自訂例外喔!

 

Oracle 內建的例外種類

其實在 Oracle 當中就已經內建了許多例外的定義了，例如：NO_DATA_FOUND(當 SELECT INTO 的語法沒有回傳任何資料列時會觸發)、CASE_NOT_FOUND(當條件中沒有任何一項符合且沒有 ELSE 選項時會觸發)，詳細的內建例外說明，可以參考官方網站的文件。

而例外的使用方式如下：

 

以下用個範例說明如何使用 Oracle 的內建範例：

 

自訂例外

自訂的例外在宣告上很有彈性，可以放在 subprogram 中，也可以放在 package specification 或是 package body 中，完全隨使用者方便。

而自訂例外的語法如下：

接著是自訂例外觸發條件與例外處理的範例：

張貼者： 於 沒有留言:
標籤： ,

[Oracle] 相同型態的資料集合 - Collection

何謂 Collection ?

之前有提到 record 型態，可以讓使用者自訂不同的型別集合；而 collection 則是相同資料型態的集合，簡單來說可以將它視為 Array。

collection 是用來存放一群相同型態的資料用，由於可以直接透過索引(index)或是鍵值(key)直接存取，因此速度很快；而在 Oracle 中提供三種不同的 collection：

  • index-by table
    高彈性且高效能的 collection 型態，也是最常用的。

  • nested table
    適合用來處理龐大資料的 collection 型態。

  • VARRAY
    適合用來處理少數資料的 collection 型態。

這裡介紹較為常用的 index-by table，其存在的型態可以是 Array(用索引值取得值)，或是 key-value 的集合(用 key 取得值)。

 

為何 Collection 效能優越?

要了解 collection 為什麼要能優越，就必須先知道 collection 是如何運作的，使用 collection 的方式大致如下：

  1. 定義 cursor，用來取得資料庫的資料用

  2. 定義 index-by table，並指定所包含的資料型態為 cursor 所取得的資料(通常為 %ROWTYPE)

  3. 定義 collection 變數，型態為步驟二所定義的 index-by table

  4. 開啟 cursor，並一次將所有資料取出並存入步驟三所定義的 collection 變數後，關閉 cursor

  5. 後續處理與應用 collection 變數中所包含的所有資料

從上面可以知道 collection 是與 cursor 搭配使用的，將資料從資料庫取出後便暫存於記憶體中，因此處理起來的效能才會優越。

 

使用範例

以下用範例說明：

張貼者： 於 沒有留言:
標籤： ,

2008年12月18日 星期四

[Oracle] Cursor 與 Cursor Variable 的使用

何謂 Cursor ?

cursor 為 PL/SQL 中內建的一種 pointer，可用來擷取整個資料集合，並讓使用者可以逐筆資料的進行存取。

在 Oracle 中，cursor 分為兩種，分別是：

  • implicit(內隱) cursor
    在 Oracle 所提供的功能中，可以發現許多 implicit cursor 的蹤跡，例如：PL/SQL 中的 FOR…LOOP。
    而這一類的 cursor 是沒有辦法透過程式去控制的，而是由 Oracle 本身來進行控制與管理。

  • explicit(外顯) cursor
    此種 cursor 就可以透過開發 PL/SQL 的方式來進行控制與管理，讓管理者可以有很大的彈性來使用。

預設中，Oracle 提供了上限 50 個 cursor 可供使用，但若因為開發需求而導致同時需要更多的 cursor 時，可以透過調整起始參數「OPEN_CURSOR」來達成。

使用 Explicit Cursor

每個 explicit cursor 都會有以下幾個屬性作為程式控制之用：

  • %NOTFOUND
    根據從資料集合中擷取的最後一筆資料，來決定回傳 TRUE(有資料) or FALSE(沒資料)。
  • %FOUND
    與 %NOTFOUND 相反。

  • %ROWCOUNT
    回傳資料集合中所包含的資料筆數。當 cursor 從資料集合擷取過資料後，就可以使用此屬性。

  • %ISOPEN
    若是 cursor 狀態為 open，則回傳 TRUE；反之則回傳 FALSE。

為了暫時存放從 cursor 擷取出來的資料列，必須定義一個暫存的變數，而這個變數的型態通常為一列資料(row data)。

以下介紹 cursor 使用的語法： (更詳細的語法可以參考官方網站文件) 


DECLARE
--宣告 cursor，並指定擷取資料集合的 SQL 語法(query_definition)
CURSOR cursor_name type IS query_definition;

--開啟 cursor(此時會根據宣告時中定義的 SQL 語法取得資料集合)
OPEN cursor_name
   LOOP
   --取得 cursor 目前所在的資料列(必須使用暫存變數來儲存取得的資料列)
   FETCH record INTO buffer_var;

   --當 cursor 已經將資料集合的資料巡覽完畢
   EXIT WHEN cursor_name%NOTFOUND;

   --各式各樣的處理工作
   ...;
   END LOOP;
--關閉 cursor(之後 cursor 就不能再繼續使用)
CLOSE cursor_name;

接著介紹使用範例： 


--PROCEDURE 的實作內容
PROCEDURE eval_department(department_id IN employees.department_id%TYPE) AS
    --宣告暫存變數，儲存 cursor data
    emp_record  employees%ROWTYPE;
    
    --宣告變數，檢查所有員工是否需要重新評估績效
    all_evals BOOLEAN;
    
    --今日日期
    today DATE;
    
    --宣告 cursor
    CURSOR emp_cursor IS 
        SELECT * 
        FROM employees e 
        WHERE department_id = e.department_id;        
BEGIN
    --根據月份判斷是否重新計算績效
    today := SYSDATE;
    IF(EXTRACT(MONTH FROM today) < 6) THEN
        --僅針對新進員工計算績效
        all_evals := FALSE;
    ELSE
        --全部重新計算
        all_evals := TRUE;
    END IF;
    
    --開啟 cursor
    OPEN emp_cursor;
    
    DBMS_OUTPUT.PUT_LINE('Determining evaluations necessary in department #' || department_id);
    
    LOOP
        --透過 cursor 取得一筆資料並存入暫存變數
        FETCH emp_cursor INTO emp_record;
        
        --若 cursor 已將資料取完，則跳出迴圈
        EXIT WHEN emp_cursor%NOTFOUND;
        
        IF all_evals THEN
            --呼叫之前宣告的 procedure
            add_eval(emp_record.employee_id, today);
        ELSIF(eval_frequency(emp_record.employee_id) = 2) THEN
            --呼叫之前宣告的 procedure
            add_eval(emp_record.employee_id, today);
        END IF;
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('Processed ' || emp_cursor%ROWCOUNT || ' records.');
    
    --關閉 cursor
    CLOSE emp_cursor;
END eval_department;

使用 Cursor Variable：REF Cursor

從上面 cursor 的使用方式可以知道，由於 cursor 的使用必須寫在 subprogram 中，並無法在程式中動態的變更所擷取的資料，因此在使用上是靜態的。

然而，若要在程式中動態的改變 cursor 所擷取的資料，或是將 cursor 所取到的資料集合當作參數傳到另一個 subprogram 中，就必須改用 REF Cursor 了!

什麼是 cursor variable 呢? 簡單來說，就是「儲存指向實際 cursor 的指標的變數」，以下用一張簡單的圖來說明：

其中假設實際 cursor 所在的記憶體位址為 0XBC34AD，而 cursor variable 儲存的就是 0XBC34AD 這個記憶體位址，而非 cursor 本身。

也由於 cursor variable 有如此特性，因此假設同時有多個 cursor 同時開啟，就可以將不同的記憶體位址指定給 cursor variable，這樣就可以讓 cursor variable 很快速的取得不同 cursor 從資料庫所擷取到的不同資料。

【附註】cursor 將資料從資料庫取出後，就存放於記憶體中，接著後續的處理就會很快速。

瞭解 cursor variable 與 ref cursor 的關係後，就先來說明使用的語法： 


DECLARE
--定義 ref cursor 與回傳值的資料型態(return_type)，稱為 strongly typed
--也可不定義回傳的資料型態，稱為 weakly typed
TYPE cursor_type IS REF CURSOR RETURN return_type;

--定義 cursor variable(為 cursor pointer，指向實際的 cursor)
cursor_variable cursor_type;

--定義變數儲存 cursor 回傳的資料
single_record return_type;

--使用 cursor variable 開啟 cursor，並依照 query_definition 從資料庫取得資料 
OPEN cursor_variable FOR query_definition;
LOOP
    --取得一筆資料，暫存入 single_record 中
    FETCH record INTO single_record;
    
    --資料集合已經巡覽完畢，離開迴圈
    EXIT WHEN cursor_name%NOTFOUND;
    
    --任何其他實作置於此處
    ...;
END LOOP;

--關閉 cursor
--注意! 不是 cursor_variable 喔!
CLOSE cursor_name;

有一點需要注意到的是，在定義 ref cursor 時不一定需要同時定義回傳值(return_type)。

若是有定義回傳值，則 cursor 取回來的資料為 strongly typed；反之若是沒有定義回傳值，cursor 取回的資料則為 weakly typed。

若是 strongly typed，若是在程式撰寫中發生與定義型別不符的錯誤，就可以在編譯(compile)時期發現；而若是 weakly typed，由於沒有定義回傳型別，因此在使用時可以給定任何型別，若是使用上小心點，似乎也提供了蠻大的彈性。

REF CURSOR 使用範例

首先先將 ref cursor 與其他 subprogram 的定義放到 package specification 中： 


CREATE OR REPLACE PACKAGE EMP_EVAL AS
    -- 宣告 PROCEDURE 與 FUNCTION
    PROCEDURE eval_department(department_id IN employees.department_id%type);

    --計算所有人的績效
    PROCEDURE eval_everyone;

    --定義 ref cursor
    TYPE emp_refcursor_type IS REF CURSOR RETURN employees%ROWTYPE;
END EMP_EVAL;

接著是其他 subprogram 的實作部份： 


--PROCEDURE 的實作內容
PROCEDURE eval_department(department_id IN employees.department_id%TYPE) AS
--宣告 cursor variable (型態為 package specification 中定義的 emp_refcursor_type)
emp_cursor  emp_refcursor_type;

--目前正在處理的部門ID
department_curr departments.department_id%TYPE; 
BEGIN
    department_curr := department_id;
    FOR loop_c IN 1..3 
    LOOP
        --開啟 cursor
        OPEN emp_cursor FOR
            SELECT *
            FROM employees e
            WHERE e.department_id = department_curr;
            
            
        eval_loop_control(emp_cursor);
        
        DBMS_OUTPUT.PUT_LINE('Determining necessary evaluations in department #' || department_curr);
        
        --關閉 cursor
        CLOSE emp_cursor;
        
        department_curr := department_curr + 10;
    END LOOP;
END eval_department;


PROCEDURE add_eval(emp_record IN employees%ROWTYPE, today IN DATE) AS
BEGIN
    --新增資料
    INSERT INTO evaluations VALUES( evaluations_seq.NEXTVAL, 
                                    emp_record.employee_id, 
                                    today, 
                                    emp_record.job_id,
                                    emp_record.manager_id, 
                                    emp_record.department_id,
                                    0);
END add_eval;


PROCEDURE eval_loop_control(emp_cursor IN emp_refcursor_type) AS
    --定義暫存變數，儲存透過 cursor 擷取的資料
    emp_record  employees%ROWTYPE;

    --宣告變數，檢查所有員工是否需要重新評估績效
    all_evals   BOOLEAN;
    
    --今日日期
    today   DATE;
BEGIN
    --取得今天的日期
    today := SYSDATE;
    IF(EXTRACT(MONTH FROM today) < 6) THEN
        all_evals := FALSE;
    ELSE
        all_evals := TRUE;
    END IF;
    
    LOOP
        --透過 cursor 擷取一筆資料
        FETCH emp_cursor INTO emp_record;
        
        --當 cursor 已經將資料擷取完畢，跳離迴圈
        EXIT WHEN emp_cursor%NOTFOUND;
        
        IF all_evals THEN
            add_eval(emp_record, today);
        ELSIF(eval_frequency(emp_record.employee_id) = 2) THEN  --呼叫其他 subprogram，先忽略這邊
            add_eval(emp_record, today);
        END IF;
    END LOOP;
END　eval_loop_control;


PROCEDURE eval_everyone AS
    --宣告 cursor variable (型態為 package specification 中定義的 emp_refcursor_type)
    emp_cursor emp_refcursor_type;
BEGIN
    --開啟 cursor，取得資料集合
    OPEN emp_cursor FOR SELECT * FROM employees;
    
    DBMS_OUTPUT.PUT_LINE('Determining the number of necessary evaluations');
    
    --呼叫 subprogram，傳入 cursor variable
    eval_loop_control(emp_cursor);
    
    DBMS_OUTPUT.PUT_LINE('Processed ' || emp_cursor%ROWCOUNT || ' records.');
    
    --關閉 cursor (上面呼叫的 subprogram 並未把 cursor 關閉)
    CLOSE emp_cursor;
END eval_everyone;

張貼者： 於 4 則留言:
標籤： ,

[Oracle] compile package 時總會出現的警告

每次編輯 package 完後，要 compile 時總是會出現以下的警告訊息：

Warning(1): PLW-06015: 參數 PLSQL_DEBUG 已不使用; 請改用 PLSQL_OPTIMIZE_LEVEL = 1

訊息中告訴我們 PLSQL_DEBUG 已經不使用了，但 Oracle 好像預設還是會開啟的樣子，因此使用下列語法把 PLSQL_DEBUG 功能關閉，並將 PLSQL_OPTIMIZE_LEVEL 設定為 1：

ALTER SESSION SET PLSQL_DEBUG = false;
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 1;

如此一來這惱人的警告訊息就不會再出現囉!

 

參考資料

張貼者： 於 沒有留言:
標籤：

[Oracle] 複雜資料結構 - RECORD

何謂 RECORD ?

RECORD 是由一群各自儲存於不同欄位的相關資料所組合而成的群組，每個資料都有各自的名稱與資料型態。

使用 RECOED 的好處在於當 RECORD 被當作參數丟入 subprogram 處理的時候，或是同時包含多個不同 table 中的不同欄位時，處理的效率是很好的。

 

建立 RECORD 資料型態

首先，先了解建立 RECORD 型態的語法：


接著是實際定義 RECORD 的範例：(定義於 package specification 中)


RECORD 的使用

當 RECORD 定義好後就可以使用了，以下為使用範例：


以下擷取之前範例(EMP_EVAL)的部份程式碼作為範例：
張貼者： 於 沒有留言:
標籤： ,

2008年12月17日 星期三

[Oracle] 開發 & 使用 Stored Procedures

概觀

PL/SQL 是在資料庫端執行的，因此若是將資料庫處理的程式都寫成 stored procedure，撰寫程式時就不需要對資料庫進行頻繁的連線與資料往來，如此一來效能也會相對有所提昇。

在 PL/SQL 中，程式設計的基本單元為獨立的 stored procedure、function 或是 package：

  1. stored procedure：沒有回傳值。

  2. function：有回傳值。

  3. package：類似 Java 中的 package 或是 .NET 中的 namespace 一樣，可以用來區隔相同名稱的 stored procedure 或是 function。

Procedures & Functions

procedure 與 function 是由三個主要部份所組成，分別是：

  1. 宣告部份(declarative-part)
    此部份所使用的關鍵字為「DECLARE」，用來定義變數與常數之用。

  2. 執行部份(executable-part)
    此部份是程式執行的部份，會使用關鍵字「BEGIN」與「END」包住來表示開始與結束。

  3. 例外處理部份(exception-handling part)
    透過關鍵字「EXCEPTION」來處理程式中可能會發生的錯誤情況。

建立 Stored Procedures & Functions

建立 stored procedure 的語法如下：

CREATE OR REPLACE procedure_name(arg1 data_type, ...)
AS
BEGIN
   ....
END procedure_name;

以下來個建立 procedure 的範例：(官方網站使用說明)

CREATE OR REPLACE PROCEDURE ADD_EVALUATION
(
   EVALUATION_ID   IN    NUMBER,
   EMPLOYEE_ID     IN    NUMBER,
   EVALUATION_DATE IN    DATE,
   JOB_ID          IN    VARCHAR2,
   MANAGER_ID      IN    NUMBER,
   DEPARTMENT_ID   IN    NUMBER
)
AS
BEGIN
   NULL;
END ADD_EVALUATION;

再來個建立 function 的範例：

CREATE OR REPLACE FUNCTION CALCULATE_SCORE
(
   CAT     IN  VARCHAR2,
   SCORE   IN  NUMBER,
   WEIGHT  IN  NUMBER
)
   RETURN NUMBER
AS
BEGIN
   RETURN NULL;
END CALCULATE_SCORE;


刪除 Stored Procedures & Functions

語法更簡單了：

DROP PROCEDURE ADD_EVALUATION;

Packages

procedure 與 function 根據其所在之處，有三種不同的有效範圍：

  1. standalone procedure(function)：不包含於任何的 package 或是 subprogram 中，屬於 schema-level。

  2. packaged subprogram：定義於 package 中，由 package 進行區隔。

  3. local subprogram：定義於 subprogram 或是 PL/SQL 程式碼區段中，這一類的程式無法從外界被呼叫，類似 Inner(Nested) Class。

將 procedure 或是 function 定義於 package 之下有什麼好處呢? 若是 standalone 的 procedure(function)，所能接收的參數型態僅能是向量型態(NUMBER、VARCHAR2、DATE)的變數，若是比較複雜的結構像是 RECORD 就沒辦法了；但若是定義在 package 中就可以囉!

而 package 包含了兩個部份，分別是定義(specification)的部份與實作(body)的部份；而整個作法很類似 C 語言中針對 function 的定義與處理，也就是「在 specification 中定義公用的 procedure(or function) 以及相關參數，並在 body 中實作定義」。

關於 package 的詳細使用方式，可以參考官方網站的文章。

建立 Package

範例(specification)：

--定義 PACKAGE
CREATE OR REPLACE PACKAGE EMP_EVAL
AS
   -- 宣告 PROCEDURE 與 FUNCTION
   PROCEDURE EVAL_DEPARTMENT(DEPARTMENT_ID IN NUMBER);
   FUNCTION CALCULATE_SCORE(EVALUATION_ID  IN NUMBER, PERFORMANCE_ID IN NUMBER) RETURN NUMBER;
END EMP_EVAL;

範例(body)：

--設定 PACKAGE 的實作內容
CREATE OR REPLACE PACKAGE BODY EMP_EVAL
AS
   --PROCEDURE 的實作內容
   PROCEDURE EVAL_DEPARTMENT(DEPARTMENT_ID IN NUMBER)
   AS
   BEGIN
       --實作於此處
       NULL;
   END EVAL_DEPARTMENT;
  
   --FUNCTION 的實作內容
   FUNCTION CALCULATE_SCORE(EVALUATION_ID  IN NUMBER, PERFORMANCE_ID IN NUMBER) RETURN NUMBER
   AS
   BEGIN
       --實作於此處
       RETURN NULL;
   END CALCULATE_SCORE;
END EMP_EVAL;

刪除 Package

DROP PACKAGE EMP_EVAL;

變數(variable)與常數(constant)的定義

撰寫 procedure 與 function 的優點是在 PL/SQL 中，可以使用由 Oracle 提供，但不能用於 table 欄位定義的資料型態，像是 BOOLEAN、RECORD、REF … 等等。

以下用個簡單範例說明 variable 與 constant 的使用方式：

--設定 PACKAGE 的實作內容
CREATE OR REPLACE PACKAGE BODY EMP_EVAL
AS
   --PROCEDURE 的實作內容
   PROCEDURE EVAL_DEPARTMENT(DEPARTMENT_ID IN NUMBER)
   AS
   BEGIN
       --實作於此處
       NULL;
   END EVAL_DEPARTMENT;
  
   --FUNCTION 的實作內容
   FUNCTION CALCULATE_SCORE(EVALUATION_ID  IN NUMBER, PERFORMANCE_ID IN NUMBER) RETURN NUMBER
   AS
       --變數(variable)與常數(constant)宣告於此處
       n_score   NUMBER(1, 0);
      
       --VARIABLE
       n_weight  NUMBER;      
      
       --VARIABLE
       max_score   CONSTANT NUMBER(1, 0) := 9;
      
       --CONSTANT
       max_weight  CONSTANT NUMBER(8, 8) := 1;
      
   BEGIN
       --實作於此處
       RETURN NULL;
   END CALCULATE_SCORE;
END EMP_EVAL;


將變數(or 常數)與資料表欄位型態連結

若是要將 variable 或是 constant 與 table 中欄位的型態設定為相同，為了避免欄位型態的改變而造成程式發生錯誤，可透過「%TYPE」關鍵字來處理，以下舉個例子：

--設定 PACKAGE 的實作內容
CREATE OR REPLACE PACKAGE BODY EMP_EVAL
AS
   --PROCEDURE 的實作內容
   PROCEDURE EVAL_DEPARTMENT(DEPARTMENT_ID IN NUMBER)
   AS
   BEGIN
       --實作於此處
       NULL;
   END EVAL_DEPARTMENT;
  
   --FUNCTION 的實作內容
   --將 EVALUATION_ID 的型態與 SCORES 中的 EVALUATION_ID 欄位型態連結
   --將 PERFORMANCE_ID 的型態與 SCORES 中的 PERFORMANCE_ID 欄位型態連結
   FUNCTION CALCULATE_SCORE(EVALUATION_ID  IN SCORES.EVALUATION_ID%TYPE, PERFORMANCE_ID IN SCORES.PERFORMANCE_ID%TYPE) RETURN NUMBER
   AS
       --變數(variable)與常數(constant)宣告於此處
       --將 n_score 的型態與 SCORES 中的 SCORE 欄位型態連結
       --將 n_weight 的型態與 PERFORMANCE_PARTS 中的 WEIGHT 欄位型態連結
       --常數的部分也是連結到 TABLE 的欄位型態
       n_score   SCORES.SCORE%TYPE;             
      
       --VARIABLE
       n_weight  PERFORMANCE_PARTS.WEIGHT%TYPE; 
      
       --VARIABLE
       max_score   CONSTANT SCORES.SCORE%TYPE := 9;            
      
       --CONSTANT
       max_weight  CONSTANT PERFORMANCE_PARTS.WEIGHT%TYPE := 1;
   BEGIN
       --實作於此處
       RETURN NULL;
   END CALCULATE_SCORE;
END EMP_EVAL;

如此一來，若是 SCORE 中的 EVALUATION_ID、PERFORMANCE_ID 的型態改變，程式中定義的 variable 或是 constant 的型態也會跟著改變囉。

設定變數值

設定變數值有很多方式，以下舉例：

直接指定或是經由計算：

FUNCTION CALCULATE_SCORE(EVALUATION_ID  IN SCORES.EVALUATION_ID%TYPE, PERFORMANCE_ID IN SCORES.PERFORMANCE_ID%TYPE) RETURN NUMBER
AS
   n_score     SCORES.SCORE%TYPE;               
  
   --VARIABLE
   n_weight    PERFORMANCE_PARTS.WEIGHT%TYPE;   
   --VARIABLE
   running_total NUMBER := 0; 
  
   --計算中使用
   max_score   CONSTANT SCORES.SCORE%TYPE := 9; 
  
   --CONSTANT
   max_weight  CONSTANT PERFORMANCE_PARTS.WEIGHT%TYPE := 1;
BEGIN
   --實作於此處
   running_total := max_score * max_weight;
   RETURN running_total;
END CALCULATE_SCORE;

從資料庫中取值：

FUNCTION CALCULATE_SCORE(EVALUATION_ID  IN SCORES.EVALUATION_ID%TYPE, PERFORMANCE_ID IN SCORES.PERFORMANCE_ID%TYPE) RETURN NUMBER
AS
   n_score     SCORES.SCORE%TYPE;               
  
   --VARIABLE
   n_weight    PERFORMANCE_PARTS.WEIGHT%TYPE;   
  
   --VARIABLE
   running_total NUMBER := 0; 
  
   --計算中使用
   max_score   CONSTANT SCORES.SCORE%TYPE := 9; 
  
   --CONSTANT
   max_weight  CONSTANT PERFORMANCE_PARTS.WEIGHT%TYPE := 1;
      
BEGIN
   --實作於此處
   SELECT s.SCORE INTO n_score
   --設定 n_score 的值
   FROM SCORES s
   WHERE EVALUATION_ID = s.EVALUATION_ID;
  
   SELECT p.weight INTO n_weight
   --設定 n_weight 的值
   FROM PERFORMANCE_PARTS p
   WHERE PERFORMANCE_ID = p.PERFORMANCE_ID;
  
   --計算 running_total
   running_total := max_score * max_weight;
  
   --回傳值
   RETURN running_total;
END CALCULATE_SCORE;


從資料庫取值，並將新增一筆資料至另外一個 table

procedure add_eval(employee_id in employees.employee_id%type, today in date)
as
   job_id        employees.job_id%type;
   manager_id    employees.manager_id%type;
   department_id employees.department_id%type;
begin
   --從 employees 表格中取得值
   select e.job_id into job_id
   from employees e
   where employee_id = e.employee_id;
  
   select e.manager_id into manager_id
   from employees e
   where employee_id = e.employee_id;
  
   select e.department_id into department_id
   from employees e
   where employee_id = e.employee_id;
  
   --新增資料
   insert into evaluations values(evaluations_seq.NEXTVAL, employee_id, today, job_id, manager_id, department_id, 0);
end add_eval;

流程控制

IF 與 CASE 的搭配範例：

function eval_frequency(employee_id in employees.employee_id%type) return pls_integer
as
   hire_date   employees.hire_date%type;
   today       employees.hire_date%type;
   eval_freq   pls_integer;
   job_id      employees.job_id%type;
begin
   --設定今天日期
   select sysdate into today from dual;
  
   --取得員工開始工作日期
   select e.hire_date into hire_date from employees e where e.employee_id = employee_id;
  
   --判斷年資是否大於十年
   if((hire_date + (interval '120' month)) < employee_id =" employee_id;">


另外還有 WHILE…LOOP 與 LOOP…EXIT WHEN 兩種不同的用法，以下就不寫這麼長的範例了，直接給 syntax，要會用應該也不難了!

WHILE…LOOP

WHILE condition 
LOOP
    ……
END LOOP;

LOOP…EXIT WHEN


LOOP
    ……
    EXIT WHEN condition
    ……
EXIT LOOP

張貼者： 於 2 則留言:
標籤： ,
訂閱： 文章 (Atom)