利用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;BEGINfor s_student IN s_info LOOPDbms_Output.put_line(s_student.StudentName||''||s_student.Address);end lOOP;end;--Grant execute on SHOWINFO to jbit;beginShowInfo(10011);end;--create or replace procedure showinfo(empid in number,empno out varchar2,ename out varchar2,job out varchar2,)ascursor 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 stubBaseDao 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 stubBaseDao 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);// 设置小数部分为2double 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 stubBaseDao 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 stubBaseDao 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 stubint 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);}}