oracle存储代码,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;