java调用oracle存储过程例子

来源：互联网发布：天津seo 页面优化编辑：程序博客网时间：2024/06/06 12:57

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>

0 0