oracle - PL/SQL 如何从创建或替换函数返回用户定义的记录

Question

我正在尝试学习 PL/SQL我似乎不明白如何创建一个函数并让它返回一个记录

我正在尝试做这样的事情:

create or replace FUNCTION getMovie(movieID number)
RETURN record IS titleAndYear record(title varchar(100), production_Year number);
BEGIN
  if (titleAndYear is null) then
    titleAndYear:= MovieTitleAndYear('',0);
  end if;
  select TITLE ,YEAR into titleAndYear.title ,titleAndYear.production_Year from movie where MOVIE_ID = movieID;
  return titleAndYear;
END;

我知道这不起作用，但我不知道为什么？

编辑1:我也尝试过这个:

create or replace TYPE MovieTitleAndYear is OBJECT(title varchar(100), production_Year number);
/
create or replace FUNCTION getMovie(movieID number)
RETURN MovieTitleAndYear IS titleAndYear MovieTitleAndYear;
BEGIN
  if (titleAndYear is null) then
    titleAndYear:= MovieTitleAndYear('',0);
  end if;
  select TITLE ,YEAR into titleAndYear.title ,titleAndYear.production_Year from movie where MOVIE_ID = movieID;
  return titleAndYear;
END;

但是当我运行这个语句时的结果是:

select 
GETMOVIE(
2540943 
) from dual;

变成这样

  GETMOVIE(2540943)
1 [DB_036.MOVIETITLEANDYEAR]

而不是两列标题和生产年份。

Answer 1

您使用记录的第一个示例将不起作用。首先，函数不能返回仅在函数内部声明的类型的值。您可以尝试将记录类型声明移动到包中，但即使您随后获得了要编译的函数，运行查询 select getMovie(2540943) from Dual 也会返回 ORA-00902 invalid数据类型错误。

所以我建议您改用类型。

如果您使用类型，那么要分别获取电影和年份，您需要单独访问类型中的字段，例如:

select getMovie(2540943).title, getMovie(2540943).production_year from dual

或者，如果您想避免调用 getMovie() 两次，则可以使用子查询:

select x.movie_info.title, x.movie_info.production_year from (select getMovie(2540943) as movie_info from dual) x;

请注意，我们需要为子查询使用别名。以下内容将给出 ORA-00904: "MOVIE_INFO"."PRODUCTION_YEAR": invalididentifier 错误:

select movie_info.title, movie_info.production_year from (select getMovie(2540943) as movie_info from dual);

这里的问题是 Oracle 在查询中查找表 movie_info，但找不到。它没有意识到 movie_info 实际上是一个列。如果我们引入别名​​ x，Oracle 就会意识到 x.movi​​e_info 是一个列，因此 x.movi​​e_info.title 是一个在列中输入。