java调用存储过程分页
【摘要】
create or replace package mypack 2 as 3 type emp_cursor is ref cursor; 4 end mypack;
视频课：https://edu.csdn.net/course/play/7940
分页存储过程
create or replace proce...
-
create or replace package mypack
-
2 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 copy
-
import 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();
-
}
-
}
-
}
文章来源: aaaedu.blog.csdn.net，作者：tea_year，版权归原作者所有，如需转载，请联系作者。
原文链接：aaaedu.blog.csdn.net/article/details/80206703
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）