Oracle相当于PostgreSQL INSERT ... RETURNING *; [英] Oracle equivalent of PostgreSQL INSERT...RETURNING *;

问题描述

我已经将一堆DML（INSERT / UPDATE / DELETE）查询从Oracle转换为PostgreSQL，现在我需要检查它们是否产生相同的行集，即删除操作会删除相同的行，假设使用oracle和postgresql数据库最初包含相同的数据，update更新相同的行，等等。在PostgreSQL方面，我可以将returning子句与DML语句一起使用，即

I've converted a bunch of DML (INSERT/UPDATE/DELETE) queries from Oracle into PostgreSQL and now I need to check whether they produce the same set of rows, i.e. that delete removes the same rows, assuming the oracle and postgresql databases contain the same data initially, update updates the same rows etc. On PostgreSQL side, I can use the returning clause with DML statements, i.e.

INSERT INTO test(id, name) VALUES(42, 'foo') RETURNING *;

上面的语句的好处是我可以在不知道任何DML语句的情况下添加返回*

What's good about the statement above is that I can prepend 'returning *' to any DML statement without knowing the structure or even the name of the table it's executed against and just get all rows like it's a select statement.

但是，在Oracle上看起来并不那么闪亮侧。根据文档，Oracle 8i（我正在使用的那个）支持RETURNING子句，但是它必须将结果存储到变量中，并且似乎没有明显的方法来获取所有结果列，而不是手动指定列名。

However, it seems to be not that shiny on the Oracle side. According to the documentation, Oracle 8i (the one I'm working with) supports RETURNING clause, but it has to store the result into variables and there seem to be no obvious way to get all result columns instead of manually specifying the column name.

因此，问题是是否存在一个oracle语句（或语句序列）来模拟PostgreSQL的返回*而无需对表或列名进行硬编码。换句话说，有没有办法编写这样的Oracle函数：

Hence, the question is if there is an oracle statement (or sequence of statements) to emulate PostgreSQL 'returning *' without hard-coding table or column names. In other words, is there a way to write an Oracle function like this:

fn('INSERT INTO test(id, name) VALUES(42, ''foo'')') 

它应该返回插入的行集（或在一般情况下进行修改）。

It should return the set of rows inserted (or modified in the generic case) by the SQL statement.

更新：
我实际上发现了非常相似的问题（用于从SQL Server（而不是PostgreSQL）转换为Oracle）。尽管如此，如果可能的话，我还是希望听到一个更简单的答案。

Update: I actually found a very similar question (for the conversion from SQL server, not PostgreSQL, into Oracle). Still, I'd love to hear a more simple answer to that if possible.

推荐答案

目前不可能，尤其是在Oracle的旧版本，例如8i。看到这个答案类似问题。

It's not currently possible, especially in an old version of Oracle such as 8i. See this answer to a similar question.

这篇关于Oracle相当于PostgreSQL INSERT ... RETURNING *;的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！