Implementing stored procedures and functions that invoke Oracle under Java

Create a test account under Oracle, and then follow the steps:

1. CREATE TABLE: Stock_prices

--Create TABLE Stock_prices (RIC VARCHAR (6) PRIMARY KEY, price number (7,2), UPDATED DATE);

2. Insert the test data:

--Inserting data 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. Create a return cursor: pkg_pub_utils

--Create a return cursor Create OR REPLACE package Pkg_pub_utils is--dynamic cursor TYPE refcursor is REF CURSOR; END pkg_pub_utils;

4. Creating and storing procedures: P_get_price

--Creating a stored procedure 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) Isbegin An_o_ret_code: = 0;        Ac_o_ret_msg: = ' operation succeeded '; 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: = ' error code: ' | | SQLCODE | | CHR (13) | | ' Error message: ' | | SQLERRM; END P_get_price;

5. Create the function:

--Create function: F_get_pricecreate OR REPLACE function F_get_price (v_price in number) RETURN pkg_pub_utils. Refcursoras 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 Call stored procedure returns result set

code example: Jdbcoracle10g_invokeprocedure.java

import java.sql.*;import oracle.jdbc.oraclecallablestatement;import oracle.jdbc.oracletypes;/*   This example returns a result set by invoking an Oracle stored procedure:  * oracle 9i, 10g  JDBC consists of 1 jar packages: 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  Users         user  =  "test";        // oracle  password          pwd =  "Test";         init ();         // mysid: Must be the SID name of the machine to be connected, or the following error will be wrapped:         // java.sql.SQLException: Io  Abnormal: connection         // refused (description= (tmp=) (vsnnum=169870080) (err=12505) (ERROR_STACK= (ERROR= ( code=12505) (emfi=4)))         //  Reference connection method:         // class.forname (  "Oracle.jdbc.driver.OracleDriver"  );       &nbsP; // 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 =&nbsP;conn.createstatement ();            //  Conn.setautocommit (false);            //  input parameters             in_price =  "3.0";             //  calling Functions              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);            &nbSp;stmt.setstring (4, in_price);             Stmt.executeupdate ();             int retcode  = stmt.getint (1);             string  retmsg = stmt.getstring (2);             if  (retcode == -1)  { //  error message if error is returned                  SYSTEM.OUT.PRINTLN ("Error! ");            } else {                 //  How to take the result set one:                 rs =  ( oraclecallablestatement)  stmt). GetCursor (3);  &Nbsp;              //  The result set in two ways:                 // rs  =  (ResultSet)  stmt.getobject (3);                 String ric;                 String price;                 String updated;                 //  Output of results                  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[])//  Replace yourself []    {        new jdbcoracle10g_ Invokeprocedure ();     }}

7. Developing a Java calling function to return a result set

code example: Jdbcoracle10g_invokefunction.java

import java.sql.*;import oracle.jdbc.oraclecallablestatement;import oracle.jdbc.oracletypes;/*  *  This example returns a result set by invoking the function of Oracle:  * oracle 9i, 10g  JDBC consists of 1 jar packages: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         user  =  "test";        // oracle  password          pwd =  "Test";         init ();         // mysid: Must be the SID name of the machine to be connected, or the following error will be wrapped:         // java.sql.SQLException: Io  Abnormal: connection         // refused (description= (tmp=) (vsnnum=169870080) (ERR=12505) (ERROR_STACK= ( Error= (code=12505) (emfi=4)))         //  Reference connection method:         // class.forname (  "Oracle.jdbc.driver.OracleDriver"  );       &Nbsp; // 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 =&Nbsp;conn.createstatement ();            //  Conn.setautocommit (false);            //  input parameters             in_price =  "5.0";             //  calling Functions              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);       &nbsP;     stmt.setstring (2, in_price);             stmt.executeupdate ();             //  How to take the result set one:             rs  =  ((oraclecallablestatement)  stmt). GetCursor (1);             //  How to take the result set two:             // 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[])//  own replacement []    {         new jdbcoracle10g_invokefunction ();     }}

Implementing stored procedures and functions that invoke Oracle under Java