JDBC调用Oracle存储过程

来源：互联网发布：东北人假义气知乎编辑：程序博客网时间：2024/05/16 07:28

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

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

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

create or replace procedure insert_student_batch(param1 in varchar2, param2 in integer) isbegin  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) isbegin  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) isbegin  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);}}

0 0