Object Tables   «Prev 

SQL Query Example in Oracle

Query example 
SELECT product_id, product_name
FROM product_obj_table
WHERE product_id=39;
The SQL query above is requesting specific information from a database. Specifically, it seeks to extract the product_id and product_name from a table named product_obj_table, but only for those entries where the product_id equals 39.
To further explain, let's break it down:
  1. SELECT: This is a SQL command used to fetch data from a database. It is followed by the names of the columns you want to retrieve. Here, product_id and product_name are the columns that will be returned from the product_obj_table.
  2. FROM: This command specifies from which table in the database the system will pull the data. Here, it's the product_obj_table.
  3. WHERE: This clause is used to filter records, ensuring that only relevant data is fetched. It includes a condition that the data must meet. Here, it only pulls records where product_id equals 39.

Thus, the request is seeking to find the product name associated with the product ID of 39 from the table named product_obj_table. The information returned should include the specific product_id and product_name for this item, if it exists.

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

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.

Implicit SQL cursor attributes for queries

SQL%FOUND Returns TRUE if one row (or more, in the case of BULK COLLECT INTO) was fetched successfully, and FALSE otherwise (in which case the database will also raise the NO_DATA_FOUND exception).
SQL%NOTFOUND Returns TRUE if a row was not fetched successfully (in which case the database will also raise the NO_DATA_FOUND exception), and FALSE otherwise.
SQL%ROWCOUNT Returns the number of rows fetched from the specified cursor thus far. For a SELECT INTO, this will be 1 if a row was found and 0 if the database raises the NO_DATA_FOUND exception.
SQL%ISOPEN Always returns FALSE for implicit cursors because the database opens and closes implicit cursors automatically.

Object Oriented Databases