代码之家 › 专栏 › 技术社区 › Rhys Miller

使用Select Count的Oracle SQL函数为每一行返回重复的答案

count select function oracle sql

Rhys Miller · 技术社区 · 2 年前

尝试做一个简单的用户函数,我可以得到年龄>100

这是我现在的桌子

从用户中选择*

**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

我目前正在尝试实现用户功能,其中输出是一行,其中年龄大于100的总人数。

这是我试图做的函数

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;

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

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

	计数(年龄)
1.	3.

但是当我运行函数时

select agetest from persons;

我得到的输出为:

	老化试验
1.	3.
2.	3.
3.	3.
4.	3.
5.	3.
6.	3.
7.	3.
8.	3.

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

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

	老化试验
1.	3.

1 回复 | 直到 2 年前

MT0 2 年前

执行查询时:

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

将所有行聚合为一行。

当您这样做时:

select agetest from persons;

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

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

也就是说,对于 persons 执行所述子查询;这就是为什么为每个人重复函数的返回值,并且输出为:

(SELECTCOUNT(年龄)FROMPERSONSHOREAGE>100)
3.
3.
3.
3.
3.
3.
3.
3.

如果您想获得一次函数值,那么使用只有一行的表(例如 DUAL ):

SELECT agetest FROM dual;

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

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;
/

然后:

SELECT CountGreaterThan100(age) FROM persons;

输出:

计数大于100(年龄)
3.

fiddle

Tim Biegeleisen 2 年前

尝试对执行函数 DUAL :

SELECT agetest FROM dual;

在Oracle中 对偶的 表是一种只有一条记录的伪表。