Loading

Friday, October 28, 2011

ORACLE SQL TUTORIAL: 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



1 Responses to “ORACLE SQL TUTORIAL: MISCELLANEOUS FUNCTIONS”

Sridevi K said...
October 8, 2016 at 1:02 AM

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.


Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.