General Functions in SQL

Functions in SQL enable we to perform feats such as determining the sum of a column or converting all the characters of a string to uppercase.

The following are the some of the functions supported by oracle

uid #

This function returns the integer value corresponding to the user currently logged in.

SQL> select uid from dual;

user #

This function returns the login’s user name,which is in varchar2 datatype.

SQL> select user from dual;

Rank #

RANK calculates the rank of a value in a group of values. The return type is NUMBER. Rows with equal values for the ranking criteria receive the same rank. Oracle Database then adds the number of tied rows to the tied rank to calculate the next rank. Therefore, the ranks may not be consecutive numbers. This function is useful for top-N and bottom-N reporting.

As an aggregate function, RANK calculates the rank of a hypothetical row identified by the arguments of the function with respect to a given sort specification. As an analytic function, RANK computes the rank of each row returned from a query with respect to the other rows returned by the query, based on the values of the value_exprs in the order_by_clause.

Aggregate Example #

The following example calculates the rank of a hypothetical employee in the sample table hr.employees with a salary of $15,500 and a commission of 5%:

SQL> SELECT RANK(15500, .05) WITHIN GROUP (ORDER BY salary, commission_pct)
"Rank"
FROM employees;
Rank
----------
105

Similarly, the following query returns the rank for a $15,500 salary among the employee salaries:

SQL> SELECT RANK(15500) WITHIN GROUP(ORDER BY salary DESC) "Rank of 15500"
FROM employees;
Rank of 15500
--------------
4

nvi #

This function is used in case where we want to consider Null values as zero.

SQL> select ename,nvl(comm,0) from emp;

vsize #

The function returns the number of bytes in the expression if expression is null it returns null.

SQL> select vsize('hello') from dual;

case #

CASE expression let you use IF-THEN-ELSE logic in SQL statements without having to invoke procedures.

SQL> select ename,job,sal case job when 'CLERK' then 1.10*sal when 'MANAGER'
then 1.15*sal when 'SALESMAN' then 1.20*sal else sal end from emp;

In this query Oracle searches for the first WHEN…THEN pair for which expr is equal to comparision_expr and return return_expr.If none of when..then pairs meets this condition and an else clause exists, then Oracle returns else_expr. otherwise oracle returns null.

DECODE function #

The decode function decodes an expression in a way similar to the IF-THEN-ELSE logic used in various languages. The DECODE function decodes expression after comparing it to each search valu. if the expression is same as search,result is returned.

SQL> select ename,job,sal,DECODE(job,'CLERK',1.10*sal,'MANAGER',1.15*sal,
SALESMAN',1.20*sal,sal) "revised salary" from emp;