sql - 处于无效状态的 Oracle 包或函数

Question

尝试创建一个基于 pl/sql 游标的函数以从 oracle 数据库返回详细信息。

相关表 MEETING 包含列MEETING_ID: number(10), TIME: timestamp(4), TITLE: varchar(20)

CREATE OR REPLACE FUNCTION GetMeeting
                    (meetingnumber MEETING.MEETING_ID%TYPE)
RETURN VARCHAR
IS
    CURSOR current_meeting(meetingnumber MEETING.MEETING_ID%TYPE)
    IS
        SELECT TITLE, TIME
        FROM MEETING
        WHERE MEETING_ID = meetingnumber;

    r_meeting current_meeting%ROWTYPE;
BEGIN
    OPEN current_meeting(meetingnumber);

    FETCH current_meeting INTO r_meeting;

    IF current_meeting%NOTFOUND THEN
        r_meeting.TITLE := 'UNKNOWN APPOINTMENT';
        END IF;

        CLOSE current_meeting;

        RETURN r_meeting.TITLE;
    END;


SELECT GetMeeting (27) name
FROM MEETING;

该函数似乎编译正常 - 但调用时抛出

ORA-06575: Package or function GETMEETING is in an invalid state

Answer 1

也许这对你更有效:

create or replace function
  getmeeting(
    meeting_id number)
return
  varchar
is
  meeting_title meeting.title%Type;
begin
  select title
  into   meeting_title
  from   meeting
  where  meeting_id = getmeeting.meeting_id;

  return meeting_title;
exception
  when NO_DATA_FOUND then
     return 'UNKNOWN APPOINTMENT';
end;
/

未检查语法。