Function in Oracle

Function

A function is a block of code that returns a single value. This block of code is known by its name. Users can create their own function using oracle pl-sql code.

Built-in/Sql Function

Built-in functions definition is created by oracle itself. you can directly use built-in function in select query to retrieve information.  

User Define Function

User can also create their own function using pl-sql code. 

Single Row Function

Single row function operate on single rows and return one result per row. 

Multiple Row Function

Multiple row functions also called aggregate function manipulate group of rows to give one result per group of rows.

Single Row Function

1. Character Function

Character Functions
Syntax	Descriptions
LOWER(string)	Convert every letter in a string to lower case. Ex. Lower(‘READER VIEWER’) Result reader viewer
UPPER(string)	Convert every letter in a string to upper case. Ex. UPPER(‘reader viewer’) Result READER VIEWER
INITCAP(string)	Capitalizes the first letter of a word or series of words. Ex. Initcap(‘reader viewer’) Result Reader Viewer
CONCAT(value1,value2)	Concates or combine string together. Ex. Concat(‘reader’, ‘viewer’) or ‘reader’ || ‘viewer’ Result readerviewer
SUBSTR(string,start,no of character)	Pick substring from string Ex. Substr(‘READERVIEWER’,7,6) R E A D E R V I E W E R 1 2 3 4 5 6 7 8 9 10 11 12 No of Character à 1 2 3 4 5 6 Result VIEWER
LENGTH(string)	Length of string Ex. Length(‘READERVIEWER’) Result 12
INSTR(string,Char,Occurance) *Occurance is optional	Finds numeric position of named character. R E A D E R V I E W E R 1 2 3 4 5 6 7 8 9 10 11 12    1st Occ.          2nd Occ.               3rd  Occ.    4th Occ. Function Result Instr(‘READERVIEWER’,’E’) Instr(‘READERVIEWER’,’E’,1) 1st Occurance of [E] at position 2 Instr(‘READERVIEWER’,’E’,2) 2nd Occurance of [E] at position 5 Instr(‘READERVIEWER’,’E’,3) 3rd Occurance of [E] at position 9 Instr(‘READERVIEWER’,’E’,3) 4th Occurance of [E] at position 11
LPAD(value,length,padding_char.)	Makes a string a certain length by adding a certain set of character to the left. Ex. Lpad(1000,10,#) Result ######1000
RPAD(value,length,padding_char.)	Makes a string a certain length by adding a certain set of character to the right. Ex. Rpad(1000,10,*) Result 1000******
TRIM(char from string);	Enables you to trim heading or trailling characters(or both) from a character string. Ex. Trim(‘R’ from ‘READER’) Result EADER
LTRIM(string,trim_char)	Trim left side character. Ex. Ltrim(‘###READER’,’#’) Result READER
RTRIM(string,trim_char)	Trim right side character. Ex. Rtrim(‘READER###’,’#’) Result READER
REPLACE(string,Search_string, Replace_string)	Searches a text expression for a character string and, if found, replaces it with a specified replacement string. Ex. Replace(‘READER’,’R’,’L’) Result LEADER

Character Functions Example
Syntax	Example	Result
LOWER(string)	Select LOWER('READER Viewer') from dual;	reader viewer
UPPER(string)	Select UPPER('reader Viewer') from dual;	READER VIEWER
INITCAP(string)	Select INITCAP('READER VIEWER') from dual;	Reader Viewer
CONCAT(value1,value2)	Select CONCAT(‘Reader’,’Viewer’) from dual;	ReaderViewer
SUBSTR(string,start,no of character)	Select SUBSTR(‘ReaderViewer’,1,6) from dual;	Reader
LENGTH(string)	Select LENGTH(‘ReaderViewer’) from dual;	12
INSTR(string,Charcter Value)	Select INSTR(‘ReaderViewer’,’V’) from dual;	7
LPAD(value,length,padding char.)	Select LPAD(1000,10,’#’) from dual;	######1000
RPAD(value,length,padding char.)	Select RPAD(1000,10,’*’) from dual;	1000******
TRIM(char from string);	Select TRIM(‘R’ from ‘READER’) from dual;	EADER
LTRIM(string,char)	Select LTRIM(‘###READER’,’#’) from dual;	READER
RTRIM(string,char)	Select RTRIM(‘READER###’,’#’) from dual;	READER
REPLACE(string,old char, new char)	Select REPLACE(‘READER’,’R’,’ L’) from dual;	LEADER

...

Ex. : LOWER,UPPER and INITCAP.

Ex. SUBSTR function.

Ex. LENGTH function in employee table.

Ex. RPAD ( Right Side padding)

Ex. LPAD (Left side padding)

Ex. TRIM, LTRIM and RTRIM functions

Ex. REPLACE function.

2. NUMBER functions

Number Functions
ABS(value)	Absolute value | Non negative value Ex. Abs(150), Abs(-150) Result 150
CEIL(value)	Smallest integer larger than or equal to value Ex. Ceil(2.1), ….,Ceil(2.9),Ceil(3) Result 3 Ex. Ceil(-2.1), ….,Ceil(-2.9) Result -2   ceil(-3) Result is -3
FLOOR(value)	Largest integer smaller than or equal to value Ex. Floor(2.1), ….,floor(2.9) Result 2    floor(3) Result is 3 Ex. Floor(-2.1), ….,floor(-2.9) Result -3   floor(-3) Result is -3
POWER(value,exponent)	Value raised to an exponent Power. Ex.  Power(2,4), Power(2,5) Result       16              32
ROUND(value,precision)	Rounding of value to precision Ex. Round(20.45,0),Round(20.456,2) Result               20             20.46
MOD(value,divisor)	Returns remainder of division. Ex. Mod(20,2),, Mod(20,7) Result          0                6
TRUNC(value,precision)	value truncated to precision Ex. Trunc(20.45,0), Trunc(20.456,2) Result               20               20.45

Ex. ABS (Absolute Value).

Ex. CEIL and FLOOR Functions

Ex. Power Function.

Ex. MOD Function.

Ex. Round Fucntion

Ex. TRUNC Function

3. DATE functions

Date Functions
MONTHS_BETWEEN(date1,date2)	Months between date1 and date2 months_between('28-SEP-2014','28-OCT-2014') à  -1 months_between('28-OCT-2014','28-SEP-2014') à   1 months_between('28-OCT-2014','30-SEP-2014') à 1.0645161
ADD_MONTHS(date,n)	Add Months to date Add_Months(SYSDATE,6)        à 28-MAR-15 Add_Months(’28-SEP-2014’,6) à 28-MAR-15
NEXT_DAY(date,’day’)	Gives date of next day after date. Day like Monday, Tuesday etc. Next_Day(sysdate,'FRIDAY')    à 03-OCT-14
LAST_DAY(date)	Gives date of last day of month that date is in. Last_Day(‘28-Sep-2014’)         à 30-SEP-14
ROUND(date,’format’)	Returns date rounded to the unit specified by the format. Round(System,’MONTH’) à 01-OCT-14   Sysdate ’28-SEP-2014’ Round(sysdate,'YEAR')    à 01-JAN-15
TRUNC(date,’format’)	Returns date with the time portion of the day truncated to the unit specified by the format model. Trunc(System,’MONTH’) à 01-SEP-14    Sysdate ’28-SEP-2014’ Trunc(sysdate,'YEAR')    à 01-JAN-14

Ex. MONTHS(BETWEEN and ADD_MONTHS functions.

Ex. NEXT_DAY and LAST_DAY Functions

Ex. ROUND and TRUNC functions

4. Conversion Functions.

Conversion Functions
TO_CHAR(number/date,’format’)	Converts a number or date value to a VARCHAR2 character string with format. To_Char(2467,’$9,999.000’) à $2,467.000 To_char(sysdate,'fmMonth DD, YYYY')    à September 29, 2014
TO_NUMBER(string,’format’)	Convert a character string containing digits to a number in the format. To_Number(‘756’,999) à 756 756 is in inverted comma is now converted to number.
TO_DATE(string,’format’)	Converts a string in a given format into an oracle date. To_Date(‘29/Sep/2014’,’DD/MON/2014’) à 29-Sep-2014

Ex. TO_CHAR function

Ex. TO_NUMBER function

Ex. TO_DATE function

5. General Functions

General Functions
NVL(expr1, expr2)	Conversion of expr1 null value to expr2 value. Nvl(null,100) à 100
NVL2(expr1,expr2,expr3)	if expr1 is not null then returns expr2 otherwise expr3 Nvl2(50,100,200)à 100 Nvl2(null,100,200)à 200
NULLIF(expr1,expr2)	Comparers two expression and returns null if they are equal otherwise returns first expression Nullif(100,100)à null Nullif(100,200)à100
COALESCE(expr1,…,exprn)	Returns the first non-null expression in the expression list. COALESCE(null,null,200,300,null)à200

Ex. NVL, NVL2, NULLIF, COALESCE

CASE Expression and DECODE function

Case and Decode functions provide  IF-THEN-ELSE logic in SQL statement.

CASE Expression
Syntax:
CASE <expr> 
       when <Cond1> then <resultant value or calculation1>
       when <Cond2> then <resultant value or calculation2>
       .....
       .....
       Else <else_expr>
END

EX.

DECODE Function
Syntax:
DECODE
(
<expr>,
         <Cond1>,<resultant value or calculation1>,
         <Cond2>,<resultant value or calculation2>,      
         ...........,
         ...........,
<else_expr>
)                   

Ex.