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 則留言:

[ZeroShell] Firewall 設定

ZeroShell Firewall 說明

由於 ZeroShell 為 Linux-based firewall，在 Linux firewall 中，有三個 table(mangle、nat、filter) 可供管理者進行進階的管理與設定，但為了簡化設定工作，ZeroShell 的 web 管理介面僅提供 filter table 的設定，因此預設只有三個 chain，分別是 FORWARD、INPUT 以及 OUTPUT。

雖然僅有三個 chain 可供使用，但還是可以透過 FORWARD chain 管理流經 ZeroShell 的網路封包；也可以透過 INPUT 與 OUTPUT chain 來管理流入 ZeroShell 與從 ZeroShell 流出的網路封包。

以下是 ZeroShell firewall 的設定畫面：

 

Firewall Chain 的管理

首先要先瞭解 Chain 要如新增與刪除，並在 Chain 中增加自訂的規則，用以下圖來說明：

系統預設有 FORWARD、INPUT 與 OUTPUT 三條 chain，若要新增可以按「New」，若要移除則按「Remove」；而若要針對 chain 中的規則進行新增、修改或是刪除，則是按中間右邊的「Add」、「Change」、「Delete」三個按鈕。

 

Default Policy 的設定

了解 ZeroShell 預設的 firewall chain 後，接著就是要設定每條 Chain 的 Default Policy；假設要針對雙向的流量進行嚴格控管的話，可以將 FORWARD chain 的 default policy 設定為 DROP。(預設為 ACCEPT)

 

設定案例一：FTP 服務

由於之前架設了 FTP 服務，因此這邊以開放 DMZ 中的 FTP 服務為例，進行設定；而為了方便管理，我們針對不同的服務都各自用不同的 chain 將其分開。

因此，首先按「New」增加一條 chain，名稱為「FTP_TRAFFIC」：
 

接著在 Chain 的選項選到剛剛新增的「ftp_traffic」，並準備增加規則：(Chain 的名稱會自動變成全部小寫)

接著按下「Add」新增防火牆的規則：

以下為幾項設定時要注意的部份：

  • Destination IP：目的地 IP，要指定是 DMZ 中提供 FTP 服務的主機 IP address

  • Protocol Matching：選擇所要過濾的通訊協定種類

  • Source Port：一般 client 都會以 1024 到 65535 間隨機取一個 port 來與 server 進行連線

  • Dest. Port：FTP service 的 port number 為 21

  • Connection State：「NEW」必須要勾選，才可以讓 client 正確的連線

  • ACTION：這個部份必須選擇「ACCEPT」

這裡比較需要注意的是，若是所有設定都省略，僅從 Layer 7 Filter 中選擇 FTP 服務，這樣還是不行的，因為在 client 與 server 建立連線的 3-way handshake 過程中，是無法判斷是那一種 layer 7 的 application，因此這邊還是必須透過設定 protocol 與 port number 的方式來開放特定服務的網路流量。

接著還要回到 FORWARD chain，將 FTP_TRAFFIC 這條 chain 加入規則中：

在 ACTION 的部份要選擇「CHAIN」，並在 Jump to 的選項選擇「ftp_traffic」這一條 chain。

除了加入 ftp_traffic chain 之外，還要在 FORWARD chain 中加入另外一條規則，讓連線建立後，後續的連線可以持續的通過：

為了讓已經建立的連線可以持續的傳輸網路封包，要勾選 Connection State 中的「ESTABLISHED」及「RELATED」兩個選項，並在 ACTION 中選擇「ACCEPT」。

最後，會設定好以下的規則：

 

設定案例二：Web 服務

接著這個部份是要開放電腦瀏覽網頁的權限，首先必須先開放 DNS 服務的網路流量： (DNS_TRAFFIC)

要開放的設定為：

  • TCP port 53

  • UDP port 53

  • Connection State = NEW

再來是開放 HTTP 與 HTTPs 的網路流量：(HTTP_TRAFFIC)

要開放的設定為：

  • TCP port 80 & 443

  • Connection State = NEW

最後在 FORWARD chain 中將這兩條 chain 加入規則中，就完成設定了：

於 沒有留言:

2008年12月16日 星期二

[ZeroShell] QoS 設定

前言

QoS(Quality of Service) 的用途為流量管理，功能在於確保特定服務、特定主機可以根據管理者設定的規則，讓頻寬可以得到保證，相對也可以更穩定的提供服務，當然也可以用來杜絕相當耗損頻寬的軟體。(例如：eMule、BT …. 等等)

扮演流量管理的主機，必須位於兩個不同的網路區段之間(可以是同網段，只是實體是隔開的)，因此通常提供 QoS 功能的都 Router(連結不同網段) 或是 Bridge(連結相同網段)，而在不同的網路區段中互相傳輸的網路封包，就會受到 QoS 中的規則所規範。

 

設定時注意事項

在進行 QoS 的設定時，首先必須要先瞭解 QoS 的設定限制，並不是很簡單的直接設定上傳下載的限制即可(如果有，就是有人提供了很方便的設定工具才有辦法達成)。

QoS 的設定限制，在於「只能限制往外傳的流量，無法限制接收到的流量」。就像寄信一樣，你可以限制自己不要寄很多信，但是卻無法阻止別人寄很多信給自己。

瞭解 QoS 的限制後，在設定時就不會發生觀念錯誤而導致設定後結果不如預期的情形產生。

 

QoS 如何作到雙向流量管控

知道了 QoS 的管控限制後，雙向流量管控的需求還是在，但是要如何作呢? 以下先列出網路架構圖：

假設以外部 client(Windows XP，10.1.16.200) 到內部 server(Ubuntu，192.168.100.20) 的流量為例，假設內部 server 提供了 FTP 的服務，而若是要使用 QoS 來管控 client 的上傳與下載，就必須從兩個方面著手：

  1. client 的下載
    此時網路流量是由內部 server 傳到外部，若以 QoS 僅能管控外傳流量的限制為前提下，就必須從 ETH00(10.1.16.222) 網路介面來著手，藉由管控從 ETH00 網路介面流出的網路流量，來達到管控 client 下載流量的目的。

  2. client 的上傳
    反之若要管控 client 的上傳流量，就必須改從 ETH01(192.168.100.254) 介面來著手，藉由管控從 ETH01 網路介面的外傳流量，來達到管控 client 上傳流量的目的。

因此從上面的案例說明就可以瞭解，若要達到同時控管上下傳的流量，就必須要從不同的網路介面卡上著手。

 

ZeroShell 的 QoS 設定

ZeroShell 提供了 QoS 的功能，甚至還可以跟 Layer 7 的協定搭配使用，功能可說是相當強大，以下是我們的設定案例：

Client：Windows XP (10.1.16.200)

Server：Ubuntu (DMZ：10.1.16.224 –> 192.168.100.20)

其中 Server 提供了 FTP 服務，我們要使用 QoS 作到限制 client 上傳流量為 2048 Kbits/s，下載流量為 512 Kbits/s。

，但在設定上稍嫌複雜，因為 ZeroShell 並沒有簡化原有 QoS 的相關設定，還是必須分成以下四個部份進行設定：

1、QoS 功能的啟用

首先必須要啟用 QoS 的功能：

選擇「NETWORK –> QoS –> Interface Manager」，進入網路介面的設定，將要管控流量的網路介面勾選啟用，並按下「Activate last Changes」儲存設定。

此外，還可以按下「Global Bandwidth」設定該網路介面的最大頻寬與保證頻寬。

 

2、設定 QoS 類別

接著要設定 Qos 類別，主要是要設定類別的相關描述以及頻寬上限的設定，選擇「Class Manager」：

接著會出現以下設定畫面：

下方可以看到一個內建的預設類別，按「New」可以新增一個類別。

首先要先輸入類別名稱：(首先要設定 FTP 上傳的類別，因此輸入「FTP_UP」)

接著要輸入類別的詳細資訊：

畫面中所要輸入的包含 Description(類別描述)、Priority(封包優先權)、Maximum(最大使用頻寬)、Guaranteed(保證頻寬) … 等等，根據我們的模擬測試，要將優先權設定為中，最大使用頻寬設定為 2048 Kbits/s，設定完畢後要記得按「Save」儲存設定。

接著要加入 FTP 下載的類別(FTP_DOWN)，相關設定畫面如下：

 

3、設定 QoS 類別與規則的對應

QoS 類別設定完後，就必須要設定封包檢查規則，並與 QoS 類別對應，選擇「Classifier」：
 

按下「Add」新增封包檢查規則，就會出現以下畫面：

針對以上有使用到的設定僅行仔細說明：

  • Apply to：Routed and Bridged Packets
    這個設定表示無論 ZeroShell 扮演的是 router 或是 bridge，只要有網路封包流經都必須由 QoS 管控。

  • Input & Output
    這個部份就要特別注意了，因為「QoS 僅能管控外傳的網路流量」的緣故，途中設定 Output 為 ETH01，表示要管制從 ETH01 外傳的網路流量，亦即 client 的上傳流量。

  • Layer 7 Filter：FTP – File Transfer Protocol – RFC 959
    設定與 L7-Filter 搭配，用來判斷是否 FTP 的流量。(使用 TCP port 21 未必就真的是 FTP 的流量)

  • TARGET CLASS：FTP_UP
    選擇要繫結的 QoS 類別。(由於此處設定的判斷規則為 client 的 FTP 上傳流量，因此選擇 FTP_UP)

  • LOG：5 Minute
    是否啟用 log 功能，此處選擇每 5 分鐘 log 一次。

最後記得要按「Confirm」儲存 QoS 的規則設定。

了解設定方式後，設定檢查 FTP 下載的規則就容易多了：

 

4、將 QoS 類別與網路介面繫結

當規則繫結到 QoS 類別後，最後就要將 QoS 類別與網路介面進行繫結，選擇「Interface Manager」：

找到所要繫結的網路介面，並按下右邊的「Add Class」新增繫結：

這裡需要注意兩點：

  • client FTP 下載是管控 ETH00 的對外流量，因此將 FTP_DOWN 繫結到 ETH00 上

  • client FTP 上傳是管控 ETH01 的對外流量，因此將 FTP_UP 繫結到 ETH01 上

最後確定 QoS 服務有啟用，按下「Activate last Changes」就完成設定了。

 

Qos 使用成效檢驗

到底設定 Qos 效果如何，以下有截下幾張圖來說明，首先為尚未開啟 QoS 功能時，client 的上傳與下載大概都有 8~9 MBytes/s 的速度：

上面設定將上傳限定為 2048 Kbits(256 KBytes)/s，開啟 QoS 後的實際速度為：

下載限制的部份為 512Kbits(64 KBytes)/s，開啟 Qos 後的實際速度為：

因此看得出來，QoS 也不是相當的精確，實際上在傳輸時，速度還是會上上下下一個範圍在傳輸(大約 10%)，但最大是肯定沒有超過上面設定所設定的最大頻寬，可見 QoS 的設定是有成功的。

於 1 則留言:

2008年12月15日 星期一

[.NET] 開發 Windows Service

今天臨時幫同事寫了一個 Windows Service，只是簡單的定期去偵測遠端的主機是否存活，大概有三個部份：

1、Windows Service develop

相關參考連結：

 

2、Timer 控制項的使用

由於是開發 Windows Service，因此不能用「System.Windows.Forms.Timer」，而是要改用「System.Timers.Timer」；而 event 的部份也必須改成 Elapsed 而不是 Tick。

相關參考連結：

 

3、偵測網路是否存活

.NET Framework 中提供了 Ping 與 PingReply 兩個 class 的搭配，讓開發者可以很容易的使用 ping 的功能，而不用自己寫 socket，真的蠻方便的!

於 沒有留言: