oracle 如何在对象关系数据库SQL中引用另一个类型中的类型

rbpvctlc  于 2023-05-16  发布在  Oracle
关注(0)|答案(1)|浏览(102)

我有这个sql代码,我想引用另一个类型中的类型,就像下面引用OrderType中的PaymentType和CustomerType,并在OrderTable中定义它们的范围。但是我得到这个错误错误:类型订单类型行/列:0/0 PL/SQL:编译单元分析终止行/列:7/17 PLS-00588:只允许在成员方法中使用非限定示例属性引用 错误:类型PICKUPORDER行/列:0/0 PL/SQL:编译单元分析终止行/列:1/24 PLS-00905:对象SQL_LIIYHAAYFONIVNSUTWPPQXPDP.ORDERTYPE无效 错误:TYPE DELIVERYORDER行/列:0/0 PL/SQL:编译单元分析终止行/列:1/26 PLS-00905:对象SQL_LIIYHAAYFONIVNSUTWPPQXPDP.ORDERTYPE无效
我认为这是由于继承过程中PaymentType的子类型和CustomerType的子类型,我删除了子类型并尝试了它,仍然面临着同样的问题。

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
); 
/

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 pickupOrder UNDER OrderType ( 
    pickUpTime TIMESTAMP, 
    OVERRIDING MEMBER FUNCTION orderTotal RETURN NUMBER 
); 
/

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

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
    
);
2nbm6dog

2nbm6dog1#

从错误消息中看不出任何问题,但由于名称冲突,您会得到ORA-00588错误。
您正在使用列paymentType定义表,该列的名称与您的对象类型相同:

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 OrderType AS OBJECT ( 
    orderID NUMBER, 
    orderedFoodItems VARCHAR2(200), 
    estimatedPreparationTime NUMBER, 
    paymentTypeStr VARCHAR2(50), 
    orderPlacementTime DATE,
    payment REF PaymentType,
    customer REF CustomerType,
    deliveryDriver REF DeliveryDriverType,
    MEMBER FUNCTION orderTotal RETURN NUMBER 
) NOT FINAL;

customer表中也有一个错误,缺少一个逗号:

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

fiddle
如果你想子类型PaymentTypeCustomerType,那么你必须将它们定义为NOT FINAL--就像注解中的this fiddle一样。

相关问题