首页 > 代码库 > java代码调用oracle存储过程
java代码调用oracle存储过程
一、简介
与调用mysql存储过程类型过程都是如下
1、创建连接 Connection conn = DriverManager.getConnection(url, user, password);
2、创建CallableStatement CallableStatement statement = conn.prepareCall(sql);
3、设置参数
statement.setInt(1, id);
statement.registerOutParameter(2, Types.VARCHAR);
statement.registerOutParameter(3, Types.INTEGER);
statement.registerOutParameter(4, Types.VARCHAR);
4、执行
statement.execute(); 或 statement.executeUpdate();
5、获取返回
int age = statement.getInt(3);
只是oracle存储过程有的结果集是以游标的方式返回，此时我们需要调用ResultSet rs = (ResultSet) statement.getObject(1);方法回去结果集
二、代码
以下存储过程表结构如下：
DROP TABLE person ;CREATE TABLE person (id NUMBER(11) NOT NULL ,username VARCHAR2(255 ) NULL ,age NUMBER(11) NULL ,password VARCHAR2(255) NULL ,PRIMARY KEY (id))
1、查询所有记录
存储过程代码如下：
create or replace procedure pro_person_findall( p_cursor out pkg_const.r_cursor)isbegin open p_cursor for select * from person; exception when others then DBMS_OUTPUT.PUT_LINE(‘获取信息发生错误‘);end pro_person_findall;
调用代码如下
public static void findAll() { String driver = "oracle.jdbc.driver.OracleDriver"; String url = "jdbc:oracle:thin:@127.0.0.1:1521:wuxx"; String user = "wuxx"; String password = "wuxx"; try { Class.forName(driver); Connection conn = DriverManager.getConnection(url, user, password); String sql = "{call pro_person_findall2(?)}"; CallableStatement statement = conn.prepareCall(sql); statement.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR); statement.execute(); ResultSet rs = (ResultSet) statement.getObject(1); ResultSetMetaData rmd = rs.getMetaData(); System.out.print(rmd.getColumnName(1) + " "); System.out.print(rmd.getColumnName(2) + " "); System.out.print(rmd.getColumnName(3) + " "); System.out.print(rmd.getColumnName(4) + "\n"); while (rs.next()) { System.out.print(rs.getInt("id") + " "); System.out.print(rs.getString("username") + " "); System.out.print(rs.getInt("age") + " "); System.out.print(rs.getString("password") + " \n"); } } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } }
2、查询一条记录
存储过程如下
CREATE OR REPLACE PROCEDURE PRO_PERSON_FINDBYID( v_id IN NUMBER, v_username OUT VARCHAR2, v_age OUT NUMBER, v_password OUT VARCHAR2, p_count out number)AS BEGIN SELECT username, age, password INTO v_username, v_age, v_password from person where id = v_id; p_count := 1; exception when others then p_count := 0;END;
调用代码如下：
public static void find(Integer id) { String driver = "oracle.jdbc.driver.OracleDriver"; String url = "jdbc:oracle:thin:@127.0.0.1:1521:wuxx"; String user = "wuxx"; String password = "wuxx"; try { Class.forName(driver); Connection conn = DriverManager.getConnection(url, user, password); String sql = "{call PRO_PERSON_FINDBYID(?,?,?,?,?)}"; CallableStatement statement = conn.prepareCall(sql); BigDecimal rid = new BigDecimal(id); statement.setInt(1, id); statement.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR); statement.registerOutParameter(3, oracle.jdbc.OracleTypes.NUMBER); ; // statement.registerOutParameter(4, oracle.jdbc.OracleTypes.VARCHAR); statement.registerOutParameter(5, oracle.jdbc.OracleTypes.NUMBER); statement.execute(); int flag = statement.getInt(5); if (flag != 0) System.out.println(statement.getString(2) + " " + statement.getInt(3) + " " + statement.getString(4)); else System.out.println("data not found!"); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } }
3、增加记录
存储过程代码如下：
create or replace procedure pro_person_insert( p_id number, p_username varchar2, p_age number, p_password varchar2, p_count out number)isbegin insert into person (id, username, age, password) values(p_id, p_username, p_age, p_password); p_count := SQL%ROWCOUNT; -- SQL%ROWCOUNT为 隐士游标的属性 commit; exception when others then p_count := 0;end pro_person_insert;
调用代码如下：
public static void add(Integer id, String username, int age, String u_password) { String driver = "oracle.jdbc.driver.OracleDriver"; String url = "jdbc:oracle:thin:@127.0.0.1:1521:wuxx"; String user = "wuxx"; String password = "wuxx"; try { Class.forName(driver); Connection conn = DriverManager.getConnection(url, user, password); String sql = "{call pro_person_insert(?,?,?,?,?)}"; CallableStatement statement = conn.prepareCall(sql); statement.setInt(1, id); statement.setString(2, username); statement.setInt(3, id); statement.setString(4, u_password); statement.registerOutParameter(5, oracle.jdbc.OracleTypes.NUMBER);// 增加记录是否成功的标记,1 成功，0失败 statement.execute(); System.out.println(statement.getInt(5)); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } }
4、更新记录
存储过程代码如下：
create or replace procedure pro_person_update( p_id number, p_age number, p_password varchar2, p_count out number)isbegin update person set age = p_age, password = p_password where id = p_id; p_count := SQL%ROWCOUNT; commit; exception when no_data_found then p_count := 0; when others then p_count := -1;end pro_person_update;
调用代码如下：
public static void update(Integer id, int age, String u_password) { String driver = "oracle.jdbc.driver.OracleDriver"; String url = "jdbc:oracle:thin:@127.0.0.1:1521:wuxx"; String user = "wuxx"; String password = "wuxx"; try { Class.forName(driver); Connection conn = DriverManager.getConnection(url, user, password); String sql = "{call pro_person_update(?,?,?,?)}"; CallableStatement statement = conn.prepareCall(sql); statement.setInt(1, id); statement.setInt(2, age); statement.setString(3, u_password); statement.registerOutParameter(4, oracle.jdbc.OracleTypes.NUMBER);// 增加记录是否成功的标记 statement.execute(); System.out.println(statement.getInt(4)); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } }
5、删除记录
存储过程代码如下：
create or replace procedure pro_person_delete( p_id number, p_count out number)isbegin delete from person where id = p_id; p_count := SQL%ROWCOUNT; commit; exception when no_data_found then p_count := 0; when others then p_count := -1;end pro_person_delete;
调用代码如下：
public static void delete(Integer id) { String driver = "oracle.jdbc.driver.OracleDriver"; String url = "jdbc:oracle:thin:@127.0.0.1:1521:wuxx"; String user = "wuxx"; String password = "wuxx"; try { Class.forName(driver); Connection conn = DriverManager.getConnection(url, user, password); String sql = "{call pro_person_delete(?,?)}"; CallableStatement statement = conn.prepareCall(sql); statement.setInt(1, id); statement.registerOutParameter(2, oracle.jdbc.OracleTypes.NUMBER);// 增加记录是否成功的标记 statement.execute(); System.out.println(statement.getInt(2)); } catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); } }
java代码调用oracle存储过程
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。