Oracle8i JDBC Developer's Guide and Reference Release 2 (8.1.6) A81354-01 |
|
The following examples demonstrate the interoperability between PL/SQL and JDBC, contrasting standard SQL92 calling syntax with Oracle PL/SQL block syntax:
These samples are located in the following directory on the product CD:
[Oracle Home]/jdbc/demo/samples/oci8/basic-samples
For related discussion, see "PL/SQL Stored Procedures".
This sample defines a stored function and executes it using SQL92 CALL
syntax in a callable statement. The function takes an employee name and salary as input and raises the salary by a set amount.
/* This sample shows how to call a PL/SQL stored procedure using the SQL92 * syntax. See also the other sample PLSQL.java. */ import java.sql.*; import java.io.*; class PLSQLExample { public static void main (String args []) throws SQLException, IOException { // Load the driver DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database // You can put a database name after the @ sign in the connection URL. Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger"); // Create a statement Statement stmt = conn.createStatement (); // Create the stored function stmt.execute ("create or replace function RAISESAL (name CHAR, raise NUMBER) return NUMBER is begin return raise + 100000; end;"); // Close the statement stmt.close(); // Prepare to call the stored procedure RAISESAL. // This sample uses the SQL92 syntax CallableStatement cstmt = conn.prepareCall ("{? = call RAISESAL (?, ?)}"); // Declare that the first ? is a return value of type Int cstmt.registerOutParameter (1, Types.INTEGER); // We want to raise LESLIE's salary by 20,000 cstmt.setString (2, "LESLIE"); // The name argument is the second ? cstmt.setInt (3, 20000); // The raise argument is the third ? // Do the raise cstmt.execute (); // Get the new salary back int new_salary = cstmt.getInt (1); System.out.println ("The new salary is: " + new_salary); // Close the statement cstmt.close(); // Close the connection conn.close(); } }
This sample defines PL/SQL stored procedures and functions and executes them from within Oracle PL/SQL BEGIN...END
blocks in callable statements. Stored procedures and functions with input, output, input-output, and return parameters are shown.
/* This sample shows how to call PL/SQL blocks from JDBC. */ import java.sql.*; class PLSQL { public static void main (String args []) throws SQLException, ClassNotFoundException { // Load the driver Class.forName ("oracle.jdbc.driver.OracleDriver"); // Connect to the database // You can put a database name after the @ sign in the connection URL. Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@", "scott", "tiger"); // Create the stored procedures init (conn); // Cleanup the plsqltest database Statement stmt = conn.createStatement (); stmt.execute ("delete from plsqltest"); // Close the statement stmt.close(); // Call a procedure with no parameters { CallableStatement procnone = conn.prepareCall ("begin procnone; end;"); procnone.execute (); dumpTestTable (conn); procnone.close(); } // Call a procedure with an IN parameter { CallableStatement procin = conn.prepareCall ("begin procin (?); end;"); procin.setString (1, "testing"); procin.execute (); dumpTestTable (conn); procin.close(); } // Call a procedure with an OUT parameter { CallableStatement procout = conn.prepareCall ("begin procout (?); end;"); procout.registerOutParameter (1, Types.CHAR); procout.execute (); System.out.println ("Out argument is: " + procout.getString (1)); procout.close(); } // Call a procedure with an IN/OUT prameter { CallableStatement procinout = conn.prepareCall ("begin procinout (?); end;"); procinout.registerOutParameter (1, Types.VARCHAR); procinout.setString (1, "testing"); procinout.execute (); dumpTestTable (conn); System.out.println ("Out argument is: " + procinout.getString (1)); procinout.close(); } // Call a function with no parameters { CallableStatement funcnone = conn.prepareCall ("begin ? := funcnone; end;"); funcnone.registerOutParameter (1, Types.CHAR); funcnone.execute (); System.out.println ("Return value is: " + funcnone.getString (1)); funcnone.close(); } // Call a function with an IN parameter { CallableStatement funcin = conn.prepareCall ("begin ? := funcin (?); end;"); funcin.registerOutParameter (1, Types.CHAR); funcin.setString (2, "testing"); funcin.execute (); System.out.println ("Return value is: " + funcin.getString (1)); funcin.close(); } // Call a function with an OUT parameter { CallableStatement funcout = conn.prepareCall ("begin ? := funcout (?); end;"); funcout.registerOutParameter (1, Types.CHAR); funcout.registerOutParameter (2, Types.CHAR); funcout.execute (); System.out.println ("Return value is: " + funcout.getString (1)); System.out.println ("Out argument is: " + funcout.getString (2)); funcout.close(); } // Close the connection conn.close(); } // Utility function to dump the contents of the PLSQLTEST table and // clear it static void dumpTestTable (Connection conn) throws SQLException { Statement stmt = conn.createStatement (); ResultSet rset = stmt.executeQuery ("select * from plsqltest"); while (rset.next ()) System.out.println (rset.getString (1)); stmt.execute ("delete from plsqltest"); rset.close(); stmt.close(); } // Utility function to create the stored procedures static void init (Connection conn) throws SQLException { Statement stmt = conn.createStatement (); try { stmt.execute ("drop table plsqltest"); } catch (SQLException e) { } stmt.execute ("create table plsqltest (x char(20))"); stmt.execute ("create or replace procedure procnone is begin insert into plsqltest values ('testing'); end;"); stmt.execute ("create or replace procedure procin (y char) is begin insert into plsqltest values (y); end;"); stmt.execute ("create or replace procedure procout (y out char) is begin y := 'tested'; end;"); stmt.execute ("create or replace procedure procinout (y in out varchar) is begin insert into plsqltest values (y); y := 'tested'; end;"); stmt.execute ("create or replace function funcnone return char is begin return 'tested'; end;"); stmt.execute ("create or replace function funcin (y char) return char is begin return y || y; end;"); stmt.execute ("create or replace function funcout (y out char) return char is begin y := 'tested'; return 'returned'; end;"); stmt.close(); } }