package com.yc.ycportal.ge.util;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import com.mysql.jdbc.ResultSet;
public class TestOracle {
private Connection conn=null;
private CallableStatement call=null;
private ResultSet rs=null;
public TestOracle() {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
setConn();
}
catch (ClassNotFoundException ex) {
}
}
public void setConn(){
String url="jdbc:oracle:thin:@localhost:1521:hy";
try {
conn = DriverManager.getConnection(url, "system", "manager");
System.out.println("conn ok");
}
catch (SQLException ex) {
}
}
public void closeConn(){
if(conn!=null){
try {
conn.close();
System.out.println("conn close");
}
catch (SQLException ex) {
}
}
}
//调用单独的具有输出参数的过程
public void testShuchu(int salary ){
//实例化cal
try {
//cal = conn.prepareCall("{call hr.shuchu(?,?)}");
call=conn.prepareCall("begin hr.shuchu(?,?); end;");
call.setInt(1,salary);
//注册输出参数
//cal.registerOutParameter(2,java.sql.Types.VARCHAR);
call.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
call.execute();
//获得输出参数
// String str=cal.getString(2);
String str=(String)call.getObject(2);
System.out.println(str);
}
catch (SQLException ex) {
}
finally{
try {
call.close();
}
catch (SQLException ex1) {
}
}
}
// 调用函数
public void testHanshu(int num1,int num2){
try {
call = conn.prepareCall("begin ?:=hr.mypack.myfunc(?,?); end;");
call.setInt(2,num1);
call.setInt(3,num2);
call.registerOutParameter(1,oracle.jdbc.OracleTypes.NUMBER);
call.execute();
int num = call.getInt(1);
System.out.println(num);
}
catch (SQLException ex) {
}
finally{
try {
call.close();
}
catch (SQLException ex1) {
}
}
}
// 调用游标
public void testRs(int salary){
try {
call = conn.prepareCall("begin hr.mypack.shuchu(?,?); end;");
call.setInt(1,salary);
call.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
call.execute();
rs=(ResultSet)call.getObject(2);
if(rs!=null){
while (rs.next()) {
System.out.println(rs.getString(2));
}
}
else{
System.out.println("查无结果");
}
}
catch (SQLException ex) {
}
finally{
try {
rs.close();
call.close();
}
catch (SQLException ex1) {
}
}
}
public static void main(String[] args) {
TestOracle dbUtil1 = new TestOracle();
dbUtil1.testRs(420);
}
}
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import com.mysql.jdbc.ResultSet;
public class TestOracle {
private Connection conn=null;
private CallableStatement call=null;
private ResultSet rs=null;
public TestOracle() {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
setConn();
}
catch (ClassNotFoundException ex) {
}
}
public void setConn(){
String url="jdbc:oracle:thin:@localhost:1521:hy";
try {
conn = DriverManager.getConnection(url, "system", "manager");
System.out.println("conn ok");
}
catch (SQLException ex) {
}
}
public void closeConn(){
if(conn!=null){
try {
conn.close();
System.out.println("conn close");
}
catch (SQLException ex) {
}
}
}
//调用单独的具有输出参数的过程
public void testShuchu(int salary ){
//实例化cal
try {
//cal = conn.prepareCall("{call hr.shuchu(?,?)}");
call=conn.prepareCall("begin hr.shuchu(?,?); end;");
call.setInt(1,salary);
//注册输出参数
//cal.registerOutParameter(2,java.sql.Types.VARCHAR);
call.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
call.execute();
//获得输出参数
// String str=cal.getString(2);
String str=(String)call.getObject(2);
System.out.println(str);
}
catch (SQLException ex) {
}
finally{
try {
call.close();
}
catch (SQLException ex1) {
}
}
}
// 调用函数
public void testHanshu(int num1,int num2){
try {
call = conn.prepareCall("begin ?:=hr.mypack.myfunc(?,?); end;");
call.setInt(2,num1);
call.setInt(3,num2);
call.registerOutParameter(1,oracle.jdbc.OracleTypes.NUMBER);
call.execute();
int num = call.getInt(1);
System.out.println(num);
}
catch (SQLException ex) {
}
finally{
try {
call.close();
}
catch (SQLException ex1) {
}
}
}
// 调用游标
public void testRs(int salary){
try {
call = conn.prepareCall("begin hr.mypack.shuchu(?,?); end;");
call.setInt(1,salary);
call.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
call.execute();
rs=(ResultSet)call.getObject(2);
if(rs!=null){
while (rs.next()) {
System.out.println(rs.getString(2));
}
}
else{
System.out.println("查无结果");
}
}
catch (SQLException ex) {
}
finally{
try {
rs.close();
call.close();
}
catch (SQLException ex1) {
}
}
}
public static void main(String[] args) {
TestOracle dbUtil1 = new TestOracle();
dbUtil1.testRs(420);
}
}