 |
|
Advanced Oracle SQL: Using Multiple
Expressions
Oracle Tips by Laurent Schneider
|
Laurent
Schneider is considered one of the top Oracle SQL experts, and
he is the author of the book "Advanced
Oracle SQL Programming" by Rampant TechPress. The following
is an excerpt from the book.
1.
Compounded expression
An expression can be composed of multiple
expressions. The operators available in Oracle are addition,
subtraction, multiplication, division and concatenation.
The string concatenation operator is formed
by two pipe symbols:
SELECT
'X'||'Y'
FROM
DUAL;
'X
--
XY
Or:
SELECT
CONCAT('X','Y')
FROM
DUAL;
CO
--
XY
The internal string concatenation operator's
equivalent function is CONCAT.
The numerical operators are +, -, * and /:
SELECT
1+1,
3-2,
5*5,
10/4
FROM
DUAL;
1+1 3-2 5*5 10/4
---------- ---------- ---------- ----------
2 1 25 2.5
Those operators can be used with date and time
expressions:
SELECT
DATE '1582-10-15' – DATE '1582-10-04'
FROM
DUAL;
DATE'1582-10-15'-DATE'1582-10-04'
---------------------------------
1
The difference in days between the last day of
the Julian calendar to the first day of the Gregorian calendar:
SELECT
DATE '2000-02-29' + INTERVAL '1' MONTH
FROM
DUAL;
DATE'2000-02-29'+IN
-------------------
29.03.2000 00:00:00
One month after February 29th, 2000:
SELECT
2*INTERVAL '7' MONTH,
INTERVAL '1' / 7
FROM
DUAL;
2*INTERVAL'7'MONTH INTERVAL'1'DAY/7
------------------ ------------------------------
+000000001-02 +000000000 03:25:42.857142857
Two periods of seven months is 1 year + 2
months. A seventh of a day is close to 3 hours and 26 minutes.
2.
NULL
NULL is a special expression that represents the
absence of a value:
SELECT
TO_DATE(NULL),
TO_CHAR(NULL),
TO_NUMBER(NULL)
FROM
DUAL;
TO_DATE(NULL) TO_CHAR(NULL) TO_NUMBER(NULL)
------------- ------------- ---------------
<NULL> <NULL> <NULL>
To display nulls as something more visible than
an empty string in SQL*Plus, it is possible to set it to a specific
string like '<NULL>':
SET NULL
"<NULL>"
Null has no type in itself; it has also no
length and no value. When comparing NULL with NULL, the result is
NULL.
BEGIN
IF (NULL=NULL) IS NULL
THEN
DBMS_OUTPUT.PUT_LINE('(NULL=NULL) IS NULL');
END IF;
IF (NULL!=NULL) IS NULL
THEN
DBMS_OUTPUT.PUT_LINE('(NULL!=NULL) IS NULL');
END IF;
IF (NULL OR FALSE) IS NULL
THEN
DBMS_OUTPUT.PUT_LINE('(NULL OR FALSE) IS NULL');
END IF;
IF (NULL OR TRUE)=TRUE
THEN
DBMS_OUTPUT.PUT_LINE('(NULL OR TRUE) IS TRUE');
END IF;
IF (NULL AND TRUE) IS NULL
THEN
DBMS_OUTPUT.PUT_LINE('(NULL AND TRUE) IS NULL');
END IF;
IF (NULL AND FALSE)=FALSE
THEN
DBMS_OUTPUT.PUT_LINE('(NULL AND FALSE) IS FALSE');
END IF;
IF (NOT(NULL)) IS NULL
THEN
DBMS_OUTPUT.PUT_LINE('(NOT(NULL)) IS NULL');
END IF;
END;
/
(NULL=NULL) IS NULL
(NULL!=NULL) IS NULL
(NULL OR FALSE) IS NULL
(NULL OR TRUE) IS TRUE
(NULL AND TRUE) IS NULL
(NULL AND FALSE) IS FALSE
(NOT(NULL)) IS NULL
The logical operations with NULL are printed
out.
3.
Pseudo column
A pseudo column is an expression that has a
special meaning in Oracle but is neither a function nor a literal or
a column:
SELECT
ROWNUM,
DNAME
FROM
DEPT
ROWNUM DNAME
---------- --------------
1 ACCOUNTING
2 RESEARCH
3 SALES
4 OPERATIONS
ROWNUM is probably the most common pseudo
column. It is used to enumerate the rows.
A common usage of ROWNUM is to select a limited
number of rows:
SELECT
ENAME,
SAL
FROM
(
SELECT
*
FROM
EMP
ORDER BY SAL DESC
)
WHERE
ROWNUM<=5;
ENAME
SAL
---------- ----------
KING 5000
SCOTT 3000
FORD 3000
JONES 2975
BLAKE 2850
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 100 | 3
(34)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | |
| |
| 2 | VIEW | | 14 | 280 | 3
(34)| 00:00:01 |
|* 3 | SORT ORDER BY STOPKEY| | 14 | 518 | 3
(34)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP | 14 | 518 | 2
(0)| 00:00:01 |
--------------------------------------------------------------------------------
1 -
filter(ROWNUM<=5)
3 - filter(ROWNUM<=5)
The top 5 rows are returned:
SELECT
ENAME,
SAL
FROM
(
SELECT
E.*,
ROWNUM R
FROM
(
SELECT
*
FROM
EMP
ORDER BY
SAL DESC
) E
WHERE ROWNUM<=10
)
WHERE
R>5;
ENAME SAL
---------- ----------
CLARK 2450
ALLEN 1600
TURNER 1500
MILLER 1300
WARD 1250
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 330 | 3
(34)| 00:00:01 |
|* 1 | VIEW | | 10 | 330 | 3
(34)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | |
| |
| 3 | VIEW | | 14 | 280 | 3
(34)| 00:00:01 |
|* 4 | SORT ORDER BY STOPKEY| | 14 | 518 | 3
(34)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 518 | 2
(0)| 00:00:01 |
---------------------------------------------------------------------------------
1 - filter("R">5)
2 - filter(ROWNUM<=10)
4 - filter(ROWNUM<=10)
Note: WHERE ROWNUM>1 is always false,
because ROWNUM is evaluated after the WHERE condition, but before
GROUP BY, HAVING and ORDER BY. It is incremented only for returned
rows, so if ROWNUM 1 is not returned, no rows are returned.
The rows 6 to 10 are returned
ROWID is useful when accessing a single row of a
table:
SELECT
*
FROM
EMP
WHERE
ROWID
IN
(
SELECT
ROWID
FROM
(
SELECT
ROWID
FROM
EMP
ORDER BY
EMPNO
)
WHERE
ROWNUM=1
);
EMPNO ENAME
JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- --------- ----- ----- ------
7369 SMITH CLERK 7902 17-DEC-80 1300 20
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 49 |
3 (34)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 49 |
3 (34)| 00:00:01 |
| 2 | VIEW | VW_NSO_1 | 1 | 12 |
1 (0)| 00:00:01 |
| 3 | HASH UNIQUE | | 1 | 12
| | |
|* 4 | COUNT STOPKEY | | |
| | |
| 5 | VIEW | | 1 | 12 |
1 (0)| 00:00:01 |
| 6 | INDEX FULL SCAN | PK_EMP | 1 | 16 |
1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY USER ROWID| EMP | 1 | 37 |
1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
4 -
filter(ROWNUM=1)
The primary key index returns ROWID. The first
entry is selected to access the table. Note the operation TABLE
ACCESS BY USER ROWID.
Compare with:
SELECT
*
FROM
(
SELECT
*
FROM
EMP
ORDER BY
EMPNO
)
WHERE
ROWNUM=1;
EMPNO ENAME
JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- --------- ----- ----- ------
7369 SMITH CLERK 7902 17-DEC-80 1300 20
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 |
2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | |
| | |
| 2 | VIEW | | 1 | 87 |
2 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 14 | 518 |
2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | PK_EMP | 1 | |
1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
1 -
filter(ROWNUM=1)
The second query may be less efficient,
depending on the size of the table and the size of the index because
the STOPKEY operation is executed after the table access.
ROWID is often used by the database
administrator to identify the block or the datafile where a row is
physically stored; for example, in case of a block corruption. The
package DBMS_ROWID contains some useful conversion functions.
