1.Oralce的分页查询
分页查询:可以简化表复杂度,让一张很大的表,分成很多块,不要一次性全部显示成一整块;方便阅览
可以将下列语句当成一个模版使用select * from (select t1.*,rownum rn from emp t1 where rownum < 8) where rn > 3;
定义一个包,包里面有游标变量类型:(记住游标变量是不能在包中或包体中声明的比如 mycursor sys_refcursor这是不允许的)
create or replace package mypage
is
type cursor_emp is ref cursor;
end mypage;
接着写存储过程,来实现分页的业务逻辑
下面定义了六个变量, 三个输入参数(表名,第几页,一页显示几行数据),三个输出变量(表中数据总共的行数,表总共分了几页,游标变量(用来指明你需要查询的记录)).
--pageCount 显示第几页
--pageSize 一页显示的数据
create or replace procedure emp_pro(tableName varchar2,pageCount number,pageSize number,
allSize out number,allPage out number,mycursor out mypage.cursor_emp)
is
Sql_s varchar2(1000);
Sql_count varchar2(1000);
startSize number:=pageCount*pageSize-pageSize+1;
endSize number:=pageCount*pageSize;
begin
Sql_s:='select * from
(select t1.*,rownum rn from '||tableName||' t1 where rownum <= '||endSize||') where rn >= '||startSize;
open mycursor for Sql_s;
Sql_count:='select count(*) from emp';
execute immediate Sql_count into allSize;
if mod(allSize,pageSize)=0 then
allPage:=allSize/pageSize;
else
allPage:=allSize/pageSize+1;
end if;
end;
我们来分析下面的语句
Sql_s:='select * from(select t1.*,rownum rn from '||tableName||' t1 where rownum <= '||endSize||') where rn >= '||startSize; open mycursor for Sql_s;
其实在PL/SQL编程中,可以把你需要写的SQL语句给一个字符变量,当执行存储过程的时候,oracle自动会辨认出来,
execute immediate SQL语句 into 变量
这一句的意思是:立即执行给定的SQL语句,把返回的结果给变量
JAVA调用存储过程:
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:Class","scott","admin");
CallableStatement call = con.prepareCall("{call emp_pro(?,?,?,?,?,?)}");
call.setString(1, "EMP");
call.setInt(2, 5);
call.setInt(3, 4);
//注册属性值
call.registerOutParameter(4, OracleTypes.INTEGER);
call.registerOutParameter(5, OracleTypes.INTEGER);
call.registerOutParameter(6, OracleTypes.CURSOR);
call.execute();
int ALLSIZE = call.getInt(4);
int ALLPAGE = call.getInt(5);
System.out.println("记录数: "+ALLSIZE);
System.out.println("总页数: "+ALLPAGE);
ResultSet rs = (ResultSet)call.getObject(6);
while(rs.next())
{
System.out.println("EMPNO="+rs.getInt(1)+" "+"ENAME="+rs.getString(2)+
" "+"SAL="+rs.getInt(6));
}
}
catch(Exception e)
{
e.printStackTrace();
}
.