一下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嵌套表中的某记录,但在最后实行插入是报缺少右括号,但我实在找不出什么地方缺右括号啊。
(
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嵌套表中的某记录,但在最后实行插入是报缺少右括号,但我实在找不出什么地方缺右括号啊。
解决方案 »
- 求救:无法连接数据库,但是我ping ip 地址却连接的上
- oracle中,数据文件被删除了,重新启动服务会报错,这时该怎么办?
- 怎样实现这样的连接语句?
- 求助,在log中记录执行过的SQL和Trace.
- 在线求救:一套数据库系统建立两个数据库问题。(其中一个数据库看不到)
- rman target连接的安全问题
- 用命令符格式 导数据
- 请问哪里有《oracle 9i数据库管理员实用技术指南》和《oracle 9i 数据库管理员高级技术指南》
- 请问ORACLE有自增数值型吗?
- 关于如何写oracle的function欢迎大家来讨论(参与有分)
- oracle中用什么命令来显示数据库名列表?急~~~~~~~~~
- 多表查询结果写到一张表中的问题
不知道其中的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*/
values() 返回的是表中的对象即:TEACHERS
而ref(参数);参数的类型要求为对象表的别名.
我测试了一个这个语句:
SELECT VALUE(T) FROM TABLE(SELECT TEACHERS FROM CENTER WHERE NAME = '徐汇') T
WHERE NAME = '张老师';
返回:TEACHER('张老师', '01-1月 -80')
这好像不是一个对象表吧,而REF()的参数要求是个对象表别名.不知道这样分析有没有问题?
而我的代码中VALUE(T)得到的却是一个对象
所以不能这么用,我在考虑是不是要用到对象视图自己顶一下
REF的是一个OBJECT, 没有实例化
而INSERT...SELECT的时候挑出来的是一个实例, 不能完成从实例到类型的转换
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;
就可以了