ORACLE FUNCTIONS
Oracle functions serve the purpose of manipulating data items and returning a result. Functions are also capable of accepting user-supplied variables or constants and operating on them. Such variables or constants are called as arguments. Any number of arguments (or no arguments at all) can be passed to a function in the following format:
Function_name (argument1, argument2…)
ORACLE functions can be clubbed together depending upon whether they operate on a single row or a group of rows retrieved from a table. Accordingly, functions can be classified as follows:
Group functions (Aggregate functions)
Scalar functions (Single row functions)
GROUP FUNCTIONS (Aggregate functions)
Functions that act on a set of values are called as group functions. For example, SUM, is a function, which calculates the total of a set of numbers. A group functions returns single result row for a group of queried rows.
SCALAR FUNCTIONS (Single row functions)
Functions that act on only one value at a time are called as scalar functions. For example, LENGTH is a function, which calculates the length of one particular string value. A single row function returns one result for every row of a queried table or view.
Single row functions can be further grouped together by the data type of their arguments and return values. For example, LENGTH relates to the string data type. Functions can be classified corresponding to different data types as:
String functions : work for string data type
Numeric functions : work for number data type
Conversion functions : work for conversion of one data type to another
Date functions : work for date data type
AGGREGATE FUNCTIONS
(1) AVG
PURPOSE: -
Returns average value of ‘n’, ignoring null values.
SYNTAX: -
AVG (DISTINCT [ALL] n)
EXAMPLE: -
SQL> SELECT AVG (amount)"AVERAGE" FROM banking1;
OUTPUT: -
AVERAGE
---------
675098
(2)MIN
PURPOSE: -
Returns minimum value of ‘expr’
SYNTAX: -
MIN ([DISTINCT|ALL] expr)
EXAMPLE: -
SQL>SELECT MIN (amount)"MINIMUM BALANCE" FROM banking1;
OUTPUT: -
MINIMUM BALANCE
---------------
50000
(3) COUNT (expr)
PURPOSE: -
Returns the number of rows where ‘expr’ is not null.
SYNTAX: -
COUNT [(DISTINCT\ALL] expr)
EXAMPLE: -
SQL> SELECT COUNT (name)"name" FROM banking1;
OUTPUT: -
Name
---------
4
(4) COUNT (*)
PURPOSE: -
Returns the number of rows in the table, including duplicates and those with nulls.
SYNTAX: -
COUNT (*)
EXAMPLE: -
SQL> SELECT COUNT (*)"amount" FROM banking1;
OUTPUT: -
Amount
---------
4
(5) MAX
PURPOSE: -
Returns the maximum value of ‘expr’
SYNTAX: -
MAX [(DISTINCT | ALL] expr)
EXAMPLE: -
SQL> SELECT MAX (amount) " MAXIMUM " FROM banking1;
OUTPUT: -
MAXIMUM
---------------
90000
(6) SUM
PURPOSE: -
Returns sum of values of ‘n’
SYNTAX: -
SUM [DISTINCT | ALL] n)
EXAMPLE: -
SQL> SELECT SUM (amount) "TOTAL BALANCE" FROM banking1;
OUTPUT: -
TOTAL BALANCE
-------------
270000
NUMERIC FUNCTIONS
(1) ABS
PURPOSE: -
Returns the absolute value of ‘n’
SYNTAX: -
ABS (n)
EXAMPLE: -
SQL> SELECT ABS (-27) " ABSOLUTE" FROM dual;
OUTPUT: -
ABSOLUTE
---------
27
(2) POWER
PURPOSE: -
Returns ‘m’ raised to ‘nth’ power. ‘n’ must be an integer, else an error is returned.
SYNTAX: -
POWER (M, N)
EXAMPLE: -
SQL> SELECT POWER (7,2)"RAISED" FROM dual;
OUTPUT: -
RAISED
---------
49
(3) ROUND
PURPOSE: -
Returns ‘n’ rounded to ‘m’ places right of the decimal point. If ‘m’ is omitted, ‘n’ is returned to 0 places. ‘m’ can be negative to round off digits left of the decimal point. ‘m’ must be an integer.
SYNTAX: -
ROUND (n [, m])
EXAMPLE: -
SQL> SELECT ROUND (27.268,2) " ROUND" FROM dual;
OUTPUT: -
ROUND
---------
27.27
(4) SQRT
PURPOSE: -
Returns square root of ‘n’, if ‘n’<0, style="font-weight: bold;">SYNTAX: -
SQRT (n)
EXAMPLE: -
SQL> SELECT SQRT (900)" SQUARE ROOT" FROM dual;
OUTPUT: -
SQUARE ROOT
------------
30
(5)COS
PURPOSE: -
Returns the cosine of the ‘n’.
SYNTAX: -
COS (n)
EXAMPLE: -
SQL> SELECT COS (0) FROM dual;
OUTPUT: -
COS (0)
---------
1
(6) SIN
PURPOSE: -
Return the sin of the ‘n’.
SYNTAX: -
SIN (n)
EXAMPLE: -
SQL>SELECT SIN (0) FROM dual;
OUTPUT: -
SIN (0)
----------
0
STRING FUNCTIONS
(1) LOWER
PURPOSE: -
Returns char, with all letters in lowercase.
SYNTAX: -
LOWER (char)
EXAMPLE: -
SQL>SELECT LOWER (‘RIMS’) “LOWER” FROM dual;
OUTPUT: -
LOWER
----
rims
(2) UPPER
PURPOSE: -
Returns char, with all letters forced to uppercase.
SYNTAX: -
UPPER( char)
EXAMPLE: -
SQL> SELECT UPPER (‘Smarim’)”TITLE CASE” FROM dual;
OUTPUT: -
TITLE
------
SMARIM
(3) LENGTH
PURPOSE: -
Returns the length of character.
SYNTAX: -
LENGTH (char)
EXAMPLE: -
SQL> SELECT LENGTH (‘LOVABLE’)”LENGTH” FROM dual;
OUTPUT: -
LENGTH
---------
7
(4) LTRIM
PURPOSE: -
Removes characters from the left of char with initial characters removed up to the first character not in set.
SYNTAX: -
LTRIM (char {, set])
EXAMPLE: -
SQL> SELECT LTRIM ('ASMARAG','A')"LEFT TRIM" FROM dual;
OUTPUT: -
LEFT TRIM
------
SMARAG
(5) RTRIM
PURPOSE: -
Returns char, with final characters removed after the last character not in the set. ‘ Set’ is optional, it defaults to spaces.
SYNTAX: -
RTRIM ( char, [set])
EXAMPLE: -
SQL>SELECT RTRIM ('SMARAGA','A')"RIGTH TRIM" FROM dual;
OUTPUT: -
RIGTH TRIM
----------------
SMARAG
(6) LPAD
PURPOSE: -
Returns ‘char1’, left added to length ‘n’ with the sequences of characters in ‘char2’,’char2’ defaults to blanks.
SYNTAX: -
LPAD (char1, n, [, char2])
EXAMPLE: -
SQL> SELECT LPAD ('SMAR', 10,'*')"LPAD" FROM dual;
OUTPUT: -
LPAD
-----------------
******SMAR
(7) RPAD
PURPOSE: -
Returns ‘char1’, right-padded to length ‘n’ with the characters. In ‘char2’, replicated as many times as necessary. If ‘char2’, is omitted, right-pad is with blanks.
SYNTAX: -
RPAD (char1, n [char2])
EXAMPLE: -
SQL> SELECT RPAD ('SMAR', 10,'^')"RPAD" FROM dual;
OUTPUT: -
RPAD
----------------
SMAR^^^^^^
CONVERSION FUNCTIONS
(1) TO_NUMBER
PURPOSE: -
Converts ‘char’ a CHARACTER value containing a number, to a value of NUMBER data type.
SYNTAX: -
TO_NUMBER (char)
EXAMPLE: -
SQL>UPDATE banking1
SETamount=amount +
TO_NUMBER (SUBSTR ('$100',2,3));
(2) TO_CHAR
PURPOSE: -
Converts the value of NUMBER data type to a value of CHAR data type, using the optional format string. It accepts a number (n) and a numeric format (fmt) in which the number has to appear. If (fmt) is omitted, ‘n’ is converted to a char value exactly long enough to hold significant digits.
SYNTAX: -
TO_CHAR (n [, fmt])
EXAMPLE: -
SQL> SELECT TO_CHAR (17145,'$099,999')"CHAR" FROM dual;
OUTPUT: -
CHAR
---------
$017,145
(3) TO_CHAR (Date Conversion)
PURPOSE: -
Converts the value of DATE data type to CHAR value. It accepts date (date), as well as the format (fmt) in which the date has to appear. ‘fmt’ must be a date format. If ‘fmt’ is omitted, ‘date’ is converted to a character value in the default date format, i.e. “DD-MON-YY”.
SYNTAX: -
TO_CHAR (date [, fmt])
EXAMPLE: -
SQL> SELECT TO_CHAR (SYSDATE,'Month DD, YYYY’)
"NEW DATE FORMAT" FROM dual;
OUTPUT: -
NEW DATE FORMAT
-----------------
March 25,2008
DATE CONVERSION FUNCTIONS
The DATE data type is used to store date and time format. The DATE data type has special properties associated with it. It stores information about century, year, month, day, hour, minute and second for each date value.
The value in the column of a DATE data type is always stored in a specific DEFAULT format. This default format is ‘DD-MM-YY HH:MM: SS’. Hence, when a date has to be inserted in the date field, its value has to be specified in the same format. Also, values of DATE columns are displayed in the default format when retrieved from the table.
The same function can be used for inserting a date into a DATE field in a particular format. This can be achieved by specifying the date value, along with the format in which it is to be inserted. This function also allows part insertion of a DATE, for example, only the day and month portion of the value.
To enter the time portion of a date, the TO_DATE function must be used with a FORMAT MASK indicating the time portion.
(1)TO_DATE
PURPOSE: -
Converts a character field to a date field.
SYNTAX: -
TO_DATE ( char [, fmt ])
EXAMPLE: -
SQL> SELECT TO_DATE ('20-MAR-06','DD-MM-YY')"DISPLAY”
FROM dual;
OUTPUT: -
DISPLAY
---------
20-MAR-08
DATE FUNCTIONS
To manipulate and extract values from the date column of a table some date functions have been provided by ORACLE. These are: -
(1) ADD_MONTHS
PURPOSE: -
Returns date after adding the number of months specified with the functions.
SYNTAX: -
ADD_MONTHS (d,n)
EXAMPLE: -
SQL> SELECT ADD_MONTHS (SYSDATE, 4) FROM dual;
OUTPUT: -
ADD_MONTH
---------
25-JUL-06
(2) LAST_DAY
PURPOSE: -
Return the last date of the month specified with the function.
SYNTAX: -
LAST_DAY (d)
EXAMPLE: -
SQL> SELECT SYSDATE, LAST_DAY (SYSDATE) "LAST DAY"
FROM dual;
OUTPUT: -
SYSDATE LAST DAY
------------- -------------
25-MAR-06 31-MAR-06
Oracle functions serve the purpose of manipulating data items and returning a result. Functions are also capable of accepting user-supplied variables or constants and operating on them. Such variables or constants are called as arguments. Any number of arguments (or no arguments at all) can be passed to a function in the following format:
Function_name (argument1, argument2…)
ORACLE functions can be clubbed together depending upon whether they operate on a single row or a group of rows retrieved from a table. Accordingly, functions can be classified as follows:
Group functions (Aggregate functions)
Scalar functions (Single row functions)
GROUP FUNCTIONS (Aggregate functions)
Functions that act on a set of values are called as group functions. For example, SUM, is a function, which calculates the total of a set of numbers. A group functions returns single result row for a group of queried rows.
SCALAR FUNCTIONS (Single row functions)
Functions that act on only one value at a time are called as scalar functions. For example, LENGTH is a function, which calculates the length of one particular string value. A single row function returns one result for every row of a queried table or view.
Single row functions can be further grouped together by the data type of their arguments and return values. For example, LENGTH relates to the string data type. Functions can be classified corresponding to different data types as:
String functions : work for string data type
Numeric functions : work for number data type
Conversion functions : work for conversion of one data type to another
Date functions : work for date data type
AGGREGATE FUNCTIONS
(1) AVG
PURPOSE: -
Returns average value of ‘n’, ignoring null values.
SYNTAX: -
AVG (DISTINCT [ALL] n)
EXAMPLE: -
SQL> SELECT AVG (amount)"AVERAGE" FROM banking1;
OUTPUT: -
AVERAGE
---------
675098
(2)MIN
PURPOSE: -
Returns minimum value of ‘expr’
SYNTAX: -
MIN ([DISTINCT|ALL] expr)
EXAMPLE: -
SQL>SELECT MIN (amount)"MINIMUM BALANCE" FROM banking1;
OUTPUT: -
MINIMUM BALANCE
---------------
50000
(3) COUNT (expr)
PURPOSE: -
Returns the number of rows where ‘expr’ is not null.
SYNTAX: -
COUNT [(DISTINCT\ALL] expr)
EXAMPLE: -
SQL> SELECT COUNT (name)"name" FROM banking1;
OUTPUT: -
Name
---------
4
(4) COUNT (*)
PURPOSE: -
Returns the number of rows in the table, including duplicates and those with nulls.
SYNTAX: -
COUNT (*)
EXAMPLE: -
SQL> SELECT COUNT (*)"amount" FROM banking1;
OUTPUT: -
Amount
---------
4
(5) MAX
PURPOSE: -
Returns the maximum value of ‘expr’
SYNTAX: -
MAX [(DISTINCT | ALL] expr)
EXAMPLE: -
SQL> SELECT MAX (amount) " MAXIMUM " FROM banking1;
OUTPUT: -
MAXIMUM
---------------
90000
(6) SUM
PURPOSE: -
Returns sum of values of ‘n’
SYNTAX: -
SUM [DISTINCT | ALL] n)
EXAMPLE: -
SQL> SELECT SUM (amount) "TOTAL BALANCE" FROM banking1;
OUTPUT: -
TOTAL BALANCE
-------------
270000
NUMERIC FUNCTIONS
(1) ABS
PURPOSE: -
Returns the absolute value of ‘n’
SYNTAX: -
ABS (n)
EXAMPLE: -
SQL> SELECT ABS (-27) " ABSOLUTE" FROM dual;
OUTPUT: -
ABSOLUTE
---------
27
(2) POWER
PURPOSE: -
Returns ‘m’ raised to ‘nth’ power. ‘n’ must be an integer, else an error is returned.
SYNTAX: -
POWER (M, N)
EXAMPLE: -
SQL> SELECT POWER (7,2)"RAISED" FROM dual;
OUTPUT: -
RAISED
---------
49
(3) ROUND
PURPOSE: -
Returns ‘n’ rounded to ‘m’ places right of the decimal point. If ‘m’ is omitted, ‘n’ is returned to 0 places. ‘m’ can be negative to round off digits left of the decimal point. ‘m’ must be an integer.
SYNTAX: -
ROUND (n [, m])
EXAMPLE: -
SQL> SELECT ROUND (27.268,2) " ROUND" FROM dual;
OUTPUT: -
ROUND
---------
27.27
(4) SQRT
PURPOSE: -
Returns square root of ‘n’, if ‘n’<0, style="font-weight: bold;">SYNTAX: -
SQRT (n)
EXAMPLE: -
SQL> SELECT SQRT (900)" SQUARE ROOT" FROM dual;
OUTPUT: -
SQUARE ROOT
------------
30
(5)COS
PURPOSE: -
Returns the cosine of the ‘n’.
SYNTAX: -
COS (n)
EXAMPLE: -
SQL> SELECT COS (0) FROM dual;
OUTPUT: -
COS (0)
---------
1
(6) SIN
PURPOSE: -
Return the sin of the ‘n’.
SYNTAX: -
SIN (n)
EXAMPLE: -
SQL>SELECT SIN (0) FROM dual;
OUTPUT: -
SIN (0)
----------
0
STRING FUNCTIONS
(1) LOWER
PURPOSE: -
Returns char, with all letters in lowercase.
SYNTAX: -
LOWER (char)
EXAMPLE: -
SQL>SELECT LOWER (‘RIMS’) “LOWER” FROM dual;
OUTPUT: -
LOWER
----
rims
(2) UPPER
PURPOSE: -
Returns char, with all letters forced to uppercase.
SYNTAX: -
UPPER( char)
EXAMPLE: -
SQL> SELECT UPPER (‘Smarim’)”TITLE CASE” FROM dual;
OUTPUT: -
TITLE
------
SMARIM
(3) LENGTH
PURPOSE: -
Returns the length of character.
SYNTAX: -
LENGTH (char)
EXAMPLE: -
SQL> SELECT LENGTH (‘LOVABLE’)”LENGTH” FROM dual;
OUTPUT: -
LENGTH
---------
7
(4) LTRIM
PURPOSE: -
Removes characters from the left of char with initial characters removed up to the first character not in set.
SYNTAX: -
LTRIM (char {, set])
EXAMPLE: -
SQL> SELECT LTRIM ('ASMARAG','A')"LEFT TRIM" FROM dual;
OUTPUT: -
LEFT TRIM
------
SMARAG
(5) RTRIM
PURPOSE: -
Returns char, with final characters removed after the last character not in the set. ‘ Set’ is optional, it defaults to spaces.
SYNTAX: -
RTRIM ( char, [set])
EXAMPLE: -
SQL>SELECT RTRIM ('SMARAGA','A')"RIGTH TRIM" FROM dual;
OUTPUT: -
RIGTH TRIM
----------------
SMARAG
(6) LPAD
PURPOSE: -
Returns ‘char1’, left added to length ‘n’ with the sequences of characters in ‘char2’,’char2’ defaults to blanks.
SYNTAX: -
LPAD (char1, n, [, char2])
EXAMPLE: -
SQL> SELECT LPAD ('SMAR', 10,'*')"LPAD" FROM dual;
OUTPUT: -
LPAD
-----------------
******SMAR
(7) RPAD
PURPOSE: -
Returns ‘char1’, right-padded to length ‘n’ with the characters. In ‘char2’, replicated as many times as necessary. If ‘char2’, is omitted, right-pad is with blanks.
SYNTAX: -
RPAD (char1, n [char2])
EXAMPLE: -
SQL> SELECT RPAD ('SMAR', 10,'^')"RPAD" FROM dual;
OUTPUT: -
RPAD
----------------
SMAR^^^^^^
CONVERSION FUNCTIONS
(1) TO_NUMBER
PURPOSE: -
Converts ‘char’ a CHARACTER value containing a number, to a value of NUMBER data type.
SYNTAX: -
TO_NUMBER (char)
EXAMPLE: -
SQL>UPDATE banking1
SETamount=amount +
TO_NUMBER (SUBSTR ('$100',2,3));
(2) TO_CHAR
PURPOSE: -
Converts the value of NUMBER data type to a value of CHAR data type, using the optional format string. It accepts a number (n) and a numeric format (fmt) in which the number has to appear. If (fmt) is omitted, ‘n’ is converted to a char value exactly long enough to hold significant digits.
SYNTAX: -
TO_CHAR (n [, fmt])
EXAMPLE: -
SQL> SELECT TO_CHAR (17145,'$099,999')"CHAR" FROM dual;
OUTPUT: -
CHAR
---------
$017,145
(3) TO_CHAR (Date Conversion)
PURPOSE: -
Converts the value of DATE data type to CHAR value. It accepts date (date), as well as the format (fmt) in which the date has to appear. ‘fmt’ must be a date format. If ‘fmt’ is omitted, ‘date’ is converted to a character value in the default date format, i.e. “DD-MON-YY”.
SYNTAX: -
TO_CHAR (date [, fmt])
EXAMPLE: -
SQL> SELECT TO_CHAR (SYSDATE,'Month DD, YYYY’)
"NEW DATE FORMAT" FROM dual;
OUTPUT: -
NEW DATE FORMAT
-----------------
March 25,2008
DATE CONVERSION FUNCTIONS
The DATE data type is used to store date and time format. The DATE data type has special properties associated with it. It stores information about century, year, month, day, hour, minute and second for each date value.
The value in the column of a DATE data type is always stored in a specific DEFAULT format. This default format is ‘DD-MM-YY HH:MM: SS’. Hence, when a date has to be inserted in the date field, its value has to be specified in the same format. Also, values of DATE columns are displayed in the default format when retrieved from the table.
The same function can be used for inserting a date into a DATE field in a particular format. This can be achieved by specifying the date value, along with the format in which it is to be inserted. This function also allows part insertion of a DATE, for example, only the day and month portion of the value.
To enter the time portion of a date, the TO_DATE function must be used with a FORMAT MASK indicating the time portion.
(1)TO_DATE
PURPOSE: -
Converts a character field to a date field.
SYNTAX: -
TO_DATE ( char [, fmt ])
EXAMPLE: -
SQL> SELECT TO_DATE ('20-MAR-06','DD-MM-YY')"DISPLAY”
FROM dual;
OUTPUT: -
DISPLAY
---------
20-MAR-08
DATE FUNCTIONS
To manipulate and extract values from the date column of a table some date functions have been provided by ORACLE. These are: -
(1) ADD_MONTHS
PURPOSE: -
Returns date after adding the number of months specified with the functions.
SYNTAX: -
ADD_MONTHS (d,n)
EXAMPLE: -
SQL> SELECT ADD_MONTHS (SYSDATE, 4) FROM dual;
OUTPUT: -
ADD_MONTH
---------
25-JUL-06
(2) LAST_DAY
PURPOSE: -
Return the last date of the month specified with the function.
SYNTAX: -
LAST_DAY (d)
EXAMPLE: -
SQL> SELECT SYSDATE, LAST_DAY (SYSDATE) "LAST DAY"
FROM dual;
OUTPUT: -
SYSDATE LAST DAY
------------- -------------
25-MAR-06 31-MAR-06