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