如何从 Oracle 中的 select 语句调用带有 Rowtype 参数的函数 [英] How to call a function with Rowtype parameter from a select statement in Oracle

问题描述

我有一个 oracle 函数，它有一个表的行类型的 in 参数，我需要从 select 语句中将当前行传递给这个函数，以便它进行一些处理并返回一个值.是否有一个伪变量可以在 select 语句的上下文中使用，相当于触发器中的旧的和新的.

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.

我想做类似的事情

select *,function1(rowtype) from table1

我想避免传递多个参数，所以应该在那个上下文中看到这个问题.

I want to avoid passing multiple parameters, so the question should be seen in that context.

推荐答案

您无法使用 %ROWTYPE 执行此操作.%ROWTYPE 实际上是 PL/SQL 记录类型，在 SQL 中不是合法类型，所以不能在 SELECT 中使用.您应该创建一个与表具有相同列的对象类型，更改为期望该对象类型而不是 %ROWTYPE 的函数，然后您可以编写如下内容:

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:

SELECT function(table1_typ(column1, column2, column3))
  FROM table1 t1

缺点:您仍然需要在 SELECT 中键入所有列，并且如果您更改表，则还需要更改对象类型和 SELECT.

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.

这篇关于如何从 Oracle 中的 select 语句调用带有 Rowtype 参数的函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！