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, "[e-mail]");
             } 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, "[e-mail]");
             } 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,name,pword,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();
        }
    }