时间戳

首页 > 解决方案 > 如果引用了客户表，如何将数据插入到汽车表中？

sql - 如果引用了客户表，如何将数据插入到汽车表中？

问题描述

CREATE TYPE Customer_t AS OBJECT 
(
    cid char(6),
    name varchar(15),
    birthdate date,
    phone char(10),
    address varchar(50)  
);

CREATE TABLE Customer OF Customer_t 
(
    cid PRIMARY KEY 
);

CREATE TYPE Car_t AS OBJECT 
(
    regno char(9),
    make varchar(12),
    model varchar(10),
    mdate date,
    owner REF Customer_t,
    value number(8,2)
);
/

CREATE TABLE Cars OF Car_t (regno PRIMARY KEY );

INSERT INTO Cars (regno, make, model, mdate, owner, value)
VALUES ('car1', 'german','honda',
  TO_DATE('2000/12/16 12:00:00', 'yyyy/mm/dd hh:mi:ss'), 
  'c1', 8000 );

错误 ：

SQL 错误：ORA-00932：不一致的数据类型：预期的 REF 得到了 CHAR 00932。00000 -“不一致的数据类型：预期的 %s 得到了 %s”

标签： sqldatabaseoracleoracle11g

解决方案

首先，您需要已经创建了一个客户，以便引用它。INSERT然后在声明中包含引用。见下文：

CREATE TYPE Customer_t AS OBJECT (
  cid  char(6),
  name  varchar(15) ,
  birthdate  date ,
  phone char(10),
  address varchar(50)  
);

CREATE TABLE Customer OF Customer_t (
             cid      PRIMARY KEY );

insert into customer values (customer_t('cl1234', 'Peter', 
  to_date('1990-05-04', 'YYYY-MM-DD'), '1234567890', 
  '123 Maple St'));

CREATE TYPE Car_t AS OBJECT (
  regno char(9),
  make varchar(12) ,
  model varchar(10) ,
  mdate date,
  owner REF  Customer_t,
  value number(8,2)
  );

CREATE TABLE Cars OF Car_t (
            regno      PRIMARY KEY );

insert into cars values (
  car_t('reg123456', 'honda', 'accord',
    to_date('2000-12-16 12:00:00', 'YYYY-MM-DD HH24:MI:SS'),
    (select ref(cu) from customer cu where cid = 'cl1234'),
    8000)
  );

推荐阅读