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