博客 >> php Oracle 生成批量插入或更新的 SQL

// 生成批量插入的 SQL 语句 @
function generateBatchUpdateSQL($table, $update, $where) {
    $update_sql = '';
    foreach ($update as $k=>$v) {
        if(stristr(strtolower($v),'to_date')){
            $update_sql .= $k.'='.$v .",";
        }else{
            // echo gettype(23.52);
            switch (gettype($v)) {
                case 'integer':
                case 'boolean':
                    $update_sql .= $k.'='. $v .",";
                    break;
                case 'string':
                    $update_sql .= $k.'='."'". $v ."',";
                    break;
                case 'NULL':
                    $update_sql .= $k.'='."null ,";
                    break;
                case 'double':
                    $update_sql .= $k.'='. floatval($v) .",";
                break;
                default:
                    break;
            }
        }
    }
    $where_sql = "";
    foreach ($where as $k=>$v) {
        // echo gettype(23.52);
        switch (gettype($v)) {
            case 'integer':
            case 'boolean':
                $where_sql .= $k.'='. $v ." and ";
                break;
            case 'string':
                $where_sql .= $k.'='."'". $v ."' and ";
                break;
            case 'NULL':
                $where_sql .= $k.'='."null and ";
                break;
            case 'double':
                $where_sql .= $k.'='.floatval($v)." and ";
                break;
            default:
                break;
        }
    }
    $where_sql .= ';';
    $where_sql = str_replace("and ;", "", $where_sql);
    
    $update_sql .= ';';
    $update_sql = str_replace(",;", "", $update_sql);
    
    $str = sprintf("UPDATE %s SET %s WHERE %s", $table, $update_sql, $where_sql);
    return $str;
}
// 生成批量插入的 SQL 语句 @
function generateBatchInsertSQL($table, $data) {
    $data = isset($data[0])?$data:[$data];
    $key = implode(',', array_keys($data[0]));
    $sql = "INSERT INTO $table ( $key )  ";
    $values = [];
    $i=0;
    foreach ($data as $row) {
        $str = "select ";
        foreach ($row as $value) {
            // 时间参数直接保留  可以只能保留很多Oracle函数，这里只是举例说明
            if(stristr(strtolower($value),'to_date')){
                $str .=  $value .",";
            }else{
                $str .= "'". $value ."',";
            }
        }
        $i++;
        //空值处理
        if(trim($str) == 'select'){
            continue;
        }
        if(count($data)>$i){
            $str .= " from dual union all";
        }else{
            $str .= " from dual ";
        }
        $str =  str_replace(", from", " from", $str);
        $values[] = $str;
    }
    if(empty($values)){
        return "select * from dual";
    }
    $sql .= implode(" ", $values);
    return $sql;
}