oracle利用包向JDBC返回结果集

1.创建包

create or replace PACKAGE package_user   AS    
TYPE MY_RESULTSET_CURSOR IS REF CURSOR;    
PROCEDURE PERSON_QUERY(RS OUT MY_RESULTSET_CURSOR,uesrid VARCHAR2);    
END;

2.创建包的内容

CREATE OR REPLACE PACKAGE BODY package_user
AS
  PROCEDURE PERSON_QUERY(
      RS OUT MY_RESULTSET_CURSOR,
      uesrid VARCHAR2)
  IS
    v_sql CLOB; --VARCHAR2(30000); --查询语句
    v_mandatescope CLOB;
    av_split    VARCHAR2(2) := ';';
    v_length    NUMBER;
    v_start     NUMBER;
    v_index     NUMBER;
    v_tablename VARCHAR2(1000);
    v_sqlwhere  VARCHAR2(1000);
    v_sqlselect VARCHAR2(1000);
  BEGIN
    DBMS_OUTPUT.ENABLE(buffer_size => NULL); --表示输出buffer不受限制
    --v_mandatescope := 'tlk_p_information;tlk_p_recruitment;tlk_p_personnel';
    v_mandatescope := 'tlk_p_news;tlk_p_readwrold;tlk_p_culturalexchange;tlk_p_regionalpresence;tlk_p_landtrends;tlk_p_trainworld;tlk_p_financialissue;tlk_p_investmentweek;tlk_p_speciallegal;tlk_p_supervisoryspecial;tlk_p_videocenter;tlk_p_staffinstructions;tlk_p_commonproblem;tlk_p_staffinstruction;tlk_p_information;tlk_p_recruitment;tlk_p_personnel;tlk_p_announcements;tlk_p_newpeople';
    v_length       := LENGTH(v_mandatescope);
    v_start        :=1;
    v_index        :=0;
    v_tablename    :='';
    v_sql          :='';
    WHILE(v_start  <= v_length)
    LOOP
      v_sqlwhere :='  union all  ';
      v_index    := instr(v_mandatescope, av_split, v_start);
      --对新闻速递进行特殊处理
      IF v_index     = 0 THEN
        v_tablename :=SUBSTR(v_mandatescope, v_start);
        v_start     := v_length + 1;
      ELSE
        v_tablename :=SUBSTR(v_mandatescope, v_start, v_index - v_start);
        v_start     := v_index                                + 1;
      END IF;
      v_sqlselect                              :='''''AS imageinfo,business.item_title AS Subject FROM '||v_tablename||' business, ';
      IF instr(v_tablename,'tlk_p_news')        >0 THEN
        v_sqlwhere                             :='';
      elsif instr(v_tablename,'tlk_p_newpeople')>0 THEN
        v_sqlselect                            :='''''AS imageinfo,TO_CHAR(business.item_name||''''||dep.name||'' ''||business.item_position||'' ''||TO_CHAR(business.item_hiredate,''yyyy-mm-dd'')) AS Subject   FROM  t_department dep,tlk_p_newpeople business,';
      END IF;
      v_sql:=v_sql||v_sqlwhere||'SELECT    
A.*,    
ROWNUM RN  
FROM    
(SELECT business.id AS newsid,      
business.formid,      
business.domainid,
fb.type, 
business.item_releasedate AS pubdate, 
get_tabletype('''||v_tablename||''')  AS TABLETYPE,  
'||v_sqlselect||' 
(SELECT u1.id,        
u1.name,        
u1.field1,        
CASE          
WHEN u1.field1 = ''11e5-4249-677e3c4b-b7ce-83d34dc35707''          
THEN ''1''          
ELSE ''0''        
END AS type      
FROM t_user u1      
) fb    
WHERE ((((EXISTS      
(SELECT u2.id      
FROM t_user u2      
WHERE u2.field1 = fb.field1      
AND u2.id       = '''||uesrid||'''      
)    
AND type = 1)    
OR (EXISTS      
(SELECT u3.id      
FROM t_user u3,        
t_department d1      
WHERE u3.defaultdepartment         = d1.id      
AND instr(d1.indexcode, fb.field1) > 0      
AND u3.id                          = '''||uesrid||
      '''      
)    
AND type                         = 0))    
AND business.item_publishedscope = ''本公司'')    
OR ( (EXISTS      
(SELECT newsmsd.id      
FROM '||v_tablename||'_mds newsmsd      
WHERE newsmsd.DATAID    = business.id      
AND newsmsd.MANDATETYPE =''u''      
AND newsmsd.MandateScope='''||uesrid||'''      
)    
OR EXISTS      
(SELECT newsmsd.id      
FROM t_user u,        
t_department d,        
'||v_tablename||'_mds newsmsd      
WHERE u.defaultdepartment=d.id      
AND newsmsd.MANDATETYPE  =''d''      
AND u.id                 ='''||uesrid||'''      
AND newsmsd.DATAID       = business.id      
AND newsmsd.MandateScope =d.id      
))    
AND business.item_publishedscope = ''授权'')    
OR author                        = '''||uesrid||''')    
AND business.author              = fb.id    
AND business.item_releaseDate   IS NOT NULL    
ORDER BY pubdate DESC    
)A  
WHERE ROWNUM < 2';
    END LOOP;
    -- dbms_output.put_line('INPUT1:'||v_sql ); --監視SQL語句
    OPEN RS FOR v_sql ;
  END PERSON_QUERY;
END;