Pseudocolumn

Pseudocolumn In Oracle

A pseudocolumn behaves like a table column, but it's not actually stored in the table.

TYPES::

1.SYSDATE
2.SYSTIMESTAMP
3.USER
4.UID
5.ROWNUM
6.ROWID
7.NEXTVAL   --- Sequence Related Pseudo Columns
8.CURVAL --  Sequence Related Pseudo Columns


1.SYSDATE::

SYSDATE returns the current date set for the operating system on which the database resides. The datatype of the returned value is DATE.

Example::

SQL> SELECT SYSDATE FROM Dual;

SYSDATE
---------
10-JUN-16

2.SYSTIMESTAMP::

SYSTIMESTAMP is the built in function which returns the current database system date including fractional seconds and region time zone.

Example::

SQL> SELECT SYSTIMESTAMP FROM Dual;

SYSTIMESTAMP
-----------------------------------
10-JUN-16 07.20.13.909000 PM +05:30

3.USER::

The USER indicate in Which USER We are Currently loged In.

Example::

SQL> SELECT USER FROM Dual;

USER
-----
HR

4.UID::

The Oracle UID is a pseudo-column containing a numeric value identifying the current user. Calling the UID() function will return the ID number, the user who is currently logged in.

Example::

SQL> SELECT UID FROM Dual;

       UID
----------
        33

SQL> SELECT USER,UID FROM Dual;

USER                                  UID
------------------------------ ----------
HR                                     33

5.ROWNUM::

ROWNUM is a pseudocolumn returning a sequential number along with the rows retrieved.

Example::

SQL> SELECT ROWNUM "S.NO",Department_Id,Department_Name From Departments;

------------------------------------------------------------------------
      S.NO     DEPARTMENT_ID         DEPARTMENT_NAME
---------- ------------- -----------------------------------------------
         1            10                                Administration
         2            20                                Marketing
         3            30                                 Purchasing
         4            40                                 Human Resources
         5            50                                 Shipping
         6            60                                 IT
         7            70                                 Public Relations
         8            80                                 Sales
         9            90                                 Executive
        10           100                               Finance
        11           110                               Accounting
        12           120                               Treasury
        13           130                               Corporate Tax
        14           140                               Control And Credit
        15           150                               Shareholder Services

Example::

To display &n(th ) Maximum Salary 
=======================================================
SELECT MIN(SALARY)
FROM
  (
   SELECT ROWNUM,SALARY FROM
             (
SELECT SALARY FROM EMPLOYEES ORDER BY 1 DESC)
WHERE ROWNUM <= nth
   );
=======================================================

6.ROWID::

An Oracle server assigns each row in each table with a unique ROWID(Address ID) to identify the row in the table.

Example::

SQL> SELECT ROWID,Department_Name From Departments;
------------------                            -----------------------------
ROWID                                      DEPARTMENT_NAME
------------------                         -----------------------------
AAAC8/AAEAAAAA3AAA        Administration
AAAC8/AAEAAAAA3AAB       Marketing
AAAC8/AAEAAAAA3AAC       Purchasing
AAAC8/AAEAAAAA3AAD       Human Resources
AAAC8/AAEAAAAA3AAE       Shipping
AAAC8/AAEAAAAA3AAF       IT
AAAC8/AAEAAAAA3AAG       Public Relations

-----END-----