在oracle下，使用java调用oracle的存储过程：
1.先建一个存储过程：
create or replace procedure p(v_a in number, v_b number,v_ret out number, v_temp in out number)
（ 注释：v_a in number in是指明输入参数 默认为是输入参数
v_ret out number 是指明输出参数
v_temp in out number 即是输出又是输入参数）
is begin
if(v_a > v_b)
v_ret := v_a;
else
v_ret := v_b;
endif;
v_temp : = v_temp + 1;
end;
2 java 程序调用：
import java.sql.*;
public class TestProc {
/**
* @param args
*/
public static void main(String[] args) throws Exception {
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@192.168.0.1:1521:SXT", "scott", "tiger");
CallableStatement cstmt = conn.prepareCall("{call p(?, ?, ?, ?)}");
cstmt.registerOutParameter(3, Types.INTEGER); //注册输出的参数和类型 cstmt.registerOutParameter(4, Types.INTEGER);
cstmt.setInt(1, 3); // 给存储过程输入参数的设值
cstmt.setInt(2, 4);
cstmt.setInt(4, 5);
cstmt.execute();
System.out.println(cstmt.getInt(3)); // 输出存储过程中要输出的参数
System.out.println(cstmt.getInt(4));
cstmt.close();
conn.close();
}
}
3.使用Transcation的例子
import java.sql.*;
public class TestTransaction {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:SXT", "scott", "tiger");
conn.setAutoCommit(false); //conn默认自动提交， 改为非自动提交
stmt = conn.createStatement();
stmt.addBatch("insert into dept2 values (51, '500', 'haha')");
stmt.addBatch("insert into dept2 values (52, '500', 'haha')");
stmt.addBatch("insert into dept2 values (53, '500', 'haha')");
stmt.executeBatch();
conn.commit(); // 执行提交
conn.setAutoCommit(true); // 还原默认自动提交
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch(SQLException e) {
e.printStackTrace();
try {
if(conn != null)
{
conn.rollback(); // 回滚操作
conn.setAutoCommit(true); // 还原自动提交
}
} catch (SQLException e1) {
e1.printStackTrace();
}
}finally {
try {
if(stmt != null)
stmt.close();
if(conn != null)
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
4.滚动结果集
import java.sql.*;
// 可以滚动的结果集
public class TestScroll {
public static void main(String args[]) {
try {
new oracle.jdbc.driver.OracleDriver();
String url = "jdbc:oracle:thin:@192.168.0.1:1521:SXT";
Connection conn = DriverManager
.getConnection(url, "scott", "tiger");
Statement stmt = conn.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt
.executeQuery("select * from emp order by sal");
rs.next();
System.out.println(rs.getInt(1));
rs.last();
System.out.println(rs.getString(1));
System.out.println(rs.isLast());
System.out.println(rs.isAfterLast());
System.out.println(rs.getRow());
rs.previous();
System.out.println(rs.getString(1));
rs.absolute(6);
System.out.println(rs.getString(1));
rs.close();
stmt.close();
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
5,可以更新的结果集 oracle class12.jar 不支持下面的方式，其他数据库请自己尝试。当然不不推荐使用
import java.sql.*;
//可更新的滚动结果集
public class TestUpdataRs {
public static void main(String args[]){
try{
new oracle.jdbc.driver.OracleDriver();
String url="jdbc:oracle:thin:@192.168.0.1:1521:SXT";
Connection conn=DriverManager.getConnection(url,"scott","tiger");
Statement stmt=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
ResultSet rs=stmt.executeQuery("select * from emp2");
rs.next();
//更新一行数据
rs.updateString("ename","AAAA");
rs.updateRow();
//插入新行
rs.moveToInsertRow();
rs.updateInt(1, 9999);
rs.updateString("ename","AAAA");
rs.updateInt("mgr", 7839);
rs.updateDouble("sal", 99.99);
rs.insertRow();
//将光标移动到新建的行
rs.moveToCurrentRow();
//删除行
rs.absolute(5);
rs.deleteRow();
//取消更新
//rs.cancelRowUpdates();
}catch(SQLException e){
e.printStackTrace();
}
}
}
6.Datasource是用来替换DriverManager的，主要支持连接池和分布式。
7.RowSet继承ResultSet；支持断开的结果集（ResultSet不支持）；支持javaBean标准。