首先在idea中新建一个java项目,目录层级如下图所示:
连接oracle数据库的jar包在oracle的安装目录中找即可(附上我的路径G:\app\41919\product\11.2.0\dbhome_1\jdbc\lib),随便一个jar包即可,我的是ojdbc6.jar。
然后把jar包导入即可(具体操作步骤百度即可)
在scott用户中新建一个存储过程(查询某个员工姓名 月薪和职位):
create or replace procedure queryempinform(eno in number,
pename out varchar2,
psal out number,
pjob out varchar2)
as
begin
--得到该员工的姓名 月薪和职位
select ename,sal,job into pename,psal,pjob from emp where empno = eno;
end;
/
准备工作到此完毕,下面是java源代码:
//这个是JDBCUtils.java文件中的代码(工具类)
package demo.utils;
import java.sql.*;
/**
* Created by 41919 on 2017/10/15.
*/
public class JDBCUtils {
private static String driver = "oracle.jdbc.OracleDriver";
private static String url = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
private static String user = "scott";
private static String password = "1";
//注册数据库的驱动
static{
try {
//反射
Class.forName(driver);
//DriverManager.registerDriver(driver);
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
}
}
//获取数据库连接
public static Connection getConnection(){
try {
return DriverManager.getConnection(url,user,password);
} catch (SQLException e) {
e.printStackTrace();
}
return null;
}
//释放数据库的资源
public static void release(Connection conn, Statement st, ResultSet rs){
if(rs != null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}finally {
rs = null;
}
}
if(st != null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}finally {
st = null;
}
}
if(conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}finally {
conn = null;
}
}
}
}
//这个是TestProcedure.java文件中的代码
package demo.oracle;
import demo.utils.JDBCUtils;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.oracore.OracleType;
import org.junit.Test;
import java.sql.CallableStatement;
import java.sql.Connection;
/**
* Created by 41919 on 2017/10/15.
*/
public class TestProcedure {
@Test
public void testProcedure(){
//{call <procedure-name>[(<arg1>,<arg2>,...)]}
String sql = "{call queryempinform(?,?,?,?)}";
Connection conn = null;
CallableStatement call = null;
try{
//得到一个连接
conn = JDBCUtils.getConnection();
//通过连接创建出statment
call = conn.prepareCall(sql);
//对于in参数,赋值,取出员工编号为7839的员工信息
call.setInt(1,7839);
//对于out参数,申明
call.registerOutParameter(2, OracleTypes.VARCHAR);
call.registerOutParameter(3, OracleTypes.NUMBER);
call.registerOutParameter(4, OracleTypes.VARCHAR);
//执行调用
call.execute();
//取出结果
String name = call.getString(2);
double sal = call.getDouble(3);
String job = call.getString(4);
System.out.println(name+"\t"+sal+"\t"+job);
} catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.release(conn,call,null);
}
}
}
运行TestProcedure.java文件,即可访问oracle数据库的存储过程,下面贴上我的运行结果图。
调用存储函数(新建存储函数)
create or replace function query(eno in number)
return number
as
--定义变量保存员工的薪水和奖金
psal emp.sal%type;
pcomm emp.comm%type;
begin
--得到该员工的月薪和奖金
select sal,comm into psal,pcomm from emp where empno = eno;
--直接返回年收入
return psal*12+nvl(pcomm,0);
end;
/
在oracle目录下新建TestFunction.java文件
package demo.oracle;
import demo.utils.JDBCUtils;
import oracle.jdbc.OracleTypes;
import org.junit.Test;
import java.sql.CallableStatement;
import java.sql.Connection;
/**
* Created by 41919 on 2017/10/15.
*/
public class TestFunction {
@Test
public void testFunction(){
//{?= call <procedure-name>[(<arg1>,<arg2>,...)]}
String sql = "{?=call query(?)}";
Connection conn = null;
CallableStatement call = null;
try{
//得到数据库连接
conn = JDBCUtils.getConnection();
//基于连接创建statement
call = conn.prepareCall(sql);
//对于输出参数,申明
call.registerOutParameter(1, OracleTypes.NUMBER);
//对于输入参数,赋值
call.setInt(2,7839);
//执行调用
call.execute();
//取出年收入的结果
double income = call.getDouble(1);
System.out.println("该员工的年收入是:"+income);
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.release(conn,call,null);
}
}
}
运行TestFunction.java文件,即可访问oracle数据库的存储函数,下面贴上我的运行结果图。
接下来,访问带有光标的存储过程,首先新建一个包,
create or replace package mypackage is
--申明一个光标的引用
type empcursor is ref cursor;
procedure mysal(dno in number,empList out empcursor);
end mypackage;
包体如下:
create or replace package body mypackage is
procedure mysal(dno in number, empList out empcursor) as
begin
--打开光标
open empList for
--查询某个部门的员工信息
select t.* from emp t where t.deptno = dno;
end mysal;
end mypackage;
在oracle目录下新建TestCursor.java文件:
package demo.oracle;
import demo.utils.JDBCUtils;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import org.junit.Test;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* Created by 41919 on 2017/10/16.
*/
public class TestCursor {
@Test
public void testCursor(){
//{call <procedure-name>[(<arg1>,<arg2>,...)]}
String sql = "{call mypackage.mysal(?,?)}";
Connection conn = null;
CallableStatement call = null;
ResultSet rs = null;
try {
//获取数据库的连接
conn = JDBCUtils.getConnection();
//创建statment
call = conn.prepareCall(sql);
//对于in参数,赋值
call.setInt(1,20);
//对于out参数,申明
call.registerOutParameter(2, OracleTypes.CURSOR);
//执行调用
call.execute();
//取出该部门中所有员工的信息
rs = ((OracleCallableStatement)call).getCursor(2);
while (rs.next()){
//取出该员工的工号,姓名,薪水,职位
int empno = rs.getInt("empno");
String name = rs.getString("ename");
double sal = rs.getDouble("sal");
String job = rs.getString("job");
System.out.println(empno+"\t"+name+"\t"+sal+"\t"+job);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.release(conn,call,rs);
}
}
}
运行TestCursor.java文件,即可访问oracle数据库中mypackage下的mysal存储过程,下面贴上我的运行结果图。