- 论坛徽章:
- 0
|
一个SQL查询的问题:通过OID得到数据
- CREATE TABLE cola_markets (
- mkt_id NUMBER PRIMARY KEY,
- name VARCHAR2(32),
- shape MDSYS.SDO_GEOMETRY);
- declare
- i number;
- begin
- i :=100;
- while i<100000 loop
- i:= i+1;
- INSERT INTO cola_markets VALUES(
- i,
- 'cola_a',
- MDSYS.SDO_GEOMETRY(
- 2003, -- 2-dimensional polygon
- NULL,
- NULL,
- MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3), -- one rectangle (1003 = exterior)
- MDSYS.SDO_ORDINATE_ARRAY(1,1, i,i) -- only 2 points needed to
- -- define rectangle (lower left and upper right) with
- -- Cartesian-coordinate data
- )
- );
- end loop;
- exception
- when no_data_found then
- NULL;
- end;
- Create GLOBAL temporary table oid_tmp(oid number PRIMARY KEY) on commit delete rows;
- declare
- i number;
- begin
- i:= 100;
- while i<4000 loop
- i:= i+1;
- insert into oid_tmp(OID) values(i);
- end loop;
- end;
-
- select * from cola_markets L ,oid_tmp R where L.mkt_id = R.OID;
- commit
复制代码
上面是实现代码和测试代码,
临时表看来是一个不错的方法,多谢rollingpig兄 |
|