Single Row Functions

Single row functions operate on a single row only then return one result for each row, there are many types of single-row functions.

Following are the types of Single Row Functions.

1. Number Function.
2. Date Function.
3. Character Function.
4. Conversion Function.
5. General Function.

Number Function

Always accept only numeric values and also return always numeric values. Number Function has three sub-functions like ROUND, TRUNC, MODE.

ROUND:- It’ll be round a value to the specified decimal point.

Ex1:- SELECT ROUND(456.5) FROM DUAL; 

This query will return 457 as an output because of the round function round that value which comes after the decimal point and 1 add-in before the decimal point value.

Ex2: SELECT ROUND(456.526,2) FROM DUAL; 

This query will return 456.53 as an output, in this case, 2 is the length of the decimal point that we want to print with value.

Ex3: SELECT ROUND(456.526,-1) FROM DUAL; 

This query will return 460 as an output, in this case, -1 is a length of before decimal point what we want to print.

TRUNC:- It’ll be truncated values specified decimal point.

Ex1. SELECT TRUNC(456.5) FROM DUAL; 

This query will return  456 as an output, trunc never be increment in value.

Ex2. SELECT TRUNC(456.426,2) FROM DUAL; 

This query will return 456.42 as output, 2 is a length of after decimal point value.

Ex3. SELECT TRUNC(456.426,-1) FROM DUAL; 

This query will return 450 as output, -1 is a length of before decimal point value.

MOD:- It’ll be always a return remainder of the division.

Ex1: SELECT MOD(100,3) FROM DUAL; 

Mod always return that value that remains after divination this query will return 1.

Date Function: 

Always work on DATE type Data type except for MOTHS_BETWEEN function because it returns a number.

These functions come under in Date Function.

• MONTHS_BETWEEN (Count number of months between two dates).

Ex1: SELECT MONTHS_BETWEEN(SYSDATE,HIREDATE) RESULT FROM OCP; 

This will show you the number of months between in sysdate and hiredate.

Ex2. SELECT MONTHS_BETWEEN('06-SEP-2014','06-JAN-1989') FROM DUAL; 

This will show you the number of months between these two dates.

• ADD_MONTHS (Add number of months in date).

Ex: SELECT ADD_MONTHS(SYSDATE,6) FROM DUAL; 

This will show you a date which is after six months.

• NEXT_DAY (It’ll show next day of calendar date specified).

Ex1: SELECT NEXT_DAY(SYSDATE,'SUNDAY') FROM DUAL;
Ex2: SELECT NEXT_DAY('06-SEP-2014','MONDAY') FROM DUAL;

• LAST_DAY (It’ll show the last day of the month).

Ex1: SELECT LAST_DAY(SYSDATE) FROM DUAL;
Ex2: SELECT LAST_DAY('06-JAN-1989') FROM DUAL;

1. Conversion Function: It converts a value from one data type to another data type.
2. General Function: It has many types of sub-functions like NULLIF, NVL, NVL2, COALESCE, CASE, DECODE, these all functions have different functionality.

Read - How to create a SEQUENCE step by step