基于Oracle的存储过程

存储过程oracle代码：

CREATE OR REPLACE PROCEDURE select_pro(tablename in varchar2, s_count number, e_count number, v_cur out SYS_REFCURSOR) AS  v_sql varchar2(1024); BEGIN v_sql:='select t.* from (select rownum r,e.* from ( ' || tablename ||' ) e) t where r between ' || s_count || ' and ' || e_count; OPEN v_cur FOR v_sql; END;

//函数做分页用java代码实现 package jdbc; //给test用户下所有的表做分页 import java.sql.*; import java.util.Scanner;

public class ConnectionOr5 {  public static CallableStatement cs = null;  public static ResultSet rs = null;  public static ResultSetMetaData rsm = null;  // 没new StringBuffer()对象  public static StringBuffer sbf = new StringBuffer();  public static Scanner sc = new Scanner(System.in);

 public static void main(String[] args) {   try {    // 调用函数 函数名没有写对

   cs = DriverConnection.driverConnection("test", "test").prepareCall(      "{? = call FUNTION_SELECT(?,?,?) }");    // 注册    cs.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);

   System.out.println("请输入表名：");    String str = sc.next();    cs.setString(2, str);    System.out.println("请输入初始值：");    int star = sc.nextInt();    System.out.println("请输入结束值：");    int end = sc.nextInt();    cs.setInt(3, star);    cs.setInt(4, end);    // 忘记更新    cs.executeUpdate();    rs = (ResultSet) cs.getObject(1);    rsm = rs.getMetaData();    int count = rsm.getColumnCount();    for (int i = 1; i <= count; i++) {     sbf.append(rsm.getColumnName(i)).append("/t").append("/t");    }    sbf.append("/n");    while (rs.next()) {     for (int i = 1; i <= count; i++) {      sbf.append(rs.getString(rsm.getColumnName(i))).append("/t")        .append("/t");     }     sbf.append("/n");    }    System.out.println(sbf);   } catch (SQLException e) {    e.printStackTrace();   } finally {    try {     if (rs != null)      rs.close();     if (cs != null)      cs.close();    } catch (SQLException e) {     e.printStackTrace();    }   }  } }

存储过程做分页

package jdbc;

import java.sql.*;

import oracle.jdbc.driver.OracleTypes;

public class ConnectionOr4 {  public static CallableStatement call = null;  public static ResultSet rs = null;  public static void main(String[] args) {   try {    //調用用过程    call = DriverConnection.driverConnection("test", "test").prepareCall("{call select_pro(?,?,?,?)}");    call.setString(1, "dept");    call.setInt(2, 3);    call.setInt(3, 6);    //输出参数必须注册    call.registerOutParameter(4, OracleTypes.CURSOR);    call.execute();    rs=(ResultSet) call.getObject(4);    while(rs.next()){     int deptno=rs.getInt("deptno");     String dname=rs.getString("dname");     String loc=rs.getString("loc");     System.out.println(deptno+"/t"+dname+"/t"+loc);    }   } catch (SQLException e){    e.printStackTrace();   }  } }