java调用Oracle存储过程的方法实例

 1.测试添加数据的procedure

复制代码 代码如下:
public void testProcedure() {
        Connection con = getConnction();

        // **1.测试添加数据的procedure
          String procedure = "{call users_insert_proc(?,?,?) }";

        CallableStatement cs = null;
        try {
             cs = con.prepareCall(procedure);
             cs.setInt(1,123450);
             cs.setString(2,"xxiaox");
             cs.setString(3,"Ww342864");
             cs.setString(4,"742621646@qq.com");
             } catch (SQLException e) {
              e.printStackTrace();
        }
        try {
             cs.executeUpdate();
        } catch (SQLException e) {
               e.printStackTrace();
        }
    }

2.测试删除数据的procedure

复制代码 代码如下:
public void testDelPro() {
        Connection con = getConnction();

        // **2.测试删除数据的procedure
         String procedure = "{call delete_usersbyid_proc(?) }";

        CallableStatement cs = null;
        try {
             cs = con.prepareCall(procedure);
                      cs.setInt(1,123450);
             } catch (SQLException e) {
              e.printStackTrace();
        }
        try {
             cs.executeUpdate();
        } catch (SQLException e) {
               e.printStackTrace();
        }
    }

3.测试更新数据的procedure

复制代码 代码如下:
public void testDelPro() {
        Connection con = getConnction();

        // **3.测试更新数据的procedure
        String procedure = "{call users_updatebyId_proc(?,?) }";

        CallableStatement cs = null;
        try {
             cs = con.prepareCall(procedure);
         cs.setInt(1,101);          cs.setString(2,"小第三方的浩");          cs.setString(3,"asdf342864");         cs.setString(4,"742621646@qq.com");
             } catch (SQLException e) {
              e.printStackTrace();
        }
        try {
             cs.executeUpdate();
        } catch (SQLException e) {
               e.printStackTrace();
        }
    }

4.测试查找数据的procedure

      a)建包体

      b)创建查询的procedure

复制代码 代码如下:
create or replace package userspackage as
type users_cursor is ref cursor;
end  userspackage;

复制代码 代码如下:
create or replace procedure users_packageAll(
s_id in number,u_cursor out userspackage.users_cursor) is
begin
   if s_id = 0 then
       open u_cursor for select id,name,pword,email  from users;
      else
       open u_cursor for select id,email  from users where id=s_id;
      end if;

  end;

c)Java调用

复制代码 代码如下:
public void testDelPro() {
        Connection con = getConnction();

        // 返回查询procedure
       String procedure = "{call users_packageAll(?,?) }";

        CallableStatement cs = null;
        try {
             cs = con.prepareCall(procedure);
                     cs.setInt(1,0);
     cs.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);

             } catch (SQLException e) {
              e.printStackTrace();
        }
        try {
             cs.execute();
              ResultSet rs = (ResultSet)cs.getObject(2);
            while (rs.next()) {
              System.out.println(rs.getInt(1) + " " + rs.getString(2));
            }
        } catch (SQLException e) {
               e.printStackTrace();
        }
    }

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!