[ASP.NET] 解決Oracle IN clause 超過1000個參數

不管在SQL Plus或其他Tool，甚至ASP.NET中，只要出現以下錯誤訊息：
ORA-01795 maximum number of expressions in a list is 1000

不用懷疑，就是你的SQL中，IN的參數超過1000個了。

最快的解決方法有兩種：
1. OR
2. Union

OR Sample如下：

select * from TestTable where ID in (1,2,3,4,...,1000)
union all
select * from TestTable where ID in (1001,1002,...)

Union Sample如下：

select * from TestTable where ID in (1,2,3,4,...,1000) or ID in (1001,1002,...,2000)

在ASP.NET中，Sample如下(使用OR)：

strSQL = "select * from TestTable where {0} ";
int limitCount = 900;
                double dLoopCycle = (IDs.Count / limitCount);
                int loopCycle = Convert.ToInt32(Math.Ceiling(dLoopCycle));
                loopCycle = loopCycle == 0 ? 1 : loopCycle;

                string strIDs = string.Empty;
                string strTempSQL = string.Empty;
                //more than 1000 parameter would raise error
                for (int i = 0; i < loopCycle; i++)
                {
                    int rangeIndex = (i * limitCount);
                    List tempList = IDs.GetRange(rangeIndex, Math.Min(limitCount, IDs.Count - rangeIndex));

                    strIDs = string.Join(",", tempList.ToArray());
                    if (i == 0)
                        strTempSQL = string.Format("ID in ({0})", strIDs);
                    else
                        strTempSQL += string.Format("or ID in ({0}) ", strIDs);
                }

strSQL = string.Format(strSQL, strTempSQL);

[Oracle]Update data from another Table

Web系統不免會有由Excel Upload的資料去更新資料表的需求，當然如果Excel資料一筆一筆的對Table做更新是不符合效益，這將造成對database的transaction過多，而且因為目前需Update的資料表資料量相當大，所以採用的方法如下：
1.Insert data in db temp table from excel data
2.update data from temp table

上述的從Temp table取用資料Update資料表，就切入本篇的主題，可用的方法有三：
1.Update by sub-query
2.Update table view
3.Merge

方法一：

UPDATE bigTable b
   SET (col1,col2,col3) = (
       SELECT t.col1,t.col2,t.col3
         FROM tempTable t
        WHERE b.col = t.col)
where exists(
       SELECT t.col1,t.col2,t.col3
         FROM tempTable t
        WHERE b.col = t.col);

方法二：

UPDATE (
SELECT b.col1 as old_col1,
       b.col2 as old_col2,
       b.col3 as old_col3,
       t.col1 as new_col1,
       t.col2 as new_col2,
       t.col3 as new_col3 
  FROM bigTable b, tempTable t
 WHERE b.col = t.col)
   SET old_col1 = new_col1,
       old_col2 = new_col2,
       old_col3 = new_col3;

方法三：

MERGE INTO bigTable b
 USING (SELECT col1 , col2 , col3 , col
          tempTable t ) t
    ON ( b.col = t.col)
WHEN matched THEN
UPDATE
   SET old_col1 = new_col1,
       old_col2 = new_col2,
       old_col3 = new_col3;

測試結果：
方法一：140 sec

方法二：1 sec

方法三：未測

透過方法二 明顯是效能最佳的解法，但若會被update的bigTable並非符合UK或PK的條件，將會產生"ORA-01779: cannot modify a column which maps to a non-key-preserved table"的錯誤，若你的table不適合建立UK或PK時，可透過hint的方式/*+ BYPASS_UJVC */來忽略UK的檢查。

UPDATE (
SELECT /*+ BYPASS_UJVC */ b.col1 as old_col1,
       b.col2 as old_col2,
       b.col3 as old_col3,
       t.col1 as new_col1,
       t.col2 as new_col2,
       t.col3 as new_col3 
  FROM bigTable b, tempTable t
 WHERE b.col = t.col)
   SET old_col1 = new_col1,
       old_col2 = new_col2,
       old_col3 = new_col3;

參考網址：http://blog.csdn.net/yuhua3272004/archive/2008/08/06/2776121.aspx

[Oracle]處理中文亂碼 -- NLS_LANG設定

開發連結oracle資料庫不可不注意中文的處理問題

首先必須確認DB Server的編碼設定

select userenv('language') from dual;

取得結果為TRADITIONAL_CHINESE.TAIWAN.ZHT16BIG5

接下來就是設定client端的NLS_LANG，只要與DB Server相同，中文顯示就會正確了

1. 開始 -> 執行 -> regedit
2. 找出 HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
3. NLS_LANG改為TRADITIONAL_CHINESE.TAIWAN.ZHT16BIG5

這樣由這台client連到TRADITIONAL_CHINESE.TAIWAN.ZHT16BIG5 的DB Server中文顯示就會是正確的。

另一個議題，單一client要連結的DB Server有多種NLS_LANG，擔任Web Server的client常有這種狀況!!!

以ASP.NET為例：

public void ReadMyData(string connectionString)
{
    //設定Oracle NLS_LANG,在connect db前設定環境變數
    System.Environment.SetEnvironmentVariable("NLS_LANG", "AMERICAN_AMERICA.ZHT32EUC");
    string queryString = "SELECT OrderID, CustomerID FROM Orders";
    using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        OleDbCommand command = new OleDbCommand(queryString, connection);
        connection.Open();
        OleDbDataReader reader = command.ExecuteReader();

        while (reader.Read())
        {
            Console.WriteLine(reader.GetInt32(0) + ", " + reader.GetString(1));
        }
        // always call Close when done reading.
        reader.Close();
    }
}

其關鍵就是

System.Environment.SetEnvironmentVariable("NLS_LANG","AMERICAN_AMERICA.ZHT32EUC");

針對單一Web Site設定連線到DB NLS_LANG，就可以一勞永逸的解決中文問題，不管程式被Deploy到哪裡，
都不受那台Web Server的NLS_Lang影響。

[Oracle]箱型圖(BoxPlot)統計值運算

繪製箱型圖顯示一組數據的分散情況，而若從資料庫中的Raw Data，來繪製箱型圖，則需結算出六個必要數值：最大值、最小值、中位數、平均值、Q1下四分位數、Q3下四分位數

以Oracle範例資料庫中的employees資料表為例，計算每個部門的箱型圖數值：

select department_id,
       max(salary) Upper_whisker,
       min(salary) Lower_whisker,
       round(avg(salary),3) Average,
       PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY salary ASC) Q1,
       PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary ASC) Median,
       PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary ASC) Q3
  from employees t
 group by department_id

[Oracle]PL/SQL與T-SQL的語法比較文件

今日從企圖找出PL/SQL中continue的相似語法，無意間找到了一份PL/SQL與T-SQL的語法用法比較的word文件。也同時也試用google共用文件的功能。

PL/SQL與T-SQL比較Word文件

稍後讀完文件後，再做補充說明吧~~今天雖然是文章發表日，就讓我先偷懶一下下吧!!!

[Oracle]Week的時間區間

日前有一個需求為呈現從今日往前推53週的日期，列出每週的週別與開始與結束日期。每週的開始日期為週二。

呈現如下：

[Oracle]Mutil Row To One Row應用(二)

本次範例應用北風資料For Oracle中 orders 的訂單資料Table做為Sample。本次範例想要取得訂單資料中每天訂單中每個貨運公司的最大運費，並且有優先次序的選擇呈現貨運公司的資料，例如：如果當天有托運公司1,2,3呈現資料的次序為1 > 2 > 3 。

select orderid, orderdate, shipvia, freight from orders t

結果如下：

[Oracle]PL/SQL Developer Tool

提到Oracle的相關開發工具，就非得提到我每天都一定會使用的PL/SQL Developer，用他來做什麼呢??

當然不外乎就是寫SQL Statement，會用到就是SQL Window ，同一個SQL Window裡面可以下多個Statement，只要用分號隔開，SQL Window下方的結果集就會依照Statement順序分頁籤。

[Oracle]Mutil Row To One Row應用(一)

我有一張Log Table，會紀錄著procedure開始執行與結束的時間，該Log Table的紀錄方式為procedure Begin時寫入一筆資料，procedure End時寫入一筆資料，所以每次procedure執行只要是成功的狀態，就會有雙雙成對的Begin/End的資料，我期許可以算出每次procedure執行所需要的時間，於是就有把每兩筆Log Data變成一筆後去做時間運算的需求。

[Oracle]無中生有的月曆

本日課題是無中生有的產出一個月份天數的筆數，並且在今天的日期標示Today的字串於第二個欄位。

第一種解法為利用Oracle內建的all_object的sys table去做無中生有的筆數生成。
第二種解法是同事爬出文來的，用於沒有all_object的select的權限時，利用connect by level去生成所需筆數 。

[Oracle]Not 的妙用

最近遇到了一個需求，讓我驚覺Not 的妙用~~

欄位一等於A，且欄位二等於B，不列入計算
欄位一等於A，且欄位二等於C，不列入計算

這個看似簡單的條件，可讓我小小思考了一下，才寫出自己滿意的寫法。

[Oracle]Group By Rollup Function介紹

有時候User開的需求有列出所有資料後，資料集的最後為所有數值的總計，或是特定群組的小計。

如何在Oracle做到小計與總計的功能呢???答案就是Rollup !!!

Rollup的翻譯：捲起 奇摩字典

在SQL的世界中，就是可以針對Group By的欄位做小計的功能。

[Oracle]安裝Oracle HR範例資料庫

安裝Oracle Express 10g後，Oracle有一份HR的範例資料庫，就在Oracle的系統安裝路徑中。

 依我的Oracle Express為例，範例資料庫的SQL會放置於 C:\oraclexe\app\oracle\product\10.2.0\server\demo\schema\human_resources