如何使用 REF 引用行对象？

发布于 2024-08-09 13:13:41 字数 913 浏览 10 评论 0原文

我有两个实体：客户和订单，我都为其创建了类型。 SQL 类型声明是：

CREATE OR REPLACE TYPE  "CUSTOMERTYPE" AS OBJECT (
customerId CHAR(6),
name VARCHAR2(50),
address VARCHAR2(255),
telephone CHAR(11)
);

CREATE OR REPLACE TYPE  "ORDERTYPE" AS OBJECT (
customer REF CustomerType,
orderId CHAR(10),
orderDate DATE,
totalCost FLOAT
);

这个想法是客户可以下 1..* 订单。 1..1 位客户下了订单。

我还创建了 CustomerTable 和 OrderTable 表，如下所示：

CREATE TABLE  "CUSTOMERTABLE" OF  "CUSTOMERTYPE";
CREATE TABLE  "ORDERTABLE" OF  "ORDERTYPE";

CustomerTable 中有一个条目的 customerId = '123456'。当我执行以下命令时：

INSERT INTO OrderTable
  SELECT OrderType (REF(c), '1234567890', '02-Nov-2009', 99)
    FROM CustomerTable c
    WHERE c.customerId = '123456';

Oracle 报告该行已被插入。但是，当我检查数据时，出现错误：

ORA-00932：不一致的数据类型：预期的 NUMBER 得到 REF MILKPLUSVELLOCET.CUSTOMERTYPE

任何帮助将不胜感激。

原文

I have two entities, Customer and Order, both of which I have created types for. The SQL type declarations are:

CREATE OR REPLACE TYPE  "CUSTOMERTYPE" AS OBJECT (
customerId CHAR(6),
name VARCHAR2(50),
address VARCHAR2(255),
telephone CHAR(11)
);

CREATE OR REPLACE TYPE  "ORDERTYPE" AS OBJECT (
customer REF CustomerType,
orderId CHAR(10),
orderDate DATE,
totalCost FLOAT
);

The idea being that a customer can place 1..* orders. An order is placed by 1..1 customer.

I've also created CustomerTable and OrderTable tables like so:

CREATE TABLE  "CUSTOMERTABLE" OF  "CUSTOMERTYPE";
CREATE TABLE  "ORDERTABLE" OF  "ORDERTYPE";

There is an entry in CustomerTable with customerId = '123456'. When I execute the following:

INSERT INTO OrderTable
  SELECT OrderType (REF(c), '1234567890', '02-Nov-2009', 99)
    FROM CustomerTable c
    WHERE c.customerId = '123456';

Oracle reports that the row has been inserted. However, when I check the data I get error:

ORA-00932: inconsistent datatypes: expected NUMBER got REF MILKPLUSVELLOCET.CUSTOMERTYPE

Any help would be greatly appreciated.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

只涨不跌 2024-08-16 13:13:41

“当我检查数据时”是什么意思？

这适用于我的机器：

declare
  cust customertype;
begin
  cust := new customertype('111111', 'Mr. Burns', '42 Mil,ky Way', '+41 75 000');
  insert into customertable values cust;
end;
/


INSERT INTO OrderTable
  SELECT OrderType (REF(c), '1234567890', '02-Nov-2009', 99)
      FROM CustomerTable c
          WHERE c.customerId = '111111';

--

select * from ordertable;

select o.customer.name from ordertable o where o.orderid = '1234567890';

What do you mean by 'when I check the data'?

This works on my machine:

declare
  cust customertype;
begin
  cust := new customertype('111111', 'Mr. Burns', '42 Mil,ky Way', '+41 75 000');
  insert into customertable values cust;
end;
/


INSERT INTO OrderTable
  SELECT OrderType (REF(c), '1234567890', '02-Nov-2009', 99)
      FROM CustomerTable c
          WHERE c.customerId = '111111';

--

select * from ordertable;

select o.customer.name from ordertable o where o.orderid = '1234567890';

回复收藏 0 原文

~没有更多了~