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

待我称王封你为后i 2022-10-16 14:23 256阅读 0赞

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0OTY1Mzkz_size_16_color_FFFFFF_t_70]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ0OTY1Mzkz_size_16_color_FFFFFF_t_70]: /images/20221014/85f2e783668147fcb039d837c1a85367.png