从 PL-SQL 获取受影响的行
我正在使用 ODP.Net 并运行 PL/SQL 命令来合并 Oracle 10G 数据库中的表。 我的命令如下：
MERGE INTO TestTable t
USING (SELECT 2911 AS AR_ID FROM dual) s
ON (t.AR_ID = s.AR_ID)
WHEN MATCHED THEN
UPDATE SET t.AR_VIUAL_IMPAIRMENT = 1
WHEN NOT MATCHED THEN
INSERT (AR_S_REF)
VALUES ('abcdef');
SELECT sql%ROWCOUNT FROM dual;
合并命令成功运行并根据需要更新/插入。问题是我想知道有多少记录被更新。
当我运行上面的语句时，“ORA-00911：无效字符错误”。
请告诉我如何恢复受影响的行。谢谢万。
I am using ODP.Net and run the PL/SQL Command to merge the table in the Oracle 10G database.
My command is as follow:
MERGE INTO TestTable t
USING (SELECT 2911 AS AR_ID FROM dual) s
ON (t.AR_ID = s.AR_ID)
WHEN MATCHED THEN
UPDATE SET t.AR_VIUAL_IMPAIRMENT = 1
WHEN NOT MATCHED THEN
INSERT (AR_S_REF)
VALUES ('abcdef');
SELECT sql%ROWCOUNT FROM dual;
The Merge command runs successfully and update/insert as I want. The problem is I want to know how many records are updated.
When I run the above statement, "ORA-00911: invalid character error".
Please advise me how I could get the affected rows back. Thanks million.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您混淆了一些内容：MERGE 语句是一个普通的 SQL 命令，而 PL/SQL 代码始终由 BEGIN/END（以及可选的 DECLARE）分隔。此外，
SQL%ROWCOUNT是一个不能在 PL/SQL 之外出现的 PL/SQL 变量。我不太明白您是否使用两个单独的 ODP.NET 调用或一个公共的 ODP.NET 调用运行 MERGE 和 SELECT 语句。
不管怎样，使用 ODP.NET 的解决方案是直接的：使用
OracleCommand.ExecuteNonQuery()执行 MERGE 命令。此方法返回受影响的行数。You're mixing up a few things: a MERGE statement is a plain SQL command while PL/SQL code is always delimited by BEGIN/END (and optional DECLARE). Furthermore,
SQL%ROWCOUNTis a PL/SQL variable that cannot occur outside of PL/SQL.And I don't quite understand whether you ran the MERGE and the SELECT statement with two separate or a common ODP.NET call.
Anyway, the solution is straightfowrad with ODP.NET: Execute the MERGE command with
OracleCommand.ExecuteNonQuery(). This method returns the number of affected rows.您可以做的一件事是将代码放入返回 %ROWCOUNT 的 PLSQL 函数中。
然后从 ODP.net 调用此函数，将命令类型设置为存储过程并使用 ExecuteLiteral 方法，该方法将以对象实例的形式返回行计数，您可以将其转换为 int。
One thing you could do is put your code in a PLSQL function that returns %ROWCOUNT.
Then call this function from ODP.net setting the command type to stored procedure and using the
ExecuteLiteralmethod which is going to return you the row count as an object instance you can cast as an int.不可能只返回“更新的”行计数。
（正如已经提到的，行计数是受影响（插入和更新）的行数）
ask tom 有一个很好的讨论：http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:122741200346595110
It is not possible to return just the "updated" row count.
(as already mentioned the row count is the number of affected (inserted and updated) rows)
there is a good discusion on ask tom: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:122741200346595110