In SQL, a function is a reusable block of code that performs a specific task.
It takes zero or
more input parameters, processes them, and returns a value. Functions can be used to
simplify complex queries, perform calculations, and manipulate data.
Here are the key characteristics of functions in SQL:
1. Reusability: Functions can be called multiple times from different parts of a query or
application.
2. Input parameters: Functions can accept zero or more input parameters, which are used
to pass data to the function.
3. Return value: Functions return a value, which can be a scalar value, a record, or a
collection.
4. Deterministic: Functions should be deterministic, meaning that they return the same
result for the same input parameters.
Types of Functions in SQL
1. Built-in functions: provides a set of built-in functions, such as SUM, AVG, MAX, and
MIN, which can be used to perform common tasks.
Built-in functions are further classified into two types of functions:
a. Scalar Functions: Scalar functions are a type of function in SQL that return a
single value based on a single input value. They operate on a single row or value
and return a corresponding value.
Characteristics of Scalar Functions
1. Single input: Scalar functions take a single input value.
2. Single output: Scalar functions return a single output value.
3. Row-by-row operation: Scalar functions operate on each row
individually.
Function Description Syntax
return absolute value SELECT abs(value/column_name) FROM
ABS()
means positive value. table_name;
SELECT ceil(value/column_name) FROM
Ceil() Returns high value
table_name
SELECT floor(value/column_name)
Floor() returns low value
FROM table_name
SELECT power(value/column_name)
Power() Returns multiplied value
FROM table_name;
SELECT round(value/column_name)
Round() It round offs the value
FROM table_name;
Truncate removes decimal SELECT trunc(value/column_name)
Trunc()
points FROM table_name;
It gives the square root of SELECT sqrt(value/column_name)
SQRT()
the value FROM table_name;
SELECT
RANDOM() Gives the random values DBMS_RANDOM.VALUE(range)from
table_name;
gives the SELECT mod(value/column_name)
MOD()
reminder of the value FROM table_name;
SELECT
Returns text strings concat(‘value1’/column_name1,
Concat()
concatenated ‘value2’/column_name2 ) FROM
table_name;
Returns the location (only
SELECT instr(‘value’/column_name ,
Instr() the first occurance) of a
‘substring’) FROM table_name;
substring in a string.
Returns the number of
SELECT instr(‘value’/column_name)
Length() characters of the specified
FROM table_name;
string expression.
Returns a character string
SELECT rtrim(‘value’/column_name)
Rtrim() after truncating all trailing
FROM table_name;
blanks.
Returns a character
expression after it SELECT ltrim(‘value’/column_name)
Ltrim()
removes leading blanks. FROM table_name;
Replaces all occurrences
SELECT replace(‘value’/column_name ,
of a specified string value
Replace() characterToBeReplaced, Replacement)
with another string value.
FROM table_name;
Returns the reverse order SELECT reverse(‘value’/column_name)
Reverse()
of a string value. FROM table_name;
SELECT substr(‘value’/column_name,
Substr() Returns part of a text.
start, length) FROM table_name;
Returns a character
expression after
SELECT lower(‘value’/column_name)
Lower() converting uppercase
FROM table_name;
character data to
lowercase.
Returns a character
expression with lowercase SELECT upper(‘value’/column_name)
Upper()
character data converted FROM table_name;
to uppercase.
Returns a character SELECT initcap(‘value’/column_name)
Initcap()
expression, with the first FROM table_name;
letter of each word in
uppercase, all other
letters in lowercase.
Converts a date or SELECT to_char(‘value’/column_name,
To_char()
number to a string format) FROM table_name;
Converts a string value to SELECT to_date(‘value’/column_name,
To_date()
a date format) FROM table_name;
SELECT
Converts a string value to
To_number() to_number(‘value’/column_name,
a number
format) FROM table_name;
Returns the current
database system date.
This value is derived from
Sysdate() the operating system of SELECT sysdate from table_name;
the computer on which
the instance of Oracle is
running.
Returns a specified date SELECT
Add_months() with additional n month add_months(‘value’/column_name ,
numberOfMonths) FROM table_name;
Returns the count of SELECT months_between(‘value1’/
months between the column_name1,
Months_between()
specified startdate and ‘value2’/column_name2 ) FROM
enddate table_name;
b. Aggregate Functions: Aggregate functions are a type of function in SQL that
return a single value based on a group of input values. They operate on multiple
rows or values and return a summary value.
Characteristics of Aggregate Functions
1. Multiple inputs: Aggregate functions take multiple input values.
2. Single output: Aggregate functions return a single output value.
3. Group operation: Aggregate functions operate on a group of rows.
Function Description Syntax
SELECT AVG(column_name) FROM
AVG() Calculates the average value
table_name;
SELECT COUNT(column_name) FROM
COUNT() Counts the number of rows
table_name
Returns the first value in an SELECT FIRST(column_name) FROM
FIRST()
ordered set of values table_name
Returns the last value in an ordered SELECT LAST(column_name) FROM
LAST()
set of values table_name;
Retrieves the maximum value from SELECT MAX(column_name) FROM
MAX()
a column table_name;
Retrieves the minimum value from SELECT MIN(column_name) FROM
MIN()
a column table_name;
Calculates the total sum of values SELECT SUM(column_name) FROM
SUM()
in a numeric column table_name;
2. User-defined functions: You can create your own custom functions using PL/SQL, which
is 's procedural language.
Benefits of Using Functions in SQL
1. Improved readability: Functions can simplify complex queries by breaking them down into
smaller, more manageable pieces.
2. Reusability: Functions can be reused across multiple queries and applications, reducing
code duplication.
3. Easier maintenance: Functions can be modified or updated in one place, without affecting
other parts of the application.
4. Improved performance: Functions can be optimized for performance, reducing the
overhead of complex calculations or data manipulation.
Common Use Cases for Functions in SQL
1. Data validation: Functions can be used to validate data, such as checking for invalid dates
or formatting phone numbers.
2. Data transformation: Functions can be used to transform data, such as converting data
types or performing calculations.
3. Business logic: Functions can be used to implement business logic, such as calculating
taxes or discounts.
4. Reporting: Functions can be used to generate reports, such as summarizing data or
creating charts.
