Chapter 3: Oracle Database Functions


Every DBA needs to know about built-in functions. Many of the day-to-day tasks of a DBA involve queries, and these queries often need to transform or summarize information in database tables and views. Many DBAs will also create and maintain a library of customized functions (also known as user-defined functions) for business areas in the company and help to deploy these user-defined functions.

This chapter covers the built-in functions and provides an introduction to user-defined functions. However, before we dig into the functions themselves, we’ll talk about some of the general rules for building queries, including how the DUAL table is used, how NULL values work, and how numbers and strings are constructed.

Query Basics

In order to use functions, you need to know how to call them and how to construct their arguments. This section begins by explaining how the DUAL table allows you to use queries that don’t involve a real table. Next, you’ll learn about the ubiquitous NULL value and how it acts as a double-edged sword at times. Then it covers string literals and how to construct larger strings from one or more other strings and columns. Finally, you’ll learn about numeric literals and operator precedence.

Once you know how to use the SELECT statement with the DUAL table, along with how string and numeric literals work, you’ll be ready to explore the built-in functions. You’ll see that they are a potent tool to put into your DBA bag of tricks.

The DUAL Table

Because Oracle SQL is table-centric, most operations performed with SQL must reference some kind of table or view. For example, consider the following SQL statement:

SELECT NAME; SELECT NAME           * ERROR at line 1: ORA-00923: FROM keyword not found where expected

This returns an error, because the basic syntax of a SELECT statement requires that you select FROM something—in this case, a table.

But what if you want to use the SELECT statement to perform some calculations or do some other operation that doesn’t involve a particular table, such as check the system date and time? The DUAL table makes this possible. You reference the DUAL table when you need a table for syntactical reasons, not necessarily for the data in the table.

DUAL

A special table, owned by the Oracle SYS user, that has one row and one column. It is useful for ad-hoc queries that don’t require rows from a specific table.

The DUAL table is a real table. It’s owned by the user SYS and has one row. The table has only one column, which is named DUMMY and has a string with a length of 1. The value of DUMMY in the one and only row is X. You can see the DUAL table’s structure in the iSQL*Plus output shown below.

It’s true that anyone could create a table like this, with one row, and accomplish the same thing. But it’s good practice to have one place where you always have one row and you always know the table name.

click to expand

Since DUAL is a real table, you could certainly do something like this:

select sysdate, dummy from dual; SYSDATE   D --------- - 31-AUG-02 X 1 row selected.

But you already know what the value of DUMMY is in DUAL, so you really don’t need to include this field on a query with DUAL.

And to make it clear that DUAL is a table just like any other, you could also do something like this:

select sysdate from dept; SYSDATE --------- 31-AUG-02 31-AUG-02 31-AUG-02 31-AUG-02 4 rows selected. 

Since the DEPT table has four rows, you get the SYSDATE four times.

Since you really need only one row, the DUAL table will fill the bill nicely:

select sysdate from dual; SYSDATE --------- 31-AUG-02 1 row selected.
Note

The DUAL table originally had two rows in early versions of Oracle, thus the origin of the table name.

NULLs: What, When, Why, and How

Simply put, a NULL value in an Oracle table is nothing. A NULL is not zero, a blank character, or an empty string. It is no value whatsoever. NULLs can be the source of much consternation when a query is not returning the expected results.

NULL

A possible value for any Oracle column that indicates the absence of any known value for that column. A NULL is usually used to represent a value that is unknown, not applicable, or not available.

Using a NULL in an arithmetic expression returns a NULL, regardless of what other operands and operations are in the expression. As an example, consider the following query:

select 5+8, 5+0, 5+null, null+null from dual; 5+8        5+0        5+NULL     NULL+NULL ---------- ---------- ---------- ----------         13          5 1 row selected.

NULL values are useful, however, to indicate when a value is unavailable, unknown, or not applicable. For example, the commission for an employee who is not in the Sales department would be NULL, or the department assigned to a new employee could be NULL.

Note

In certain functions—for example NVL, NVL2, and COALESCE—a NULL value as an argument to the function will return a non-NULL result. This result is the exception, not the rule.

String Literals and Concatenating Strings

A string literal in a SQL query is a sequence of zero, one, or more characters enclosed in single quotation marks (called quotes for short). Here are some valid string literals:

  • ‘JOHN SMITH’

  • ‘’

  • ‘123 Main St.’

string literal

A constant that can consist of any string of letters, digits, and special characters enclosed in single quotation marks.

String literals may be combined with other string literals or table columns, and they may also be arguments to a function. Note that a zero-length string is not the same as a NULL string. You may use a NULL string to indicate that a value is missing or not yet known, and a zero-length string to indicate that the value is blank, but known. For example, a new employee may not have a middle initial, and therefore their middle initial would be set to a zero-length string. But until we find out that they don’t have a middle initial, it will temporarily be set to a NULL string.

Concatenation is the process of combining two or more string literals or columns into a single result. The concatenation operator || (two vertical bars) is used between the strings or columns to be combined. Alternatively, you can use the built-in string function CONCAT.

concatenation

The process of combining two or more data elements into a single element. In Oracle SQL, concatenation can be accomplished by using the concatenation operator (a pair of vertical bars, ||) or the CONCAT function.

The following query demonstrates how string literals and database columns may be concatenated and act as arguments of a function:

select    ‘Employee: ‘ || initcap(ename),    concat(‘Dept: ‘,deptno)    from emp; ‘EMPLOYEE:’||INITCAP CONCAT(‘DEPT:’,DEPTNO) -------------------- -------------------------- Employee: Smith      Dept: 20 Employee: Allen      Dept: 30 Employee: Ward       Dept: 30 Employee: Jones      Dept: 20 Employee: Martin     Dept: 30 Employee: Blake      Dept: 30 Employee: Clark      Dept: 10 Employee: Scott      Dept: 20 Employee: King       Dept: 10 Employee: Turner     Dept: 30 Employee: Adams      Dept: 20 Employee: James      Dept: 30 Employee: Ford       Dept: 20 Employee: Miller     Dept: 10 14 rows selected.

In the above query, there are two columns in the output: the string literal ‘Employee: ’ concatenated with the result of a string function on employee name and the string literal ‘Dept: ’ concatenated with the department number of the employee. Notice how the case of a string is preserved within the single quotes. This example demonstrates both the concatenation operator || and the CONCAT function. Which you use depends on how many strings are to be concatenated, as well as programming style. If you have more than two or three strings to concatenate, using vertical bars is more readable than using the CONCAT function over and over. However, if you are dealing with translating your queries from one character set to another on a different platform, vertical bars may not translate correctly; in this case, using the CONCAT function would be the best option for concatenating any number of strings.

Numeric Literals

Numeric literals in Oracle are very straightforward and are similar to what is allowed in many programming languages: the digits 0–9, an optional decimal point, an optional sign, and an optional exponent using the letter E with its own optional sign. Here are some valid numeric literals:

  • 1.456

  • –.01

  • 00000052

  • +12.10

  • –3.774E–16

numeric literal

A constant that can consist of numeric digits, plus the characters +, -, ., and E.

Numbers are stored internally in scientific notation, with up to 20 bytes for the mantissa and 1 byte for the exponent. This results in a maximum precision of up to 38 digits.

Operators and Operator Precedence

Operator precedence specifies the order in which the operators are applied to the arguments of a mathematical expression when there is more than one operator in the expression. Think back to your middle school algebra class when you had to answer questions such as “A man bought 20 chickens and ducks, with a $2 discount per chicken and 50 cent discount per duck…” and you’ll probably remember a few things about the order in which you had to evaluate an expression, once you figured out why a man was buying the chickens and ducks.

For example, the expression 5 * 6 + 10 is typically evaluated in most programming languages by multiplying 5 by 6, then adding 10 to the result. The expression 10 + 5 * 6 is typically evaluated in a similar manner. Because multiplication has a higher precedence than addition, 5 is multiplied by 6 first, then 10 is added to the result. If you want to add 10 to 5 first, then multiply that result by 6, write the expression with parentheses to override the assumed precedence: (10 + 5) * 6.

For operators that have an equal precedence, such as addition and subtraction or multiplication and division, the expression is evaluated left to right. The expression 10 / 6 * 5 is evaluated by dividing 10 by 6 first, then multiplying the result by 5. When two operators have the same precedence, it’s a good idea to use parentheses to eliminate any possible ambiguity: (10 / 6) * 5.

The rules for operator and conditional operator precedence in Oracle SQL are very similar to the rules in other programming languages such as C++ and Visual Basic. All standard operators have precedence over conditional operators.

Oracle’s standard and conditional operators are presented in Table 3.1, listed in order of precedence (from highest to lowest).

Table 3.1: Standard and Conditional Operators and Precedence

Operator/Conditional

Description

+, - (unary), PRIOR

Positive, negative, tree traversal

*, /

Multiplication, division

+, - (binary), ||

Addition, subtraction, concatenation

=, !=, <, >, <=, >=

Comparison operators

IS [NOT] NULL, LIKE, [NOT] BETWEEN, [NOT] IN, EXISTS, IS OF

SQL-specific comparison operators

**, NOT

Exponentiation, logical negation

AND

True if both operands are true

OR

True if either operand is true

UNION, UNION ALL, INTERSECT, MINUS

Set operators

The use of the standard and conditional operators will be explained throughout the rest of this book.




Oracle9i DBA JumpStart
Oracle9i DBA JumpStart
ISBN: 0782141897
EAN: 2147483647
Year: 2006
Pages: 138
Authors: Bob Bryla

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net