一 . 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 视图,索引