-查找网格所对应的街道2
FUNCTION get_grid_street2(
p_address_ids IN VARCHAR2,
p_grid_ids IN VARCHAR2
)
RETURN typ_out_cur
IS
v_address_ids i_array;
out_street2_info typ_out_cur;
v_sql dbms_sql.varchar2a;
v_cursor PLS_INTEGER;
v_grid_ids i_array;
v_rows PLS_INTEGER;
test_insert VARCHAR2(255) := 'temp_test';
e_table_already_exist EXCEPTION;
PRAGMA EXCEPTION_INIT(e_table_already_exist,-955);
BEGIN
--切割网格关联标准地址
v_address_ids := pg_public.split(p_address_ids,'|');
--切割网格ID
v_grid_ids := pg_public.split(p_grid_ids,'|');
--编辑网格是返回街道
IF v_grid_ids.count = 1 AND v_grid_ids(1) IS NOT NULL THEN
v_sql(1) :=
'CREATE TABLE '||test_insert||' AS SELECT DISTINCT ga.gwm_fid,ga.name
FROM gs_address_info ga
WHERE ga.hierarchy = 3
START WITH ga.gwm_fid IN
(SELECT t.address_id
FROM r_grid_address t
WHERE t.grid_id = '||v_grid_ids(1)||')
CONNECT BY PRIOR ga.gwm_parentfid = ga.gwm_fid ';
ELSE
--新增以及合并网格时返回街道
FOR i IN 1..v_address_ids.count LOOP
IF i <=1 THEN
v_sql(1) :=
'CREATE TABLE '||test_insert||' AS SELECT ga.gwm_fid,ga.name
FROM gs_address_info ga
WHERE ga.hierarchy = 3
START WITH ga.gwm_fid = '||v_address_ids(i)||'
CONNECT BY PRIOR ga.gwm_parentfid = ga.gwm_fid ';
ELSIF i>=2 THEN
v_sql(i) :=' UNION
SELECT ga.gwm_fid,ga.name
FROM gs_address_info ga
WHERE ga.hierarchy = 3
START WITH ga.gwm_fid = '||v_address_ids(i)||'
CONNECT BY PRIOR ga.gwm_parentfid = ga.gwm_fid';
END IF;
END LOOP;
END IF;
v_cursor := dbms_sql.open_cursor;
BEGIN
dbms_sql.parse(c => v_cursor,
statement => v_sql,
lb => v_sql.first,
ub => v_sql.last,
lfflg => TRUE,
language_flag => dbms_sql.native);
EXCEPTION
WHEN e_table_already_exist THEN
EXECUTE IMMEDIATE 'DROP TABLE '||test_insert||' PURGE';
dbms_sql.parse(c => v_cursor,
statement => v_sql,
lb => v_sql.first,
ub => v_sql.last,
lfflg => TRUE,
language_flag => dbms_sql.native);
END;
v_rows := dbms_sql.execute(v_cursor);
OPEN out_street2_info FOR
'SELECT DISTINCT * FROM ' || test_insert;
EXECUTE IMMEDIATE 'DROP TABLE '||test_insert||' PURGE';
dbms_sql.close_cursor(v_cursor);
RETURN out_street2_info;
END get_grid_street2;
FUNCTION get_grid_street2(
p_address_ids IN VARCHAR2,
p_grid_ids IN VARCHAR2
)
RETURN typ_out_cur
IS
v_address_ids i_array;
out_street2_info typ_out_cur;
v_sql dbms_sql.varchar2a;
v_cursor PLS_INTEGER;
v_grid_ids i_array;
v_rows PLS_INTEGER;
test_insert VARCHAR2(255) := 'temp_test';
e_table_already_exist EXCEPTION;
PRAGMA EXCEPTION_INIT(e_table_already_exist,-955);
BEGIN
--切割网格关联标准地址
v_address_ids := pg_public.split(p_address_ids,'|');
--切割网格ID
v_grid_ids := pg_public.split(p_grid_ids,'|');
--编辑网格是返回街道
IF v_grid_ids.count = 1 AND v_grid_ids(1) IS NOT NULL THEN
v_sql(1) :=
'CREATE TABLE '||test_insert||' AS SELECT DISTINCT ga.gwm_fid,ga.name
FROM gs_address_info ga
WHERE ga.hierarchy = 3
START WITH ga.gwm_fid IN
(SELECT t.address_id
FROM r_grid_address t
WHERE t.grid_id = '||v_grid_ids(1)||')
CONNECT BY PRIOR ga.gwm_parentfid = ga.gwm_fid ';
ELSE
--新增以及合并网格时返回街道
FOR i IN 1..v_address_ids.count LOOP
IF i <=1 THEN
v_sql(1) :=
'CREATE TABLE '||test_insert||' AS SELECT ga.gwm_fid,ga.name
FROM gs_address_info ga
WHERE ga.hierarchy = 3
START WITH ga.gwm_fid = '||v_address_ids(i)||'
CONNECT BY PRIOR ga.gwm_parentfid = ga.gwm_fid ';
ELSIF i>=2 THEN
v_sql(i) :=' UNION
SELECT ga.gwm_fid,ga.name
FROM gs_address_info ga
WHERE ga.hierarchy = 3
START WITH ga.gwm_fid = '||v_address_ids(i)||'
CONNECT BY PRIOR ga.gwm_parentfid = ga.gwm_fid';
END IF;
END LOOP;
END IF;
v_cursor := dbms_sql.open_cursor;
BEGIN
dbms_sql.parse(c => v_cursor,
statement => v_sql,
lb => v_sql.first,
ub => v_sql.last,
lfflg => TRUE,
language_flag => dbms_sql.native);
EXCEPTION
WHEN e_table_already_exist THEN
EXECUTE IMMEDIATE 'DROP TABLE '||test_insert||' PURGE';
dbms_sql.parse(c => v_cursor,
statement => v_sql,
lb => v_sql.first,
ub => v_sql.last,
lfflg => TRUE,
language_flag => dbms_sql.native);
END;
v_rows := dbms_sql.execute(v_cursor);
OPEN out_street2_info FOR
'SELECT DISTINCT * FROM ' || test_insert;
EXECUTE IMMEDIATE 'DROP TABLE '||test_insert||' PURGE';
dbms_sql.close_cursor(v_cursor);
RETURN out_street2_info;
END get_grid_street2;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货