Oracle 临时表 OracleDataAdapter 批量更新，Merge into

        /// <summary>
        /// 注意，字段名必需大小写保持一致
        /// </summary>
        protected static string updateSql = @"
Merge into Table_Name  T 
Using TempTable  S 
ON (T.USER_ID = S.USERID )
WHEN MATCHED 
THEN UPDATE SET T.NICK_NAME = S.NICKNAME,T.PHONENUMBER = S.PHONENUMBER ,T.AVATAR = S.AVATAR,T.JOB = S.JOB
WHEN NOT MATCHED THEN
Insert (USER_ID,NICK_NAME,PHONENUMBER,AVATAR,JOB)
VALUES(S.USERID,S.NICKNAME,S.PHONENUMBER,S.AVATAR,S.JOB)";

protected static string crateTemplateSql = @"
declare tableExistedCount number;   --声明变量存储要查询的表是否存在
 p_sql varchar(200);
Begin
     select count(1) into tableExistedCount  from user_tables t where t.table_name = upper('TempTable'); --从系统表中查询当表是否存在
DBMS_OUTPUT.PUT_LINE(tableExistedCount);
     if tableExistedCount  >0 then --如果不存在，使用快速执行语句创建新表
     DBMS_OUTPUT.PUT_LINE('删除旧表');
     for r in (select a.table_name from user_tables a where a.table_name =upper('TempTable')) loop
      p_sql:='TRUNCATE TABLE '||r.table_name;
      DBMS_OUTPUT.PUT_LINE(p_sql);
      execute immediate p_sql;
      p_sql:='drop table '||r.table_name;
      DBMS_OUTPUT.PUT_LINE(p_sql);
      execute immediate p_sql;
      end Loop;
     
     end if;
     
     execute Immediate
         '
        create global temporary table TempTable (
          userId Varchar(255),
          nickName Varchar(255) NULL,
          phonenumber Varchar(255) NULL,
          avatar Varchar(255) NULL,
          job Varchar(255) NULL
        )
         on commit delete  rows
 ';
end;";

        /// <summary>
        /// SqlBulkCopy 批量更新数据
        /// </summary>
        /// <param name="dataTable">数据集</param>
        /// <param name="crateTemplateSql">临时表创建字段</param>
        /// <param name="updateSql">更新语句</param>
        public static void BulkUpdateData(DataTable dataTable, string crateTemplateSql, string updateSql)
        {
            using (var conn = new Oracle.ManagedDataAccess.Client.OracleConnection(ConfigurationManager.ConnectionStrings["dbCon"].ConnectionString))
            {
                OracleTransaction trans = null;//关键第一步
                using (var command = new Oracle.ManagedDataAccess.Client.OracleCommand("", conn))
                {
                    try
                    {
                        conn.Open();
                        trans = conn.BeginTransaction();
                        //数据库并创建一个临时表来保存数据表的数据
                        command.CommandText = crateTemplateSql;
                        command.ExecuteNonQuery();
                        OracleCommand selectCmd=conn.CreateCommand();
                        //查询表头
                        selectCmd.CommandText = "select userId,nickName,phonenumber,avatar,job from TempTable where rownum=0";
                        OracleDataAdapter myDataAdapter = new OracleDataAdapter(selectCmd);
                        DataTable data = new DataTable();
                        myDataAdapter.Fill(data);
                        foreach (DataRow row in dataTable.Rows) {
                            DataRow newRow = data.NewRow();
                            newRow["userId"] = row["userId"];
                            newRow["nickName"] = row["nickName"];
                            newRow["phonenumber"] = row["phonenumber"];
                            newRow["avatar"] = row["avatar"];
                            newRow["job"] = row["job"];
                            
                            data.Rows.Add(newRow);
                        }
                        //插入语句
                        myDataAdapter.InsertCommand=new OracleCommand("insert into TempTable(userId,nickName,phonenumber,avatar,job) values(:userId,:nickName,:phonenumber,:avatar,:job)", conn);

                        
                        OracleCommandBuilder custCB = new OracleCommandBuilder(myDataAdapter);
                        custCB.ConflictOption = ConflictOption.OverwriteChanges;
                        custCB.SetAllValues = true;
                        
                        foreach (DataColumn c in dataTable.Columns)
                        {
                            OracleParameter oraParameter = new OracleParameter(c.ColumnName, OracleDbType.Varchar2);
                            oraParameter.SourceColumn = c.ColumnName;
                            oraParameter.SourceVersion = DataRowVersion.Current;
                            myDataAdapter.InsertCommand.Parameters.Add(oraParameter);
                        }

                        int count = myDataAdapter.Update(dataTable);

                        dataTable.AcceptChanges();
                        myDataAdapter.Dispose();
                        /*
                        var cmd = new OracleCommand("select count(*) from transaction_temp_tb_lz2 --where user_Id in('615896266','602185346') ", conn);
                        var obj = cmd.ExecuteScalar();
                        */
                        // 执行Command命令 使用临时表的数据去更新目标表中的数据  然后删除临时表
                        command.CommandTimeout = 300;
                        command.CommandText = updateSql;
                        try {
                            int num = command.ExecuteNonQuery();
                            trans.Commit();
                        }
                        catch (Exception ex) {
                            trans.Rollback();
                            throw ex;
                        }
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }

 

整体需求如下：

从外部获取数据，要更新到数据库中的一张表，且每天都需要进行数据同步

实现思路

在数据库中创建一个事务临时表，表结构与外部获得的数据保持，将数据导入到临时表，然后使用 Merge into 语法进行增删改 操作

 

可能会遇到ORA-14452: 试图创建, 更改或删除正在使用的临时表中的索引

遇到问题，把SQL代码放到PLSQL中进行排查，很多时候都是不够细心，反而花了更多的时间去排查