Java调用Oracle存储函数与存储过程

Oracle连接四要素(个人写法，用户与用户密码因人而异)
driverClassName=oracle.jdbc.OracleDriver
jdbc_url=jdbc:oracle:thin:@localhost:1521:ORCL
jdbc_username=scott
jdbc_password=scott

调用存储过程的关键代码：CallableStatement psta = con.prepareCall("{call proc_name(?, …)}");

调用存储函数的关键代码：CallableStatement psta = con.prepareCall("{? = call func_name(?, …)}");

pom依赖：Oracle 10g对应的依赖为ojdbc14.jar，而Oracle 11g对应的依赖为ojdbc6.jar

<dependencies>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>com.oracle.database.jdbc</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0.4</version>
            <scope>runtime</scope>
        </dependency>
    </dependencies>

主程序代码：

package com.wanakiko.oracle;

import oracle.jdbc.OracleTypes;
import org.junit.Test;

import java.sql.*;

/**
 * @author WanAkiko
 * @create-time 2021-05-30
 */
public class JavaCallsProcAndFunc {

    private static final String url = "jdbc:oracle:thin:@localhost:1521:ORCL";
    private static final String user = "scott";
    private static final String password = "scott";

    @Test
    public void javaCallsFunction() throws Exception {
        // 加载数据库驱动
        Class.forName("oracle.jdbc.OracleDriver");
        // 获取Connection连接
        Connection con = DriverManager.getConnection(url, user, password);
        // 准备调用自定义存储函数
        CallableStatement psta = con.prepareCall("{? = call func_calc_salary(?)}");
        psta.registerOutParameter(1, OracleTypes.NUMBER);
        psta.setObject(2, 7788);
        // 执行自定义存储函数
        psta.execute();
        // 接收并输出存储函数的RETURN结果
        System.out.println(psta.getObject(1));
        psta.close();
        con.close();
    }

    @Test
    public void javaCallsProcedure() throws Exception {
        // 加载数据库驱动
        Class.forName("oracle.jdbc.OracleDriver");
        // 获取Connection连接
        Connection con = DriverManager.getConnection(url, user, password);
        // 准备调用自定义存储过程
        CallableStatement psta = con.prepareCall("{call proc_calc_sal(?, ?)}");
        psta.setObject(1, 7788);
        psta.registerOutParameter(2, OracleTypes.NUMBER);
        // 执行自定义存储过程
        psta.execute();
        // 输出OUT参数附带的结果
        System.out.println(psta.getObject(2));
        psta.close();
        con.close();
    }

    @Test
    public void testEnvironmentSetup() {
        try {
            // 加载数据库驱动
            Class.forName("oracle.jdbc.OracleDriver");
            // 获取Connection连接
            final String url = "jdbc:oracle:thin:@localhost:1521:ORCL";
            final String user = "scott";
            final String password = "scott";
            Connection con = DriverManager.getConnection(url, user, password);
            // 获取预处理语句并为查询参数赋值
            PreparedStatement psta = con.prepareStatement("SELECT * FROM emp WHERE empno = ?");
            psta.setObject(1, 7788);
            // 执行查询操作
            ResultSet rs = psta.executeQuery();
            while (rs.next()) {
                System.out.println(rs.getString("ename"));
            }
            rs.close();
            psta.close();
            con.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    
}