Samples of PL/SQL in JDBC



The following examples demonstrate the interoperability between PL/SQL and JDBC, contrasting standard SQL92 calling syntax with Oracle PL/SQL block syntax:

• Executing Procedures in PL/SQL Blocks--PLSQL.java

• Calling PL/SQL Stored Procedures--PLSQLExample.java

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".

Calling PL/SQL Stored Procedures--PLSQLExample.java

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();  }}

Executing Procedures in PL/SQL Blocks--PLSQL.java

This sample defines PL/SQL stored procedures and functions and executes them from within Oracle PL/SQLBEGIN...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();  }}

转自：http://docs.oracle.com/cd/A84870_01/doc/java.816/a81354/samapp2.htm