Oracle对象关系数据库中类型间引用编译报错求助

错误原因分析 #

1. PLS-00588：声明了成员函数的对象类型仅提供了函数签名，未实现函数体。Oracle要求对象类型的成员函数必须包含完整的实现，否则无法通过编译。
2. PLS-00905：pickupOrder和deliveryOrder继承自OrderType，但OrderType因函数未实现编译失败，导致子类型无法引用无效的父类型。
3. 表定义语法错误：customer表中customerID PRIMARY KEY语句末尾缺少逗号，导致后续SCOPE定义语法不合法。

修正后的完整代码 #

CREATE TYPE AddressType AS OBJECT ( 
    street VARCHAR2(50), 
    city VARCHAR2(50), 
    plot NUMBER 
); 
/

CREATE TYPE PaymentType AS OBJECT ( 
    paymentID NUMBER, 
    totalAmount NUMBER,
    paymentMethod VARCHAR2(20)
);
/

CREATE TYPE CustomerType AS OBJECT ( 
    customerID NUMBER, 
    firstName VARCHAR2(50), 
    lastName VARCHAR2(50), 
    address AddressType,
    typeOfCustomer VARCHAR2(20),
    payment REF PaymentType,
    MEMBER FUNCTION discount RETURN NUMBER
)
INSTANTIABLE NOT FINAL;
/

CREATE TYPE BODY CustomerType AS
    MEMBER FUNCTION discount RETURN NUMBER IS
    BEGIN
        -- 示例实现：根据客户类型返回折扣率，默认返回0
        IF typeOfCustomer = 'VIP' THEN
            RETURN 0.1;
        ELSE
            RETURN 0;
        END IF;
    END discount;
END;
/

CREATE TYPE DeliveryDriverType AS OBJECT ( 
    driverID NUMBER, 
    firstName VARCHAR2(50), 
    lastName VARCHAR2(50)
) 
/

CREATE TYPE OrderType AS OBJECT ( 
    orderID NUMBER, 
    orderedFoodItems VARCHAR2(200), 
    estimatedPreparationTime NUMBER, 
    paymentType VARCHAR2(50), 
    orderPlacementTime DATE,
    payment REF PaymentType,
    customer REF CustomerType,
    deliveryDriver REF DeliveryDriverType,
    MEMBER FUNCTION orderTotal RETURN NUMBER 
) NOT FINAL; 
/

CREATE TYPE BODY OrderType AS
    MEMBER FUNCTION orderTotal RETURN NUMBER IS
    BEGIN
        -- 示例实现：从关联的Payment对象中获取总金额
        RETURN payment.totalAmount;
    END orderTotal;
END;
/

CREATE TYPE pickupOrder UNDER OrderType ( 
    pickUpTime TIMESTAMP, 
    OVERRIDING MEMBER FUNCTION orderTotal RETURN NUMBER 
); 
/

CREATE TYPE BODY pickupOrder AS
    OVERRIDING MEMBER FUNCTION orderTotal RETURN NUMBER IS
    BEGIN
        -- 自提订单无额外费用，直接返回父类计算的总额
        RETURN SELF.orderTotal;
    END orderTotal;
END;
/

CREATE TYPE deliveryOrder UNDER OrderType ( 
    estimatedDeliveryTime TIMESTAMP, 
    OVERRIDING MEMBER FUNCTION orderTotal RETURN NUMBER 
); 
/

CREATE TYPE BODY deliveryOrder AS
    OVERRIDING MEMBER FUNCTION orderTotal RETURN NUMBER IS
    BEGIN
        -- 配送订单额外加10元配送费
        RETURN SELF.orderTotal + 10;
    END orderTotal;
END;
/

CREATE TABLE payment OF PaymentType ( 
    paymentID PRIMARY KEY 
);

CREATE TABLE customer OF CustomerType ( 
    customerID PRIMARY KEY,
    payment SCOPE IS payment WITH ROWID 
);

CREATE TABLE deliveryDriver OF DeliveryDriverType ( 
    driverID PRIMARY KEY
);

CREATE TABLE orderTable OF OrderType ( 
    orderID PRIMARY KEY,
    payment SCOPE IS payment WITH ROWID,
    customer SCOPE IS customer WITH ROWID,
    deliveryDriver SCOPE IS deliveryDriver WITH ROWID
);

关键修正说明 #

• 为所有包含成员函数的对象类型添加了TYPE BODY实现，示例中包含简单业务逻辑，可根据实际需求修改。
• 修正了customer表定义中的逗号缺失问题，保证SCOPE语法合法。
• 保留了原代码中REF属性的SCOPE设置，该设置可限制引用仅指向指定表中的对象，避免无效引用。

内容的提问来源于stack exchange，提问作者jayCoder