Oracle atial类型添加检查限制(checkco traint)

文章作者 100test 发表时间 2007:10:10 12:12:56
来源 100Test.Com百考试题网

下面的例子简单地演示了如何为Oracle中的Spatial类型添加检查限制(check constraint).

SQL> connect scott/tiger
Connected.

SQL> 0drop table footprints.
0drop table footprints
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create type footprint_type as object(
geometry sdo_geometry,
name varchar2(32)
).
2 3 4 5 6 /

Type created.


SQL> create table footprints(
footprint footprint_type,
time timestamp,
constraint validsrid check (footprint.geometry.sdo_srid = -1),
constraint validgtype check (footprint.geometry.sdo_gtype = 2004 or footprint.geometry.sdo_gtype=2007)
).
2 3 4 5 6 Table created.


SQL> insert into footprints values(
footprint_type(
sdo_geometry(2004, 8307, null,
sdo_elem_info_array(1, 1003, 1),
sdo_ordinate_array(2,4,4,3,10,3,2,4)),
validate srid constraints),
2 sysdate
).
3 4 5 6 7 8 insert into footprints values(
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.VALIDSRID) violated

SQL> insert into footprints values(
footprint_type(
sdo_geometry(2003, -1, null,
sdo_elem_info_array(1, 1003, 1),
sdo_ordinate_array(2,4,4,3,10,3,2,4)),
validate gtype constraints),
2 sysdate
).
3 4 5 6 7 8 insert into footprints values(
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.VALIDGTYPE) violated


SQL> insert into footprints values(
footprint_type(
sdo_geometry(2004, -1, null,
sdo_elem_info_array(1, 1003, 1),
sdo_ordinate_array(2,4,4,3,10,3,2,4)),
2 validate srid constraints),
sysdate
).

3 4 5 6 7 8 1 row created.

SQL> 0select * from footprints.

FOOTPRINT(GEOMETRY(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_O
--------------------------------------------------------------------------------
TIME
---------------------------------------------------------------------------
FOOTPRINT_TYPE(SDO_GEOMETRY(2004, -1, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO
_ORDINATE_ARRAY(2, 4, 4, 3, 10, 3, 2, 4)), validate srid constraints)
31-AUG-07 05.24.58.000000 PM