1.创建存储过程
create or replace procedure query(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;
2.创建存储函数
create or replace function nianshouru(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;
3.创建java工程，并且导入连接数据库的jar包
image.png
4.创建JDBCUtils工具类
package com.xdl.utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCUtils {
private static String driver="oracle.jdbc.OracleDriver";
private static String url="jdbc:oracle:thin:@localhost:1521:mldn";
private static String username="scott";
private static String password ="tiger";
//注册驱动
static {
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
throw new ExceptionInInitializerError(e);
}
}
public static Connection getConnecction(){
try {
return DriverManager.getConnection(url, username, password);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return null;
}
//释放数据库
public static void release(Connection conn,Statement st,ResultSet rs) {
if(rs!=null) {
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
rs=null;
}
}
if(st!=null) {
try {
st.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
st=null;
}
}
if(conn!=null) {
try {
conn.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
conn=null;
}
}
}
}
5.创建调用存储过程的测试类
package com.xdl.oracle;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import org.junit.Test;
import com.xdl.utils.JDBCUtils;
import oracle.jdbc.OracleTypes;
/**
* 调用存储过程
* @author Administrator
*
*/
public class Procedure {
@Test
public void testProcedure(){
String sql="{call query(?,?,?,?)}" ;
Connection conn=null;
Statement st=null;
ResultSet rs=null;
CallableStatement call=null;
try {
//得到一个连接
conn=JDBCUtils.getConnecction();
//通过连接创建出statement
call=conn.prepareCall(sql);
//对于输入参数需要赋值
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, st, rs);
}
}
}
6.创建调用存储函数的测试类Function
package com.xdl.oracle;
import java.sql.CallableStatement;
import java.sql.Connection;
import org.junit.Test;
import com.xdl.utils.JDBCUtils;
import oracle.jdbc.OracleTypes;
/**
* 调用存储函数
* @author Administrator
*
*/
public class Function {
@Test
public void testFunction() {
String sql="{?=call nianshouru(?)}";//存储函数的名字
Connection conn=null;
CallableStatement call=null;
try {
//得到数据库连接
conn=JDBCUtils.getConnecction();
//基于连接获取到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);
}
}
}
