 |
|
|
|
|
|
The previous chapter introduced you to the basics of using SQL to access the Oracle database. There
is, of course, much more to using SQL against Oracle than was covered in the previous chapter.
This chapter takes you further into using SQL against Oracle. The chapter covers a variety of topics,
including more detail on additional syntax, database objects, and the use of one of Oracle’s unique
features with your SQL.
Although this chapter offers you more information on the type of SQL used with Oracle, it cannot
give you and complete and exhaustive examination of all the facets of SQL and Oracle. There have
been entire books written on using SQL effectively against Oracle, so even two chapters will not be
able to deliver everything you might use in the course of a development career. But we, the authors,
hope that the material in this chapter will help you to become more accomplished in using SQL
against your Oracle database.
Extended Conditions
This section covers some of the conditional expressions that can be used with Oracle that were
outside the scope of the previous chapter.
LIKE and REGEXP_LIKE
Oracle, like most modern databases, divides data into a few basic types—numbers, text and dates,
with some other more peripheral types as well as the possibility of user-defined types. Numbers use
a standard set of mathematical comparison operators, including +, -, >, and <. Number comparisons
are fairly black-and-white, in that they are either absolutely true or absolutely false.
Text, on the other hand, is more nuanced. The words “rich,” “ricochet,” and “rice” have some
commonality—their first three letters are the same, even though their meanings are completely
different. The comparison operator LIKE is designed to help you to identify these similarities.The use of LIKE goes hand in hand with the use of wildcards. The only way to identify all three of the
previous words as similar is to tell Oracle to only pay attention to the first three letters of the words. The
syntax to enable this comparison uses the LIKE operator and the standard Oracle wildcard ‘%’, such as
WHERE WORD_VALUE LIKE ‘ric%’
This selection criteria would retrieve all three words when they were contained in the WORD_VALUE column
in this example.
There are other wildcards you can use in Oracle, but with strings, the target of the LIKE condition, the
most relevant one is the _ (underscore) character. The underscore is used to substitute for a single
character, as opposed to the % wildcard, which can be replaced by any number of characters.
These wildcards can be used for more exact specifications. For instance, the names of the figures used in
this book have to be in the format of the number of the book, followed by a space, followed by the letters
“FG,” followed by the two-digit representation of the chapter. If the figures could have a variable number
of alphanumeric characters following this initial set of identifiers, the selection criterion to retrieve the
figures for this book from a column in an Oracle database could be as follows:
WHERE FIGURE_ID LIKE ‘564825 FG__%’
In this example, wildcards were used at the end of a string. This arrangement is typically how wildcards
are used effectively. Trailing wildcards make it faster for Oracle to determine if a value matches the expression.
Trailing wildcards also can use indexes, if available, for rapid identification of rows that will pass the
condition. If you use a wildcard at the start of a comparison, Oracle will usually have to go through all
the rows or index values in the target to evaluate the condition.
The LIKE condition provides some capabilities for identifying particular character strings. Oracle Database 10g
supports the use of regular expressions, which are covered in detail in Chapter 20, “Regular Expressions and
Expression Filter.” If you want to use a regular expression to match character strings, you will have to use the
new REGEXP_LIKE, which allows you to replace the character pattern on the right of the expression with a
regular expression. You can use this capability to implement functionality like case-insensitive searches.
Finally, if you believe that extensive text search might be an essential part of your data usage, you should
investigate the use of Oracle Text, which gives you much more control over search and retrieval of data
based on text content. Although this feature of Oracle is beyond the scope of this book, it provides fairly
sophisticated functionality, such as the ability to find text strings in large objects based on the string
appearing anywhere in the object, one string occurring a set number of words away from another string,SapereOnline |
|
|
|
|
|
|
|
ORACLE
|
|
|
|
|
| |
|
 |
|
|
|
|
