oracle查询blob数据,C#查询Oracle clob blob数据

最新推荐文章于 2023-10-11 16:15:14 发布

转载最新推荐文章于 2023-10-11 16:15:14 发布 · 831 阅读

文章标签：

#oracle查询blob数据

本文介绍了如何使用SQL查询从数据库中获取特定日期的数据，并展示了通过Oracle存储过程进行复杂查询的方法。包括参数设置、结果集读取等关键步骤。

1.写SQL的话直接写就好了，注意不要出现count聚合函数等，一定要用的话，用count(*) over()

public static ListQueryDate(string date, string sn)

{

try

{

Listlist = new List(); //DataTable2 自定义类，存储DB返回数据

string sql = @" select HISDATA,STARTTIME,SN,DEL from app001.DataTable2 where 1=1";

if (!string.IsNullOrEmpty(date))

{

sql += " and STARTTIME = to_date('" + date + "','yyyy-mm-dd hh24:mi:ss') ";

}

if (!string.IsNullOrEmpty(sn))

{

sql += " and sn = '" + sn + "'";

}

using (IDataReader dr = ExecDataReader(sql))

{

while (dr.Read())

{

DataTable2 model = new DataTable2();

model.HISDATA = dr["HISDATA"].ToString();

model.STARTTIME = dr["STARTTIME"].ToString();

model.SN = dr["SN"].ToString();

model.DEL = dr["DEL"].ToString();

list.Add(model);

}

return list;

}

public static List QueryDate(string ID, string User_Name, string Model_Name, string SN, string Start_Time, string End_Time, string Page, string PageNums)

{

try

{

List list = new List();

OracleConnection conn = CreateConnection();

OracleCommand cmd = new OracleCommand(“APP022.APP_OXYGEN_PRO.PRO_QUERY_DATA”, conn);

cmd.CommandType = CommandType.StoredProcedure;

OracleParameter p1 = new OracleParameter(“ID_v”, OracleDbType.Varchar2, 100);

p1.Direction = System.Data.ParameterDirection.Input;

p1.Value = ID;

cmd.Parameters.Add(p1);

OracleParameter p2 = new OracleParameter(“User_Name_v”, OracleDbType.Varchar2, 100);

p2.Direction = System.Data.ParameterDirection.Input;

p2.Value = User_Name;

cmd.Parameters.Add(p2);

OracleParameter p3 = new OracleParameter(“Model_Name_v”, OracleDbType.Varchar2, 100);

p3.Direction = System.Data.ParameterDirection.Input;

p3.Value = Model_Name;

cmd.Parameters.Add(p3);

OracleParameter p4 = new OracleParameter(“SN_v”, OracleDbType.Varchar2, 100);

p4.Direction = System.Data.ParameterDirection.Input;

p4.Value = SN;

cmd.Parameters.Add(p4);

OracleParameter p5 = new OracleParameter("Start_Time_v", OracleDbType.Varchar2 , 100);

p5.Direction = System.Data.ParameterDirection.Input;

p5.Value = Start_Time;

cmd.Parameters.Add(p5);

OracleParameter p6 = new OracleParameter("End_Time_v", OracleDbType.Varchar2 , 100);

p6.Direction = System.Data.ParameterDirection.Input;

p6.Value = End_Time;

cmd.Parameters.Add(p6);

OracleParameter p7 = new OracleParameter("Page", OracleDbType.Varchar2, 100);

p7.Direction = System.Data.ParameterDirection.Input;

p7.Value = Page;

cmd.Parameters.Add(p7);

OracleParameter p8 = new OracleParameter("PageNums", OracleDbType.Varchar2, 100);

p8.Direction = System.Data.ParameterDirection.Input;

p8.Value = PageNums;

cmd.Parameters.Add(p8);

OracleParameter p9 = new OracleParameter("RET", OracleDbType.RefCursor);

p9.Direction = System.Data.ParameterDirection.Output;

cmd.Parameters.Add(p9);

// cmd.ExecuteNonQuery();

// OracleDataReader dr = (OracleDataReader)cmd.ExecuteScalar();

// OracleDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

// OracleDataReader dr = cmd.ExecuteReader();

// IDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

OracleDataAdapter da = new OracleDataAdapter(cmd);

DataSet ds = new DataSet();

da.Fill(ds);

for (int i = 0; i < ds.Tables[0].Rows.Count; i++)

{

HEARTRATEDATA model = new HEARTRATEDATA(); //自定义类

model.ID = ds.Tables[0].Rows[i]["ID"].ToString();

model.User_Name = ds.Tables[0].Rows[i]["USER_NAME"].ToString();

model.Model_Name = ds.Tables[0].Rows[i]["Model_Name"].ToString();

model.SN = ds.Tables[0].Rows[i]["SN"].ToString();

model.Start_Time = ds.Tables[0].Rows[i]["START_TIME"].ToString();

model.End_Time = ds.Tables[0].Rows[i]["END_TIME"].ToString();

model.Test_Time = ds.Tables[0].Rows[i]["TEST_TIME"].ToString();

model.Heart_Rate = ds.Tables[0].Rows[i]["HEART_RATE"].ToString();

list.Add(model);

}

conn.Close();

return list;

}

catch (Exception e)

{

JUSTEC_LogHelper.LogHelper.WriteLog("Disposes错误提示：", "Disposes.QueryDate方法出错：" + e.ToString());

//throw;

return null;

}

也可以用OracleDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);接收存储过程返回结果集

while(dr.Read())

{

HEARTRATEDATA model = new HEARTRATEDATA();

model.ID = dr[“ID”].Tostring();

…

model.Heart_Rate = dr[“HEART_RATE”].Tostring();

list.Add(model);

}

查询不到结果的时候：

1.先看Oracle中 Clob字段类型是否已经保存了数据，用编辑器查

2.所需执行的SQL 在数据库中查出来的结果是否Clob字段有数据，要使用编辑器看