java調(diào)用Oracle存儲過程的方法實例

2019-11-26 15:59:38

字體：大中小

供稿：網(wǎng)友

1.測試添加數(shù)據(jù)的procedure

public void testProcedure() {
Connection con = getConnction();

// **1.測試添加數(shù)據(jù)的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.測試刪除數(shù)據(jù)的procedure

復(fù)制代碼代碼如下:

public void testDelPro() {
Connection con = getConnction();

// **2.測試刪除數(shù)據(jù)的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.測試更新數(shù)據(jù)的procedure

復(fù)制代碼代碼如下:

public void testDelPro() {
Connection con = getConnction();

// **3.測試更新數(shù)據(jù)的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.測試查找數(shù)據(jù)的procedure

a)建包體

b)創(chuàng)建查詢的procedure

復(fù)制代碼代碼如下:

create or replace package userspackage as
type users_cursor is ref cursor;
end  userspackage;

復(fù)制代碼代碼如下:

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,name,pword,email from users where id=s_id;
      end if;

end;

c)Java調(diào)用

復(fù)制代碼代碼如下:

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