ODP.NET更新...返回到...多行，参数类型 [英] ODP.NET UPDATE... RETURNING INTO... multiple rows, Parameter type

问题描述

我想知道是否可以采取以下行动方案:

我正在实现一个简单的数据输入应用程序.每个可编辑的记录都驻留在一个表中.用户必须同时编辑多个记录(以减少数据输入时间).我目前想做的是在记录中实现某种锁定机制.

我想到的第一件事是执行(ExecuteNonQuery)UPDATE some_table SET status ='locked'WHERE rownum = 1 RETURNING id INTO:locked_id和一个输出参数，为我提供了锁定记录的ID.我可能会执行另一个SELECT语句以根据此ID读取我想要的任何其他信息.

虽然上述方法似乎适用于单个锁定记录，但我真的不知道如何对多个返回的行执行此操作. -在上面的示例中，如果WHERE子句是"rownum< 4"而不是"rownum = 1"怎么办?

我的OracleParameter应该是什么样的? 当我这样指定我的return oracle参数(适用于单行)时，

OracleParameter p = cmd.Parameters.Add("ID", OracleDbType.Int32, 10, 0, ParameterDirection.Output);

给出了ORA-24369.我尝试使用ArrayBindCount和数组作为参数的.Value属性，但无济于事.

还有，您发现整个特定的锁定方式有什么根本不对的地方吗?

谢谢

一些说明- 1.有一个名为user_name的列，可以保存例如登录用户的名称-我将其与锁定UPDATE一起更新 2.还有另一列我称为locked_timestamp，用于保存记录被锁定的时间.可能会有一个后台进程在夜间运行，并将锁定的记录重置为未锁定"-鉴于这种情况不会经常发生，并且由于崩溃等原因而保持锁定"的记录的比例很小.与已编辑记录的数量比较 3.并发更新应该由Oracle自动处理，是的，我在每次UPDATE期间都使用事务处理-因此几乎没有机会同时由两个用户锁定记录-或我所听到的(尝试一下，一旦我找到如何更新...返回....多行)

解决方案

最后，经过数小时的搜索和使用代码，我得出了以下结论(除了头痛):

使用

的组合，我得到了想要的东西

1. 此处将UPDATE..RETURNING语句包装到匿名PL/SQL块中(以BEGIN开始并以END结尾；)-这没有任何解释，我仍然不知道为什么行为会有所不同
Oracle文档中有关OracleCommand的
2. 代码片段，特别是有关绑定PL/SQL的部分带有大容量收集的关联数组(无法使简单的数组绑定起作用.):

i was wondering if the following scheme of action is possible:

i am implementing a simple data entry application. each editable record resides in a table. the user has to edit multiple records at the same time (to reduce data entry time). what i try to do at the moment is to implement some sort of locking mechanism in the records.

the first thing i thought of was to execute (ExecuteNonQuery) an UPDATE some_table SET status = 'locked' WHERE rownum = 1 RETURNING id INTO :locked_id with an output parameter, giving me the ID of the locked record. i may execute another SELECT statment to read whatever other information i want, basing on this ID.

while the above approach seems to work for a single locked record, i don't really know how to do this for multiple returned rows. - what if, in the above example, the WHERE clause was "rownum < 4" instead of "rownum = 1"?

what my OracleParameter should be like? when i specify my return oracle parameter like this (works with a single row),

OracleParameter p = cmd.Parameters.Add("ID", OracleDbType.Int32, 10, 0, ParameterDirection.Output);

ORA-24369 is given. i tried using ArrayBindCount and arrays as .Value property of the parameter, but to no avail.

also, do you find anything fundamentally wrong with the whole particular manner of locking?

thank you

edit: some clarifications - 1. there is a column called user_name, which can hold e.g. the name of the logged in user - i update it along with the locking UPDATE 2. there is another column that i call locked_timestamp, which holds the time when the record got locked. there could be a background process that would run during the night and would reset the locked records back to "unlocked" - given that this will not happen frequently, and the proportion of records that stay "locked" because of crashes etc. is small in comparison to the volume of the edited records 3. the concurrent updates are supposed to be handled automatically by Oracle, and yes, I use transactions during each UPDATE - so there is little chance that a record gets locked by two users simultaneously - or that's what I hear (will try this out, once i find how to UPDATE...RETURNING.. multiple rows)

解决方案

finally, after hours of searching and playing with code, i came to the following conclusions (apart from the headache):

i got what i wanted using combination from

1. a hint here, which suggested to wrap the UPDATE..RETURNING statement into an anonymous PL/SQL block (start with BEGIN and end with END;) - this went without explanation and I still don't know exactly why the behaviour is different
2. code snippet in Oracle documentation about OracleCommand, specifically the part about binding PL/SQL associative arrays with BULK COLLECT INTO (couldn't get the simple array binding to work..):

try
{
    conn.Open();
    transaction = conn.BeginTransaction();

    cmd = new OracleCommand();
    cmd.Connection = GetConnection();

    cmd.CommandText =
        "BEGIN UPDATE some_table " +
        "SET status = 'locked', " +
        "    locked_tstamp = SYSDATE, " +
        "    user_name = '" + user + "' " +
        "WHERE rownum <= 4 " +
        "RETURNING id BULK COLLECT INTO :id; END;";

    cmd.CommandType = CommandType.Text;

    cmd.BindByName = true;
    cmd.ArrayBindCount = 4;

    p = new OracleParameter();
    p.ParameterName = "id";
    p.Direction = ParameterDirection.Output;
    p.OracleDbType = OracleDbType.Int64;
    p.Size = 4;
    p.ArrayBindSize = new int[] { 10, 10, 10, 10 };
    p.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
    cmd.Parameters.Add(p);

    int nRowsAffected = cmd.ExecuteNonQuery();

    // nRowsAffected is always -1 here
    // we can check the number of "locked" rows only by counting elements in p.Value (which is returned as OracleDecimal[] here)
    // note that the code also works if less than 4 rows are updated, with the exception of 0 rows
    // in which case an exception is thrown - see below
    ...
}
catch (Exception ex)
{
    if (ex is OracleException && !String.IsNullOrEmpty(ex.Message) && ex.Message.Contains("ORA-22054")) // precision underflow (wth)..
    {
        Logger.Log.Info("0 rows fetched");
        transaction.Rollback();
    }
    else
    {
        Logger.Log.Error("Something went wrong during Get : " + ex.Message);
        ret = null;
        transaction.Rollback();
    }
}
finally
{
    // do disposals here
}
...

这篇关于ODP.NET更新...返回到...多行，参数类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！