Oracle

Saturday, May 4, 2013

ORACLE ENVIRONMENT FUNCTIONS

SYS_CONTEXT
SYS_GUID
SYS_TYPEID
UID
USER
USERENV

ASCIISTR
BIN_TO_NUM
CAST
CHARTOROWID
COMPOSE
CONVERT
DECOMPOSE
HEXTORAW
NUMTODSINTERVAL
NUMTOYMINTERVAL
RAWTOHEX
RAWTONHEX
ROWIDTOCHAR
ROWIDTONCHAR
SCN_TO_TIMESTAMP

TIMESTAMP_TO_SCN
TO_BINARY_DOUBLE
TO_BINARY_FLOAT
TO_CHAR (character)
TO_CHAR (datetime)
TO_CHAR (number)
TO_CLOB
TO_DATE
TO_DSINTERVAL
TO_LOB
TO_MULTI_BYTE
TO_NCHAR (character)
TO_NCHAR (datetime)
TO_NCHAR (number)
TO_NCLOB
TO_NUMBER
TO_DSINTERVAL
TO_SINGLE_BYTE
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL
TO_YMINTERVAL
TRANSLATE ... USING
UNISTR

ORACLE CHARACTER FUNCTIONS RETURNING NUMBERS

ASCII
INSTR
LENGTH
REGEXP_INSTR

oracle nls character functions

NLS_CHARSET_DECL_LEN
NLS_CHARSET_ID
NLS_CHARSET_NAME

ORACLE CHARACTER FUNCTIONS RETURNING CHARS

CHR
CONCAT
INITCAP
LOWER
LPAD
LTRIM
NLS_INITCAP
NLS_LOWER
NLSSORT
NLS_UPPER
REGEXP_REPLACE
REGEXP_SUBSTR
REPLACE
RPAD
RTRIM
SOUNDEX
SUBSTR
TRANSLATE
TREAT
TRIM
UPPER

oracle numeric functions

ABS
ACOS
ASIN
ATAN
ATAN2
BITAND
CEIL
COS
COSH
EXP
FLOOR
LN
LOG
MOD
NANVL
POWER
REMAINDER
ROUND (number)
SIGN
SIN
SINH
SQRT
TAN
TANH
TRUNC (number)
WIDTH_BUCKET

ORACLE DATETIME FUNCTIONS

The datetime functions are:
ADD_MONTHS
CURRENT_DATE
CURRENT_TIMESTAMP
DBTIMEZONE
EXTRACT (datetime)
FROM_TZ
LAST_DAY
LOCALTIMESTAMP
MONTHS_BETWEEN
NEW_TIME
NEXT_DAY
NUMTODSINTERVAL
NUMTOYMINTERVAL
ROUND (date)
SESSIONTIMEZONE
SYS_EXTRACT_UTC
SYSDATE
SYSTIMESTAMP
TO_CHAR (datetime)
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_DSINTERVAL
TO_YMINTERVAL
TRUNC (date)
TZ_OFFSET

oracle object reference functions

MAKE_REF
REF
REFTOHEX
VALUE

ORACLE MODEL FUNCTIONS

CV
ITERATION_NUMBER
PRESENTNNV
PRESENTV
PREVIOUS

ORACLE BITAND FUNCTION

BIT_AND FUNCTION DOES ANDING OF 2 BINARY VALUES

syntax: BIT_AND(EXPR1,EXPR2)

1 1 -> 1

1 0 -> 0

0 1 -> 0

0 0 -> 0

SQL> SELECT BITAND(1,0) FROM DUAL;

BITAND(1,0)
-----------
          0

SQL> SELECT BITAND(01,) FROM DUAL;
SELECT BITAND(01,) FROM DUAL
                 *
ERROR at line 1:
ORA-00936: missing expression

SQL> SELECT BITAND(0,1) FROM DUAL;

BITAND(0,1)
-----------
          0

SQL> SELECT BITAND(0,0) FROM DUAL;

BITAND(0,0)
-----------
          0

SQL> SELECT BITAND(1,1) FROM DUAL;

BITAND(1,1)
-----------
          1

ORACLE BIN_TO_NUM FUNCTION

                                                   bin_to_num converts binary value to Oracle Number.
SYNTAX: bin_to_num(expr1,expr2,.....,exprn);

QUERY: Display equivalent of 110 in decimal

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

BIN_TO_NUM(1,1,0)
-----------------
                6

QUERY: Display equivalent of 1010 in decimal

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

BIN_TO_NUM(1,0,1,0)
-------------------
                 10

QUERY: PL/SQL VARRAY has 5 binary numbers, converts into equivalent Number.

declare
type vab is varray(5) of number(1);
v_t vab:=vab(1,0,1,1,1);
n number;
begin
select bin_to_num(v_t(1),v_t(2),v_t(3),v_t(4),v_t(5)) into n from dual;
dbms_output.put_line(n);
end;
/

SQL> /
23

P/SQL procedure successfully completed.

ORACLE AVG FUNCTION

avg function calculates avg of the values supplied

QUERY: Calculate Average Weight of the Person.

SQL> desc person;
Name                                                  Null?    Type
----------------------------------------------------- -------- ------------
ID                                                             NUMBER(2)
NAME                                                           VARCHAR2(20)
DOB                                                            DATE
EMAIL                                                          VARCHAR2(20)
WEIGHT                                                         NUMBER(5,2)

SQL> select * from person;

        ID NAME                 DOB       EMAIL                    WEIGHT
---------- -------------------- --------- -------------------- ----------
         1 Peter                12-DEC-97 1@gmail.com                 100
         6 life style           11-SEP-97
         2 shyam purru          12-JAN-00 2@gmail.com                 111
         3 Jho                  01-NOV-02 3@gmail.com                 133
         4 Jhony                30-AUG-60 4@gmail.com              133.44
         5 pearso               21-APR-13 5@gmail.com              150.01

6 rows selected.

SQL> select avg(weight) from person;

AVG(WEIGHT)
-----------
     125.49

QUERY: GET AVERAGE SALARY FROM EACH DEPARTMENT

SQL> select DEPARTMENT_ID,AVG(SALARY) from hr.employees
2 group by DEPARTMENT_ID;

DEPARTMENT_ID AVG(SALARY)
------------- -----------
          100 8601.33333
           30        4150
                     7000
           90 19333.3333
           20        9500
           70       10000
          110       10154
           50 4376.08889
           80 8955.88235
           40        6500
           60        5760

           10        4400

12 rows selected.

ORACLE ABS FUNCTION

ABS FUNCTION RETURNS ABSOLUTE VALUE OF THE EXPRESSION

SYNTAX: abs(expr)

QUERY: get absolute value of -10

SQL> SELECT ABS(-10.22) FROM DUAL;

ABS(-10.22)
-----------
10.22

QUERY: get absolute value of '-10+20-30'

SQL> select abs(-10+20-30) from dual;

ABS(-10+20-30)
--------------
20

QUERY: get absolute value of dates.

SQL> select abs(months_between(to_date('1973-12-12','yyyy-dd-mm'),sysdate)) from dual;

MONTHS_BETWEEN(TO_DATE('1973-12-12','YYYY-DD-MM'),SYSDATE)
----------------------------------------------------------
472.7595

QUERY: Display absolute values of variable.

SQL>declare n number;

SQL>exec :n:=-10;

SQL>select :n,abs(:n) from dual;

:n abs(:n)

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

-10 10

Friday, May 3, 2013

ORACLE ROUND FUNCTION

Round function rounds the number into nearest integer

round(decimal number, n);

SQL>select round(17.7) from dual; --rounds to 18

SQL> select round(17.49) from dual; --rounds to 17.

Note: the number >.5 will be rounded to next integer <0.5 rounded to lowest number.

SQL>select round(17.7,1) from dual;

SQL> select round(17.71,1) from dual;

ROUND(17.71,1)
--------------
          17.7

SQL> select round(17.7,1) from dual;

ROUND(17.7,1)
-------------
         17.7

SQL> select round(17.7,10) from dual;

ROUND(17.7,10)
--------------
          17.7

SQL> select round(17.9,10) from dual;

ROUND(17.9,10)
--------------
          17.9

SQL> select round(17.99,10) from dual;

ROUND(17.99,10)
---------------
          17.99

SQL> select round(17.999,1) from dual;

ROUND(17.999,1)
---------------
             18

SQL> select round(17.9,1) from dual;

ROUND(17.9,1)
-------------
         17.9

SQL> select round(17.99,1) from dual;

ROUND(17.99,1)
--------------
            18

SQL> select round(17.56,1) from dual;

ROUND(17.56,1)
--------------
          17.6

SQL> select round(17.55,1) from dual;

ROUND(17.55,1)
--------------
          17.6

SQL> select round(17.49,1) from dual;

ROUND(17.49,1)
--------------
          17.5

ORACLE SYSTIMESTAMP

systimestamp returns OS Date & time along with timezone on which oracle DB runs.

--Display Date and Time with timezone

SQL>select systimestamp from dual;

--Display only Date from systimestamp

SQL> select trunc(systimestamp) from dual;

SQL> select to_char(systimestamp,'yyyy-mm-dd') from dual;

--Display only Time from systimestamp

SQL> select systimestamp-trunc(systimestamp) from dual;

oracle trunc number function

truncates the specified value to specified decimal places.

SYNTAX: trunc(n1,n2)

it truncates n1 by n2 decimal places

for ex: trunc(15.71, 1) ->outputs 15.7

SQL> select trunc(15.71) from dual;

TRUNC(15.71)
------------
15

*Default 0.

SQL> select trunc(15.71,-1) from dual;

TRUNC(15.71)
------------
10

GET Fractional PORTION OF THE REAL VALUE.

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

SQL> select 15.71-trunc(15.71,0) from dual;

15.71-TRUNC(15.71,0)
--------------------
.71

ORACLE CONCAT FUNCTION

conact FUNCTION CONCATENATE 2 STRINGS.

syntax: CONCAT(STR1,STR2);

SQL>SELECT CONCAT('HELLO',' WORLD') FROM DUAL;

CONCAT('HELLO',' WORLD')

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

HELLO WORLD

SQL>
SELECT CONCAT('HELLO',NULL) FROM DUAL;

CONCAT('HELLO',NULL)

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

HELLO

SQL>SELECT CONCAT(NULL,' WORLD') FROM DUAL;

CONCAT(NULL,' WORLD')

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

WORLD

CONCATENATE 'I' , 'LOVE','PARIS' USING CONCAT

SQL> SELECT CONCAT(CONCAT('I','LOVE'),'PARIS') FROM DUAL;

CONCAT(CON
----------
ILOVEPARIS

ORACLE init cap function

InitCAP functions makes first letter in each word as Upper case.

Synatx: InitCap(char expr);

SQL>select initcap('hello world') from dual;

initcap('hello world')

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

Hello World

SQL> select initcap(first_name),initcap(last_name),initcap(first_name||' '||last_name) from employees

it makes first_name ,last_name,concatenating name and displays Upper case in each word.

ORACLE ASCII FUNCTION

oracle ascii function finds ascii value of the char.

SYNTAX: ascii(char)

SQL>select ascii('a') from dual;

ascii('a')

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

SQL> select ascii('ab') from dual;

ASCII('AB')
-----------
97

Note: ascii value of ab , it takes only first character. i.e a

Ascii value of blank is 32.

SQL>select ascii(' ') from dual;

ascii(' ')

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

DISPLAY ASCII VALUES OF THE STRING

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

declare
v varchar2(20):='hello oracle!';
c char;
begin
for i in 1..length(v)
loop
c:=substr(v,i,1);
dbms_output.put_line(c||'='||ascii(c));
end loop;
end;

SQL> /
h=104
e=101
l=108
l=108
o=111
=32
o=111
r=114
a=97
c=99
l=108
e=101
!=33

PL/SQL procedure successfully completed.

ASCII values for a-z : 97-122

ASCII values for A-Z: 65-90

ASCII values for 0-9: 48-57