Oracle 数据库 JDBC调用存储过程和存储函数

一 . Oracle 数据库 JDBC调用存储过程和存储函数

ojdbc：在中央仓库没有
需要手动安装
mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc14
-Dversion=10.2.0.4.0 -Dpackaging=jar
-Dfile=E:\oracle\product\10.2.0\db_1\jdbc\lib\ojdbc14-10.2.0.4.0.jar
–>

com.oracle
ojdbc14
10.2.0.2.0

2.package com.itheima;

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

import java.sql.*;

public class TestJdbc {

String driver = "oracle.jdbc.driver.OracleDriver";
String url = "jdbc:oracle:thin:@192.168.174.129:1521:orcl";
String username = "scott";
String password = "tiger";
Connection conn;
PreparedStatement pst;
CallableStatement cst;
ResultSet rs;
/**
 * 初始化：加载驱动，创建连接
 */
@Before
public void init() throws Exception {
    Class.forName(driver);
    conn =  DriverManager.getConnection(url,username,password);
}

/**
 * 测试访问存储函数
 * create or replace function getYearSalFun(eno in number) return number
 * {?= call <procedure-name>[(<arg1>,<arg2>, ...)]}
 */
@Test
public void testFunction() throws  Exception{
    //1. sql
    String sql = "{?= call getYearSalFun(?)}";
    //2. 创建Statement对象
    cst = conn.prepareCall(sql);
    // 3.1 设置输入占位符
    cst.setInt(2, 7788);
    // 3.2 设置输出占位符
    cst.registerOutParameter(1, OracleTypes.NUMBER);
    //4. 执行sql语句
    cst.executeUpdate();
    //5. 处理结果集
    int yearsal = cst.getInt(1);
    System.out.println("年薪："+yearsal);
}

/**
 * 测试存储过程
 * create or replace procedure getEmpsByDeptno(dno in number ,emps out sys_refcursor)
 *{call <procedure-name>[(<arg1>,<arg2>, ...)]}
 */
@Test
public void testProcedureOutCursor() throws  Exception{
    //1. sql
    String sql = "{call getEmpsByDeptno(?,?)}";
    //2. 创建Statement对象
    cst = conn.prepareCall(sql);
    // 3.1 设置输入占位符
    cst.setInt(1, 20);
    // 3.2 设置输出占位符
    cst.registerOutParameter(2, OracleTypes.CURSOR);
    //4. 执行sql语句
    cst.executeUpdate();
    //5. 处理结果集
    Object o = cst.getObject(2);
    rs = (ResultSet) o;
    while(rs.next()){
        System.out.println(rs.getInt("empno")+ rs.getString("ename"));
    }
}

/**
 * 测试存储过程
 * create or replace procedure getYearSal(eno in number ,yearsal out number)
 *{call <procedure-name>[(<arg1>,<arg2>, ...)]}
 */
@Test
public void testProcedure() throws  Exception{
    //1. sql
    String sql = "{call getYearSal(?,?)}";
    //2. 创建Statement对象
    cst = conn.prepareCall(sql);
    // 3.1 设置输入占位符
    cst.setInt(1, 7788);
    // 3.2 设置输出占位符
    cst.registerOutParameter(2, OracleTypes.NUMBER);
    //4. 执行sql语句
    cst.executeUpdate();
    //5. 处理结果集
    int yearsal = cst.getInt(2);
    System.out.println("年薪："+yearsal);
}

/**
 * 查询员工表中所有的数据
 */
@Test
public void test() throws  Exception{
    //1. sql
    String sql = "select * from emp";
    //2. 创建Statement对象
    pst = conn.prepareStatement(sql);
    // 3. 设置占位符

    //4. 执行sql语句
    rs = pst.executeQuery();
    //5. 处理结果集
    while(rs.next()){
        System.out.println(rs.getInt("empno")+ rs.getString("ename"));
    }
}

/**
 * 关闭资源
 */
@After
public void close() throws SQLException {
    if(rs != null){
        rs.close();
    }
    if(pst != null){
        pst.close();
    }
    if(cst != null){
        cst.close();
    }
    if(conn != null){
        conn.close();
    }
}

}

Oracle 触发器
Oracle 存储函数
Oracle 存储过程
Oracle 游标
Oracle plsql基本语句
Oracle 视图,索引