编写存储过程并使用java代码调用

--编写存储过程向数据库中的book表中插入一条数据

create or replace procedure sp_pro7
(spBookId in number,spBookName in varchar2,spPublish in varchar2) is
begin
insert into book values(spBookId,spBookName,spPublish);
end;
--使用java代码调用存储过程

 

package test;
import java.sql.*;
public class TestOracle{
 //定义Connection对象
 public static Connection conn = null;
 //定义CallableStatement对象
 public static CallableStatement cs = null;
 public static void main(String[] args){
   try{
   //1、加载Oracle驱动
   Class.forName("oracle.jdbc.driver.OracleDriver");
   //2、取得数据库连接
   conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL","scott","tiger");
   //3、实例化CallableStatement对象
   cs = conn.prepareCall("{call sp_pro7(?,?,?)}");
   //4、给？赋值
   cs.setInt(1,10);
   cs.setString(2,"笑傲江湖");
   cs.setString(3,"人民出版社");
   //5、执行
   cs.execute();
  }catch(Exception e){
   e.printStackTrace();
  }finally{
   try{
    if(cs!=null){
     cs.close();
     cs = null;
    }
   }catch(Exception e1){
    e1.printStackTrace();
   }finally{
    try{
     if(conn!=null){
      conn.close();
      conn = null;
     }
    }catch(Exception e2){
     e2.printStackTrace();
    }
   }
  }
 }
}

--编写有输入和输出参数的过程

--编写一个有输入和输出的存储过程
create or replace procedure sp_pro8
(spNo in number,spName out varchar2) is
begin
select ename into spName from emp where empno=spNo;
end;

--使用java代码调用该存储过程并打印输出得到的结果集

package test;
import java.sql.*;
public class TestOracle{
 //定义Connection对象
 public static Connection conn = null;
 //定义CallableStatement对象
 public static CallableStatement cs = null;
 public static void main(String[] args){
   try{
   //1、加载Oracle驱动
   Class.forName("oracle.jdbc.driver.OracleDriver");
   //2、取得数据库连接
   conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL","scott","tiger");
   //3、实例化CallableStatement对象
   cs = conn.prepareCall("{call sp_pro8(?,?)}");
   //4、给？赋值
   cs.setInt(1,7788);
   //5、给第二个？赋值
   cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
   //6、执行
   cs.execute();
   //7、获取返回的值，注意返回值是第二个，此处必须写2
   String name = cs.getString(2);
   //8、打印输出取得的值
   System.out.println("7788号员工的姓名是:" + name);
  }catch(Exception e){
   e.printStackTrace();
  }finally{
   try{
    if(cs!=null){
     cs.close();
     cs = null;
    }
   }catch(Exception e1){
    e1.printStackTrace();
   }finally{
    try{
     if(conn!=null){
      conn.close();
      conn = null;
     }
    }catch(Exception e2){
     e2.printStackTrace();
    }
   }
  }
 }
}

=========多个输出参数和一个输入参数的存储过程===

--编写一个存储过程，使用多个输出参数接收变量
create or replace procedure sp_pro9
(spNo in number,spName out varchar2,spSal out number,spJob out varchar2) is
begin
select ename,sal,job into spName,spSal,spJob from emp where empno=spNo;
end;

==java代码调用

package test;
import java.sql.*;
public class TestOracle{
 //定义Connection对象
 public static Connection conn = null;
 //定义CallableStatement对象
 public static CallableStatement cs = null;
 public static void main(String[] args){
   try{
   //1、加载Oracle驱动
   Class.forName("oracle.jdbc.driver.OracleDriver");
   //2、取得数据库连接
   conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORCL","scott","tiger");
   //3、实例化CallableStatement对象
   cs = conn.prepareCall("{call sp_pro9(?,?,?,?)}");
   //4、给？赋值
   cs.setInt(1,7788);
   //5、给第二个？赋值
   cs.registerOutParameter(2,oracle.jdbc.OracleTypes.VARCHAR);
   //5、给第三个？赋值
   cs.registerOutParameter(3,oracle.jdbc.OracleTypes.DOUBLE);
   //6、给第四个？赋值
   cs.registerOutParameter(4,oracle.jdbc.OracleTypes.VARCHAR);
   //7、执行
   cs.execute();
   //8、获取返回的值，注意返回值是第二个，此处必须写2
   String name = cs.getString(2);
   double sal = cs.getDouble(3);
   String job = cs.getString(4);
   //9、打印输出取得的值
   System.out.println("7788号员工的姓名是:" + name + ",工资是：" + sal + ",职位是：" +job);
  }catch(Exception e){
   e.printStackTrace();
  }finally{
   try{
    if(cs!=null){
     cs.close();
     cs = null;
    }
   }catch(Exception e1){
    e1.printStackTrace();
   }finally{
    try{
     if(conn!=null){
      conn.close();
      conn = null;
     }
    }catch(Exception e2){
     e2.printStackTrace();
    }
   }
  }
 }
}