Single row functions operate on a single row only then return one result each row, there are many types of single-row functions.
Following are the types of Single Row Functions.
- Number Function.
- Date Function.
- Character Function.
- Conversion Function.
- General 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 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 a output, in this case -1 is a length of before decimal point what we want to print.
TRUNC:- It’ll be truncates a values specified decimal point.
Ex1. SELECT TRUNC(456.5) FROM DUAL;
This query will return 456 as a 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 return reminder of division.
Ex1: SELECT MOD(100,3) FROM DUAL;
Mod always return that value which is remain after divination this query will return 1.
Always work on DATE type Data type except MOTHS_BETWEEN function because it return a number.
These functions comes 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 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;
- Conversion Function: It converts a value from one data type to another data type.
- General Function: It has many types of sub-functions like NULLIF, NVL, NVL2, COALESCE, CASE, DECODE, these all functions have different functionality.