Java调用Oracle分页存储过程

来源：互联网发布：ip与mac地址扫描工具编辑：程序博客网时间：2024/06/05 08:32

Java调用Oracle分页存储过程

由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于是集合，所以不能用一般的参数，必须要用pagkage了.所以要分两部分，

1，建一个程序包。如下：

create or replace package testpackage as  type test_cursor is ref cursor;end testpackage;

2，建立存储过程，存储过程为：

create or replace procedure my_page(v_in_tableName in varchar2,                                      v_in_pageNow   in number,                                      v_in_pagesize  in number,                                      myrows         out number,                                      myPageCount    out number,                                      p_cursor       out testpackage.test_cursor) is    --sql语句    v_sql varchar2(500);    --计算分页数    v_begin number := (v_in_pageNow - 1) * v_in_pagesize + 1;    v_end   number := v_in_pageNow * v_in_pagesize;  begin    --拼接sql    v_sql := 'select t2.* from (select t1.*,rownum rn from (select *from ' ||             v_in_tableName || ') t1 where rownum<=' || v_end ||             ') t2  where rn>=' || v_begin;    open p_cursor for v_sql;--打开游标    select count(*) into myrows from employee;    if mod(myrows, myPageCount) = 0 then      myPageCount := myrows / v_in_pagesize;    else      myPageCount := myrows / v_in_pagesize + 1;    end if;  end;

Java调用的代码：

import java.sql.*;import oracle.jdbc.OracleCallableStatement;public class TestProcedurePage {public TestProcedurePage() {}public static void main(String[] args) {String driver = "oracle.jdbc.driver.OracleDriver";String strUrl = "jdbc:oracle:thin:@10.10.1.29:1521:testdb";Statement stmt = null;ResultSet rs = null;Connection conn = null;try {Class.forName(driver);conn = DriverManager.getConnection(strUrl, "neb", "testneb");CallableStatement proc = null; // 创建执行存储过程的对象// proc = conn.prepareCall("{call p_get_price(?,?,?,?) }"); //设置存储过程// call为关键字.proc = conn.prepareCall("{call my_page(?,?,?,?,?,?) }"); // 设置存储过程/* * proc.registerOutParameter(1, oracle.jdbc.OracleTypes.NUMBER); * proc.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR); * 下面这样写也可以 proc.registerOutParameter(1, * oracle.jdbc.OracleTypes.FLOAT); proc.registerOutParameter(2, * oracle.jdbc.OracleTypes.CHAR); proc.registerOutParameter(3, * oracle.jdbc.OracleTypes.CURSOR); proc.setString(4, "3"); * //设置第一个输入参数 */proc.setString(1, "employee"); // 设置第一个输入参数proc.setInt(2, 2);proc.setInt(3, 10);// 设置普通输出参数proc.registerOutParameter(4, oracle.jdbc.OracleTypes.NUMBER);proc.registerOutParameter(5, oracle.jdbc.OracleTypes.NUMBER);// 设置游标输出参数proc.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);proc.execute();// 执行// 获取结果集的方式一：// rs = (ResultSet) proc.getObject(3);// 获取结果集的方式二：rs = ((OracleCallableStatement) proc).getCursor(6);while (rs.next()) {System.out.println(rs.getString(1) + "," + rs.getString(2)+ "," + rs.getString(3) + "," + rs.getString(4));}} catch (SQLException ex2) {ex2.printStackTrace();} catch (Exception ex2) {ex2.printStackTrace();} finally {try {if (rs != null) {rs.close();if (stmt != null) {stmt.close();}if (conn != null) {conn.close();}}} catch (SQLException ex1) {}}}}

阅读全文

0 0