JDBC调用Oracle存储过程

首先建立一个测试用的表Student

create table STUDENT
(
  student_id NUMBER not null,
  nickname   VARCHAR2(100),
  age        NUMBER
);
create sequence STUDENT_SEQ
minvalue 0
maxvalue 999999
start with 1
increment by 1
nocache;

第一个存储过程insert_student_batch，只有2个入参，无返回值

create or replace procedure insert_student_batch(param1 in varchar2, param2 in integer) is
begin
  for i in 1..99999 loop 
    insert into student (student_id,nickname,age) values (student_seq.nextval,param1,param2);
  end loop;
end insert_student_batch;

第二个存储过程query_cnt，只有一个类型为int的返回值

create or replace procedure query_cnt(param1 out number) is
begin
  select count(*) into param1 from student;
end query_cnt;

第三个存储过程，有一个入参，一个类型为list的返回值。要返回list，必须配合使用package定义的游标

create or replace procedure querylist(param1 in varchar2, param2 out studentpackage.student_cursor) is
begin
  open param2 for select * from student where nickname = param1;
end querylist;

create or replace package studentpackage is
  type student_cursor is ref cursor;
end studentpackage;

下面为JDBC的调用代码：

package jdbc;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class ConnOracle {
	
	public static void main(String[] args) {
		long time1 = System.currentTimeMillis();
		Connection conn = null;
		ResultSet rs = null;
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "zlz","zlz");
			CallableStatement proc = null;
			
			// 调用有2个入参、无返回值的存储过程
			proc = conn.prepareCall("{ call insert_student_batch(?,?) }");
			proc.setString(1, "紫霄大魔王");
			proc.setInt(2, 99);
			proc.execute();
			
			// 调用无入参、1个返回值（非列表）的存储过程
//			proc = conn.prepareCall("{ call query_cnt(?) }");
//			proc.registerOutParameter(1, Types.INTEGER);
//			proc.execute();
//			int count = proc.getInt(1);
//			System.out.println("count = " + count);
			
			// 调用有1个入参、1个返回值（列表）的存储过程
//			proc = conn.prepareCall("{ call querylist(?,?) }");
//			proc.setString(1, "紫霄小魔王");
//			proc.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);
//			proc.execute();
//			rs = (ResultSet)proc.getObject(2);
//			while(rs.next()) {
//				System.out.println("student_id = " + rs.getInt(1));
//				System.out.println("nickname = " + rs.getString(2));
//				System.out.println("age = " + rs.getInt(3));
//			}
			
//			Statement stmt = conn.createStatement();
//			rs = stmt.executeQuery("SELECT * FROM student where nickname = '紫霄小魔王'");
//			while(rs.next()) {
//				System.out.println("student_id = " + rs.getInt(1));
//				System.out.println("nickname = " + rs.getString(2));
//				System.out.println("age = " + rs.getInt(3));
//			}
			
			conn.close();
		} catch(Exception e) {
			e.printStackTrace();
		}
		long time2 = System.currentTimeMillis();
		System.out.println(time2 - time1);
	}
}