create or replace package mypack2 as 3 type emp_cursor is ref cursor;4 end mypack;
视频课：https://edu.csdn.net/course/play/7940
分页存储过程
create or replace procedure pro_emp(--传入表名 v_in_tableName in varchar2,--传人每页显示几条记录 v_in_pageSize in number,--传入显示第几页的记录 v_in_pageNow in number,--输出总共多少页 v_out_pageCount out number,--输出总共有多少条记录 v_out_myRows out number,--输出当页的记录 p_cursor out mypack.emp_cursor)as v_sql varchar2(1000);v_begin number(4):=(v_in_pageNow-1)*v_in_pageSize+1;v_end number(4):=v_in_pageNow*v_in_pageSize;begin --oracle里字符串用单引号，赋值用:=，连接用|| v_sql:='select * from (select t.*,rownum rn from (select * from '||v_in_tableName||' ) t where rownum<='||v_end||' ) where rn>='||v_begin;open p_cursor for v_sql;v_sql:='select count(*) from '||v_in_tableName; --将执行结果写入v_out_myRows execute immediate v_sql into v_out_myRows; --注意oracle里取余必须用mod()函数，判断是否等于0用=，不是== if mod(v_out_myRows,v_in_pageSize)=0 then v_out_pageCount:=v_out_myRows/v_in_pageSize; else v_out_pageCount:=v_out_myRows/v_in_pageSize+1; end if; --close p_cursor; end pro_emp; /
java代码调用sql存储过程;
public static void main(String[] args) {Connection conn=null;CallableStatement cstmt=null;ResultSet rs=null;try{//注册驱动Class.forName("oracle.jdbc.driver.OracleDriver");//获取连接conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORA","scott","tiger");//调用存储过程cstmt=conn.prepareCall("{call pro_emp(?,?,?,?,?,?)}"); //对?赋值 cstmt.setString(1, "EMP"); cstmt.setInt(2, 3); cstmt.setInt(3, 2); cstmt.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER); cstmt.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER); cstmt.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR); //执行 cstmt.execute(); //获取结果 int myCounts=cstmt.getInt(4); System.out.println("总页数为："+myCounts); int myRows=cstmt.getInt(5); System.out.println("总记录数为："+myRows); rs=(ResultSet)cstmt.getObject(6); while(rs.next()){ System.out.println("用户名为："+rs.getString(2)+" 职位为："+rs.getString(3)+" 薪水为"+rs.getInt(6)); } } catch(Exception e){ e.printStackTrace(); } finally{ try{ //逆序关闭资源 rs.close(); cstmt.close(); conn.close(); } catch(Exception e1){ e1.printStackTrace(); } } } } [java] view plain copyimport java.sql.*; public class oracleFenYe { public static void main(String[] args) { Connection conn=null; CallableStatement cstmt=null; ResultSet rs=null; try{ //注册驱动 Class.forName("oracle.jdbc.driver.OracleDriver"); //获取连接 conn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORA","scott","tiger"); //调用存储过程 cstmt=conn.prepareCall("{call pro_emp(?,?,?,?,?,?)}"); //对?赋值 cstmt.setString(1, "EMP"); cstmt.setInt(2, 3); cstmt.setInt(3, 2); cstmt.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER); cstmt.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER); cstmt.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR); //执行 cstmt.execute(); //获取结果 int myCounts=cstmt.getInt(4); System.out.println("总页数为："+myCounts); int myRows=cstmt.getInt(5); System.out.println("总记录数为："+myRows); rs=(ResultSet)cstmt.getObject(6); while(rs.next()){ System.out.println("用户名为："+rs.getString(2)+" 职位为："+rs.getString(3)+" 薪水为"+rs.getInt(6)); } } catch(Exception e){ e.printStackTrace(); } finally{ try{ //逆序关闭资源 rs.close(); cstmt.close(); conn.close(); } catch(Exception e1){ e1.printStackTrace(); } } }
