SQL UPSERT 語法

在 SQL 中的 UPSERT 語法指的是：若資料不存在則新增一筆，否則更新已存在資料。原文見維基百科 Merge (SQL)條目

A relational database management system uses SQL MERGE (also called upsert) statements to INSERT new records or UPDATE existing records depending on whether or not a condition matches.

在 MySQL 實作語法為 INSERT ... ON DUPLICATE KEY UPDATE
實際用法：

INSERT INTO table (IDX, IPAddr, Msg, Status)
VALUES ('1', '127.0.0.1', 'foorbar', 'UNSENT')
ON DUPLICATE KEY UPDATE Status = 'SENT'

說明：

• 若記錄不存在，則新增一筆 (1, 127.0.0.1, 'foorbar', 'UNSENT') 
• 如果原記錄存在，修正更新為 (1, 127.0.0.1, 'foorbar', 'SENT')

針對資料列存在與否，不須仰賴其他程式進行判斷，直接透過 SQL 處理。若資料列不存在，則進行插入(INSERT)動作，否則進行更新(UPDATE)動作。

實例更新(Jan. 22, 2018)：

資料表 daily_transaction 結構 ( idx, date, qty, amount )

• idx 為主鍵自動遞增(AUTO INCREMENT)
• date 為唯一鍵(UNIQUE key)
• qty 代表件數、amount 代表總金額

在此例中只在乎日期(date)是否存在、不管 idx 值；存在則更新資料，否則插入資料：

INSERT INTO daily_transaction (date, qty, amount)  //date 是唯一鍵
VALUES('1070122', '168', '368100')
ON DUPLICATE KEY UPDATE qty='168', amount='368100';

透過 PHP 連線 Oracle 資料庫

先談比較容易處理的 PHP 語法

在 Oracle 資料庫系統中，連線方式有兩種區別：Service Name 與 SID，不要弄錯了。手上的開發環境以 Service Name 為例。

• 語法範例 (Oracle 11g 資料庫)：

$db_id = "ORACLE-USER";
$db_pwd = "ORACLE-PASSWORD";
$oracle_db = "(DESCRIPTION = (ADDRESS_LIST = 
  (ADDRESS = (PROTOCOL = TCP)(HOST=10.1.1.1)(PORT=1521) ) )
  (CONNECT_DATA = (SERVICE_NAME=LODA) ) )";

/* Go, Using UTF-8 Encoding */
$conn = oci_connect($db_id, $db_pwd, $oracle_db, 'utf8');  
$sql = "SELECT X,Y,Z FROM TABLE";

if( !$conn ) { print_r (oci_error()); /* ErrCode */ }
else
{
 try
 {
  $stid=oci_parse($conn, $sql);
  oci_execute($stid);  /* Do Query */
  while( $row = oci_fetch_array($stid, OCI_BOTH) )
  { 
   $X = $row['X']; /* Fetch result, encoding to BIG5 */ 
   $Y_big5 = mb_convert_encoding ($row['Y'],"big5","utf-8");
  }
 }
 catch (Exception $err) { echo $err->getMessage(); }
}
/* Connection release */
if($stid){ oci_free_statement($stid); }
if($conn){ oci_close($conn); }

• 指令不難，但在編碼的地方卡了一下，因為 Client 端只收 Big5 編碼。後來翻到函式 mb_convert_encoding($str, newEncode, oriEnconde) 用來轉換，所以順利解決。

再來是卡關好幾次的環境設定

因為在 Windows 環境下使用了 Uniform Server 作為開發平臺，原以為只要在控制面板內啟用 php_oci8.dll 與 php_pdo_oci.dll 兩項延伸模組就大公告成，沒想到整臺炸掉了！關於連線 Oracle 資料庫所需的函式套件：

• 取得 Oracle Instant Client Package (例：instantclient-basic-nt-11.2.0.3.0.zip)
• 解壓縮後將：oci.dll、ociw32.dll、orannzsbb11.dll、oraociei11.dll 丟到 C:\Windows\System32 目錄下
• 啟用 php_oci8_11g.dll 與 php_pdo_oci.dll 兩項延伸模組
• 重新啟動 Apache 應該沒問題了

最後是 PHP-CLI 環境參數

透過瀏覽器檢索，資料可從 Oracle 中讀出並在網頁中顯示。但此次開發最終要丟進排程執行，所以直覺把 PHP script 餵給 PHP-CLI(即php.exe) 處理應該就行了...。沒想到 PHP-CLI 使用另組環境參數運作(炸)，暗雷何其多。舉凡任何 oci_* 語法均是未定義：

Fatal error: Call to undefined function oci_connect()

查詢 PHP-CLI 使用的環境參數並進行修正(此例為 php-cli.ini)：

C:\UniServerZ\core\php54>php --ini
Configuration File (php.ini) Path: C:\Windows
Loaded Configuration File:         C:\UniServerZ\core\php54\php-cli.ini
Scan for additional .ini files in: (none)
Additional .ini files parsed:      (none)

這邊很明顯，只要在設定內把 extension 補上去就沒問題了。