欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-12-20 13:52:16
 1.测试添加数据的procedure 复制代码 代码如下:public void testprocedure() {   &n...

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

上一篇:

下一篇: