--编写存储过程向数据库中的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();
}
}
}
}
}