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();        }    }