代码之家 › 专栏 › 技术社区 › Helbo

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

oracle-sqldeveloper plsql oracle

Helbo · 技术社区 · 8 年前

我正在尝试学习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]

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

2 回复 | 直到 8 年前

Luke Woodward 8 年前

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

所以我建议您改用类型。

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

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": invalid identifier 错误:

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

这里的问题是Oracle正在寻找一个表 movie_info 在查询中,但找不到一个。它没有意识到 电影_信息 实际上是一列。如果我们引入别名 x 甲骨文意识到 x.movie_info 是一列,因此 x.movie_info.title 是列中类型内的字段。

Avrajit Roy 8 年前

尝试这种方法。我想你的答案就在这一小段之内。允许我知道这是否有用。

--Create object type
CREATE OR REPLACE type av_obj_test
IS
  object
  (
    col1 VARCHAR2(100),
    col2 VARCHAR2(100) );


--C reate table type    
CREATE OR REPLACE type av_ntt_test
IS
  TABLE OF av_obj_test;


--Createfunction
CREATE OR REPLACE FUNCTION AV_RECORD RETURN 
AV_NTT_TEST
AS
av_record av_ntt_test;
BEGIN
NULL;
SELECT av_obj_test(LEVEL,'av'||LEVEL) BULK COLLECT INTO av_record FROM DUAL
CONNECT BY LEVEL < 10;
RETURN av_record;
END;


--Calling function
SELECT * FROM TABLE(AV_RECORD);

--------------------------------OUTPUT-------------------------------------

COL1    COL2
1       av1
2       av2
3       av3
4       av4
5       av5
6       av6
7       av7
8       av8
9       av9


-------------------------------OUTPUT----------------------------------------