Java调用Oracle存储过程和函数（三）

来源：互联网发布：图像算法工程师容易吗? 编辑：程序博客网时间：2024/06/05 14:40

一、创建表

create table stock_prices(
pic varchar(6) primary key,
price number(7,2),
updated date
)

二、增加数据

INSERT INTO stock_prices values('1111',1.0,SYSDATE);
INSERT INTO stock_prices values('1112',2.0,SYSDATE);
INSERT INTO stock_prices values('1113',3.0,SYSDATE);
INSERT INTO stock_prices values('1114',4.0,SYSDATE);

三、建立一个返回游标

create or replace package pkg_pub_utils is(as 注意：is和as都可以)
type refcursor is ref cursor;
end pkg_pub_utils;

四、创建存储过程

create or replace procedure p_get_price(
an_o_ret_code out number,
ac_o_ret_msg out varchar2,
cur_ret out pkg_pub_utils.refcursor,
an_i_price in number
) is
begin
an_o_ret_code :=0;
ac_o_ret_msg :='操作成功';

open cur_ret for
select * from stock_prices where price<an_i_price;
exception
when others then
an_o_ret_code :=-1;
ac_o_ret_msg :='错误代码' || sqlcode || chr(13) || '错误信息' || sqlerrm;
end p_get_price;

Java代码调用如下：

import java.sql.*;import oracle.jdbc.OracleCallableStatement;public class TestProcedure {public TestProcedure() {} public static void main(String[] args ){    String driver = "oracle.jdbc.driver.OracleDriver";    String strUrl = "jdbc:oracle:thin:@10.10.1.29:1521:testdb";    Statement stmt = null;    ResultSet rs = null;    Connection conn = null;    try {      Class.forName(driver);      conn = DriverManager.getConnection(strUrl, "neb", "testneb");      CallableStatement proc = null; //创建执行存储过程的对象      proc = conn.prepareCall("{call p_get_price(?,?,?,?) }"); //设置存储过程 call为关键字.      proc.registerOutParameter(1, oracle.jdbc.OracleTypes.NUMBER);      proc.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);      /*下面这样写也可以      proc.registerOutParameter(1, oracle.jdbc.OracleTypes.FLOAT);      proc.registerOutParameter(2, oracle.jdbc.OracleTypes.CHAR);*/      proc.registerOutParameter(3, oracle.jdbc.OracleTypes.CURSOR);      proc.setString(4, "3.5"); //设置第一个输入参数      proc.execute();//执行      int retcode = proc.getInt(1);      String msg = proc.getString(2);      if(-1 == retcode) {      System.out.println("报错");      }else{      System.out.println(msg);      System.out.println(retcode);      System.out.println("正确");      }      //获取结果集的方式一：      //rs = (ResultSet) proc.getObject(3);      //获取结果集的方式二：      rs = ((OracleCallableStatement) proc).getCursor(3);        while (rs.next()) {System.out.println(rs.getString(1)+"," + rs.getString(2)+","+rs.getString(3));      }    }catch (SQLException ex2) {      ex2.printStackTrace();    }catch (Exception ex2) {      ex2.printStackTrace();    }finally{      try {        if(rs != null){          rs.close();          if(stmt!=null){            stmt.close();          }          if(conn!=null){            conn.close();          }        }      }      catch (SQLException ex1) {      }    } }}

②创建存储过程中带if判断

create or replace procedure p_get_price_if(
an_o_ret_code out number,
ac_o_ret_msg out varchar2,
cur_ret out pkg_pub_utils.refcursor,
an_i_price in number
) is
begin
an_o_ret_code :=0;
ac_o_ret_msg :='操作成功';

if an_i_price = 0 then
open cur_ret for select * from stock_prices;
else
open cur_ret for select * from stock_prices where price<an_i_price;
end if;
exception
when others then
an_o_ret_code :=-1;
ac_o_ret_msg :='错误代码' || sqlcode || chr(13) || '错误信息' || sqlerrm;
end p_get_price_if;

java代码调用如下：

import java.sql.*;import oracle.jdbc.OracleCallableStatement;public class TestProcedure {public TestProcedure() {} public static void main(String[] args ){    String driver = "oracle.jdbc.driver.OracleDriver";    String strUrl = "jdbc:oracle:thin:@10.10.1.29:1521:testdb";    Statement stmt = null;    ResultSet rs = null;    Connection conn = null;    try {      Class.forName(driver);      conn = DriverManager.getConnection(strUrl, "neb", "testneb");      CallableStatement proc = null; //创建执行存储过程的对象      //proc = conn.prepareCall("{call p_get_price(?,?,?,?) }"); //设置存储过程 call为关键字.      proc = conn.prepareCall("{call p_get_price_if(?,?,?,?) }"); //设置存储过程 call为关键字.      proc.registerOutParameter(1, oracle.jdbc.OracleTypes.NUMBER);      proc.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);      /*下面这样写也可以      proc.registerOutParameter(1, oracle.jdbc.OracleTypes.FLOAT);      proc.registerOutParameter(2, oracle.jdbc.OracleTypes.CHAR);*/      proc.registerOutParameter(3, oracle.jdbc.OracleTypes.CURSOR);      proc.setString(4, "3"); //设置第一个输入参数      proc.execute();//执行      int retcode = proc.getInt(1);      String msg = proc.getString(2);      if(-1 == retcode) {      System.out.println("报错");      }else{      System.out.println(msg);      System.out.println(retcode);      System.out.println("正确");      }      //获取结果集的方式一：      //rs = (ResultSet) proc.getObject(3);      //获取结果集的方式二：      rs = ((OracleCallableStatement) proc).getCursor(3);        while (rs.next()) {System.out.println(rs.getString(1)+"," + rs.getString(2)+","+rs.getString(3));      }    }catch (SQLException ex2) {      ex2.printStackTrace();    }catch (Exception ex2) {      ex2.printStackTrace();    }finally{      try {        if(rs != null){          rs.close();          if(stmt!=null){            stmt.close();          }          if(conn!=null){            conn.close();          }        }      }      catch (SQLException ex1) {      }    } }}

结果如下：

操作成功
0
正确
1111,1,2017-10-11 15:13:05.0
1112,2,2017-10-11 15:13:05.0

五、创建函数

create or replace function f_get_price(v_price in number)
return pkg_pub_utils.refcursor
as
stock_cursor pkg_pub_utils.refcursor;
begin
open stock_cursor for
select * from stock_prices where price<v_price;
return stock_cursor;
end;

Java代码调用如下：

import java.sql.*;import oracle.jdbc.OracleCallableStatement;public class TestProcedureFunction {public TestProcedureFunction() {} public static void main(String[] args ){    String driver = "oracle.jdbc.driver.OracleDriver";    String strUrl = "jdbc:oracle:thin:@10.10.1.29:1521:testdb";    Statement stmt = null;    ResultSet rs = null;    Connection conn = null;    try {      Class.forName(driver);      conn = DriverManager.getConnection(strUrl, "neb", "testneb");      CallableStatement proc = null; //创建执行存储过程的对象      proc = conn.prepareCall("{?=call f_get_price(?) }"); //设置存储过程 call为关键字.      proc.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);      proc.setString(2, "3.5"); //设置第一个输入参数      proc.execute();//执行      //这个也可以       //proc.executeUpdate();            //获取结果集的方式一：      //rs = (ResultSet) proc.getObject(1);      //获取结果集的方式二：      rs = ((OracleCallableStatement) proc).getCursor(1);        while (rs.next()) {System.out.println(rs.getString(1)+"," + rs.getString(2)+","+rs.getString(3));      }          }catch (SQLException ex2) {      ex2.printStackTrace();    }catch (Exception ex2) {      ex2.printStackTrace();    }finally{      try {        if(rs != null){          rs.close();          if(stmt!=null){            stmt.close();          }          if(conn!=null){            conn.close();          }        }      }      catch (SQLException ex1) {      }    } }}

六、参考图片

阅读全文

0 0