| 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:
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;
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.
When to use PL/SQL to query object tables in Oracle
PL/SQL is appropriate or required for querying object tables in Oracle in certain situations, such as:
- When complex business logic is involved: If the querying of an object table involves complex business logic that cannot be expressed using standard SQL queries, PL/SQL may be required. PL/SQL allows developers to write complex algorithms and manipulate data in ways that would be difficult or impossible to do using SQL alone.
- When using user-defined types: If an object table contains user-defined types, PL/SQL may be required to manipulate the data. This is because SQL has limited support for user-defined types, and PL/SQL provides more flexibility and control over how the data is accessed and manipulated.
- When using advanced features: Oracle provides advanced features such as pipelined table functions, which enable developers to process data in a streaming fashion, and row-level security, which restricts access to specific rows in a table. These features often require the use of PL/SQL code to implement.
- When using dynamic SQL: If the query against an object table needs to be constructed dynamically at runtime, PL/SQL may be required. This is because SQL does not provide a way to dynamically generate queries, and PL/SQL must be used to generate the necessary SQL statements.
In summary, PL/SQL may be appropriate or required for querying object tables in Oracle when the querying involves complex business logic, user-defined types, advanced features, or dynamic SQL.
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: