3.7 PL/SQL in SQLJ
PL/SQL is Oracle's proprietary procedural language, built around SQL, that includes flow control, loops, and the ability to define procedures and functions that may be stored in the database. You learned the basics of PL/SQL in the previous chapter. In this section, you will see how to call PL/SQL procedures and functions using SQLJ executable statements, how to embed blocks of anonymous PL/SQL in SQLJ statements, and how the PL/SQL REF CURSOR type may be used in conjunction with SQLJ iterators.
3.7.1 PL/SQL Procedures
To invoke a PL/SQL stored procedure, use the SQLJ CALL statement. Before getting into the syntax for CALL, let's examine the procedure update_product_price( ), which is stored in the fundamental_user schema. This procedure was created by the SQL*Plus script fundamental_user_schema.sql, and it sets the price of a product to a value calculated by multiplying the original price by a given factor. The source for the update_product_price( ) procedure is as follows:
-- procedure update_product_price updates the price of -- a product by a given factor (both specified as parameters) CREATE PROCEDURE update_product_price( p_product_id IN products.id%TYPE, p_factor IN NUMBER ) AS product_count INTEGER; BEGIN -- ensure that the product exists SELECT COUNT(*) INTO product_count FROM products WHERE id = p_product_id; -- if the product doesn't exist then return IF product_count = 0 THEN RETURN; END IF; -- if the product does exist then perform the update UPDATE products SET price = price * p_factor; -- commit the transaction COMMIT; END update_product_price; /
As you can see, the update_product_price( ) procedure takes two parameters. You identify the product using the first parameter, and you specify the factor using the second parameter.
You can invoke the update_product_price( ) procedure from a SQLJ program by using the CALL statement. The syntax for CALL is:
#sql { CALL procedure_name([parameter_list])}; The syntax elements are as follows:
- procedure_name
-
Specifies the name of the PL/SQL stored procedure.
- parameter_list
-
Specifies a comma-delimited list of parameters to pass to the PL/SQL stored procedure.
Using the syntax shown here, you would call the update_product_price( ) procedure as follows:
#sql { CALL update_product_price(1, .9) }; Here, the parameters to the PL/SQL procedure are constants. However, it's also possible to use host expressions to pass in parameter values from your Java code.
3.7.2 PL/SQL Functions
PL/SQL functions differ from procedures in that a function must return a value. The PL/SQL function update_product_price_func( ) (also defined by the fundamental_user_schema.sql script) updates the price of a product by a given factor, and returns 0 if the update was successful. Otherwise, 1 is returned, indicating that the product was not found. The function update_product_price_func( ) is defined as follows:
-- function update_product_price_func( ) updates the price of -- a product by a given factor (both specified as a parameters), -- and returns 0 if successful, otherwise returns 1 (indicating that the -- product was not found) CREATE FUNCTION update_product_price_func( p_product_id IN products.id%TYPE, p_factor IN NUMBER ) RETURN INTEGER AS product_count INTEGER; BEGIN -- ensure that the product exists SELECT COUNT(*) INTO product_count FROM products WHERE id = p_product_id; -- if the product doesn't exist then return 1 IF product_count = 0 THEN -- return 1, indicating that the function was unsuccessful RETURN 1; END IF; -- if the product does exist then perform the update UPDATE products SET price = price * p_factor; -- commit the transaction COMMIT; -- return 0, indication that the function was successful RETURN 0; END update_product_price_func; /
Because functions must return a value, you should use a Java variable to store the result. The syntax for calling a PL/SQL function from a SQLJ executable statement is:
#sql host_variable = { VALUES ( function_name([ parameter_list])) }; The syntax elements are as follows:
- host_variable
-
Specifies the host variable to use in storing the value returned from the PL/SQL function. The host variable type must be compatible with the return type of the PL/SQL function (see Appendix A for information on which types are compatible with each other).
- function_name
-
Specifies the name of the PL/SQL function. The parentheses surrounding the function call are optional, though you should supply them if you want your code to conform to the SQLJ standard.
- parameter_list
-
Specifies a list of parameters to be passed to the PL/SQL function.
The VALUES statement in the following code calls the PL/SQL function update_product_price_func( ):
int result; #sql result = { VALUES(update_product_price_func(1, 2)) }; If you prefer, you may leave out the optional parentheses surrounding the function invocation. For example:
#sql result = { VALUES update_product_price_func(1, 2) }; 3.7.3 The SET Statement
The SET statement is used to assign a value to a host expression. The syntax for a SQLJ executable statement that uses the SET statement is:
#sql { SET :host_expression = expression }; The syntax elements are as follows:
- host_expression
-
Specifies the host expression. This has a default mode of OUT and may not be set to IN or INOUT (doing so will result in a translation time error).
- expression
-
Specifies any valid SQL statement that returns a value that can be assigned to the host expression, including multiple PL/SQL function calls.
The following example calls the function update_product_price_func( ) twice, and sets the host variable result_sum equal to the sum of the values returned by the function calls:
int result_sum = 0; #sql { SET :result_sum = update_product_price_func(1, .9) + update_product_price_func(2, .8) }; The major advantage of the SET statement over the VALUES statement is that you can make multiple PL/SQL function calls in one statement -- in fact, you can specify any valid SQL expression.
You can also use the SET statement to access Oracle's built-in variables and functions. The following example uses the SET statement to set the host variable system_date equal to the current date set in the database:
Date system_date = null; #sqlj { SET :system_date = sysdate }; 3.7.4 Embedding Anonymous PL/SQL Blocks
Blocks of PL/SQL may be placed within a SQLJ executable statement. These blocks of PL/SQL are known as anonymous PL/SQL because the PL/SQL blocks are not associated with a procedure or function name. The syntax for a SQLJ executable statement containing an anonymous PL/SQL block is:
#sql { [DECLARE ...] BEGIN ... END; }; The following SQLJ executable statement contains an anonymous PL/SQL block that uses a FOR loop to insert three rows into the customers table:
#sql { DECLARE l_count INT; BEGIN -- insert test customers into the customers table FOR l_count IN 10 .. 12 LOOP INSERT INTO customers (id, first_name, last_name, dob, phone) VALUES (l_count, 'Test', 'Test', '01-JAN-2000', '650-555-1212'); END LOOP; COMMIT; END; }; 3.7.5 REF CURSORs
The PL/SQL REF CURSOR type is conceptually similar to a pointer in the C programming language. A variable of the REF CURSOR type can be used to point to a set of rows returned by a SELECT statement; in addition, a variable of the REF CURSOR type can be assigned to a SQLJ iterator object. This allows you to use any existing PL/SQL functions that return REF CURSOR variables, and process the rows using SQLJ.
To provide an example showing how REF CURSOR types are used, the fundamental_user_schema.sql script created a package named ref_cursor_package. In that package, you'll find a function named get_customers_ref_cursor( ) that returns a REF CURSOR value pointing to the results of the following query:
SELECT id, first_name, last_name, dob, phone FROM customers;
The idea here is that you can call the PL/SQL function to execute the query, and yet access the results of that query from Java. The package specification for ref_cursor_package is as follows:
-- package ref_cursor_package illustrates the use of the -- REF CURSOR type CREATE PACKAGE ref_cursor_package AS TYPE t_ref_cursor IS REF CURSOR; FUNCTION get_customers_ref_cursor RETURN t_ref_cursor; END ref_cursor_package; / CREATE PACKAGE BODY ref_cursor_package AS -- function get_customers_ref_cursor returns a REF CURSOR FUNCTION get_customers_ref_cursor RETURN t_ref_cursor IS customers_ref_cursor t_ref_cursor; BEGIN -- get the REF CURSOR OPEN customers_ref_cursor FOR SELECT id, first_name, last_name, dob, phone FROM customers; -- return the REF CURSOR RETURN customers_ref_cursor; END get_customers_ref_cursor; END ref_cursor_package; /
The get_customers_ref_cursor( ) function executes an SQL query and returns a REF CURSOR value pointing to the results. To access those results from SQLJ, you need to get the REF CURSOR value into a SQLJ iterator. The iterator must be compatible with the original SQL statement. For example, the following SQLJ statements declare an iterator class named CustomerIteratorClass and an iterator object named customer_iterator. The class definition includes iterator columns and Java types that match those returned by the SELECT statement in the PL/SQL function. The iterator customer_iterator, which is based on that class, will then be used to store the REF CURSOR variable returned by the get_customers_ref_cursor( ) function.
#sql public static iterator CustomerIteratorClass ( int id, String first_name, String last_name, Date dob, String phone ); ... CustomerIteratorClass customer_iterator;
Next, the following SQLJ statement calls the get_customers_ref_cursor( ) PL/SQL function and stores the returned REF CURSOR value in customer_iterator:
#sql customer_iterator = { VALUES(ref_cursor_package.get_customers_ref_cursor) }; Once you've assigned the REF CURSOR value to a compatible iterator, you can process the results using the iterator accessor methods that you read about earlier in this chapter. For example:
// process the iterator while (customer_iterator.next( )) { System.out.println("id = " + customer_iterator.id( )); System.out.println("first name = " + customer_iterator.first_name( )); System.out.println("last name = " + customer_iterator.last_name( )); System.out.println("dob = " + customer_iterator.dob( )); System.out.println("phone = " + customer_iterator.phone( )); } // end of while loop // close the iterator customer_iterator.close( ); The following section contains a complete program that illustrates the use of PL/SQL within a SQLJ program.
3.7.6 Example Program: FundamentalExample6.sqlj
The program FundamentalExample6.sqlj, shown in Example 3-3, illustrates the use of PL/SQL procedure and function calls and the SET statement. It also shows you how to embed blocks of anonymous PL/SQL into a SQLJ statement and how to use PL/SQL REF CURSORs with SQLJ iterators.
Example 3-3. FundamentalExample6.sqlj
/* The program FundamentalExample6.sqlj illustrates the use of PL/SQL in SQLJ. This program illustrates the use of the following: - PL/SQL procedure calls - PL/SQL function calls - SET statement - embedding blocks of anonymous PL/SQL - PL/SQL REF CURSORs */ // import required packages import java.sql.*; import oracle.sqlj.runtime.Oracle; public class FundamentalExample6 { #sql public static iterator CustomerIteratorClass ( int id, String first_name, String last_name, Date dob, String phone ); public static void main(String [] args) throws SQLException { try { Oracle.connect( "jdbc:oracle:thin:@localhost:1521:orcl", "fundamental_user", "fundamental_password" ); // PL/SQL procedure calls System.out.println("PL/SQL procedure calls."); // display the price of product 1 displayPrice(1); System.out.println("Reducing the price of product 1 by 10% " + "using the PL/SQL procedure update_product_price( )."); #sql { CALL update_product_price(1, .9) }; displayPrice(1); // PL/SQL function calls System.out.println("\nPL/SQL function calls."); int result; System.out.println("Doubling the price of product 1 using the " + "PL/SQL function update_product_price_func( )."); #sql result = { VALUES(update_product_price_func(1, 2)) }; System.out.println("The result returned from " + "update_product_price_func( ) was " + result + "."); System.out.println("A result of 0 indicates success " + "(1 indicates failure to find part)."); displayPrice(1); // SET statement System.out.println("\nSET statement."); System.out.println("Halving the price of product 1 using " + "update_product_price_func( )."); #sql { SET :result = update_product_price_func(1, .5) }; System.out.println("The result returned " + "from update_product_price_func( ) was " + result + "."); displayPrice(1); // anonymous PL/SQL blocks System.out.println("\nAnonymous PL/SQL blocks."); System.out.println("Adding rows to the customers table."); #sql { DECLARE l_count INT; BEGIN -- insert three test customers into the customers table FOR l_count IN 10 .. 12 LOOP INSERT INTO customers (id, first_name, last_name, dob, phone) VALUES (l_count, 'Test', 'Test', '01-JAN-2000', '650-555-1212'); END LOOP; COMMIT; END; }; // PL/SQL REF CURSORs System.out.println("\nPL/SQL REF CURSORs."); // call the function get_customers_ref_cursor( ) and // set the customer_iterator to the REF CURSOR returned // by the function CustomerIteratorClass customer_iterator; #sql customer_iterator = { VALUES(ref_cursor_package.get_customers_ref_cursor) }; // process the iterator System.out.println("Customers from REF CURSOR:"); while (customer_iterator.next( )) { System.out.println("id = " + customer_iterator.id( )); System.out.println("first name = " + customer_iterator.first_name( )); System.out.println("last name = " + customer_iterator.last_name( )); System.out.println("dob = " + customer_iterator.dob( )); System.out.println("phone = " + customer_iterator.phone( )); } // close the iterator customer_iterator.close( ); Oracle.close( ); } catch ( SQLException e ) { System.err.println("SQLException " + e); System.exit(1); } } // end of run( ) // displayPrice( ) displays the price of the specified product private static void displayPrice( int product_id ) { double price = 0.0; try { #sql { SELECT price INTO :price FROM products WHERE id = :product_id }; } catch (SQLException e) { System.err.println("SQLException " + e); System.exit(1); } System.out.println("Price of product " + product_id + " is " + price); } // end of displayPrice( ) } The output from the program FundamentalExample6.sqlj is as follows:
PL/SQL procedure calls. Price of product 1 is 19.95 Reducing the price of product 1 by 10% using the PL/SQL procedure update_product_price( ). Price of product 1 is 17.96 PL/SQL function calls. Doubling the price of product 1 using the PL/SQL function update_product_price_func( ). The result returned from update_product_price_func( ) was 0. A result of 0 indicates success (1 indicates failure to find part). Price of product 1 is 35.92 SET statement. Halving the price of product 1 using update_product_price_func( ). The result returned from update_product_price_func( ) was 0. Price of product 1 is 17.96 Anonymous PL/SQL blocks. Adding rows to the customers table. PL/SQL REF CURSORs. Customers from REF CURSOR: id = 1 first name = John last name = Smith dob = 1965-01-01 phone = 650-555-1212 id = 2 first name = Cynthia last name = Stevens dob = 1968-03-05 phone = 650-555-1213 id = 3 first name = Steve last name = Seymour dob = 1971-06-16 phone = 650-555-1214 id = 4 first name = Gail last name = Williams dob = 1975-12-01 phone = 650-555-1215 id = 5 first name = Doreen last name = Heyson dob = 1970-08-20 phone = 650-555-1216 id = 10 first name = Test last name = Test dob = 2000-01-01 phone = 650-555-1212 id = 11 first name = Test last name = Test dob = 2000-01-01 phone = 650-555-1212 id = 12 first name = Test last name = Test dob = 2000-01-01 phone = 650-555-1212
