Trying to do a simple User function where I get the number of people whos age is > 100.

试着做一个简单的用户函数，我得到了年龄>100的人的数量。

this is my current table

这是我现在的桌子

select * from peoples

SELECT*From People

**NAME**      **AGE**
----------------------
Joe Bloggs  12
Paul Smith  15
Jonah James 534
Mika Rive   31
Hannah Bananas  56
Harry Heelz 4
Brian Bolton    232
Jack Ripper 123

I'm currently trying to implement user function where the output is a single row with the total number of people with an age greater than 100.

我目前正在尝试实现用户函数，其中输出是年龄大于100岁的总人数的单行。

Here is the function I have attempted to make

以下是我尝试创建的函数

create or replace function agetest return number is
v_count number;

begin
    select count(age) into v_count
    from persons
    where  age > 100;
    
        return v_count;
end;
 

When I execute the SQL query directly I get the correct answer

当我直接执行SQL查询时，我会得到正确的答案

select count(age) from persons where age > 100;

but when I run the function

但当我运行该函数时

select agetest from persons;

I get the output of:

我得到以下输出：

Just looking for guidance on why the output is showing the same result for each row.

我只是想了解一下为什么输出每一行都显示相同的结果。

My ideal output for the function would just be:

我对该函数的理想输出是：

When you execute the query:

执行查询时：

select count(age) from persons where age > 100;

You aggregate all the rows into a single row.

您将所有行聚合到一行中。

When you do:

当您这样做时：

select agetest from persons;

You are not executing the first query because agetest is a scalar function (not an aggregation function) and you are effective executing:

您没有执行第一个查询，因为agetest是一个标量函数(不是聚合函数)，并且您正在有效地执行：

SELECT (select count(age) from persons where age > 100) FROM persons;

Which is saying, for each row in persons execute the sub-query; which is why you get the function's return value repeated for each person and the output is:

这就是说，对于Person中的每一行，执行子查询；这就是为什么您会获得针对每个Person重复的函数返回值，并且输出是：

If you want to get the function value once then only execute it once by using a table that only has one row (such as DUAL):

如果要获取一次函数值，则使用只有一行的表(如DUAL)只执行一次：

SELECT agetest FROM dual;

Or, you can change the function from a scalar function to a user-defined aggregation function:

或者，您可以将函数从标量函数更改为用户定义的聚合函数：

CREATE OR REPLACE TYPE CountGreaterThan100Type AS OBJECT(
  cnt NUMBER(10,0),

  STATIC FUNCTION ODCIAggregateInitialize(
    ctx         IN OUT CountGreaterThan100Type
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateIterate(
    self        IN OUT CountGreaterThan100Type,
    value       IN     NUMBER
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateTerminate(
    self        IN OUT CountGreaterThan100Type,
    returnValue    OUT NUMBER,
    flags       IN     NUMBER
  ) RETURN NUMBER,

  MEMBER FUNCTION ODCIAggregateMerge(
    self        IN OUT CountGreaterThan100Type,
    ctx         IN OUT CountGreaterThan100Type
  ) RETURN NUMBER
);
/

CREATE OR REPLACE TYPE BODY CountGreaterThan100Type
IS
  STATIC FUNCTION ODCIAggregateInitialize(
    ctx         IN OUT CountGreaterThan100Type
  ) RETURN NUMBER
  IS
  BEGIN
    ctx := CountGreaterThan100Type( 0 );
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateIterate(
    self        IN OUT CountGreaterThan100Type,
    value       IN     NUMBER
  ) RETURN NUMBER
  IS
  BEGIN
    IF value > 100 THEN
      self.cnt := self.cnt + 1;
    END IF;
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateTerminate(
    self        IN OUT CountGreaterThan100Type,
    returnValue    OUT NUMBER,
    flags       IN     NUMBER
  ) RETURN NUMBER
  IS
  BEGIN
    returnValue := self.cnt;
    RETURN ODCIConst.SUCCESS;
  END;

  MEMBER FUNCTION ODCIAggregateMerge(
    self        IN OUT CountGreaterThan100Type,
    ctx         IN OUT CountGreaterThan100Type
  ) RETURN NUMBER
  IS
  BEGIN
    self.cnt := self.cnt + ctx.cnt;
    RETURN ODCIConst.SUCCESS;
  END;
END;
/

CREATE FUNCTION CountGreaterThan100( value NUMBER )
RETURN NUMBER
PARALLEL_ENABLE AGGREGATE USING CountGreaterThan100Type;
/

Then:

然后：

SELECT CountGreaterThan100(age) FROM persons;

Outputs:

产出：

fiddle

小提琴

Try executing the function against DUAL:

尝试针对DUAL执行函数：

SELECT agetest FROM dual;

In Oracle, the DUAL table is a sort of dummy table which has only one record in it.

在Oracle中，DUAL表是一种只有一条记录的虚表。