Pl/SQL将字符串赋值给变量，并每次打印该值

Question

创建一个名为'display_player_skill‘的函数，它接受一个输入参数。输入参数为'player_id‘，其数据为number，type.This函数应返回给定’skil_id _id‘的播放器。

我试过了

create or replace function display_player_skill (player_id in number)
    return varchar2
is
    skill_name varchar2(100);
begin
    select s.name into skill_name from k_skill s 
    inner join k_player p on p.skill_id=s.id
    where p.skill_id= player_id;

    case skill_name
        when 'Raider' then return 'Player is a Raider';
        when 'All rounder' then return 'Player is a All Rounder';
    end case;

exception
    when case_not_found then return 'Player is a Defender';
    when no_data_found then return 'No Such Player';
end;
/

但是我必须返回给定player_id的skill_id并显示skill_name。将字符串赋给一个变量，并每次打印该值。

Answer 1

where子句似乎不正确(将skill_id与player_id进行比较很奇怪)。

where p.skill_id= player_id;

此外，如果您在k_player中有列player_id，Oracle会将player_id作为列名。因此，您需要将参数命名为类似于p_player_id (p_表示参数)的名称

所以我希望你的代码看起来像这样：

create or replace function display_player_skill (p_player_id in number)
    return varchar2
    is
    skill_name varchar2(100);
    skill_id   number;
    BEGIN
    select s.name, s.id into skill_name, skill_id from k_skill s 
    inner join k_player p on p.skill_id=s.id
    where p.player_id= p_player_id;

    dbms_output.put_line('Skill name is a '||skill_name);

    return skill_id;

EXCEPTION
when no_data_found then return 'No Such Player';
END;
/

我不知道您的表的结构，所以一些列名可能需要更正。

Answer 2

试试看：

 select s.name, s.id into skill_name,skill_id from k_skill s 
        inner join k_player p on p.skill_id=s.id
        where p.player_id= p_player_id;

  --      dbms_output.put_line('Skill name is a '||skill_name);

        return skill_id;