Oracle 10g – Upsert | Fabio Falcinelli

Oracle 10g – Upsert

Recentemente mi è capitato di dover eseguire una insert or update, anche detta upsert, su Oracle 10g. Su MySQL ero solito usare la forma “INSERT … ON DUPLICATE KEY UPDATE …” che funziona piuttosto bene, ma che non era supportata dal database che stavo utilizzando.

La versione 10g di Oracle, però, supporta l’operazione “MERGE”, entrata a far parte dello standard SQL nel 2003, e si è rivelata molto comoda utilizzandola tramite JDBC.

Supponiamo di dover memorizzare in una tabella l’ultima pagina visitata da un utente in un dato giorno, possiamo utilizzare il seguente prepared statement java:

PreparedStatement pstmt = conn.prepareStatement(
"MERGE INTO last_page_table dst "+
"USING ( "+
"SELECT \'"+username+"\' USER_ID, "+
"TO_DATE(\'"+
new java.sql.Date(System.currentTimeMillis())+
"\'YYYY-MM-DD') DATE_FIELD, "+
"\'"+lastPage+"\' LAST_PAGE "+
"FROM DUAL) src "+
"ON (dst.USER_ID = src.USER_ID AND "+
"dst.DATE_FIELD = src.DATE_FIELD) "+
"WHEN MATCHED THEN "+
"UPDATE SET dst.LAST_PAGE = src.LAST_PAGE "+
"WHEN NOT MATCHED THEN "+
"INSERT (dst.USER_ID, "+
"dst.DATE_FIELD, "+
"dst.LAST_PAGE) "+
"VALUES (src.USER_ID, "+
"src.DATE_FIELD, "+
"src.LAST_PAGE)");

In sostanza, l’operazione “MERGE” funziona tra tabelle, prende i valori di una e li inserisce o aggiorna nell’altra in base al matching della condizione nella clausola “ON”.
Tuttavia, la necessità non era di utilizzare valori preesistenti di una tabella ed inserirli o aggiornarli nell’altra, ma l’effettivo popolamento della tabella. Esigenza risolta appoggiandosi alla tabella “DUAL”.
Ovviamente, invece di inserire direttamente nella stringa i valori necessari, si poteva utilizzare anche il ‘?’ e chiamare poi le funzioni della classe PreparedStatement.

PreparedStatement pstmt = conn.prepareStatement(
"MERGE INTO last_page_table dst "+
"USING ( "+
"SELECT ? USER_ID, ? DATE_FIELD, ? LAST_PAGE "+
"FROM DUAL) src "+
"ON (dst.USER_ID = src.USER_ID AND "+
"dst.DATE_FIELD = src.DATE_FIELD) "+
"WHEN MATCHED THEN "+
"UPDATE SET dst.LAST_PAGE = src.LAST_PAGE "+
"WHEN NOT MATCHED THEN "+
"INSERT (dst.USER_ID, "+
"dst.DATE_FIELD, "+
"dst.LAST_PAGE) "+
"VALUES (src.USER_ID, "+
"src.DATE_FIELD, "+
"src.LAST_PAGE)");

Se non vi sono particolari necessità in termini di performance, sicurezza, flessibilità, in genere preferisco inserire tutto direttamente nella stringa in modo da poterla scrivere facilmente su un file di log prima di inviarla al driver:

String sqlMerge = "MERGE INTO ...";
PreparedStatement pstmt = conn.prepareStatement(sqlMerge);
logger.debug("Executing "+sqlMerge);
pstmt.executeUpdate();