将Insert Into TABLE与PHP ARRAY和for循环合并 - Merge Insert Into TABLE with an PHP ARRAY and for loop

我有一个PHP数组，来自数据库。这个数组我想插入到一个不同的db/表。两个字段(WEBINV_ID,HOSTNAME)是唯一的。我将每天更新这些表每个脚本。如果条目存在，它们必须跳过插入操作，而应该使用update操作。这工作得很好。但是，如果我在php for循环中运行它，它只会将数组的第一个条目插入或更新到新表中我使用

MERGE INTO TABLE ...

。这是我的输入数组:

[0] => Array
    (
        [CI_ID] => 39778
        [NODEALIAS] => rt-2
        [NODE] => 10.1.2.3
        [SERIALNUMBER] => 8374378584
        [VENDORNAME] => Cisco
        [STATUS] => active
    )
[1] => Array
    (
        [CI_ID] => 72909
        [NODEALIAS] => rt-1
        [NODE] => 10.1.1.3
        [SERIALNUMBER] => 1276731237
        [VENDORNAME] => Cisco
        [STATUS] => active
    )...

这是我的for循环，我尝试插入或更新我所有的设备。

for ($i = 0; $i < count($router); $i++) {
    $sql = "MERGE INTO DEVICES USING ( SELECT "
            . "'" . $router[$i]['CI_ID'] . "' AS WEBINV_ID,"
            . "'" . $router[$i]['NODEALIAS'] . "' AS DEVICE_NAME,"
            . "'" . $router[$i]['NODE'] . "' AS NODE,"
            . "'" . $router[$i]['SERIALNUMBER'] . "' AS SERIALNUMBER,"
            . "'" . $router[$i]['VENDORNAME'] . "' AS VENDORNAME,"
            . "'" . $router[$i]['STATUS'] . "' AS STATUS,"
            . "CURRENT_TIMESTAMP AS DEVICE_INSERT FROM DUAL
    ) S ON ('" . $router[$i]['CI_ID'] . "' = S.WEBINV_ID
         OR '" . $router[$i]['NODEALIAS'] . "' = S.DEVICE_NAME)
    WHEN MATCHED THEN UPDATE SET STATUS = '" . $router[$i]['STATUS'] . "', DEVICE_UPDATE = CURRENT_TIMESTAMP
    WHEN NOT MATCHED THEN INSERT(WEBINV_ID,DEVICE_NAME,NODE,SERIALNUMBER,VENDORNAME,STATUS,DEVICE_INSERT)
    VALUES ('" . $router[$i]['CI_ID'] . "',"
            . "'" . $router[$i]['NODEALIAS'] . "',"
            . "'" . $router[$i]['NODE'] . "',"
            . "'" . $router[$i]['SERIALNUMBER'] . "',"
            . "'" . $router[$i]['VENDORNAME'] . "',"
            . "'" . $router[$i]['STATUS'] . "',"
            . "CURRENT_TIMESTAMP)";
    $stid = oci_parse($gnedb, $sql);
    oci_execute($stid);  // executes and commits
}

我尝试了与"oci_bind_by_name"相同的代码，但结果是相同的，只有第一个条目从数组中使用。任何想法?

如果你使用oci_bind_by_name，我知道你使用Oracle数据库。

也许你应该这样做:

$insert_query = "insert all ";
foreach($router as $record) {
  $fields = "";
  $values = "";
  foreach($record as $field => $value) {
    $fields .= $field . ($field != 'STATUS' ? ',' : ""); // this is only if in the array the last field is STATUS - if it is not you might try to figure out last column diferently or remove last ',' differently
    $values .= "'" . $value . "'" . ($field != 'STATUS' ? ',' : ""); // this is only if in the array the last field is STATUS - if it is not you might try to figure out last column diferently or remove last ',' differently
  }
  $insert_query .= " into devices (" . $fields . ") values (" . $values . ") ";
}

上面的代码将创建一个查询到$insert_query，你只需要将它发送到数据库。您唯一需要弄清楚的是哪些字段应该引号，哪些不应该引号。我引用了所有这些，因为Oracle应该能够正确地转换值-但你需要仔细检查(我没有Oracle可用来检查:))

我使用的SQL结构可以在这里找到

我找到了一个解决方案，我会添加我的工作代码，只有这篇文章是完整的，我希望能帮助其他人。下面是代码:

for ($i = 0; $i < count($router); $i++) {
    $query = "MERGE INTO CORE_INTERFACES USING dual ON "
      // The next row is the match criteria, like If "HOSTNAME" AND "IFINDEX" exist, Then Update else INSERT NEW HOSTNAMES!   
      . "( HOSTNAME = '" . $hostname . "' AND IFINDEX = '" .    t trim($router_interface[0]) . "')" 
      . "WHEN MATCHED THEN UPDATE SET "
      . "IFNAME = '" . trim($router_interface[1]) . "',IFTYPE = '" . trim(clean_iftype($router_interface[2])) . "',IFADMINSTATUS = '" . trim(clean_output($router_interface[3])) . "',"
      . "LAST_UPDATE = CURRENT_TIMESTAMP "
. "WHEN NOT MATCHED THEN INSERT ("
      . "IF_ID,HOSTNAME,IFINDEX,IFNAME,IFTYPE,IFADMINSTATUS,"
      . "FIRST_SEEN "
      . ") VALUES ("
      . " core_interfaces_seq.nextval, "
      . "'" . $hostname . "',"        //$HOSTNAME
      . "'" . trim($router_interface[0]) . "',"       //ifIndex
      . "'" . trim($router_interface[1]) . "',"       //ifName
      . "'" . trim(clean_iftype($router_interface[2])) . "',"         //ifType
      . "'" . trim(clean_output($router_interface[3])) . "',"         //ifAdminState
      . " CURRENT_TIMESTAMP )";
   $stid = oci_parse($gnedb, $sql);
}