一下SQL在执行到最后一句时报错
CREATE TYPE TEACHER AS OBJECT
(
NAME VARCHAR2(105),
BIRTH DATE
);
/
CREATE TYPE TEACHER_TABLE AS TABLE OF TEACHER;
/
CREATE TABLE CENTER(
NAME VARCHAR2(4) PRIMARY KEY,
TEACHERS TEACHER_TABLE
)NESTED TABLE TEACHERS STORE AS TEACHER_TABLE_CENTER;
INSERT INTO CENTER VALUES( '徐汇 ',
TEACHER_TABLE(
TEACHER( '张老师 ', to_date( '1980-1-1 ', 'yyyy-mm-dd ')),
TEACHER( '李老师 ', to_date( '1982-1-1 ', 'yyyy-mm-dd ')),
TEACHER( '胡老师 ', to_date( '1978-1-1 ', 'yyyy-mm-dd '))
)
);
INSERT INTO CENTER VALUES( '长宁 ',
TEACHER_TABLE(
TEACHER( '方方老师 ', to_date( '1981-12-31 ', 'yyyy-mm-dd ')),
TEACHER( '马老师 ', to_date( '1983-12-31 ', 'yyyy-mm-dd '))
)
);
CREATE TYPE STUDENT AS OBJECT
(
NAME VARCHAR2(105),
STUDNO CHAR(10)
);
/
CREATE TYPE STUDENT_ARR AS VARRAY(30) OF STUDENT;
/
CREATE TABLE CLASS(
CLASSID VARCHAR2(7) PRIMARY KEY,
CENTER_NAME VARCHAR2(4) REFERENCES CENTER(NAME),
TEACHER_REF REF TEACHER,
STUDENTS STUDENT_ARR
);
INSERT INTO CLASS
SELECT '0722T03 ', '徐汇 ', REF(VALUE(T)),
STUDENT_ARR(
STUDENT( '张三 ', '0001155245 '),
STUDENT( '李四 ', '0001152444 '),
STUDENT( '王五 ', '0001155242 '),
STUDENT( '赵六 ', '0001158357 ')
)
FROM TABLE(SELECT TEACHERS FROM CENTER WHERE NAME = '徐汇 ') T
WHERE NAME = '张老师 ';
表结构简单来说就时有个CENTER表中有个个TEACHER的嵌套表，CLASS表中的TEACHER_REF字段希望引用该TEACHER嵌套表中的某记录，但在最后实行插入是报缺少右括号，但我实在找不出什么地方缺右括号啊。
------解决方案--------------------
帮顶
------解决方案--------------------
调试了一下,感觉是 REF(VALUE(T)) 错,
不知道其中的VALUE(T)能不能这样用的?
------解决方案--------------------
参考
REF函数用于对象表的创建和插入，看下面的例子：
--创建表对象Address_TP
CREATE TYPE Address_TP AS OBJECT (
address_id number,
street VARCHAR2(40),
building VARCHAR2(40),
phone VARCHAR2(15)
);
--创建表对象Student_TP
CREATE TYPE Student_TP AS OBJECT (
Name VARCHAR2(15),
Age NUMBER,
Address REF Address_TP
);
--创建对象表Address
CREATE TABLE Address OF Address_TP (
PRIMARY KEY (address_id)
);
--创建对象表Student
CREATE TABLE Student OF Student_TP (
PRIMARY KEY (Name)
);
--插入数据
INSERT INTO Address VALUES (
1, 'BeijingJianGuoMenWaiDaJie ', 'Buiding-A ', '010-88888888 ');