创建返回游标的函数与在java中调用取值

来源：互联网发布：淘宝产品视频编辑：程序博客网时间：2024/06/05 07:23

创建包-->创建函数

create or replace PACKAGE WR_TEST AS
type wrcursor is ref cursor;
function getcursor(t_post_id in varchar2) return wrcursor;
END WR_TEST;
create or replace PACKAGE body WR_TEST AS
function getcursor(t_post_id in varchar2)
return wrcursor
is
empcursor wrcursor;
begin
OPEN empcursor
FOR SELECT * FROM REPLY where POSTID=t_post_id;
RETURN empcursor;
end getcursor;
END WR_TEST;

select a.*,WR_TEST.GETCURSOR(a.POSTID) reply from POSTCONTEXT a;
select WR_TEST.GETCURSOR(1) from dual;

---------------------------------------------------------------------------------------------------------

在java中调用-->

public List getpostlist() {

   List<Postlist> plist = new ArrayList<Postlist>();
       Connection con = null;
       PreparedStatement csmt = null;
       ResultSet rs = null;
       int postid=0;
       String posttitle="";
       String lzname="";
       String posttext="";
       int floorid=0;
       int replyid=0;
       String replyname="";
       String rcontent="";
       try{
           String sql = "select POSTCONTEXT.*,WR_TEST.GETCURSOR(POSTCONTEXT.POSTID) wrc from POSTCONTEXT";
           //String sql = "select * from POSTCONTEXT";
           con = jdbcTemplate.getDataSource().getConnection();
           csmt = con.prepareStatement(sql);

           rs = (ResultSet) csmt.executeQuery();
           while(rs.next()){
               postid=(rs.getInt("POSTID"));
               lzname=(rs.getString("LZNAME"));
               posttext=(rs.getString("POSTTEXT"));
               posttitle=(rs.getString("POSTTITLE"));
               ResultSet rs2 = (ResultSet) rs.getObject("wrc");
               List<Replylist> rlist =new ArrayList<Replylist>();
               while(rs2.next()){
                   replyid=(rs2.getInt("REPLYID"));
                   floorid=(rs2.getInt("FLOORID"));
                   replyname=(rs2.getString("REPLYNAME"));
                   rcontent=(rs2.getString("RCONTENT"));
                   postid=(rs2.getInt("POSTID"));
                   Replylist rd1 = new Replylist(replyid, replyname, rcontent, floorid, postid);
                   rlist.add(rd1);
               }

               Postlist pl = new Postlist(postid, posttitle, lzname, posttext, rlist);
               plist.add(pl);

    return plist;
   }
    返回游标的函数需要这样接收       ResultSet rs2 = (ResultSet) rs.getObject("wrc");然后才能取游标结果集里面的值

0 0