怪异的行为.oracle数据库中日期列的参数

Question

我在Oracle12中遇到了这种奇怪的行为。我在VisualStudio 2019 中使用oracle.managed.dataAccess

情况如下：我使用了一个参数变量(默认情况下是一个字符串(，但没有得到任何结果。语句本身是有效的(没有错误(，但由于date参数的原因，我没有得到任何数据库条目。date_column是数据库中的日期类型。

string sql = "select * from Table where c_id = :myC_id and date_column > to_date(:myDate, 'yyyy-mm-dd hh24:mi:ss')";
OracleCommand cmd = new OracleCommand();
cmd.Connection = connectionstring;
cmd.CommadnText = sql;
cmd.Parameters.Add(new OracleParameter("myC_id",myC_id)); 
cmd.Parameters.Add(new OracleParameter("myDate",myDate)); 

我尝试将参数作为字符串、日期和所有内容。来回转换，仍然不起作用。但是，如果我把日期硬编码成下面这样的字符串，它就起作用了。我正在获取结果和数据。

string sql = "select * from Table where c_id = :myC_id and date_column > to_date('2021-03-30 09:00:00', 'yyyy-mm-dd hh24:mi:ss')";

如果我像下面这样将字符串变量连接到sql字符串，我也会得到数据。

string myDate = "'" + "2021-03-30 09:00:00" + "'";
string sql = "select * from Table where c_id = :myC_id and date_column > to_date({myDate}, 'yyyy-mm-dd hh24:mi:ss')";

但它不适用于参数，有人能告诉我为什么，以及我是如何使它工作的吗？

Answer 1

下面的示例应该会对您有所帮助。

设置

create table tableX (id number(10), c_id number(10), date_column date );
insert into tableX (id, c_id,date_column) values (1,1,to_date('2021-09-09 08:00:00', 'yyyy-mm-dd hh24:mi:ss'));
insert into tableX (id, c_id,date_column) values (2,1,to_date('2021-03-30 08:00:00', 'yyyy-mm-dd hh24:mi:ss'));
insert into tableX (id, c_id,date_column) values (3,2,to_date('2021-09-09 08:00:00', 'yyyy-mm-dd hh24:mi:ss'));
insert into tableX (id, c_id,date_column) values (4,2,to_date('2021-03-30 08:00:00', 'yyyy-mm-dd hh24:mi:ss'));
Commit;

示例

using var con = new OracleConnection(conString);
using var cmd = con.CreateCommand();
con.Open();
int myC_id = 2;
var dateAsString = "2021-03-30 09:00:00";
var culture = CultureInfo.CreateSpecificCulture("fr-FR");
var styles = DateTimeStyles.AssumeLocal;
DateTime myDate = DateTime.Parse(dateAsString, culture, styles);
//DateTime myDate = new DateTime(2021, 3, 30, 9, 0, 0); //2021-03-30 09:00:00
cmd.CommandText = "select id, c_id, date_column from tableX where c_id = :myC_id and date_column > :myDate";
cmd.BindByName = true;
cmd.Parameters.Add(new OracleParameter(":myC_id", OracleDbType.Int32, myC_id, System.Data.ParameterDirection.Input));
cmd.Parameters.Add(new OracleParameter(":myDate", OracleDbType.Date, myDate, System.Data.ParameterDirection.Input));
OracleDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine($"Found Row: ID={reader.GetInt32(0)}, C_ID={reader.GetInt32(1)}, DATE_COLUMN={reader.GetDateTime(2)}");
}

输出

Found Row: ID=3, C_ID=2, DATE_COLUMN=09.09.2021 08:00:00

您说date_column是数据库中的日期类型，而您的输入值myDate是一个字符串。我建议用C#将myDate转换为DateTime。为了进行解析，您必须定义一个格式提供程序。看见https://learn.microsoft.com/de-de/dotnet/api/system.datetime.parse

您的日期字符串看起来像fr-FR。

在您的示例中，您使用SQl Statment中的参数:myC_id和myC_id作为OracleParameter名称。你必须使用相同的名称。

我建议使用集合属性cmd.BindByName = true。默认值为false，参数由位置而非名称绑定。通过位置意味着代码中cmd.Parameters.Add()的顺序和数量必须与SQL语句中绑定参数的顺序相同。