嵌套表的引用问题
一下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 ');
INSERT INTO Address VALUES (
2, 'BeijingChangAnJie ', 'Buiding-B ', '010-99999999 ');
INSERT INTO Student
SELECT 'LIHUA ',22,REF(A)
FROM Address A
WHERE building = 'Buiding-A ';
INSERT INTO Student
SELECT 'ZHANGMING ',21,REF(A)
FROM Address A
WHERE building = 'Buiding-B ';
--验证
select S.Name
from address A
,student S
where A.building = 'Buiding-A '
and S.address = REF(A)
/*其实相当于多建立了一个Join列。在Forms DataBlock 创建过程中，Block Wizard会自动将Student表中的Address列视为Lookup域对象，可直接创建LOV*/
[解决办法]
顶
[解决办法]
个人认为REF(VALUE(T))是不能这样用的.
values() 返回的是表中的对象即:TEACHERS
而ref(参数);参数的类型要求为对象表的别名.
我测试了一个这个语句:
SELECT VALUE(T) FROM TABLE(SELECT TEACHERS FROM CENTER WHERE NAME = '徐汇 ') T
WHERE NAME = '张老师 ';
返回:TEACHER( '张老师 ', '01-1月 -80 ')
这好像不是一个对象表吧,而REF()的参数要求是个对象表别名.
不知道这样分析有没有问题?
[解决办法]
原因很简单:
REF的是一个OBJECT, 没有实例化
而INSERT...SELECT的时候挑出来的是一个实例, 不能完成从实例到类型的转换
[解决办法]
把CLASS表改成如下:
CREATE TABLE CLASS(
CLASSID VARCHAR2(7) PRIMARY KEY,
CENTER_NAME VARCHAR2(4) REFERENCES CENTER(NAME),
TEACHER_REF TEACHER_TABLE,
STUDENTS STUDENT_ARR
) NESTED TABLE TEACHER_REF STORE AS CLASS_TEA
再把INSERT...SELECT改成:
INSERT INTO CLASS
SELECT '0722T03 ', '徐汇 ', CAST(MULTISET(SELECT VALUE(T)
FROM TABLE(SELECT TEACHERS FROM CENTER WHERE NAME = '徐汇 ') T
WHERE T.NAME = '张老师 ') AS TEACHER_TABLE),
STUDENT_ARR(
STUDENT( '张三 ', '0001155245 '),
STUDENT( '李四 ', '0001152444 '),
STUDENT( '王五 ', '0001155242 '),
STUDENT( '赵六 ', '0001158357 ')
)
FROM DUAL;
就可以了
