Oracle SQL Tutorial Contents

select empno,ename,salary,comm,coalesce(salary+comm,salary,1000) “Net Sal” from emp;

ENAME     SALARY    COMM NET SAL
-----     ------    ---- -------
SMITH     1000      100 1100
SAMI      3000           3000
SCOTT                    1000
RAVI               200   1000

DECODE

DECODE(expr, searchvalue1, result1,searchvalue2,result2,..., defaultvalue)

Decode functions compares an expr with search value one by one. If the expr does not match any of the search value then returns the default value. If the default value is omitted then returns null.

Example

The following query returns the department names according the deptno. If the deptno does not match any of the search value then returns “Unknown Department”

select decode(deptno,10,'Sales',20,'Accounts,30,'Production,
40,'R&D','Unknown Dept') As DeptName from emp;

DEPTNAME
----------
Sales
Accounts
Unknown Dept.
Accounts
Production
Sales
R&D
Unknown Dept.

GREATEST

GREATEST(expr1, expr2, expr3,expr4...)

Returns the greatest expr from a expr list.

Example

select greatest(10,20,50,20,30) from dual;

GREATEST
--------
50

select greatest('SAMI','SCOTT','RAVI','SMITH','TANYA') from dual;

GREATEST
--------
TANYA

LEAST

LEAST(expr1, expr2, expr3,expr4...)

It is simillar to greatest. It returns the least expr from the expression list.

select least(10,20,50,20,30) from dual;

LEAST
--------
10

select least('SAMI','SCOTT','RAVI','SMITH','TANYA') from dual;

LEAST
--------
RAVI

NVL

NVL2(expr1,expr2)

This function is oftenly used to check null values. It returns expr2 if the expr1 is null, otherwise returns expr1.

Example

The following query returns commission if commission is null then returns 'Not Applicable'.

Select ename,nvl(comm,'Not Applicable') “Comm” from dual;

ENAME     COMM
------    ----
Scott     300
Tiger     450
Sami      Not Applicable
Ravi      300
Tanya     Not Applicable

NVL2

NVL2(expr1,expr2,expr3)

NVL2 returns expr2 if expr1 is not null, otherwise return expr3.

Example

The following query returns salary+comm if comm is not null, otherwise just returns salary.

select salary,comm,nvl2(comm,salary+comm,salary) “Income” from emp;

SALARY    COMM      INCOME
------    ----      ------
1000      100       1100
2000                2000
2300      200       2500
3400              3400

NULLIF

NULLIF(expr1, expr2)

Nullif compares expr1 with expr2. If they are equal then returns null, otherwise return expr1.

Example.

The following query shows old jobs of those employees who have changed their jobs in the company by comparing the current job with old job in oldemp table.

Select ename,nullif(e.job,o.job) “Old Job” from emp e, oldemp o where e.empno=o.empno;

ENAME         OLD JOB
-----         -------
SMITH         CLERK
SAMI
SCOTT         MANAGER

UID

Returns the current session ID of user logged on.

Example

select uid from dual;

UID
----
20

USER

Returns the username of the current user logged on.

select user from dual;

USER
---------
SCOTT

SYS_CONTEXT

SYS_CONTEXT returns the value of parameter associated with the context namespace. You can use this function in both SQL and PL/SQL statements.

EXAMPLE

The following query returns the username of the current user.

Select sys_context('USERENV','SESSION_USER') “Username” from dual;

USERNAME
---------
SCOTT

Similar to SESSION_USER parameter for namespace USERENV the other important parameters are

ISDBA :To check whether the current user is having DBA privileges or not.

HOST :Returns the name of host machine from which the client is connected.

INSTANCE :The instance identification number of the current instance

IP_ADDRESS: IP address of the machine from which the client is connected.

DB_NAME :Name of the database as specified in the DB_NAME initialization parameter

VSIZE

VSIZE(expr)

Returns the internal representation of expr in bytes.

Example

The following query return the representation of ename in bytes.

select ename,vsize(ename) as Bytes from emp;

ENAME     BYTES
------    ------
SCOTT     5
SAMI      4
RAVI      4
KIRAN     5