Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

Oracle Built in Functions

In this article we will learn about “Oracle Built in Functions” in which we learn how we can use certain types of functions like Numeric Functions, Character or Text Functions, Conversion Functions, etc in Oracle to perform operations with ease.

What is Function in Oracle?

A function is a subprogram that returns a single value. We must declare and define a function before invoking it. We can either declare or define it at the same time, or we can declare it first and then define it later in the same block.

Types of Functions in Oracle:

  • Single Row Functions: Single row or Scalar functions return a value for every row that is processed in a query.
  • Group Functions: These functions group the rows of data based on the values returned by the query. This is discussed in Oracle Group Functions. The group functions are used to find out aggregate values like sum or average, which return just one sum or one average value after processing a group of rows.

Types of Single Row Functions:

  • Numeric Functions: These are functions that accept numeric input and return numeric values.
  • Character or Text Functions: These are functions that accept character input and can return both character and number values.
  • Date Functions: These Functions takes input in the form of DATE Datatype(in format of Date) and outputs values of DATE
  • Datatype, except for the MONTHS_BETWEEN function, which returns a number.
  • Conversion Functions: These are functions that help us to convert a value in one datatype to another datatype. For Example: a null/zero value into an actual value, or a value from one form to another form like TO_NUMBER, TO_DATE, NVL_TO_CHAR, etc.
  • Aggregate Functions:Aggregate functions are used in Oracle to find aggregate of numbers or perform some simple operations like Sum (), Round (), MIN () etc.

Note: We can merge more than one function in an expression. This is called nesting of functions.

Concept of Dual Table in Oracle:

This is a single row and single column dummy table is stored in oracle (by default). This is used to perform mathematical calculations without using a valid table. This table is by default defined in Oracle Ex:

Select * from DUAL

Output:

DUMMY
-------
X

Ex:

Select 777 * 888 from Dual

Output:

777 * 888
---------
689976

Numeric Functions

Numeric functions are used to perform operations on numbers. They use numeric data as input and return numeric data as result.

Some of the Numeric functions are given below:

Function Name Return Value Query Output
ABS (x) Absolute value of the number 'x' ABS(1)ABS(-1) 1-1
CEIL (x) Integer value that is Greater than or equal to the number 'x' CEIL (2.83) CEIL (2.49) CEIL (-1.6) 33-1
FLOOR (x) Integer value that is Less than or equal to the number 'x' FLOOR (2.83) FLOOR (2.49) FLOOR (-1.6) 22-1
TRUNC (x, y) Truncates value of number 'x' up to 'y' decimal places ROUND (125.456, 1)ROUND (125.456, 0)ROUND (124.456, -1) 125.4125120
ROUND (x, y) Rounded off value of the number 'x' up to the number 'y' decimal places TRUNC (140.234, 2) TRUNC (-54, 1) TRUNC (5.7) TRUNC (142, -1) 140.23545140

These functions are used in database columns:

Ex: Let's consider the product table used in oracle joins. We can use ROUND function to round off the unit_pric to the nearest integer, if any product has prices in real fraction.

SELECT ROUND (unit_pric) FROM product11;

Character or Text Functions

Character or text functions are used to manipulate text strings. They takes strings or characters as raw data and can results both character and number values as processed data.

Some of the character or text functions are as given below:

Function Name Return Value Query Output
LOWER (string_value) All the letters in 'string_value' is converted to lowercase. LOWER('Good Morning') good morning
UPPER (string_value) All the letters in 'string_value' is converted to uppercase. UPPER('Good Morning') GOOD MORNING
INITCAP (string_value) All the letters in 'string_value' is converted to mixed case. INITCAP('GOOD MORNING') Good Morning
LTRIM (string_value, trim_text) All occurrences of 'trim_text' is removed from the left of 'string_value'. LTRIM ('Good Morning', 'Good) Morning
RTRIM (string_value, trim_text) All occurrences of 'trim_text' is removed from the right of'string_value' . RTRIM ('Good Morning', ' Morning') Good
TRIM (trim_text FROM string_value) All occurrences of 'trim_text' from the left and right of 'string_value' ,'trim_text' can also be of atmost one character long. TRIM ('o' FROM 'Good Morning') Gd Mrning
SUBSTR (string_value, m, n) Returns 'n' number of characters from'string_value' starting from the 'm'position. SUBSTR ('Good Morning', 6, 7) Morning
LENGTH (string_value) Number of characters in 'string_value'in returned. LENGTH ('Good Morning') 12
LPAD (string_value, n, pade_value) Returns 'string_value' left-padded with'pade_value' . The size of the whole given string will be of 'n' characters. LPAD ('Good', 6, '*') **Good
RPAD (string_value, n, pade_value) Returns 'string_value' right-padded with 'pade_value' . The size of the whole given string will be of 'n' characters. RPAD ('Good', 6, '*') Good**

For Example, we can use the above UPPER() text function with the column value as follows.

SELECT UPPER (product_name) FROM product;

Date Functions

These are the functions of Oracle that take values that are of datatype DATE as input and return values of data types DATE, except for the MONTHS_BETWEEN function, which returns a number as output.

Few date functions are as given below.

Function Name Return Value Query Output
ADD_MONTHS (date, n) Returns a date value after adding 'n'months to the date 'x'. ADD_MONTHS ('16-Sep-81', 3) 16-Dec-81
MONTHS_BETWEEN (x1, x2) Returns the number of months between dates x1 and x2. MONTHS_BETWEEN ('16-Sep-81', '16-Dec-81') 3
ROUND (x, date_format) Returns the date 'x' rounded off to the closest century, year, month, date, hour, minute, or second as specified by the 'date_format'. round(to_date ('22-AUG-03'),'YEAR') '01-JAN-04'
TRUNC (x, date_format) Returns the date 'x' lesser than or equal to the closest century, year, month, date, hour, minute, or second as specified by the 'date_format'. TRUNC (to_date ('22-AUG-03'),'YEAR') 01-JAN-13
NEXT_DAY (x, week_day) Returns the next date of the 'week_day'on or after the date 'x' occurs. NEXT_DAY ('01-Jun-08', 'Wednesday') 04-JUN-08
LAST_DAY (x) It is used to determine the number of days remaining in a month from the date 'x' specified. LAST_DAY ('01-Jun-08') 30-Jun-08
SYSDATE Returns the systems current date along with time. SYSDATE() 2006-04-12 13:47:44
NEW_TIME (x, zone1, zone2) Returns the date and time in zone2 if date 'x' represents the time in zone1. NEW_TIME ('01-Jun-08', 'IST', 'EST') 31-May-08

Conversion Functions

These are functions that help us to convert a value in one form to another form. For Ex: a null/zero value into an actual/formal value, or a value from one datatype to some other datatype like TO_NUMBER, NVL, TO_CHAR ,TO_DATE.

Some of the conversion functions used in Oracle are:

Function Name Return Value Query Output
TO_CHAR (x [,y]) Converts Numeric and Date values to a character string value. It cannot be used for account since it is a string value. TO_CHAR (SYSDATE, 'Day, Month YYYY') Monday, June 2008
TO_DATE (x [, date_format]) Converts a valid Numeric and Character values to a Date value. Date is patterned to the rule specified by 'date_format'. TO_DATE ('01-Jun-08') 01-Jun-08
NVL (x, y) If 'x' is NULL, replace it with 'y'. 'x' and 'y' must be of the same datatype. NVL (null, 1) 1
DECODE (a, b, c, d, e, default_value) Checks the value of 'a', if a = b, then returns 'c'. If a = d, then returns 'e'. Else, returns default_value.

Aggregate Functions

This Functions are used to perform some simple operations on numeric data or numbers.

Function Name Return Value Query Output
SUM() compute sum SUM(Salary) 50000
COUNT() counts a number of rows Count(NO_of_Employee) 20
AVG() compute average AVG(Age) 500
MIN() compute minimum Min(Age) 25
MAX() compute maximum Max(Age) 45

Conclusion:

In this article we studied how we can perform certain functions like conversion, date, numeric and text functions with efficiency and quickly. It introduces user-defined aggregate functions evident that how to create and use them, either in single or in parallel, and represents how to work with large aggregation contexts and materialized views.



Have done the bachelor's in computer's and have done various courses on many front-end and back-end languages as Java, HTML, CSS, Javascript.

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login