关于Oracle大字段中存储主键集合后的读取问题

业务场景：空间叠加分析计算当前项目范围与底图中哪些项目有重叠，求重叠项目明细。经过空间分析后，已经将重叠项目的主键存储大字段中，以“，”为分割符。

Create or replace view v_cdjc_kz as

select “XMMC”,”PZSJ” from v_cdjc_kz_gcs2000 A

where A.keyid in

 (select * from v_cdjc_get_coverKeyId

Where p_cdjc_kz.set_orecid(p_cdjc_kz.get_orecid())=p_cdjc_kz.get_orecid());

 

Create or replace view v_cdjc_get_coverKeyId as

select REGEXP_SUBSTR((select replace(to_char(b.coverKeyId),chr(39),’’)

from bd_dtcccdlb_file B

where B.orecid=p_cdjc_kz.get_orecid()),

’[^,]+’,

1,

LEVEL) as STR

from DUAL

CONNECT BY LEVEL <=

(select max(length(REGEXP_SUBSTR((select replace(to_char(b.coverKeyId),chr(39),’’)

from bd_dtcccdlb_file B

where B.orecid=p_cdjc_kz.get_orecid()),

’[^,]+’,

’’)))

 +1 max token

from dual);

 

Create or replace package p_cdjc_kz is

Function set_orecid(receiveid varchar2) return varchar2;

Function get_orecid return varchar2;

End p_cdjc_kz;

 

Create or replace package body p_cdjc_kz is

Orecid varchar2(100);

Function set_orecid(receiveid varchar2) return varchar2 is

Begin

Orecid:=receiveid ;

Return Orecid;

End;

Function get_orecid return varchar2 is

Begin

Return Orecid;

End;

End p_cdjc_kz;