lunzi

一、定义
------------------------------------------------------
-- Export file for user 24365                       --
-- Created by Administrator on 2007-11-13, 22:07:04 --
------------------------------------------------------

spool ccp_news.log

prompt
prompt Creating package CCP_NEWS
prompt =========================
prompt
create or replace package 24365.CCP_NEWS is

  -- Author  : ADMINISTRATOR
  -- Created : 2007-11-13 21:32:33
  -- Purpose : 
  
  -- Public type declarations
  type ccp is ref cursor;
  
  -- Public constant declarations
  p_code constant number := -1;

  -- Public variable declarations
  p_flag number;

  -- Public function and procedure declarations
     /**
     *标识码    [out]   p_code
		 *总数      [out]   p_count
		 *记录集    [out]   p_rcl
		 *开始索引  [in]    p_startIndex
		 *分页数    [in]    p_pageSize 
		 */
  procedure getUserList_proc(p_code out integer,
                             p_count out integer,
                             p_rcl out ccp,
                             p_startIndex in integer,
                             p_pageSize in integer);

end CCP_NEWS;
/

prompt
prompt Creating package body CCP_NEWS
prompt ==============================
prompt
create or replace package body 24365.CCP_NEWS is
  
 procedure getUserList_proc(p_code out integer,
                            p_count out integer,
                            p_rcl out ccp,
                            p_startIndex in integer,
                            p_pageSize in integer) is
  begin
       select count(*) 
           into p_count
       from tableName;
       
       open p_rcl for
         select * from (select *
                        from tableName u
                        order by u.add_date desc
                        ) 
         where rownum <= p_startIndex + p_pageSize and
               rownum >= p_startIndex;
         p_code := 1;
        exception 
           when others then
           rollback;
           p_code := -1;
 end getUserList_proc;
end CCP_NEWS;
/


spool off
二、调用
	 /**
     *标识码    [out]   p_code
	 *总数      [out]   p_count
	 *记录集    [out]   p_rcl
	 *开始索引  [in]    p_startIndex
	 *分页数    [in]    p_pageSize 
	 */
	private static void getUserListProc(){
		JdbcManager jdbc = null;
		ResultSet rs = null;
		try {
			jdbc = new JdbcManager();
			jdbc.createCallStatement("CCP_NEWS.getUserList_proc(?,?,?,?,?)");
			jdbc.registerCInt(1);
			jdbc.registerCInt(2);
			jdbc.registerOutParameter(3);
			jdbc.setCInt(4, 0);
			jdbc.setCInt(5, 1);
			jdbc.executeCQuery();
			int flag = jdbc.getCInt(1);
			rs = jdbc.getResultSet(3);
			while(rs.next()){
				System.out.println("用户名："+rs.getString("user_name"));
			}
			System.out.println("flag="+flag);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			if(rs!=null)
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			if(jdbc!=null)
				jdbc.close();
		}
	}