java下实现调用oracle的存储过程和函数
在oracle下创建一个test的账户,然后
1.创建表:STOCK_PRICES
- --创建表格
- CREATE TABLE STOCK_PRICES(
- RIC VARCHAR(6) PRIMARY KEY,
- PRICE NUMBER(7,2),
- UPDATED DATE );
2.插入测试数据:
- --插入数据
- 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);
3.建立一个返回游标:
PKG_PUB_UTILS
- --建立一个返回游标
- CREATE OR REPLACE PACKAGE PKG_PUB_UTILS IS
- --动态游标
- TYPE REFCURSOR IS REF CURSOR;
- END PKG_PUB_UTILS;
4.创建和存储过程:P_GET_PRICE
- --创建存储过程
- 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;
5.创建函数:
- --创建函数:F_GET_PRICE
- 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;
6.JAVA调用存储过程返回结果集
JDBCoracle10G_INVOKEPROCEDURE.java
- import java.sql.*;
- import oracle.jdbc.OracleCallableStatement;
- import oracle.jdbc.OracleTypes;
- /* 本例是通过调用oracle的存储过程来返回结果集:
- * oracle 9i、10G 的jdbc由1个jar包组成:classes12.zip
- */
- public class JDBCoracle10G_INVOKEPROCEDURE {
- Connection conn = null;
- Statement statement = null;
- ResultSet rs = null;
- CallableStatement stmt = null;
- String driver;
- String url;
- String user;
- String pwd;
- String sql;
- String in_price;
- public JDBCoracle10G_INVOKEPROCEDURE()
- {
- driver = "oracle.jdbc.driver.OracleDriver";
- url = "jdbc:oracle:thin:@localhost:1521:ORCL";
- // oracle 用户
- user = "test";
- // oracle 密码
- pwd = "test";
- init();
- // mysid:必须为要连接机器的sid名称,否则会包以下错:
- // java.sql.SQLException: Io 异常: Connection
- // refused(DESCRIPTION=(TMP=)(VSNNUM=169870080)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4))))
- // 参考连接方式:
- // Class.forName( "oracle.jdbc.driver.OracleDriver" );
- // cn = DriverManager.getConnection(
- // "jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd );
- }
- public void init() {
- System.out.println("oracle jdbc test");
- try {
- Class.forName(driver);
- System.out.println("driver is ok");
- conn = DriverManager.getConnection(url, user, pwd);
- System.out.println("conection is ok");
- statement = conn.createStatement();
- // conn.setAutoCommit(false);
- // 输入参数
- in_price = "3.0";
- // 调用函数
- stmt = conn.prepareCall("call P_GET_PRICE(?,?,?,?)");
- stmt.registerOutParameter(1, java.sql.Types.FLOAT);
- stmt.registerOutParameter(2, java.sql.Types.CHAR);
- stmt.registerOutParameter(3, oracle.jdbc.OracleTypes.CURSOR);
- stmt.setString(4, in_price);
- stmt.executeUpdate();
- int retCode = stmt.getInt(1);
- String retMsg = stmt.getString(2);
- if (retCode == -1) { // 如果出错时,返回错误信息
- System.out.println("报错!");
- } else {
- // 取的结果集的方式一:
- rs = ((OracleCallableStatement) stmt).getCursor(3);
- // 取的结果集的方式二:
- // rs = (ResultSet) stmt.getObject(3);
- String ric;
- String price;
- String updated;
- // 对结果进行输出
- while (rs.next()) {
- ric = rs.getString(1);
- price = rs.getString(2);
- updated = rs.getString(3);
- System.out.println("ric:" + ric + ";-- price:" + price
- + "; --" + updated + "; ");
- }
- }
- } catch (Exception e) {
- e.printStackTrace();
- } finally {
- System.out.println("close ");
- }
- }
- public static void main(String args[])// 自己替换[]
- {
- new JDBCoracle10G_INVOKEPROCEDURE();
- }
- }
7.开发JAVA调用函数返回结果集
JDBCoracle10G_INVOKEFUNCTION.java
- import java.sql.*;
- import oracle.jdbc.OracleCallableStatement;
- import oracle.jdbc.OracleTypes;
- /*
- /* 本例是通过调用oracle的函数来返回结果集:
- * oracle 9i、10G 的jdbc由1个jar包组成:classes12.zip
- */
- public class JDBCoracle10G_INVOKEFUNCTION {
- Connection conn = null;
- Statement statement = null;
- ResultSet rs = null;
- CallableStatement stmt = null;
- String driver;
- String url;
- String user;
- String pwd;
- String sql;
- String in_price;
- public JDBCoracle10G_INVOKEFUNCTION()
- {
- driver = "oracle.jdbc.driver.OracleDriver";
- url = "jdbc:oracle:thin:@localhost:1521:ORCL";
- // oracle 用户
- user = "test";
- // oracle 密码
- pwd = "test";
- init();
- // mysid:必须为要连接机器的sid名称,否则会包以下错:
- // java.sql.SQLException: Io 异常: Connection
- // refused(DESCRIPTION=(TMP=)(VSNNUM=169870080)(ERR=12505)(ERROR_STACK=(ERROR=(CODE=12505)(EMFI=4))))
- // 参考连接方式:
- // Class.forName( "oracle.jdbc.driver.OracleDriver" );
- // cn = DriverManager.getConnection(
- // "jdbc:oracle:thin:@MyDbComputerNameOrIP:1521:ORCL", sUsr, sPwd );
- }
- public void init() {
- System.out.println("oracle jdbc test");
- try {
- Class.forName(driver);
- System.out.println("driver is ok");
- conn = DriverManager.getConnection(url, user, pwd);
- System.out.println("conection is ok");
- statement = conn.createStatement();
- // conn.setAutoCommit(false);
- // 输入参数
- in_price = "5.0";
- // 调用函数
- stmt = conn.prepareCall("{? = call F_GET_PRICE(?)}");
- // stmt.registerOutParameter(1, java.sql.Types.FLOAT);
- // stmt.registerOutParameter(2, java.sql.Types.CHAR);
- stmt.registerOutParameter(1, oracle.jdbc.OracleTypes.CURSOR);
- stmt.setString(2, in_price);
- stmt.executeUpdate();
- // 取的结果集的方式一:
- rs = ((OracleCallableStatement) stmt).getCursor(1);
- // 取的结果集的方式二:
- // rs = (ResultSet) stmt.getObject(1);
- String ric;
- String price;
- String updated;
- while (rs.next()) {
- ric = rs.getString(1);
- price = rs.getString(2);
- updated = rs.getString(3);
- System.out.println("ric:" + ric + ";-- price:" + price + "; --"
- + updated + "; ");
- }
- } catch (Exception e) {
- e.printStackTrace();
- } finally {
- System.out.println("close ");
- }
- }
- public static void main(String args[])// 自己替换[]
- {
- new JDBCoracle10G_INVOKEFUNCTION();
- }
- }
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。