.NET 存取 Oracle 取得新增資料自動產生 ID 完整實作

Oracle 自增 ID 的挑戰

SQL Server 有 Identity 欄位，Oracle 則需使用 Sequence（序列）搭配觸發器或存儲過程，並在高併發下正確取回新增的 ID。

資料表結構範例

CREATE TABLE test (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(100)
);

建立 Sequence

CREATE SEQUENCE SEQ_test
MINVALUE 1
MAXVALUE 99999999999999999999999999
START WITH 21
INCREMENT BY 1
CACHE 20;

Sequence 具備 NEXTVAL（下一值）與 CURRVAL（當前值）屬性。

插入語句範例

INSERT INTO test (id, name) VALUES (SEQ_test.NEXTVAL, 'name1');
-- 緊接著取得 ID
SELECT SEQ_test.CURRVAL FROM dual;

注意：CURRVAL 只在當前 Session 的 INSERT 之後短暫有效，必須在下一個資料庫操作前取回，否則失效。

建立存儲過程：封裝插入與取回 ID

CREATE OR REPLACE PROCEDURE p_GetItemID(
    strInsertSQL IN VARCHAR2,
    seqName      IN VARCHAR2,
    ID           OUT VARCHAR2
) IS
    strSql VARCHAR2(200);
BEGIN
    EXECUTE IMMEDIATE strInsertSQL; -- 執行插入
    strSql := 'SELECT ' || seqName || '.CURRVAL FROM dual';
    EXECUTE IMMEDIATE strSql INTO ID; -- 取回 ID
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END p_GetItemID;

C# 呼叫存儲過程取得新 ID

public string ExecuteProcedure(string strInsertSQL, string proName, string seqName)
{
    try
    {
        if (m_Connection_orc.State != ConnectionState.Open)
            m_Connection_orc.Open(); // 從 web.config 取得連線字串
    }
    catch (Exception ex) { /* 記錄錯誤 */ }

    try
    {
        using (OracleCommand cmd = new OracleCommand(proName, m_Connection_orc))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("strInsertSQL", OracleType.VarChar).Value = strInsertSQL;
            cmd.Parameters.Add("seqName", OracleType.VarChar).Value = seqName;
            cmd.Parameters.Add("ID", OracleType.VarChar).Direction = ParameterDirection.Output;

            cmd.ExecuteNonQuery();
            return cmd.Parameters["ID"].Value.ToString();
        }
    }
    catch (Exception ex)
    {
        throw; // 保留堆疊追蹤
    }
    finally
    {
        CloseConnection();
    }
}

使用範例

string insertSql = "INSERT INTO test (id, name) VALUES (SEQ_test.NEXTVAL, '新資料')";
string newId = ExecuteProcedure(insertSql, "p_GetItemID", "SEQ_test");

關鍵優勢

• 單一資料庫往返完成插入與取回，避免併發下讀錯 ID。
• 存儲過程參數化，支援不同資料表、不同 Sequence 復用。
• C# 端僅需呼叫一個方法，邏輯清晰。