CS276 Advanced Oracle Using Java

1
CS276 Advanced Oracle Using Java

• Chapter 5
• PL/SQL and JDBC

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

3
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

4
Calling PL/SQL Procedures

• Step1 Create and Prepare a CallableStatement
  Object
• CallableStatement myCallableStatement
  myConnection.prepareCall(
• "call update_product_price(?, ?)"
• )

5
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)

6
Calling PL/SQL Procedures

• Step3 Call the execute() Method
• myCallableStatement.execute()
• When youre finished with your
  CallableStatement object, you should close it
• myCallableStatement.close()

7
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

8
Calling PL/SQL Functions

• Step 1 Create and Prepare a CallableStatement
  Object
• call the PL/SQL function update_product_price_f
  unc()
• myCallableStatement myConnection.prepareCall
  (
• "? call update_product_price_func(?,
  ?)"
• )

9
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)

10
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

11
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)

12
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()

13
Calling PL/SQL Functions

• Step 5 Read the Return Value
• int result myCallableStatement.getInt(1)

14
Calling PL/SQL Procedures and Functions

• Example Program
• PLSQLEXAMPLE1.java

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

16
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

17
Using PL/SQL Packages and REF CURSORs

• In Chapter 10, Ill show you how to create Java
  stored procedures, which are procedures that are
  written in Java and are stored in the database.