java存储并调用_编写存储过程并使用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();     }    }   }  } }