Java Programming with Oracle SQLJ

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