如何使用SELECT INTO子句编写PL/SQL函数计算家族表人员年龄？

需要创建一个PL/SQL函数，根据Familienbaum（家族树）表中的姓名计算人员年龄。表包含Name、Geburtsjahr（出生年份）、Sterbejahr（逝世年份）字段，年龄计算规则：

• 若存在Sterbejahr，则用该年份减去Geburtsjahr得到年龄；
• 若不存在Sterbejahr，则用Oracle当前系统年份减去Geburtsjahr得到年龄。

尝试用SELECT INTO子句编写的代码存在语法问题：

CREATE OR REPLACE FUNCTION BerechneAlter(Person VARCHAR2)
RETURN INTEGER IS 
BEGIN
SELECT Name, Sterbejahr, Geburtsjahr FROM Familienbaum
WHERE Person = Name;
RETURN (CASE 
        WHEN Sterbejahr IS NULL THEN (year(curDate()) - Geburtsjahr)
        WHEN Sterbejahr IS NOT NULL THEN (Sterbejahr - Geburtsjahr)
        END);
END BerechneAlter;

另外用游标实现的版本过于复杂，希望得到更简洁的解决方案：

create or replace FUNCTION BerechneAlter(Person VARCHAR2)
RETURN INTEGER IS 
Sterbejahr INTEGER; 
Geburtsjahr INTEGER;

CURSOR SJ IS SELECT familienbaum.sterbejahr FROM familienbaum WHERE familienbaum.name=Person;
CURSOR GJ IS SELECT familienbaum.geburtsjahr FROM familienbaum WHERE familienbaum.name=Person;

BEGIN

OPEN SJ; 
FETCH SJ INTO Sterbejahr;
CLOSE SJ;

OPEN GJ;
FETCH GJ INTO Geburtsjahr;
CLOSE GJ;

RETURN (CASE
        WHEN Sterbejahr IS NULL THEN (2022 - Geburtsjahr)
        WHEN Sterbejahr IS NOT NULL THEN (Sterbejahr - Geburtsjahr)
        END);
END BerechneAlter;

问题分析 #

原SELECT INTO代码的核心问题：

1. SELECT语句未将查询结果赋值给变量，缺少INTO子句的变量列表；
2. Oracle中获取当前年份不能用year(curDate())，正确写法是EXTRACT(YEAR FROM SYSDATE)；
3. 参数Person与字段Name的比较顺序错误，且易产生命名混淆，建议给参数加前缀区分。

优化后的代码 #

直接在SELECT INTO中完成计算，无需多变量或游标，代码简洁高效：

CREATE OR REPLACE FUNCTION BerechneAlter(p_Person VARCHAR2)
RETURN INTEGER IS
    v_Alter INTEGER;
BEGIN
    SELECT 
        CASE
            WHEN Sterbejahr IS NOT NULL THEN Sterbejahr - Geburtsjahr
            ELSE EXTRACT(YEAR FROM SYSDATE) - Geburtsjahr
        END
    INTO v_Alter
    FROM Familienbaum
    WHERE Name = p_Person;
    
    RETURN v_Alter;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL; -- 处理姓名不存在的情况
    WHEN TOO_MANY_ROWS THEN
        RETURN NULL; -- 处理同名多人的情况
END BerechneAlter;

代码说明 #

1. 参数命名为p_Person，避免与表字段Name混淆；
2. 在SELECT语句中用CASE直接完成年龄计算，结果赋值给变量v_Alter；
3. 使用EXTRACT(YEAR FROM SYSDATE)获取Oracle当前系统年份，替代错误的year(curDate())；
4. 添加异常处理，应对查询无结果或多结果的场景，提升函数健壮性；
5. 仅需一次查询即可完成计算，相比游标版本大幅简化代码逻辑，执行效率更高。

内容的提问来源于stack exchange，提问作者Alexy