MISCELLANEOUS FUNCTIONS
a) Uid
b) User
c) Vsize
d) Rank
e) 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
--------
RAM
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
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
a) Bin_to_num
b) Chartorowid
c) Rowidtochar
d) To_number
e) To_char
f) 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
1) If all the bits are zero then it produces zero.
2) 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 varchar2 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 varchar2 to a date.
a) Uid
b) User
c) Vsize
d) Rank
e) 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
--------
RAM
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
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
a) Bin_to_num
b) Chartorowid
c) Rowidtochar
d) To_number
e) To_char
f) 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
1) If all the bits are zero then it produces zero.
2) 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 varchar2 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 varchar2 to a date.
No comments:
Post a Comment