Java 调用Oracle存储过程

Java 调用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;

 

--------------------------------------------------------------------------------------------------------------------------------------------------

调用存储过程案例代码

public class User { private int id; private String username; private String password; private String email; private int age; private String description; // setter and getter ...... } 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 CallProcedure { 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; } } } 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; } } } 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; } public static List<User> queryUsers(Connection conn) { List<User> users = new ArrayList<User>(); 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; } }