Function
Functions are created for manipulating the data and returning a value
Functions must be created before calling them. Otherwise, you will have the
following error [Function has not been defined yet!]
Functions are formerly created codes that reside in the database. They can be called
easily by typing only their names and parameters.
A function is a bunch of code created for reuse.
There are two types of functions
1. Single-Row Functions
2. Multiple-Row Functions
Single-Row Functions
The single row functions operate on single rows and return only one result per row
Accept one or more arguments and return one value
Return value for each returned
Can be used alone or nested
A column or an expression can be used as arguments
The returning data type can be different than the inputs data types
Can be used in SELECT, WHERE or ORDER BY clauses
Types of Single-Row Functions
Functions are categorized by the datatypes of their input parameters
[Link] Functions- Accept character values as input, and return character or numeric
values.
[Link] Functions- Accept numeric values as input, and return numeric values as output.
[Link] Functions- Operate on values of the DATE data type
[Link] Functions- Used to convert one data type to another
[Link] Functions- These functions take in any data type. But they are mainly used to
handle the NULL values.
1. Character Functions
Character functions take in character data as input and return character or numeric data as
output.
There are two different types of character function
Case Conversion functions
[Link] function
[Link] function
[Link] function
Character Manipulation function
[Link] function
[Link] function
[Link] function
[Link] function
[Link] function
[Link] function
[Link]/RPAD function
Case Conversion Functions
Case conversion functions are used to converts the character to uppercase to lowercase
character
[Link] function-Converts all input character to lower case characters
Example: LOWER (‘HELLO’)- hello
[Link] function-Converts all input character to upper case characters
Example: UPPER (‘smith’)- SMITH
[Link] function-Converts first letters of each word to uppercase and rest of them to
lowercase
Example: INITCAP (‘Adam smith’)- Adam Smith
Example File:
Character Manipulation Functions
[Link]-String-Used to extract the sub string from the string by taking the starting position and
length of sub string
Syntax: SUBSTR (source_string, position [, length])
Example: SUBSTR (‘Sql Course’, 1, 3) Sql
Select SUBSTR ('Shaik Shoaib Akther', 1, 5) from dual; Shaik
[Link]- Used to find the length of the string
Syntax: LENGTH (string)
Example: LENGTH (‘Sql Course’) 10
select length('shaik shoaib akther') from dual; 19
[Link]- Used to combine two strings into single string
Syntax: CONCAT (string1, string2)
Example: CONCAT (‘Sql Course’) SqlCourse
[Link]-Used to find the substring position in the string with the occurrence
Syntax: INSTR (string, substring [, position, occurrence])
Example: INSTR (‘Sql Course’, ‘o’) 6
select Instr('shaik shoaib akther',i) from dual; 4
[Link] is used to trim or remove the excess spaces on either side of the text in a string.
It has two variations:
LTRIM: TRIMS only the left spaces.
RTRIM:TRIMS only the right spaces.
For more details please refer the sql-file
Syntax: Trim(String)
Example: TRIM(‘ This is an example ‘)’This is an Example’
Select Trim(‘ Shaik Shoaib Akther ‘) from dual’Shaik Shoaib Akther
6. Replace lets you replace all the occurrences of a single character with another character.
Syntax: Replace(String ,letter to be replaced ,letter to replace with)
Example:
Replace(‘This ia an example’,’a’,’e’)’This is en exemple’
select replace('Shaik Shoaib Akther','a','o') from dual;’Shoik Shooib okther’
[Link]/Rpad: it is used to pad or add particular characters on either left(lpad) or right(Rpad)
of a string to make up desired number of characters in string.
Syntax:
Lpad(string ,length of string with padding ,character to pad)//Same with Rpad
Example:
select Lpad('Shaik Shoaib Akther',24,'*') from dual;’*****Shaik Shoaib Akther’
Example File:
Numeric Functions
Numeric functions accept numeric values as the input and return numeric values as the
output.
ROUND: Takes in a number and rounds it to the specified number of decimal places.
TRUNC: Truncates values to the specified number of decimal places.
CEIL: Returns the smallest integer number greater than or equal to a specified number.
FLOOR: Returns the highest integer number less than or equal to a specified number.
• MOD : Returns the remainder of division.
Oracle Numeric Functions
Example
Result
ROUND (number[,decimal])
ROUND (12.136,2)
12.14
TRUNC (number[, decimal])
TRUNC (12.136,2)
12.13
CEIL (number)
CEIL (2.67)
3
FLOOR (number)
FLOOR (2.67)
2
MOD (m, n) +
MOD (8,5)
3
Some other Numeric functions are:
ABS Returns the absolute value of n.
If n is 0 or a positive integer, returns n.
Otherwise, n is multiplied by -1.
SELECT ABS(-1) AS one
RESULT: one = 1
LN Natural logarithm. Computes the logarithm of its single
argument, the base of which is e.
SELECT LN(1.0) AS baseE
RESULT: baseE = e^1.0 = 0
LOG Logarithm. log(n, m) takes two arguments, where n is the
base, and m is the value you are taking the logarithm of.
Log(10,1000) = 3
SIGN Returns the sign of the argument as -1, 0, or 1, depending
on whether n is negative, zero, or positive. The result is
always a double.
SELECT SIGN(-12) AS x, SIGN(0) AS y, SIGN(12) AS
z
RESULT: x = -1, y = 0, z = 1
SQRT Returns the nonnegative square root of n.
SELECT SQRT(9) AS x
RESULT: x = 3
TRUNC Returns the number n truncated to m decimal places.
If m is 0, the result has no decimal point or fractional
part.
The unary (one argument) version drops the decimal
(non-integral) portion of the input. For example:
SELECT TRUNC(3.14159265) AS x
RESULT: x = 3
The binary (two argument) version allows you to set the
number of spaces at which the number is truncated. The
binary version always returns a double. For example:
SELECT TRUNC(3.14159265, 3) AS y
RESULT: y = 3.141
SIN The sine of n, where the angle of n is in radians.
SIN(3.14159/6) = 0.499999616987256
COS The cosine of n, where the angle of n is in radians.
COS(3.14159/3) = 0.500000766025195
TAN The tangent of n, where the angle of n is in radians.
TAN(3.14159/4) = 0.999998673205984
POWER Returns the value (as a double) of n raised to the power
of m.
Power(2,8) = 256
TO_DURATION Casts a string representation of a timestamp into a
number of milliseconds so that it can be used as a
duration.
TO_DOUBLE Casts a string representation of an integer as a double.
TO_INTEGER(boolean) Casts TRUE/FALSE to 1/0
Example File:
Nested Functions:
Nested function is the practice of calling a function inside another function where the output
of inner function is taken as input of outer function.
Example:
SELECT SUBSTR('John Smith', INSTR('John Smith', ' ')+1,LENGTH('John
Smith'))
output :smith
Example File:
Date values & Formats in Oracle:
DATE is the main datatype used in Oracle for storing the date values.
Oracle Database stores the date values in an internal numeric format.
It stores a seven-byte number that contains the century, year, month, day, and hour, minute,
and second.
There are many different date representations in Oracle like ('DD-MON-RR', 'DD-MM-YY',
'DD/MM/YYYY',etc.)
The RR date format is a different time format element that allows us to show 20th-century
dates in the 21st century using only 2 digits.
DATE Data Types in Oracle
DATE: The standard data type that stores date values in Oracle.
TIMESTAMP: This data type stores year, month, day, hour, minute, second as well as
fractional seconds.
TIMESTAMP WITH TIME ZONE: This data type is the same as TIMESTAMP, but it stores
the time zone along with it.
TIMESTAMP WITH LOCAL TIME ZONE: This data type is similar to TIMESTAMP WITH
TIME ZONE, but the stored time zone is the database's time zone.
Oracle Date Functions:
SYSDATE: returns the current date and time of the OS where the Oracle Database is
installed.
CURRENT_DATE: returns the current date and time of the place where the user's session is
logged in from.
SESSIONTIMEZONE: returns time zone of the user's session.
SYSTIMESTAMP: returns the date and time of the database.
CURRENT_TIMESTAMP: returns current date and time from user's session.
Example:
SELECT CURRENT_DATE, SESSIONTIMEZONE, SYSTIMESTAMP,
CURRENT_TIMESTAMP FROM DUAL;
Output:
Note:
You can also perform arithmetic operations on dates to find the date certain days before a date
by subtracting .or finding the no of days worked by an employee by subtracting hire_date
from current sysdate.
Example
SELECT employee_id, hire_date,sysdate, sysdate-hire_date worked_in_days
FROM employees;
Output:
Date Manipulation Functions
Date functions operate on dates and return dates, numbers or texts.
Date Functions
ADD MONTHS (date, n)
Adds months to a date.
MONTHS BETWEEN (datel, date2)
Number of months between 2 dates.
ROUND (date[,format])
Rounds a date/time value to a specified element.
TRUNC (date [format])
Truncates a date/time value to a specific element.
EXTRACT (date_component FROM date)
Extracts a specific time component from a date.
NEXT_DAY (date, day of week)
Returns the date of the next specified weekday.
LAST_DAY (date)
Returns the last day of the month.
Example:
Select ADD MONTHS('18-SEP-23', 3) from dual;18-DEC-23
Select MONTHS BETWEEN('03-SEP-20","10-FEB-20') from
dual;6.51612903225806451612903225806451612903
Select ROUND (sysdate, "MONTH") from dual;01-JUL-20
Select TRUNC(sysdate, "YEAR') from dual;01-JAN-20
Select EXTRACT (month FROM sysdate) from dual;6
Select NEXT_DAY('04-JUN-20', 'TUESDAY') from dual;09-JUN-20
Select LAST DAY ('04-JUL-20") from dual;31-07-20
Example File: