思路：先从另一个数据库里把数据取出来，
然后，把这个数据集合解析，根据这个数据集合拆分组合成一个创建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") + "\r\n");
// 所需要的联合查询的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("\r\n 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[].Columns;
foreach (DataColumn c in columns)
{
sql += c.ColumnName + " " + DBTypeChange(c.DataType.Name) + ",";
}
sql = sql.TrimEnd(new char[] { ',' });
sql += ") ON COMMIT DELETE ROWS ';\r\n";
sql += " end if;"; DataRowCollection rows = his.Tables[].Rows;
foreach (DataRow r in rows)
{
sql += "execute immediate 'insert into " + tmpName.ToUpper() + " values(";
sql += GetRowValueSql(r, true);
sql += ")';\r\n";
} 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[].Columns;
foreach (DataColumn c in columns)
{
sql += c.ColumnName + " " + DBTypeChange(c.DataType.Name) + ",";
}
sql = sql.TrimEnd(new char[] { ',' });
sql += ") ON COMMIT DELETE ROWS ';\r\n";
sql += " end if;"; DataRowCollection rows = his.Tables[].Rows;
foreach (DataRow r in rows)
{
sql += "execute immediate 'insert into " + tmpName.ToUpper() + " values(";
sql += GetRowValueSql(r, true);
sql += ")';\r\n";
} 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;
}