Oracle存储过程代码

 package com.test.oracle;

import java.sql.*;

public class testOracleFenye {

	public static void main(String[] args) {
		ResultSet rs = null;
		Statement stmt = null;
		Connection conn = null;
		try {
			String URL = "jdbc:oracle:thin:@localhost:1521:myoracl";
			String user = "scott";
			String password = "tiger";
			
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection(URL, user, password);
			CallableStatement cs = conn.prepareCall("{call fenye(?,?,?,?,?,?)}");
			cs.setString(1, "emp");
			cs.setInt(2, 6);
			cs.setInt(3, 1);
			cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);
			cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);
			cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
			
			cs.execute();
			System.out.println(cs.getInt(4));
			System.out.println(cs.getInt(5));
			rs = (ResultSet)cs.getObject(6);
			
			while (rs.next()) {
				System.out.print(rs.getInt(1)+"     ");
				System.out.print(rs.getString(2)+"    ");
				System.out.print(rs.getString(3)+"    ");
				System.out.print(rs.getInt(4)+"     ");
				System.out.print("\n");
			}
			} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
					rs = null;
				}
				if (stmt != null) {
					stmt.close();
					stmt = null;
				}
				if (conn != null) {
					conn.close();
					conn = null;
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}

 

--先建包
create or replace package fenyepackage as
type test_cursor is ref cursor;
end fenyepackage;

--存储过程
create or replace procedure fenye
(tableName in varchar2,
pageSize in number,
pageNow in number,
myRows out number,  --总记录数
myPageCount out number,
zhao_cursor out fenyepackage.test_cursor ) is 
v_sql varchar2(1000);
v_begin number:=(pageNow-1)*pageSize+1;
v_end number:=pageNow*pageSize;
begin
v_sql:='select * from (select t1.*,rownum rn from (select * from '||tableName||') t1 where rownum<='||v_end||') where rn>='||v_begin;
open zhao_cursor for v_sql;
v_sql:='select count(*) from '||tableName;
execute immediate v_sql into myRows;
if mod(myRows,pageSize)=0 then
myPageCount:=myRows/pageSize;
else
myPageCount:=myRows/pageSize+1;
end if;
close zhao_cursor;
end;