取得目前自動編號值對於相依性資料非常重要，如訂單/訂單明細，先將訂單資料儲存至訂單資料表，存入後取得此筆訂單所產生的序號，再者將訂單各筆明細資料與此筆序號一併存入訂單明細表中，以兩者建立關聯。ORACLE的SEQUENCE物件及SQL SERVER的IDENTITY欄位屬性，雖然可分別利用CURRVAL虛擬欄位及IDENT_CURRENT函數取得現行序號值，但此種方式所取得序號值，僅可提供序列值現值概略參考之用，並不可代表實際存入序號值，需取得該筆資料實際存入該欄位的資料值才可確知。ORACLE提供RETURNING指令，取得剛存入資料表中欄位的資料值，可使用於取得SEQUENCE物件產生並存入資料表時的實際數值；MSSQL可由資料表的欄位屬性中指定IDENTITY屬性，則該欄位即可自動產生連續的數值序號，可採用SCOPE_IDENTITY()取得工作階段(Session)有效範圍內最後一個產生的識別值，或OUTPUT指令取得表格異動資料值兩種方式。 以下將以C#為例，取得存入該筆資料的自動編號(MSSQL官方文件稱為識別值)。
MSSQL
1. SCOPE_IDENTITY()
MSSQL2000(含)提供SCOPE_IDENTITY()函數，可用來取得目前工作階段(Session)，有效範圍內最後產生的識別值，『範圍』是指在同一個預儲程序、觸發程序、函數或批次等。另外的類似函數@@IDENTITY，由於此函數並未限制特定範圍，部分情況下將發生錯誤，因此不建議使用。以下將採用SCOPE_IDENTITY()函數以取得存入Event_Log表格中所產生的識別值。
string sConnStr ="Data Source=localhost;Initial Catalog=sqltips;User
ID=sa;Password=admin;";
string sSQL = string.Empty;
sSQL += "INSERT INTO Event_Log \n ";
sSQL += " (Event
) \n ";
sSQL += "VALUES \n ";
sSQL += " (@Event
) \n ";
sSQL += "SET @LogId =
SCOPE_IDENTITY() \n ";
//1. 以SET指令將SCOPE_IDENTITY()儲存至@LogId變數中
SqlConnection conn = new SqlConnection(sConnStr);
conn.Open();
SqlCommand cmd = new SqlCommand(sSQL, conn);
cmd.Parameters.Add("@Event", SqlDbType.Char);
cmd.Parameters["@Event"].Value = "'~!@#$%^&*";
//2. 設定輸出參數@LogId，接收傳回值
SqlParameter pmtLogId = new SqlParameter("@LogId", SqlDbType.Int);
pmtLogId.Direction = ParameterDirection.Output;
cmd.Parameters.Add(pmtLogId);
cmd.ExecuteNonQuery();
//3. 將輸出參數資料值轉換型態並儲存至nLogId
int nLogId = (int)pmtLogId.Value;
|
SQL SERVER提供3種取得IDENTITY識別值的函數，以下一併列出3種函數及其說明。
函數
|
說明
|
@@IDENTITY
|
傳回目前工作階段中，所有範圍內任何資料表所產生的最後一個識別值。
|
SCOPE_IDENTITY()
|
傳回目前工作階段中，目前範圍中任何資料表產生的最後一個識別值。
|
IDENT_CURRENT()
|
傳回特定資料表的最後一個識別值。
語法: IDENT_CURRENT('table_name')
|
其中SCOPE_IDENTITY()函數將取得目前工作階段中，目前範圍中內任何資料表，所產生的最後一個識別值；而@@IDENTITY將取得目前工作階段中，所有範圍內任何資料表所產生的最後一個識別值，恐有誤取識別值之可能；IDENT_CURRENT()函數則取得特定資料表中最後一個識別值。
2. OUTPUT
OUTPUT指令可擷取INSERT、UPDATE或DELETE等操作所影響資料列狀態，可應用於取得新增作業所產生的識別值的作法，此指令可將異動後/前資料列狀態（請注意對應順序）資料分別置於INSERTED及DELETED虛擬資料表中，藉此將更加有效率且完全精準取得識別值。以下將採用OUTPUT命令以取得存入Event_Log表格中所產生的識別值。
string sConnStr ="Data Source=localhost;Initial Catalog=sqltips;User
ID=sa;Password=admin;";
string sSQL = string.Empty;
sSQL += "INSERT INTO Event_Log \n ";
sSQL += " (Event
) \n ";
sSQL += "OUTPUT
INSERTED.Log_Id \n";
sSQL += "VALUES \n ";
sSQL += " (@Event
) \n ";
//1. 以OUTPUT子句將INSERTED虛擬表格欄位Log_Id輸出
SqlConnection conn = new SqlConnection(sConnStr);
conn.Open();
SqlCommand cmd = new SqlCommand(sSQL, conn);
cmd.Parameters.Add("@Event", SqlDbType.Char);
cmd.Parameters["@Event"].Value = "'~!@#$%^&*";
//2.執行，並將結果(輸出值)存入變數sLogId中
string sLogId = cmd.ExecuteScalar().ToString();
|
上述程式碼中INSERTED為虛擬資料表，用以代表用新增或更新作業後所加入的值。在此將取得在入Event_Log表格中所產生的識別值，即當寫入Event_Log資料表時，Log_Id欄位的IDENTITY識別值，其語法為INSERTED.Log_Id。
ORACLE
ORALCE於8i起提供RETURNING子句，可用於取得存入表格的實際資料值，範例程式如下。
string sConnStr ="Data Source=demodb;User Id=sqltips;Password=admin;";
string sSQL = string.Empty;
sSQL += "INSERT INTO Event_Log \n ";
sSQL += "
(Log_Id, Event) \n ";
sSQL += "VALUES \n ";
sSQL += "
(Event_Log_Seq.NEXTVAL, :Event) \n ";
sSQL += "RETURNING
Log_Id INTO :newLogId \n";
OracleConnection conn = new OracleConnection(sConnStr);
conn.Open();
OracleCommand cmd = new OracleCommand(sSQL,conn);
cmd.Parameters.Add("Event", OracleDbType.Varchar2);
cmd.Parameters["Event"].Value = "'~!@#$%^&*";
cmd.Parameters.Add("newLogId", OracleDbType.Int32, ParameterDirection.Output);
cmd.ExecuteNonQuery();
string sLogId=cmd.Parameters["newLogId"].Value.ToString();
|
前述是以RETURNING指令，語法如下。
RETURN <expr> INTO :<variable>
|
沒有留言:
張貼留言