Create PL/SQL Block (Query Object Tables)

Lesson 6	Creating a PL/SQL block to query object tables
Objective	Write a PL/SQL block to retrieve data from an object table.

Creating PL/SQL Block to query Object Tables

In this lesson, we will look at a PL/SQL block that uses a cursor to query an object table.
The following SlideShow shows an example of such a PL/SQL block:

Pl Sql Cursor Block

We will now look at an example that uses a cursor to extend the example from the previous lesson. In this example, we define a cursor based on the SALE_HEADER table. This cursor finds a particular record (where sale_id = 34) and updates the record within the PL/SQL block.

DECLARE
the_customer customer_type;
id_for_sale number(10);
CURSOR cur_cust_type IS 
SELECT REF(sh) refer, sh.sale_id 
INTO   the_customer, id_for_sale
FROM   sale_header sh
FOR UPDATE OF sh.tax_amount;
BEGIN
    FOR get_cust_type IN cur_cust_type LOOP
     IF get_cust_type.sale_id = 34 THEN
UPDATE sale_header sh
SET tax_amount = 200
WHERE sale_id = 34
AND REF(sh) = get_cust_type.refer;
     END IF;
    END LOOP;
    COMMIT;
END;
/

The next lesson wraps up this module.
Click the link below to learn about how to build a cursor block that queries multiple object tables.
Build PL/SQL block