如何从Oracle 11g中的对象类型列中选择?(How to SELECT from object type column in Oracle 11g?)
嗨,大家好,我有以下两个Oracle对象:
CREATE OR REPLACE TYPE car AS OBJECT(
name VARCHAR( 80 )
) NOT FINAL;
还有另一个目标:
CREATE OR REPLACE TYPE truck UNDER car (
doors NUMBER,
seats NUMBER
);
还有下面的表格:
CREATE TABLE vehicles (
id NUMBER NOT NULL,
vehicle car,
PRIMARY KEY (id)
);
以下是一些数据:
INSERT INTO vehicles ( id, vehicle ) VALUES ( 1, truck( 'ford', 4, 4 ) );
INSERT INTO vehicles ( id, vehicle ) VALUES ( 2, truck( 'toyota', 4, 5 ) );
最后,我的问题是: 如何从车辆列表中只选择车门数和座位数?
我尝试了下面,但它不起作用:
SELECT v.vehicle.doors AS doors AS seats FROM vehicles v;
我有以下错误:
ORA-00904: "V"."VEHICLE"."DOORS": invalid identifier
只有我可以得到没有任何错误的参数是来自汽车对象的参数。
仅供参考,我在CentOS 6.2上使用Oracle 11g
干杯,博扬
Hy guys, I have following two Oracle objects:
CREATE OR REPLACE TYPE car AS OBJECT(
name VARCHAR( 80 )
) NOT FINAL;
And also, there is another object:
CREATE OR REPLACE TYPE truck UNDER car (
doors NUMBER,
seats NUMBER
);
There is also following table:
CREATE TABLE vehicles (
id NUMBER NOT NULL,
vehicle car,
PRIMARY KEY (id)
);
Here is some data:
INSERT INTO vehicles ( id, vehicle ) VALUES ( 1, truck( 'ford', 4, 4 ) );
INSERT INTO vehicles ( id, vehicle ) VALUES ( 2, truck( 'toyota', 4, 5 ) );
Finally, my question is: How to select only number of doors and number of seats from vehicle table column?
I tried following but it does not work:
SELECT v.vehicle.doors AS doors AS seats FROM vehicles v;
I got following error:
ORA-00904: "V"."VEHICLE"."DOORS": invalid identifier
Only parameter that i can get without any error is one from car object.
FYI, I am using Oracle 11g on CentOS 6.2
Cheers, Bojan
原文:https://stackoverflow.com/questions/10318272
更新时间:2019-05-27 00:03
最满意答案
您需要使用TREAT函数来获取数据库引擎,以将VEHICLE作为卡车对待,如下所示:
SELECT ID, TREAT(vehicle AS TRUCK).DOORS FROM VEHICLES
分享并享受。
You need to use the TREAT function to get the database engine to treat VEHICLE as a TRUCK, as in:
SELECT ID, TREAT(vehicle AS TRUCK).DOORS FROM VEHICLES
Share and enjoy.
2012-04-25
相关问答
http://download.oracle.com/otn/nt/oracle11g/win32_11gR1_database.zip
不需要注册,直接复制到迅雷或其他下载软件中即可下载。
Oracle数据库创建用户 在dba权限的用户下执行命令"create user 用户名 identified by 密码;" 用这个命令创建的用户还不能登录,需要进行授权 赋予connect和resource权限 grant connect,resource to 用户名;
您需要使用TREAT函数来获取数据库引擎,以将VEHICLE作为卡车对待,如下所示: SELECT ID, TREAT(vehicle AS TRUCK).DOORS FROM VEHICLES
分享并享受。 You need to use the TREAT function to get the database engine to treat VEHICLE as a TRUCK, as in: SELECT ID, TREAT(vehicle AS TRUCK).DOORS FROM V
...
该问题与Oracle版本有关。 该错误显示在11.2.0.1上,但在应用11.2.0.4后,它不存在。 Oracle在这些版本之间的某处更改/修复了该问题。 The problem is related to Oracle version. The error is shown on 11.2.0.1 but after applying of 11.2.0.4 it does not exist. The issue was changed/fixed by Oracle somewhere b
...
Oracle 将表中的日期存储为7个字节 字节1 - 世纪+100 字节2 - (年MOD 100)+ 100 字节3个月 字节4天 字节5 - 小时+ 1 字节6 - 分钟+ 1 字节7 - 秒+ 1 所以120,116,3,6,1,1,1转换为: 字节1 - 世纪= 120 - 100 = 20 字节2 - 年= 116 - 100 = 16 字节3 - 月= 3 字节4 - 天= 6 字节5 - 小时= 1 - 1 = 0 字节6 - 分钟= 1 - 1 = 0 字节7 - 秒= 1 - 1
...
主要问题是:你想实现什么目标? 在内部查询中,您按A1,A2,A3进行分组,因此您可以根据A1 / A2 / A3组合获得一行。 所有这些列都在选择列表中。 所以每一行都与另一行不同,因为它们都有不同的A1 / A2 / A3组合。 一个使用DISTINCT从结果中删除重复项,但在你的情况下,可能没有重复项,因此DISTINCT在这里完全是多余的。 然后你选择A4,但是当你按A1,A2,A3分组时,你通常必须决定你想要的A4。 如果A4依赖于A1,A2,A3或其组合(例如员工号码上的员工姓名,即由
...
创建序列后,只需更新现有行: alter table name add (id integer);
create sequence name_seq;
update name
set id = name_seq.nextval;
commit;
在LOOP中不需要PL / SQL或慢速且无效的逐行处理。 不相关,但是:触发器中的赋值可以简化为: :new.id := name_seq.NEXTVAL;
不需要select .. from dual After you created
...
oracle的正确查询语法是: query.query ("select ESTREC,LOTE,FECREC
from prueba.RECAUDO_ENC
where NITREC = :P1 and ESTREC = :P2
ORDER BY FECREC DESC",
rsh, new Object[]{"1234","PG"});
Correct query s
...
您可以通过检查审核状态 SHOW parameter audit_trail
要么 SELECT name, value FROM v$parameter WHERE name = 'audit_trail';
如果它被设置为none则审核是否关闭,其他任何内容都已打开。 有关此值的选项的更多信息,请参见此处 。 You can check the audit status via SHOW parameter audit_trail
or SELECT name, value FROM v
...
JSapkota提到的Oracle Issue / Doc ID 757537.1明确指出,这不是错误,而是正确/预期的行为: mview的STALENESS,引用PL / SQL函数设置为UNKOWN,因为无法确定PL / SQL函数的变化。 根据设计和代码,当前行为是正确的。 我想使用DETERMINISTIC函数而不是默认范围可能会阻止它。 The Oracle Issue/Doc ID 757537.1 mentioned by JSapkota states clearly, that
...