在java中调用存储过程和存储函数

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