Oracle SQL编写返回表类型的指定区间国家数据自定义函数求助

原代码存在的语法/逻辑错误 #

• 变量声明语法错误：返回值段声明结果变量时多写了多余的table关键字，不符合PL/SQL变量声明规则。
• 命名冲突导致条件失效：入参名和表字段country_abv重名，where子句中country_abv = country_abv会被Oracle解析为表字段自比较，永远返回真，无法按传入国家代码过滤数据。
• 对象构造参数不匹配：自定义的african_crisis_row对象包含3个字段，但查询构造对象时仅传入2个值，会触发参数个数不匹配的运行时报错。
• 语法符号缺失：return v_ret语句末尾缺少必填的分号。
• 类型定义不规范：字符串入参使用无长度定义的varchar类型，不符合Oracle开发规范，易触发隐式转换问题。

修正后完整可运行代码 #

-- 行对象类型（原定义无问题，保留）
create or replace type african_crisis_row as object(
country_abv varchar(4),
year number(5),
banking_crisis varchar(10)
);
/

-- 嵌套表返回类型（原定义无问题，保留）
create or replace type t_african_crisis_table as table of african_crisis_row;
/

-- 修正后的自定义函数
create or replace function african_crisis (   
    p_country_abv in varchar2, -- 入参加p_前缀，和表字段区分，使用Oracle标准varchar2类型
    p_year_min in number,
    p_year_max in number
)
return t_african_crisis_table 
as 
    v_ret t_african_crisis_table; -- 修正变量声明语法
begin
    select 
       african_crisis_row(
            t.country_abv, 
            t.year,
            t.banking_crisis -- 补全缺失的banking_crisis字段赋值
       )
    bulk collect into v_ret
    from africancrisisdata t -- 给原表加别名，避免字段名歧义
    where
        t.country_abv = p_country_abv 
        and t.year between p_year_min and p_year_max;
    return v_ret; -- 补全语句分号
end african_crisis;
/

函数调用方法 #

• PL/SQL块内调用：

declare
    l_result t_african_crisis_table;
begin
    -- 传入国家代码、起止年份获取结果
    l_result := african_crisis('NGA', 1990, 2010);
    -- 遍历结果输出
    for idx in 1..l_result.count loop
        dbms_output.put_line(
            l_result(idx).country_abv || ' | ' 
            || l_result(idx).year || ' | ' 
            || l_result(idx).banking_crisis
        );
    end loop;
end;
/

• 普通SQL查询调用：

-- 直接用table()函数包装返回结果，即可像物理表一样查询
select * from table(african_crisis('KEN', 2000, 2020));

参考数据样例 #

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