CS276 Advanced Oracle Using Java

CS276 Advanced Oracle Using Java Chapter 5 PL/SQL and JDBC

Calling PL/SQL Procedures Chapter 2 showed the definition of a Pl/SQL procedure named update_product_price(). This procedure may be used to update a price of product in the products table. The procedure accepts two parameters.

Calling PL/SQL Procedures There are three steps involved in calling a PL/SQL procedure: • Step1: Create and Prepare a CallableStatement Object • Step2: Provide Parameter Values • Step3: Call the execute() Method

Calling PL/SQL Procedures • Step1: Create and Prepare a CallableStatement Object CallableStatement myCallableStatement = myConnection.prepareCall( "{call update_product_price(?, ?)}" );

Calling PL/SQL Procedures • Step2: Provide Parameter Values bind values to the CallableStatement object's parameters myCallableStatement.setInt(1, 1); myCallableStatement.setDouble(2, 1.1);

Calling PL/SQL Procedures Step3: Call the execute() Method myCallableStatement.execute(); When you’re finished with your CallableStatement object, you should close it: myCallableStatement.close();

Calling PL/SQL Functions There are five steps involved in calling a PL/SQL function: • Step 1: Create and Prepare a CallableStatement Object • Step 2: Register the Output Parameter • Step 3: Provide Parameter Values • Step 4: Run the CallableStatement • Step 5: Read the Return Value

Calling PL/SQL Functions • Step 1: Create and Prepare a CallableStatement Object call the PL/SQL function update_product_price_func() myCallableStatement = myConnection.prepareCall( "{? = call update_product_price_func(?, ?)}" );

Calling PL/SQL Functions • Step 2: Register the Output Parameter and bind values to the CallableStatement object's parameters myCallableStatement.registerOutParameter(1, java.sql.Types.INTEGER);

Calling PL/SQL Functions This method accepts two parameters: the numerical position of the placeholder fro the string supplied to the CallAbleStatement object and the PL/SQL type for the output parameter. You can use PL/SQL type from constants defined in the: • java.sql.Types class or • oracle.jdbc.OracleTypes class

Calling PL/SQL Functions • Step 3: Provide Parameter Values The following examples bind the int value 1 to second placeholder (corresponds to the product id) myCallableStatement.setInt(2, 1); and bind the double 0.8 to the third placeholder( corresponds to the multiplication factor) myCallableStatement.setDouble(3, 0.8);

Calling PL/SQL Functions • Step 4: Run the CallableStatement execute the CallableStatement object - this decreases the new price for product #1 by 20% myCallableStatement.execute();

Calling PL/SQL Functions • Step 5: Read the Return Value int result = myCallableStatement.getInt(1);

Calling PL/SQL Procedures and Functions • Example Program: PLSQLEXAMPLE1.java

Using PL/SQL Packages and REF CURSORs • Chapter 2 showed the definition of a package named ref_cursor_package. • This package declares a function named get_products_ref_cursor(); • this function retrieves the rows from the products table using a PL/SQL cursor and returns aREF CURSOR that points to the rows in that cursor. • The package body for ref_cursor_package is declared as follows.

Using PL/SQL Packages and REF CURSORs There are two example programs: • PLSQLEXAMPLE2.java uses OracleCallableStatement and OracleResultSet objects; • PLSQLEXAMPLE3.java uses CallableStatement and ResultSet objects;

Using PL/SQL Packages and REF CURSORs In Chapter 10, I’ll show you how to create Java stored procedures, which are procedures that are written in Java and are stored in the database.

CS276 Advanced Oracle Using Java