oracle 跨数据库取数据

思路：先从另一个数据库里把数据取出来，

然后，把这个数据集合解析，根据这个数据集合拆分组合成一个创建oralce临时表的方法及数据的插入。紧接着就可以写sql语句进行联合查询了。

下面是具体实例的方法：

//获取两个数据库的联合查询 
public DataSet Pacs_depts()
        {
//oracle 帮助类
            OracleHelper sqlHelper = new OracleHelper(); 
//oracle帮助类的数据库连接字符串
            sqlHelper.connectionString = Utility.DB.PubConstant.hisConstr;
            DataSet ds = new DataSet();
            strSql = new StringBuilder();
            parameters = new List<DbParameter>();
            strSql.Append(
                @"
                SELECT   depts.DEPT_ID DEPT_ID, depts.HOSPITAL_ID, depts.DEPT_CODE, 
                      depts.DEPT_NAME, depts.DEPT_ALIAS, depts.PARENT_DEPT_ID, depts.ACCOUNT_DEPT_ID, 
                      depts.INPUT_CODE, depts.INPUT_ALIAS_CODE, depts.FULL_CODE, 
                      depts.FULL_ALIAS_CODE, depts.FLAG_INVALID, depts.START_TIME, depts.STOP_TIME, 
                      depts.DEPT_CLASS_ID, depts.ORDER_NO, depts.FLAG_REGISTER  
FROM    zyCOMM.DEPTS  depts   ");

            DataSet Pacs_depts1 = new DataSet();
            Pacs_depts1 = sqlHelper.Query(strSql.ToString());//从另一个数据库里获取数据集合
            strSql.Clear();
            parameters.Clear();
//创建oracle临时表的生成及往临时表里插入数据。           
strSql.Append(CreateOraTmpSql(Pacs_depts1, "TMP_Pacs_depts") + "
");

// 所需要的联合查询的sql语句
            strSql.Append(@"OPEN :refcursor FOR 'SELECT     studydeptid.STUDYDEPT_ID, studydeptid.MODALITY_VALUE, studydeptid.MODALITY_DESCRIP, depts.DEPT_ID, depts.HOSPITAL_ID, depts.DEPT_CODE, 
                      depts.DEPT_NAME, depts.DEPT_ALIAS, depts.PARENT_DEPT_ID, depts.ACCOUNT_DEPT_ID, depts.INPUT_CODE, depts.INPUT_ALIAS_CODE, depts.FULL_CODE, 
                      depts.FULL_ALIAS_CODE, depts.FLAG_INVALID, depts.START_TIME, depts.STOP_TIME, depts.DEPT_CLASS_ID, depts.ORDER_NO, depts.FLAG_REGISTER
FROM         DIC_STUDYDEPTID  studydeptid LEFT OUTER JOIN
                      TMP_Pacs_depts  depts ON studydeptid.STUDYDEPT_ID = depts.DEPT_ID';");
            strSql.Append("
  END;");
            var p1 = new OracleParameter(":refcursor", OracleDbType.RefCursor);
            p1.Direction = ParameterDirection.Output;
            parameters.Add(p1);

            DataSet Pacs_dept = new DataSet();
            OracleHelper helper = new OracleHelper();
            ds = helper.QuerySql(strSql.ToString(), parameters);

            return ds;

        }

　　

/// <summary>
        /// 返回根据数据集创建oracle临时表的SQL语句
        /// </summary>
        /// <param name="his"></param>
        /// <returns></returns>
        public string CreateOraTmpSql(DataSet his, string tmpName)
        {
            string sql = "declare v_cnt Number; ";
            sql += " BEGIN ";
            sql += " select count(*) into v_cnt from user_tables where table_name = '" + tmpName.ToUpper() + "'; ";
            sql += " if v_cnt=0 then ";
            sql += "execute immediate 'CREATE GLOBAL TEMPORARY TABLE " + tmpName.ToUpper() + "(";
            var columns = his.Tables[0].Columns;
            foreach (DataColumn c in columns)
            {
                sql += c.ColumnName + " " + DBTypeChange(c.DataType.Name) + ",";
            }
            sql = sql.TrimEnd(new char[] { ',' });
            sql += ") ON COMMIT DELETE ROWS ';
";
            sql += " end if;";

            DataRowCollection rows = his.Tables[0].Rows;
            foreach (DataRow r in rows)
            {
                sql += "execute immediate 'insert into " + tmpName.ToUpper() + " values(";
                sql += GetRowValueSql(r, true);
                sql += ")';
";
            }

            return sql;
        }
//创建第二张oracle临时表及插入数据语句
        public string CreateOraTmpSql1(DataSet his, string tmpName)
        {
            string sql = "";
            //sql += " BEGIN ";
            sql += " select count(*) into v_cnt from user_tables where table_name = '" + tmpName.ToUpper() + "'; ";
            sql += " if v_cnt=0 then ";
            sql += "execute immediate 'CREATE GLOBAL TEMPORARY TABLE " + tmpName.ToUpper() + "(";
            var columns = his.Tables[0].Columns;
            foreach (DataColumn c in columns)
            {
                sql += c.ColumnName + " " + DBTypeChange(c.DataType.Name) + ",";
            }
            sql = sql.TrimEnd(new char[] { ',' });
            sql += ") ON COMMIT DELETE ROWS ';
";
            sql += " end if;";

            DataRowCollection rows = his.Tables[0].Rows;
            foreach (DataRow r in rows)
            {
                sql += "execute immediate 'insert into " + tmpName.ToUpper() + " values(";
                sql += GetRowValueSql(r, true);
                sql += ")';
";
            }

            return sql;


        }
        private string GetRowValueSql(DataRow row, bool doubleQuote = false)
        {
            string result = "";
            var columns = row.Table.Columns;
            foreach (DataColumn c in columns)
            {
                switch (c.DataType.Name.ToLower())
                {
                    case "boolean":
                        if (doubleQuote)
                        {
                            result += (row[c].ToString() == "False" ? "''0''" : "''1''") + ",";
                        }
                        else
                        {
                            result += (row[c].ToString() == "False" ? "'0'" : "'1'") + ",";
                        }
                        break;
                    case "string":
                        if (doubleQuote)
                        {
                            result += "''" + row[c].ToString() + "''" + ",";
                        }
                        else
                        {
                            result += "'" + row[c].ToString() + "'" + ",";
                        }
                        break;
                    case "int32":
                        result += row[c].ToString() + ",";
                        break;
                    case "decimal":
                        result += row[c].ToString() + ",";
                        break;

                    default:
                        if (doubleQuote)
                        {
                            result += "''" + row[c].ToString() + "'',";
                        }
                        else
                        {
                            result += "'" + row[c].ToString() + "',";
                        }
                        break;
                }
            }
            result = result.TrimEnd(new char[] { ',' });
            return result;
        }
        private string DBTypeChange(string str)
        {
            string outstr = "";
            switch (str.ToLower())
            {
                case "boolean":
                    outstr = "CHAR(1)";
                    break;
                case "string":
                    outstr = "VARCHAR2(500)";
                    break;
                case "int32":
                    outstr = "NUMBER(10)";
                    break;
                case "decimal":
                    outstr = "NUMBER(18)";
                    break;

                default:
                    outstr = "VARCHAR2(500)";
                    break;
            }

            return outstr;
        }