转载：jdbc连接oracle

 

1.java连接oracle的jar包

链接所使用的jar包 :ojdbc14.jar

2.数据库连接字符串

String driver="oracle.jdbc.OracleDriver";

String url="jdbc:oracle:thin:@192.168.56.10:1521:orcl";

String username="scott";

String password="tiger";

 

测试代码：

 

3.实现过程与函数的调用

1.调用过程

1.过程定义

--统计年薪的过程

create or replace procedure proc_countyearsal(eno in number,esal out number)

as

begin

   select sal*12+nvl(comm,0) into esal from emp where empno=eno;

end;

--调用

declare

   esal number;

begin

   proc_countyearsal(7839,esal);

   dbms_output.put_line(esal);

end;

2.过程调用

@Test

   public void testProcedure01(){

      String driver="oracle.jdbc.OracleDriver";

      String url="jdbc:oracle:thin:@192.168.56.10:1521:orcl";

      String username="scott";

      String password="tiger";

      

      try {

         //注册驱动

         Class.forName(driver);

         Connection con  = DriverManager.getConnection(url, username, password);

         //创建执行者并执行

         CallableStatement callSt = con.prepareCall("{call proc_countyearsal(?,?)}");

         //设置参数

         callSt.setInt(1, 7839);

         callSt.registerOutParameter(2, OracleTypes.NUMBER);

         //执行

         callSt.execute();

         //根据？的位置获取返回值

         System.out.println(callSt.getObject(2));

      } catch (Exception e) {

         e.printStackTrace();

      }

   }

 

2.调用函数

1.函数定义

--统计年薪的函数

create or replace function fun_countyearsal(eno in number)

return number

as

   esal number:=0;

begin

  select sal*12+nvl(comm,0) into esal from emp where empno=eno;

  return esal;

end;

--调用

declare

   esal number;

begin

   esal:=fun_countyearsal(7839);

   dbms_output.put_line(esal);

end;

2.函数调用

@Test

   public void testFunction01(){

      String driver="oracle.jdbc.OracleDriver";

      String url="jdbc:oracle:thin:@192.168.56.10:1521:orcl";

      String username="scott";

      String password="tiger";

      

      try {

         Class.forName(driver);

         Connection con  = DriverManager.getConnection(url, username, password);

         

         CallableStatement callSt = con.prepareCall("{?= call fun_countyearsal(?)}");

         

         callSt.registerOutParameter(1, OracleTypes.NUMBER);

         callSt.setInt(2, 7839);

         

         

         callSt.execute();

         

         System.out.println(callSt.getObject(1));

      } catch (Exception e) {

         e.printStackTrace();

      }

   }

 

4.游标引用的java测试

1.定义过程，并返回引用型游标

--定义过程

create or replace procedure proc_cursor_ref(dno in number,empList out sys_refcursor)

as

begin

  open empList for select * from emp where deptno = dno;

end;

--pl/sql中调用

declare

  mycursor_c sys_refcursor;

  myempc emp%rowtype;

begin

  proc_cursor_ref(20,mycursor_c);

 

  loop

    fetch mycursor_c into myempc;

    exit when mycursor_c%notfound;

    dbms_output.put_line(myempc.empno||','||myempc.ename);

  end loop;

  close mycursor_c;

end;

 

2.java代码调用游标类型的out参数

@Test

   public void testFunction(){

      String driver="oracle.jdbc.OracleDriver";

      String url="jdbc:oracle:thin:@192.168.56.10:1521:orcl";

      String username="scott";

      String password="tiger";

      

      try {

         Class.forName(driver);

         Connection con  = DriverManager.getConnection(url, username, password);

         

         CallableStatement callSt = con.prepareCall("{call proc_cursor_ref (?,?)}");

         

         callSt.setInt(1, 20);

         callSt.registerOutParameter(2, OracleTypes.CURSOR);

         

         callSt.execute();

         

         ResultSet rs = ((OracleCallableStatement)callSt).getCursor(2);

         while(rs.next()){

            System.out.println(rs.getObject(1)+","+rs.getObject(2));

         }

      } catch (Exception e) {

         e.printStackTrace();

      }

   }