Oracle常见操作【使用Java操作存储过程和存储函数】

使用Java操作存储过程和存储函数

一：操作Oracle数据库的常用字符串

# Properties file with JDBC-related settings.
# Applied by PropertyPlaceholderConfigurer from "dataAccessContext-local.xml".
# Targeted at system administrators, to avoid touching the context XML files.

jdbc.driverClassName=oracle.jdbc.driver.OracleDriver
jdbc.url=jdbc:oracle:thin:@localhost:1521:orclstu
jdbc.username=system
jdbc.password=oracle
jdbc.readOnly=false

二：本地的Oracle数据库驱动包坐标

<!--Oracle的驱动包-->
<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc6</artifactId>
    <version>11.2.0.4.0-atlassian-hosted</version>
</dependency>

三：具体实现

package com.dream;

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

import java.sql.*;

/**
 * @author ZhangJun
 * @date 2020-04-23
 * @description Oracle测试存储过程和存储函数
 */
public class OracleDemo {

    /**
     * 测试Oracle的连接
     */
    @Test
    public void javaCallOracle() throws Exception {
        //1.加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //2.获取Connection连接
        Connection connection = DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:orclstu",
                "dream",
                "dream");
        //3.得到预编译的Statement对象
        PreparedStatement statement = connection.prepareStatement("select * from emp where empno=?");
        //4.给参数赋值
        statement.setObject(1, 7788);
        //5.执行数据库查询操作
        ResultSet resultSet = statement.executeQuery();
        //6.输出结果
        while (resultSet.next()) {
            System.out.println(resultSet.getString("ename"));
        }
        //7.关闭连接
        resultSet.close();
        statement.close();
        connection.close();
    }

    /**
     * 测试Oracle的存储过程
     *  {?= call <procedure-name>[(<arg1>,<arg2>, ...)]} 调用存储函数使用
     *  {call <procedure-name>[(<arg1>,<arg2>, ...)]}  调用存储过程使用
     */
    @Test
    public void javaCallProcedure() throws Exception {
        //1.加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //2.获取Connection连接
        Connection connection = DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:orclstu",
                "dream",
                "dream");
        //3.得到预编译的Statement对象
        CallableStatement statement = connection.prepareCall("{call p_yearsal(?,?)}");
        //4.给参数赋值
        statement.setObject(1, 7788);
        statement.registerOutParameter(2, OracleTypes.NUMBER);
        //5.执行数据库查询操作
        statement.execute();
        //6.输出结果
        System.out.println("年薪："+statement.getObject(2));
        //7.关闭连接
        statement.close();
        connection.close();
    }

    /**
     * 测试Oracle的存储函数
     *  {?= call <procedure-name>[(<arg1>,<arg2>, ...)]} 调用存储函数使用
     *  {call <procedure-name>[(<arg1>,<arg2>, ...)]}  调用存储过程使用
     */
    @Test
    public void javaCallFunction() throws Exception {
        //1.加载数据库驱动
        Class.forName("oracle.jdbc.driver.OracleDriver");
        //2.获取Connection连接
        Connection connection = DriverManager.getConnection(
                "jdbc:oracle:thin:@localhost:1521:orclstu",
                "dream",
                "dream");
        //3.得到预编译的Statement对象
        //操作存储过程和存储函数时，必须使用CallableStatement这个对象
        CallableStatement statement = connection.prepareCall("{?=call f_yearsal(?)}");
        //4.给参数赋值
        statement.setObject(2, 7788);
        //设置out返回对象
        statement.registerOutParameter(1, OracleTypes.NUMBER);
        //5.执行数据库查询操作
        statement.execute();
        //6.输出结果
        System.out.println("年薪："+statement.getObject(1));
        //7.关闭连接
        statement.close();
        connection.close();
    }
}