PL/SQL:忽略语句和 PLS-00382:返回中的表达式类型错误

Question

我在使用 oracle 11g 的 PL SQL tesk 中遇到了问题。 我的任务是，如果音频CD发行超过一年，则可享受20%的折扣。 我所以 我做了这个声明

CREATE OR REPLACE TYPE BODY disk_type AS
OVERRIDING MEMBER FUNCTION discountPrice RETURN NUMBER IS
BEGIN
IF 
albumReleaseDate > to_char(trunc(SYSDATE - interval '1' year,'YEAR'),'YYYYMM') 
AND  mediaType = 'Audio CD'
THEN RETURN albumPrice = (albumPrice * 0.8);
END IF;
END discountPrice;
END;

但是，我收到此错误消息

7/15      PL/SQL: Statement ignored
7/33      PLS-00382: expression is of wrong type

消息说我的返回数据类型错误

这是我的超型

create or replace type album_type as object 
(albumTitle         varchar(50),
albumPlaytime      number(3), -- minutes
albumReleaseDate   date, 
albumGenre         varchar(15),
albumPrice         number(9,2),
albumTracks        number(2),
albumArtists       artist_array_type,
albumReviews       review_table_type,
member function discountPrice return number,
member function containsText (pString1 varchar2, pString2 varchar2) return integer)
not instantiable not final
/
create table albums of album_type 
object id system generated
/

这是我的亚型

create or replace type disk_type under album_type 
( mediaType         varchar(10), -- Audio CD wull be 'Audio CD'
diskNum            number(2), -- number of disks
diskUsedPrice      number(9,2),
diskDeliveryCost   number(9,2), 
overriding member function discountPrice return number)
/

Answer 1

您需要通过RETURN子句返回变量。 变量的值赋值必须是单独的逻辑。

CREATE OR REPLACE TYPE BODY disk_type AS
OVERRIDING MEMBER FUNCTION discountPrice RETURN NUMBER IS
BEGIN
IF 
albumReleaseDate > to_char(trunc(SYSDATE - interval '1' year,'YEAR'),'YYYYMM') 
AND  mediaType = 'Audio CD'
THEN return (albumPrice * 0.8); -- direct return, no assignment
END IF;
Return albumPrice; -- added this
END discountPrice;
END;

您也可以使用简单的案例语句来实现此目的：

CREATE OR REPLACE TYPE BODY disk_type AS
OVERRIDING MEMBER FUNCTION discountPrice RETURN NUMBER IS
BEGIN
Return albumPrice * ( case 
when albumReleaseDate > to_char(trunc(SYSDATE - interval '1' year,'YEAR'),'YYYYMM') 
AND  mediaType = 'Audio CD'
THEN 0.8
ELSE 1 END)
END discountPrice;
END;

干杯！！