framework/db/oci/QueryBuilder.php
public function upsert($table, $insertColumns, $updateColumns, &$params)
    {
        /** @var Constraint[] $constraints */
        list($uniqueNames, $insertNames, $updateNames) = $this->prepareUpsertColumns($table, $insertColumns, $updateColumns, $constraints);
        if (empty($uniqueNames)) {
            return $this->insert($table, $insertColumns, $params);
        }

        $onCondition = ['or'];
        $quotedTableName = $this->db->quoteTableName($table);
        foreach ($constraints as $constraint) {
            $constraintCondition = ['and'];
            foreach ($constraint->columnNames as $name) {
                $quotedName = $this->db->quoteColumnName($name);
                $constraintCondition[] = "$quotedTableName.$quotedName=\"EXCLUDED\".$quotedName";
            }
            $onCondition[] = $constraintCondition;
        }
        $on = $this->buildCondition($onCondition, $params);
        list(, $placeholders, $values, $params) = $this->prepareInsertValues($table, $insertColumns, $params);
        if (!empty($placeholders)) {
            $usingSelectValues = [];
            foreach ($insertNames as $index => $name) {
                $usingSelectValues[$name] = new Expression($placeholders[$index]);
            }
            $usingSubQuery = (new Query())
                ->select($usingSelectValues)
                ->from('DUAL');
            list($usingValues, $params) = $this->build($usingSubQuery, $params);
        }
        $mergeSql = 'MERGE INTO ' . $this->db->quoteTableName($table) . ' '
            . 'USING (' . (isset($usingValues) ? $usingValues : ltrim($values, ' ')) . ') "EXCLUDED" '
            . "ON ($on)";
        $insertValues = [];
        foreach ($insertNames as $name) {
            $quotedName = $this->db->quoteColumnName($name);
            if (strrpos($quotedName, '.') === false) {
                $quotedName = '"EXCLUDED".' . $quotedName;
            }
            $insertValues[] = $quotedName;
        }
        $insertSql = 'INSERT (' . implode(', ', $insertNames) . ')'
            . ' VALUES (' . implode(', ', $insertValues) . ')';
        if ($updateColumns === false) {
            return "$mergeSql WHEN NOT MATCHED THEN $insertSql";
        }

        if ($updateColumns === true) {
            $updateColumns = [];
            foreach ($updateNames as $name) {
                $quotedName = $this->db->quoteColumnName($name);
                if (strrpos($quotedName, '.') === false) {
                    $quotedName = '"EXCLUDED".' . $quotedName;
                }
                $updateColumns[$name] = new Expression($quotedName);
            }
        }
        list($updates, $params) = $this->prepareUpdateSets($table, $updateColumns, $params);
        $updateSql = 'UPDATE SET ' . implode(', ', $updates);
        return "$mergeSql WHEN MATCHED THEN $updateSql WHEN NOT MATCHED THEN $insertSql";
    }
public function upsert($table, $insertColumns, $updateColumns, &$params)
    {
        /** @var Constraint[] $constraints */
        list($uniqueNames, $insertNames, $updateNames) = $this->prepareUpsertColumns($table, $insertColumns, $updateColumns, $constraints);
        if (empty($uniqueNames)) {
            return $this->insert($table, $insertColumns, $params);
        }

        $onCondition = ['or'];
        $quotedTableName = $this->db->quoteTableName($table);
        foreach ($constraints as $constraint) {
            $constraintCondition = ['and'];
            foreach ($constraint->columnNames as $name) {
                $quotedName = $this->db->quoteColumnName($name);
                $constraintCondition[] = "$quotedTableName.$quotedName=\"EXCLUDED\".$quotedName";
            }
            $onCondition[] = $constraintCondition;
        }
        $on = $this->buildCondition($onCondition, $params);
        list(, $placeholders, $values, $params) = $this->prepareInsertValues($table, $insertColumns, $params);
        if (!empty($placeholders)) {
            $usingSelectValues = [];
            foreach ($insertNames as $index => $name) {
                $usingSelectValues[$name] = new Expression($placeholders[$index]);
            }
            $usingSubQuery = (new Query())
                ->select($usingSelectValues)
                ->from('DUAL');
            list($usingValues, $params) = $this->build($usingSubQuery, $params);
        }
        $mergeSql = 'MERGE INTO ' . $this->db->quoteTableName($table) . ' '
            . 'USING (' . (isset($usingValues) ? $usingValues : ltrim($values, ' ')) . ') "EXCLUDED" '
            . "ON ($on)";
        $insertValues = [];
        foreach ($insertNames as $name) {
            $quotedName = $this->db->quoteColumnName($name);
            if (strrpos($quotedName, '.') === false) {
                $quotedName = '"EXCLUDED".' . $quotedName;
            }
            $insertValues[] = $quotedName;
        }
        $insertSql = 'INSERT (' . implode(', ', $insertNames) . ')'
            . ' VALUES (' . implode(', ', $insertValues) . ')';
        if ($updateColumns === false) {
            return "$mergeSql WHEN NOT MATCHED THEN $insertSql";
        }

        if ($updateColumns === true) {
            $updateColumns = [];
            foreach ($updateNames as $name) {
                $quotedName = $this->db->quoteColumnName($name);
                if (strrpos($quotedName, '.') === false) {
                    $quotedName = '"EXCLUDED".' . $quotedName;
                }
                $updateColumns[$name] = new Expression($quotedName);
            }
        }
        list($updates, $params) = $this->prepareUpdateSets($table, $updateColumns, $params);
        $updateSql = 'UPDATE SET ' . implode(', ', $updates);
        return "$mergeSql WHEN MATCHED THEN $updateSql WHEN NOT MATCHED THEN $insertSql";
    }
public function upsert ( $ table , $ insertColumns , $ updateColumns , & $ params ) { /** @var Constraint[] $constraints */ list ( $ uniqueNames , $ insertNames , $ updateNames ) = $ this -> prepareUpsertColumns ( $ table , $ insertColumns , $ updateColumns , $ constraints ) ; if ( empty ( $ uniqueNames ) ) { return $ this -> insert ( $ table , $ insertColumns , $ params ) ; } $ onCondition = [ 'or' ] ; $ quotedTableName = $ this -> db -> quoteTableName ( $ table ) ; foreach ( $ constraints as $ constraint ) { $ constraintCondition = [ 'and' ] ; foreach ( $ constraint -> columnNames as $ name ) { $ quotedName = $ this -> db -> quoteColumnName ( $ name ) ; $ constraintCondition [ ] = "$quotedTableName.$quotedName=\"EXCLUDED\".$quotedName" ; } $ onCondition [ ] = $ constraintCondition ; } $ on = $ this -> buildCondition ( $ onCondition , $ params ) ; list ( , $ placeholders , $ values , $ params ) = $ this -> prepareInsertValues ( $ table , $ insertColumns , $ params ) ; if ( ! empty ( $ placeholders ) ) { $ usingSelectValues = [ ] ; foreach ( $ insertNames as $ index => $ name ) { $ usingSelectValues [ $ name ] = new Expression ( $ placeholders [ $ index ] ) ; } $ usingSubQuery = ( new Query ( ) ) -> select ( $ usingSelectValues ) -> from ( 'DUAL' ) ; list ( $ usingValues , $ params ) = $ this -> build ( $ usingSubQuery , $ params ) ; } $ mergeSql = 'MERGE INTO ' . $ this -> db -> quoteTableName ( $ table ) . ' ' . 'USING (' . ( isset ( $ usingValues ) ? $ usingValues : ltrim ( $ values , ' ' ) ) . ') "EXCLUDED" ' . "ON ($on)" ; $ insertValues = [ ] ; foreach ( $ insertNames as $ name ) { $ quotedName = $ this -> db -> quoteColumnName ( $ name ) ; if ( strrpos ( $ quotedName , '.' ) === false ) { $ quotedName = '"EXCLUDED".' . $ quotedName ; } $ insertValues [ ] = $ quotedName ; } $ insertSql = 'INSERT (' . implode ( ', ' , $ insertNames ) . ')' . ' VALUES (' . implode ( ', ' , $ insertValues ) . ')' ; if ( $ updateColumns === false ) { return "$mergeSql WHEN NOT MATCHED THEN $insertSql" ; } if ( $ updateColumns === true ) { $ updateColumns = [ ] ; foreach ( $ updateNames as $ name ) { $ quotedName = $ this -> db -> quoteColumnName ( $ name ) ; if ( strrpos ( $ quotedName , '.' ) === false ) { $ quotedName = '"EXCLUDED".' . $ quotedName ; } $ updateColumns [ $ name ] = new Expression ( $ quotedName ) ; } } list ( $ updates , $ params ) = $ this -> prepareUpdateSets ( $ table , $ updateColumns , $ params ) ; $ updateSql = 'UPDATE SET ' . implode ( ', ' , $ updates ) ; return "$mergeSql WHEN MATCHED THEN $updateSql WHEN NOT MATCHED THEN $insertSql" ; }
{@inheritdoc}
@see https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606
{