Lesson 5 | When to use PL/SQL for querying object tables |
Objective | Determine when PL/SQL is appropriate or required for querying object tables. |
Querying Object Tables in Oracle
Object references are used to reference an object within an object table. To query a table that has a reference to an object, you must use PL/SQL instead of standard SQL. PL/SQL is also required when you query varrays and nested tables.
Let us look at an example of a PL/SQL block where we query a referenced object and update its value:
DECLARE
the_customer customer_type;
BEGIN
SELECT REF(sh) INTO the_customer
FROM sale_header sh
WHERE sale_id = 36;
UPDATE sale_header sh
SET tax_amount = 200
WHERE sale_id = 36
AND REF(sh) = the_customer;
COMMIT;
END;
/
In the example above, we assign the reference to an object to a local variable. Based on this reference, we update tax_amount
. In the next lesson, we will look at a PL/SQL block that retrieves data from an object table.
PL/SQL Block
Within a PL/SQL block, the first section is the Declarations section. Within the Declarations section, you define the variables and cursors that the block will use. The Declarations section starts with the keyword declare and ends when the Executable Commands section starts (as indicated by the keyword begin). The Executable Commands section is followed by the Exception
Handling section; the exception keyword signals the start of the Exception Handling section. The PL/SQL block is terminated by the end keyword. The structure of a typical PL/SQL block is shown in the following listing:
declare
<declarations section>
begin
<executable commands>
exception
<exception handling>
end;