Jon-G blogs for Net-Entwicklung.de

24.06.2009

Multiple Inserts in Oracle (example using PHP)

Filed under: Coding, Databases — Schlagwörter: , , — Jonathan Gilbert @ 00:17

Everybody knows how to make multiple inserts into MySQL:

INSERT INTO [table_name] ([col_1], [col_2])
  VALUES
    ([val_1_1], [val_1_2]),
    ([val_2_1], [val_2_2]),
    ([val_3_1], [val_3_2])

That´s easy! But multiple inserts to an Oracle DB don´t work like that… here are two ways of doing the same job with Oracle:

insert all
  into [table_name] ([col1], [col2]) values ([val1_1], [val1_2])
  into [table_name] ([col1], [col2]) values ([val2_1], [val2_2])
  into [table_name] ([col1], [col2]) values ([val3_1], [val3_2])
  select 1 from dual

or alternatively

insert into [table_name] ([col1], [col2])
  select [val1_1], [val1_2] from dual
  union all
    select [val2_1], [val2_2] from dual
  union all
    select [val3_1], [val3_2] from dual

This is useful if you need to insert a lot of rows into a table – which usually occurs in a job like data import from other sources rather than as a result of user interaction. Running a PHP script for example to insert 50.000 rows into a table would fire 50.000 inserts if you don´t use multiple inserts. This is slow and can also cause you to run into memory problems.

Here is a simple example of how to do this in PHP:

class MyImportTest extends MyDatabaseModel
{
 protected static $_parallelImports = 50;
 protected static $_insertCache = array();

 public static function insertImportDataIntoDb($inputData)
 {
  $added = 0;
  foreach ($inputdata as $inputArray) {
    $added += self::_addCaseToInsertCache($inputArray['col1'], $inputArray['col2']);
    if ($added % self::$_parallelInserts == 0) {
      self::_flushInsertCache();
    }
  }
	if ($added % self::$_parallelInserts != 0) {
    self::_flushInsertCache();
  }
 }

 function addCaseToInsertCache($col1, $col2)
 {
  // maybe do validity checks here and return 0 if an element is not added
  self::$_insertCache[] = array($col1, $col2);
  return 1;
 }

 function flushInsertCache()
 {
  $db = self::getDb();
  $sql = 'INSERT INTO "TABLE_NAME" ("COL1", "COL2")';
  $values = '';
  foreach (self::$_insertCache as $insert) {
            $values .= (empty($values)) ? '' : ' UNION ALL ';
            $values .= 'SELECT ' .
                $insert[0] . ', ' .
                $insert[1] . ', ' .
                $insert[5] . ' FROM DUAL';
        } // if using string values, then don't forget to use quotes!
        $db->query($sql . $values);
        self::$_insertCache = array();
    }
 }
}
Advertisements

2 Kommentare »

  1. In many cases using oci_bind_array_by_name() and calling a PL/SQL function that uses FORALL can be faster (and doesn’t suffer from SQL inject issues). See „Array Binding and PL/SQL Bulk Processing“ on page 165 of http://www.oracle.com/technology/tech/php/pdf/underground-php-oracle-manual.pdf

    Kommentar von Christopher Jones — 24.06.2009 @ 18:31

    • Happy to see that someone other than me is actually reading my drivel 😉 Thanks for the note about FORALL – didn’t know about that yet – most of my work uses MySQL where this is a lot easier 😉 But I just wanted to publish these workarounds that I found. As far as SQL injection goes, I didn’t include any parsing to keep the example clear. Obviously, you need to parse values before pushing them into the input array or when creating the SQL string. I’m currently using the Zend Framework which has a set of handy methods for dealing with this (see here).

      Kommentar von Jonathan Gilbert — 24.06.2009 @ 19:09


RSS feed for comments on this post. TrackBack URI

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s

Bloggen auf WordPress.com.

%d Bloggern gefällt das: