在Dapper中，获取多个值作为输出参数

Question

这是我的代码：

string cmd = "UPDATE MyTable SET Status='A' WHERE CaseId='123' RETURNING DocId INTO :UpdatedDocId";
DynamicParameters para = new DynamicParameters();
para.Add("UpdatedDocId", direction: ParameterDirection.Output, dbType: DbType.String, size: 50);
await db.ExecuteAsync(cmd, para);
var tempOutputValue = para.Get("UpdatedDocId");

如果只更新了一行记录，我可以正确地获取到更新后的DocId值，
但如果有多行（两行或更多行）被更新时，我会遇到如下错误：
ORA-24369: 对于一个或多个绑定句柄，未注册必需的回调

有没有什么方法可以让我获取所有已更新的DocIds呢？

leora · Answer

我不了解Dapper，但从Oracle的角度来看，一个实现该目标的选项是将受影响的ID返回到集合中。以下示例使用了内置数据类型sys.odcinumberlist（如果您需要，可以创建自己的数据类型）。请看第7行：

SQL> set serveroutput on;
SQL> declare
  2    l_col sys.odcinumberlist;
  3  begin
  4    update test set
  5      sal = sal + 100
  6      where deptno = 10
  7      returning empno bulk collect into l_col;        -- 这里
  8
  9    for i in 1 .. l_col.count loop
 10      dbms_output.put_line(l_col(i));
 11    end loop;
 12  end;
 13  /
7782              -- 这些ID已被影响
7839
7934

PL/SQL 过程已成功完成。

SQL>

我希望您能够调整这段代码以便在Dapper中使用。遗憾的是，正如我所说，我不知道如何完成这部分工作。

Rich Scriven · Answer

在Oracle数据库中，你可以使用RETURNING column_name BULK COLLECT INTO collection_variable来实现。

如果你要在PL/SQL中实现它，代码如下：

DECLARE
  TYPE docid_list IS TABLE OF MyTable.DocID%TYPE;
  v_UpdatedDocId docid_list;
BEGIN
  UPDATE MyTable
  SET    Status='A'
  WHERE  CaseId='123'
  RETURNING DocId BULK COLLECT INTO v_UpdatedDocId;

  FOR i IN 1 .. v_UpdatedDocId.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_UpdatedDocId(i));
  END LOOP;
END;
/

你需要将原来的INTO改为BULK COLLECT INTO，并将其赋值给一个可以接受数组的绑定变量。

然而，根据StackOverflow上的一个回答，C#并不支持传递Oracle SQL集合数据类型，所以可能无法直接实现（原因未知，尽管在其他语言中可以做到这一点，但C#驱动程序不支持）。但是，C#支持PL/SQL关联数组，因此你或许可以先BULK COLLECT INTO一个SQL集合，然后将其转换为PL/SQL关联数组并返回。

首先，在数据库内部创建一个定义了关联数组的包：

CREATE PACKAGE package_name AS
  TYPE STRING_MAP IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER;
END;
/

接下来，你可以尝试使用类似这样的C#代码（由于我没有C#环境，这段代码未经测试，基于这个问题中的示例）：

string cmd = @"
DECLARE
  v_array package_name.STRING_MAP;
BEGIN
  UPDATE MyTable
  SET    Status='A'
  WHERE  CaseId='123'
  RETURNING DocId BULK COLLECT INTO v_array;

  :UpdatedDocId := v_array;
END;";
DynamicParameters para = new DynamicParameters();
para.Add(
    "UpdatedDocId",
    direction: ParameterDirection.Output,
    CollectionType = OracleCollectionType.PLSQLAssociativeArray,
    UdtTypeName = "PACKAGE_NAME.STRING_MAP"
);
await db.ExecuteAsync(cmd, para);

Oracle在线演示