You have already seen a few pseudocolumns in some of the examples in this course. Let us take a closer look at them now.
A pseudocolumn acts like a column in your query but is actually a value assigned by the Oracle server when the query is executed.
Pseudocolumns are a convenient method of retrieving information about your query, your environment, and your data. In some cases, the pseudocolumn is assigned a different value for each row of the query (see the
ROWNUM
and
ROWID
pseudocolumns). In other cases, the pseudocolumn is assigned a constant value for the entire query (see the
USER
and
SYSDATE
pseudocolumns).
You can use pseudocolumns anywhere in a query that you use a column. You cannot, however, update a pseudocolumn, and there is no actual column in your table that corresponds to the pseudocolumn you use in your query.
Here is an example of a query that uses two pseudocolumns:
SYSDATE
(current date) and
ROWNUM
(sequence number of returned row).
The figure shows the query and its results.
Pseudocolumns are especially useful when inserting or updating rows.
For example, the pet store schema contains the PET_CARE_LOG
table in which you record the date, time, and the user that inserts a new comment. You could write an INSERT
command in SQL that uses the USER
and SYSDATE
pseudocolumns to assign the values for those two columns in the PET_CARE_LOG
.