jdbc批量调用oracle存储过程,JDBC调用Oracle存储过程

Oracle存储过程：

## 没有返回值的存储过程

--创建存储过程

create or replace procedure query_user

(v_name varchar2)

as

v_count binary_integer :=0;

begin

select count(*) into v_count from nc_user where name like '%'|| v_name ||'%';

dbms_output.put_line(v_count);

end;

/

--删除过程

drop procedure query_user;

## 没有返回值的存储过程

--创建存储过程

create or replace procedure insert_user

(v_name varchar2,v_mail varchar2,v_pass in varchar2,v_age in number)

as

v_desc varchar2(255) := 'desc nice';

begin

insert into nc_user(name,mail,pass,age,descb) values(v_name,v_mail,v_pass,v_age,v_desc);

end;

/

--删除过程

drop procedure insert_user;

## 有返回值的存储过程

--创建存储过程

create or replace procedure user_count

(v_name in out varchar2,v_count out number)

as

v_tmp varchar2(10) :='oracle ';

begin

select count(*) into v_count from nc_user where name like '%'|| v_name ||'%';

v_name := v_tmp || v_name;

end;

/

--删除过程

drop procedure user_count;

## 返回多行的存储过程

--创建程序包

create or replace package user_package

as

type user_cursor is ref cursor;

end;

/

--创建存储过程

create or replace procedure query_users

(v_name varchar2, v_cursor out user_package.user_cursor )

as

begin

open v_cursor for select * from nc_user where name like '%'|| v_name ||'%';

end;

/

--删除包

drop package user_package;

--删除过程

drop procedure query_users;

调用案例：

package com.what21.jdbc.demo06;

public class User {

private int id;

private String username;

private String password;

private String email;

private int age;

private String description;

public int getId() {

return id;

}

public void setId(int id) {

this.id = id;

}

public String getUsername() {

return username;

}

public void setUsername(String username) {

this.username = username;

}

public String getPassword() {

return password;

}

public void setPassword(String password) {

this.password = password;

}

public String getEmail() {

return email;

}

public void setEmail(String email) {

this.email = email;

}

public int getAge() {

return age;

}

public void setAge(int age) {

this.age = age;

}

public String getDescription() {

return description;

}

public void setDescription(String description) {

this.description = description;

}

}

package com.what21.jdbc.demo06;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Types;

import java.util.ArrayList;

import java.util.List;

public class CallProcedureDemo {

/**

* 调用存储过程: query_user

*

* @param conn

*/

public static void queryUser(Connection conn) {

String sql = "{ call query_user(?) }";

CallableStatement cstmt = null;

try {

conn.setAutoCommit(false);

cstmt = conn.prepareCall(sql);

cstmt.setString(1, "");

cstmt.execute();

conn.commit();

} catch (SQLException e) {

e.printStackTrace();

} finally {

if (cstmt != null) {

try {

cstmt.close();

} catch (SQLException e) {

e.printStackTrace();

}

cstmt = null;

}

}

}

/**

* 调用存储过程：insert_user

*

* @param conn

* @param user

*/

public static void insertUser(Connection conn,User user) {

String sql = "{ call insert_user(?,?,?,?) }";

CallableStatement cstmt = null;

try {

conn.setAutoCommit(false);

cstmt = conn.prepareCall(sql);

cstmt.setString(1, user.getUsername());

cstmt.setString(2, user.getEmail());

cstmt.setString(3, user.getPassword());

cstmt.setInt(4, user.getAge());

cstmt.execute();

conn.commit();

} catch (SQLException e) {

e.printStackTrace();

} finally {

if (cstmt != null) {

try {

cstmt.close();

} catch (SQLException e) {

e.printStackTrace();

}

cstmt = null;

}

}

}

/**

* 调用存储过程：user_count

*

* @param conn

* @return

*/

public static int countUser(Connection conn) {

int count = 0;

String sql = "{ call user_count(?,?) }";

CallableStatement cstmt = null;

try {

conn.setAutoCommit(false);

cstmt = conn.prepareCall(sql);

cstmt.setString(1, "Liu");

cstmt.registerOutParameter(1, Types.VARCHAR);

cstmt.registerOutParameter(2, Types.INTEGER);

cstmt.execute();

System.out.println(cstmt.getString(1));

count = cstmt.getInt(2);

conn.commit();

} catch (SQLException e) {

e.printStackTrace();

} finally {

if (cstmt != null) {

try {

cstmt.close();

} catch (SQLException e) {

e.printStackTrace();

}

cstmt = null;

}

}

return count;

}

/**

* query_users

*

* @param conn

* @return

*/

public static List queryUsers(Connection conn) {

List users = new ArrayList();

String sql = "{ call query_users(?,?) }";

CallableStatement cstmt = null;

ResultSet rs = null;

try {

conn.setAutoCommit(false);

cstmt = conn.prepareCall(sql);

cstmt.setString(1, "");

cstmt.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR);

cstmt.execute();

rs = (ResultSet) cstmt.getObject(2);

User user = null;

while (rs.next()) {

user = new User();

user.setId(rs.getInt(1));

user.setUsername(rs.getString(2));

user.setEmail(rs.getString(3));

user.setPassword(rs.getString(4));

user.setDescription(rs.getString(5));

user.setAge(rs.getInt(6));

users.add(user);

}

conn.commit();

} catch (SQLException e) {

e.printStackTrace();

} finally {

if (cstmt != null) {

try {

cstmt.close();

} catch (SQLException e) {

e.printStackTrace();

}

cstmt = null;

}

}

return users;

}

}