执行Oracle存储过程显示结果问题

Question

如下存储过程：create or replace
PROCEDURE PRO_POI_SQL
( p_POIName IN VARCHAR2 
, p_CityID IN NUMBER
, p_ProvID IN NUMBER
) AS
v_CITY_ID varchar2(100);
v_ADMIN_TAB varchar2(100);
v_ADMIN_ID rowid;
v_POI_NAME varchar2(200);
v_LON  number(20,10); 
v_LAT  number(20,10); 
v_XPOS number(10,0); 
v_YPOS number(10,0);
v_Sql_Select   varchar2(500);
v_Sql_Select2   varchar2(500);
TYPE T_CUR IS REF CURSOR;            --动态游标定义
cur_poi_Rowid T_CUR;CURSOR cur_poi_name IS
    SELECT CITY_ID,POI_ADMIN_TAB
     FROM POI_ADMIN_DEFINE WHERE PROVINCE_ID=p_ProvID AND CITY_ID=p_CityID;  
BEGIN
      FOR recode_POI_NAME IN cur_poi_name LOOP
    v_CITY_ID := recode_POI_NAME.CITY_ID;
    v_ADMIN_TAB := recode_POI_NAME.POI_ADMIN_TAB;        --动态游标，查找出各市_IDX表ADMIN_ID的值
        v_Sql_Select := 'select rowidtochar(ADMIN_ID) from '|| v_ADMIN_TAB ||'_IDX where poi_name like ''%'||p_POIName||'%'' ' ;
        OPEN cur_poi_Rowid FOR v_Sql_Select;
        LOOP       
            FETCH cur_poi_Rowid INTO v_ADMIN_ID;
            EXIT WHEN cur_poi_Rowid%NOTFOUND;                 --未查到数据退出
            --DBMS_OUTPUT.PUT_LINE(v_ADMIN_ID);
        
         --根据ROWID查找各市的POI点
            v_Sql_Select2 := 'select POI_NAME, LON, LAT, XPOS, YPOS from '|| v_ADMIN_TAB ||' where rowid = '''|| v_ADMIN_ID ||''' order by display desc ';
            
        END LOOP;
        CLOSE cur_poi_Rowid;
   END LOOP;END PRO_POI_SQL;在执行 call PRO_POI_SQL('北京',1101,11) 时，希望要显示出以上 v_Sql_Select2 中查询出的结果，请问以上存储过程要如何修改？

Answer 1

这么多级游标嵌套还是第一次看到。。
你在“--根据ROWID查找各市的POI点
 v_Sql_Select2 :=。。 ” 
后面继续用游标，用DBMS_OUTPUT.PUTLINE(),打印出你的查询结果

Answer 2

再定义一个：cur_poi_POI T_CUR游标.
在 v_Sql_Select2 := 'select POI_...下面按照你上面写的动态游标那样再写一个
open cur_poi_POI for v_Sql_Select2 
     loop
          ......
          EXIT WHEN cur_poi_Rowid%NOTFOUND;    
          DBMS_OUTPUT.PUT_LINE(..)
       end loop;
close cur_poi_POI;

Answer 3

看了你的过程有一个疑问：
你的过程中用到了游标，在游标中要返回一个数据集合，如果游标中有多条数据岂不是返回多个数据集。（对你的数据结构不是很了解，下出一点疑问）解决方法：首先定义一个类型
create or replace package pack_type as  
  type mycur is ref cursor;   
end;  
create or replace
PROCEDURE PRO_POI_SQL
( p_POIName IN VARCHAR2 
, p_CityID IN NUMBER
, p_ProvID IN NUMBER
, r_cur in pack_type.mycur          ---定义一个返回参数
) AS
v_CITY_ID varchar2(100);
v_ADMIN_TAB varchar2(100);
v_ADMIN_ID rowid;
v_POI_NAME varchar2(200);
v_LON  number(20,10); 
v_LAT  number(20,10); 
v_XPOS number(10,0); 
v_YPOS number(10,0);
v_Sql_Select   varchar2(500);
v_Sql_Select2   varchar2(500);
TYPE T_CUR IS REF CURSOR;            --动态游标定义
cur_poi_Rowid T_CUR;CURSOR cur_poi_name IS
    SELECT CITY_ID,POI_ADMIN_TAB
     FROM POI_ADMIN_DEFINE WHERE PROVINCE_ID=p_ProvID AND CITY_ID=p_CityID;  
BEGIN
      FOR recode_POI_NAME IN cur_poi_name LOOP
    v_CITY_ID := recode_POI_NAME.CITY_ID;
    v_ADMIN_TAB := recode_POI_NAME.POI_ADMIN_TAB;        --动态游标，查找出各市_IDX表ADMIN_ID的值
        v_Sql_Select := 'select rowidtochar(ADMIN_ID) from '|| v_ADMIN_TAB ||'_IDX where poi_name like ''%'||p_POIName||'%'' ' ;
        OPEN cur_poi_Rowid FOR v_Sql_Select;
        LOOP       
            FETCH cur_poi_Rowid INTO v_ADMIN_ID;
            EXIT WHEN cur_poi_Rowid%NOTFOUND;                 --未查到数据退出
            --DBMS_OUTPUT.PUT_LINE(v_ADMIN_ID);
        
         --根据ROWID查找各市的POI点
            v_Sql_Select2 := 'select POI_NAME, LON, LAT, XPOS, YPOS from '|| v_ADMIN_TAB ||' where rowid = '''|| v_ADMIN_ID ||''' order by display desc ';
         --给r_cur赋值
         immediate execute 'open cur '||v_Sql_Select2;
        END LOOP;
        CLOSE cur_poi_Rowid;
   END LOOP;END PRO_POI_SQL;sql/plus显示
set pagesize 40;   
set linesize 1024;   
set serveroutput on;   
var v refcursor;   
call PRO_POI_SQL('北京',1101,11,:v) ;
print v;