思路:先从另一个数据库里把数据取出来,
然后,把这个数据集合解析,根据这个数据集合拆分组合成一个创建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[0].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[0].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[0].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[0].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; }