在java中调用存储过程

在java中调用存储过程：
一、只返回一个数值
1.存储过程
create or replace procedure getCount(a in number,
                                     b in number,
                                     c out number) is
  sql_str varchar2(100);
begin
  sql_str := 'select ' || a || '+' || b || 'into: c from dual';
  execute immediate sql_str
    into c;
  dbms_output.put_line(sql_str);
end getCount;


2.java中取值
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@127.0.0.1:1521:lubby";
Connection conn = DriverManager.getConnection(url, "admin", "admin");
CallableStatement cs = conn.prepareCall("{call GETCOUNT(?,?,?)}");
cs.setFloat(1, 33);
cs.setFloat(2,33);
cs.registerOutParameter(3, oracle.jdbc.OracleTypes.FLOAT);
cs.execute();


System.out.println(cs.getInt(3));
二、返回结果集的


1.存储过程：
create or replace procedure getinfo(param_row  in number,
                                    param_size in number,
                                    out_count  out number,
                                    out_list   out sys_refcursor) is
  sql_str       varchar2(250);
  sql_count_str varchar2(250);
begin
  --求总数
  sql_count_str := 'select count(*) from info ';
  --求结果集
  if param_row is not null and param_size is not null then
    sql_str := 'select rownum rn,t1.* from info t1 where 1=1 and rownum <=' ||
               param_row * param_size;
    sql_str := 'select * from (' || sql_str || ') t2 where 1=1 and t2.rn >' ||
               (param_row - 1) * param_size;
  end if;
  --输出查询语句
  dbms_output.put_line(sql_str);
  dbms_output.put_line(sql_count_str);
  --返回结果集和总数
  execute immediate sql_count_str
    into out_count;
  open out_list for sql_str;
end getinfo;


2.java中取结果集


public static void main(String[] args) throws ClassNotFoundException,
SQLException {
Class.forName("oracle.jdbc.driver.OracleDriver");
String url = "jdbc:oracle:thin:@127.0.0.1:1521:lubby";
Connection conn = DriverManager.getConnection(url, "admin", "admin");
CallableStatement procedure = conn.prepareCall("{call getinfo(?,?,?,?)}");
procedure.setInt(1, 1);
procedure.setInt(2, 10);
procedure.registerOutParameter(3, OracleTypes.FLOAT);
procedure.registerOutParameter(4, OracleTypes.CURSOR);
procedure.execute();
Integer count = 0;
count = procedure.getInt(3);
System.out.println("count is " + count);
ResultSet rs = (ResultSet) procedure.getObject(4);
while(rs.next()){
System.out.println("id:" + rs.getInt(1));
System.out.println("bussiness:" + rs.getString(2));
System.out.println("customer:" + rs.getString(3));
System.out.println("brand:" + rs.getString(4));
}


}