java调用oracle存储过程例子

1，导jar包---ojdbc6.jar

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 pack1.my_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 emp;
  if mod(myrows, myPageCount) = 0 then
    myPageCount := myrows / v_in_pagesize;
  else
    myPageCount := myrows / v_in_pagesize + 1;
  end if;
end;

3，编写java调用

package cn.hl.test.oracle;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 * oracle 调用存储过程
 * 
 * @Description:
 * @author Administrator
 * @date 2015-5-28 下午10:39:18
 * 
 */
public class OracleConnection {

public static void main(String[] args) throws Exception {

String driver = "oracle.jdbc.driver.OracleDriver";
String strUrl = "jdbc:oracle:thin:@192.168.56.128:1521:orcl";

String username = "scott";
String password = "root";
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
CallableStatement cstmt = null;

// 加载驱动
Class.forName(driver);
// 获取连接
conn = DriverManager.getConnection(strUrl, username, password);

CallableStatement proc = null; // 创建执行存储过程的对象
proc = conn.prepareCall("{ call scott.my_page(?,?,?,?,?,?) }"); // 设置存储过程// call为关键字.
// 设置输入参数
proc.setString(1, "emp"); // 设置第一个输入参数
proc.setInt(2, 1);
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();// 执行

// 输出参数
int i = proc.getInt(4);
System.out.println("输出参数：" + i);

//输出游标
rs = (ResultSet) proc.getObject(6); // 获得第一个参数是一个游标,转化成ResultSet类型

while (rs.next()) // 获得数据
{
System.out.println("<tr><td>" + rs.getString(1) + "</td><td>"
+ rs.getString(2) + "</td></tr>");
}

}

}

4，输出结果

输出参数：14
<tr><td>7369</td><td>SMITH</td></tr>
<tr><td>7499</td><td>ALLEN</td></tr>
<tr><td>7521</td><td>WARD</td></tr>
<tr><td>7566</td><td>JONES</td></tr>
<tr><td>7654</td><td>MARTIN</td></tr>
<tr><td>7698</td><td>BLAKE</td></tr>
<tr><td>7782</td><td>CLARK</td></tr>
<tr><td>7788</td><td>SCOTT</td></tr>
<tr><td>7839</td><td>KING</td></tr>
<tr><td>7844</td><td>TURNER</td></tr>