如何从 Oracle 中的 select 语句调用带有 Rowtype 参数的函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/605925/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to call a function with Rowtype parameter from a select statement in Oracle
提问by Dinesh Manne
I have an oracle function which has an in parameter which is of rowtype of a table, from a select statement i need to pass the current row to this function so that it does some processing and returns a value. Is there a pseudo variable that can be used within the context of a select statement something equivalent to a old and new in trigger.
我有一个 oracle 函数，它有一个 in 参数，它是表的 rowtype，从 select 语句中，我需要将当前行传递给这个函数，以便它进行一些处理并返回一个值。是否有一个伪变量可以在 select 语句的上下文中使用，相当于触发器中的旧的和新的。
I would like to do something like
我想做类似的事情
select *,function1(rowtype) from table1
I want to avoid passing multiple parameters, so the question should be seen in that context.
我想避免传递多个参数，所以应该在那个上下文中看到这个问题。
回答by Gabor Kecskemeti
You can't do this with %ROWTYPE. %ROWTYPE is actually a PL/SQL record type, which is not a legal type in SQL, so you can't use it in a SELECT. You should create an object type which has the same columns as the table, change to function to expect that object type instead of %ROWTYPE, and then you can write something like this:
你不能用 %ROWTYPE 做到这一点。%ROWTYPE 实际上是 PL/SQL 记录类型，在 SQL 中不是合法类型，所以不能在 SELECT 中使用。您应该创建一个与表具有相同列的对象类型，更改为函数以期望该对象类型而不是 %ROWTYPE，然后您可以编写如下内容：
SELECT function(table1_typ(column1, column2, column3))
FROM table1 t1
Drawbacks: You still have to type all the columns in the SELECT, and if you change the table, you will need to change the object type and the SELECT too.
缺点：您仍然需要在 SELECT 中键入所有列，并且如果您更改表，您将需要更改对象类型和 SELECT。
回答by cagcowboy
Why not just pass the id of the row in and make the function work on that?
为什么不直接传入行的 id 并使函数在其上工作？
EDIT: You might be able to construct an OBJECT / TYPE on the fly and pass that to a function. Then you'll only need to query for the data once. Let me know if you need an example.
编辑：您可以动态构建 OBJECT / TYPE 并将其传递给函数。然后您只需要查询一次数据。如果您需要示例，请告诉我。
回答by Colin Pickard
You can do this:
你可以这样做：
DECLARE
foo table1%ROWTYPE;
BEGIN
function1(foo);
END;
EDIT: You can check out this blog postfor a more detailed description of how to use this technique.
编辑：您可以查看此博客文章以获取有关如何使用此技术的更详细说明。
