Object Tables   «Prev  Next»

Lesson 7 Practical examples
ObjectiveWrite SQL to Query Object Tables (technique 1)

Write SQL to Query Object Tables

To query a row or a column object table, you can use standard SQL normally used in querying relational tables.
The following example uses standard SQL to query the CUSTOMER_OBJ_TABLE table: 
SELECT cust_id, first_name, last_name
FROM customer_obj_table ;

In this example, we query the object just like a normal relational table.
The following diagram illustrates the variables in the syntax:
Oracle Query Object 
SELECT column_name_1,  <column_name_2>
FROM object_table_name alias
WHERE <clause>;

SELECT Statement The SELECT clause, used for selecting attributes (in this case, columns) from the object table
FROM Clause The FROM clause, used for specifying the name of the object table
WHERE Clause The WHERE clause, used for specifying the criteria for selection from the object table

Object Oriented Programming

Selecting Values from Object Tables

When the ANIMAL table was created, it was based entirely on the ANIMAL_TY datatype: 
create table ANIMAL of ANIMAL_TY;

When you select from a table that contains an object type, you refer to the type of the object columns as part of the table’s columns. That is, if you used the ANIMAL_TY datatype as the basis for a column named Animal, then you would select the animal names by selecting Animal.Name from the table. An object table, though, is based on an object type, has no other columns. Therefore, you do not need to reference the object type when accessing the columns. To select the names from the ANIMAL table, just query the attributes directly: 
select Name
from ANIMAL;
NAME
-------------------------
FRANCES
BENJI
LYLE

You can refer to the columns within the where clause just as if ANIMAL were a relational table: 
select Name
from ANIMAL
where Breed = 'ALLIGATOR';
NAME
-------------------------
LYLE

If the ANIMAL_TY object type used another object type for one of its columns, that object type's column would be referenced during all selects, updates, and deletes.
The following diagram shows an example of querying an object table:
SQL Query Example in Oracle
Query example 
SELECT product_id, product_name
FROM product_obj_table
WHERE product_id=39;

Element 1 The SELECT clause
Element 2 The FROM clause
Element 3 The WHERE clause

Object Oriented Databases

Implicit SQL Cursor Attributes

The Oracle database allows you to access information about the most recently executed implicit cursor by referencing the special implicit cursor attributes. Because the cursors are implicit, they have no name, and therefore the keyword SQL is used to denote the implicit cursor.
In Oracle PL/SQL, "Implicit SQL cursor attributes" provide information about the execution of the most recent DML operation (like `INSERT`, `UPDATE`, `DELETE`, or `MERGE`) or a SELECT INTO query. These attributes are associated with the "implicit cursor", which is automatically created when a SQL statement is executed.
Common Implicit Cursor Attributes
The implicit SQL cursor attributes are prefixed with the keyword `SQL` and include the following:
Attribute Description
SQL%ROWCOUNT Returns the number of rows affected by the most recently executed SQL statement.
SQL%FOUND Returns TRUE if the most recent SQL statement affected one or more rows, otherwise FALSE.
SQL%NOTFOUND Returns TRUE if the most recent SQL statement did not affect any rows, otherwise FALSE.
SQL%ISOPEN Always returns FALSE for implicit cursors because they are automatically closed after execution.

Usage in Queries
  1. `SELECT INTO` Statement:
    • For queries that fetch data using the `SELECT INTO` statement, you can use these attributes to determine whether data was successfully retrieved.
    • DECLARE
    v_emp_name VARCHAR2(50);
BEGIN
    BEGIN
        SELECT first_name INTO v_emp_name
        FROM employees
        WHERE employee_id = 101;
        -- Check if the query returned rows
        IF SQL%FOUND THEN
            DBMS_OUTPUT.PUT_LINE('Employee found: ' || v_emp_name);
        END IF;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('No employee found with the given ID.');
    END;
END;
  2. DML Statements (`INSERT`, `UPDATE`, `DELETE`):
    • Implicit cursor attributes are frequently used to check how many rows were affected by the DML operation.
    • BEGIN
    UPDATE employees
    SET salary = salary * 1.1
    WHERE department_id = 50;

    -- Display the number of rows updated
    DBMS_OUTPUT.PUT_LINE('Number of rows updated: ' || SQL%ROWCOUNT);

    -- Check if any rows were updated
    IF SQL%FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Salaries updated successfully.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('No rows matched the criteria.');
    END IF;
END;
  3. Error Handling:
    • Using `SQL%NOTFOUND` to handle cases where no rows were affected.
    • BEGIN
    DELETE FROM employees
    WHERE department_id = 999; -- Assume no such department exists

    IF SQL%NOTFOUND THEN
        DBMS_OUTPUT.PUT_LINE('No rows deleted.');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Rows deleted: ' || SQL%ROWCOUNT);
    END IF;
END;

Key Notes
  • Scope: The implicit cursor attributes refer to the most recently executed SQL statement in the current session.
  • Automatic Closure: Implicit cursors are automatically opened and closed by Oracle, so SQL%ISOPEN is always FALSE.
  • Compatibility: These attributes cannot be used with explicit cursors. Explicit cursors have their own attributes.

By leveraging these attributes, you can enhance your PL/SQL code for better control, error handling, and reporting based on the outcome of SQL operations.
In the next lesson, an alternative way of writing SQL to query an object table will be discussed.

SEMrush Software