Oracle functions(Miscellaneous , Conversion ,Group)

MISCELLANEOUS FUNCTIONS

Ø  Uid

Ø  User

Ø  Vsize

Ø  Rank

Ø  Dense_rank

a) UID

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

     Ex:

          SQL> select uid from dual;

       UID

----------

       319

b) USER

     This will returns the login’s user name.

     Ex:

           SQL> select user from dual;

USER

----------------

SAKETH

c) VSIZE

     This will returns the number of bytes in the expression.

     Ex:

          SQL> select vsize(123), vsize('computer'), vsize('12-jan-90') from dual;

VSIZE(123) VSIZE('COMPUTER') VSIZE('12-JAN-90')

-------------  -----------------------  ----------------------

         3                         8                                  9

d) RANK

     This will give the non-sequential ranking.

     Ex:

          SQL> select rownum,sal from (select sal from emp order by sal desc);

    ROWNUM    SAL

    ---------- ----------

         1       5000

         2       3000

         3       3000

         4       2975

         5       2850

         6       2450

         7       1600

         8       1500

         9       1300

        10       1250

        11       1250

        12       1100

        13       1000

        14        950

        15        800

     SQL> select rank(2975) within group(order by sal desc) from emp;

RANK(2975)WITHINGROUP(ORDERBYSALDESC)

---------------------------------------------------------

                                    4

d) DENSE_RANK

     This will give the sequential ranking.

    

Ex:

     SQL> select dense_rank(2975) within group(order by sal desc) from emp;

DENSE_RANK(2975)WITHINGROUP(ORDERBYSALDESC)

-----------------------------------------------------------------

                                          3

CONVERSION FUNCTIONS

Ø  Bin_to_num

Ø  Chartorowid

Ø  Rowidtochar

Ø  To_number

Ø  To_char

Ø  To_date

a) BIN_TO_NUM

     This will convert the binary value to its numerical equivalent.

     Syntax: bin_to_num( binary_bits)

     Ex:

          SQL> select bin_to_num(1,1,0) from dual;

BIN_TO_NUM(1,1,0)

------------------------

                6

Ø  If all the bits are zero then it produces zero.

Ø  If all the bits are null then it produces an error.

b) CHARTOROWID

     This will convert a character string to act like an internal oracle row identifier or rowid.

c) ROWIDTOCHAR

    This will convert an internal oracle row identifier or rowid to character string.

d) TO_NUMBER

    This will convert a char or varchar to number.

e) TO_CHAR

    This will convert a number or date to character string.

f) TO_DATE

    This will convert a number, char or varchar to a date.

GROUP FUNCTIONS

Ø  Sum

Ø  Avg

Ø  Max

Ø  Min

Ø  Count

Group functions will be applied on all the rows but produces single output.

a) SUM

     This will give the sum of the values of the specified column.

     Syntax: sum (column)

     Ex:

          SQL> select sum(sal) from emp;

  SUM(SAL)

   ----------

     38600

b) AVG

     This will give the average of the values of the specified column.

     Syntax: avg (column)

     Ex:

          SQL> select avg(sal) from emp;

   AVG(SAL)

   ---------------

   2757.14286

c) MAX

     This will give the maximum of the values of the specified column.

     Syntax: max (column)

     Ex:

          SQL> select max(sal) from emp;

    MAX(SAL)

   ----------

     5000

        d) MIN

     This will give the minimum of the values of the specified column.

     Syntax: min (column)

     Ex:

          SQL> select min(sal) from emp;

   MIN(SAL)

   ----------

     500

e) COUNT

     This will give the count of the values of the specified column.

     Syntax: count (column)

     Ex:

          SQL> select count(sal),count(*) from emp;

COUNT(SAL)   COUNT(*)

--------------    ------------

        14                     14