利用jdbc对数据表进行分页，操作存储过程(有参与无参，返回游标)，函数等

select * from dept

select * from (select e.empno,e.ename,rownum r from (select empno,ename from emp order by empno) e where rownum>=20)where r<=30

--无返回值的存储过程
create or replace procedure proc_ins_dept
(procdeptno in number,
procdeptname in varchar2,
procdeptloc in varchar2)
as 
begin
  insert into dept values(procdeptno,procdeptname,procdeptloc);
 end;
 drop procedure proc_ins_dept
 
 call proc_ins_dept(01,'ffd','fdg')
 --有返回值的存储过程
create or replace procedure proc_ins_dept2
(procdeptno in number,
procdeptname in varchar2,
procdeptloc in varchar2,
 resu out varchar2)
 is 
 begin
   insert into dept values(procdeptno,procdeptname,procdeptloc);
   resu:='success';
  exception 
    when others then
      resu:='error';
      end;
       drop procedure proc_ins_dept2
      --
      call proc_ins_dept2(7,'sa','dfsa');
     --
     --创建函数
     create or replace function getDeptAvg(deno in number)
     return number as favg number;
     begin
       select avg(sal) into favg from emp where(deptno=deno);
       return favg;
       end;
       --调用
       drop function getDeptavg;
       --
       declare 
       re number;
       begin 
         re:=getDeptAvg(10);
         dbms_output.put_line(re);
         end;
       select * from emp
       
       ------------
       CREATE OR REPLACE PROCEDURE ShowInfo
  (vStudentId IN number) AS 
CURSOR s_info IS select StudentName,Address from 
Student  where StudentId = vStudentId;
BEGIN
for s_student IN s_info LOOP
Dbms_Output.put_line(s_student.StudentName||''||s_student.Address);
end lOOP;
end;

--Grant execute on SHOWINFO to jbit;
begin
ShowInfo(10011);
end;
--
create or replace procedure showinfo
(empid in number,
empno out varchar2,
ename out varchar2,
job out varchar2,
)
as
cursor c is select empno,ename,job from emp where empno=empid;
begin
  for e in c loop
    dbms_output.put_line(e.empno||' '||e.ename||' '||e.job);
    end loop;
    end;
    --
    drop procedure showinfo;
    --
    select * from emp
     call showinfo(7369)  
       begin 
         showinfo(7369);
         end;
      --
      create or replace procedure getcur(cur out sys_refcursor,
      empid in number)
      is
      begin
        open cur for select empno,ename,job from emp where empno=empid;
        end getcur; 
       
       
       
       
       

java操作：

public class Test {
	private static Connection conn;
	private static PreparedStatement ps;
	private static ResultSet rs;

	public static void main(String[] args) throws Exception {
		// toPage(1, 10);// 分页
		// proce1(02, "bob", "hpe");// 无返回值的存储过程
		// proce2(9,"admin","beijing");// 有返回值的存储过程
		// function(10);//访问过程
		proce3(7369);// 带游标的存储过程

	}

	private static void proce3(int num) throws Exception {
		// TODO Auto-generated method stub
		BaseDao bd = new BaseDao();
		conn = bd.getConn();
		String sql = "call getcur(?,?)";
		CallableStatement cs = conn.prepareCall(sql);
		cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
		cs.setObject(2, num);
		cs.execute();
		ResultSet rs=(ResultSet) cs.getObject(1);
		while(rs.next())
		{
			System.out.println(rs.getObject(1)+"\t"+rs.getObject(2)+"\t"+rs.getObject(3));
		}
		rs.close();
		cs.close();
		conn.close();
	}

	private static void function(int num) throws Exception {
		// TODO Auto-generated method stub
		BaseDao bd = new BaseDao();
		conn = bd.getConn();
		String sql = "select getDeptAvg(?) from dual";
		ps = conn.prepareStatement(sql);
		ps.setObject(1, num);
		rs = ps.executeQuery();
		NumberFormat nf = NumberFormat.getInstance();// 创建一个数字格式化对象
		nf.setMaximumFractionDigits(2);// 设置小数部分为2
		double d = 0;
		while (rs.next()) {
			d = rs.getDouble(1);
		}
		System.out.println(nf.format(d));// 对结果进行格式化
		bd.release(conn, ps, rs);
	}

	private static void proce2(int no, String name, String loc)
			throws Exception {
		// TODO Auto-generated method stub
		BaseDao bd = new BaseDao();
		conn = bd.getConn();
		String sql = "call proc_ins_dept2(?,?,?,?)";
		CallableStatement cs = conn.prepareCall(sql);
		cs.setObject(1, no);
		cs.setObject(2, name);
		cs.setObject(3, loc);
		cs.registerOutParameter(4, java.sql.Types.VARCHAR);
		cs.execute();
		String result = (String) cs.getObject(4);
		System.out.println(result);
		cs.close();
		conn.close();

	}

	public static void proce1(int no, String name, String loc) throws Exception {
		// TODO Auto-generated method stub
		BaseDao bd = new BaseDao();
		conn = bd.getConn();
		String sql = "call proc_ins_dept(?,?,?)";
		CallableStatement cs = conn.prepareCall(sql);
		cs.setObject(1, no);
		cs.setObject(2, name);
		cs.setObject(3, loc);
		cs.execute();
		cs.close();
		conn.close();

	}

	public static void toPage(int currentPage, int pageSize) throws Exception {
		// TODO Auto-generated method stub
		int startPage = (currentPage - 1) * pageSize;// 起始页
		int endPage = currentPage * pageSize;// 结束页
		BaseDao bd = new BaseDao();
		conn = bd.getConn();
		String sql = "select * from (select e.empno,e.ename,rownum r from (select empno,ename from emp order by empno) e where rownum>=?)where r<=? ";
		ps = conn.prepareStatement(sql);
		ps.setObject(1, startPage);
		ps.setObject(2, endPage);
		rs = ps.executeQuery();
		while (rs.next()) {
			System.out.println(rs.getObject(1) + "\t" + rs.getObject(2));
		}
		bd.release(conn, ps, rs);

	}

}