Oracle SQL Functions

Oracle database supplied with many in built SQL functions. Oracle recommends to use in built SQL function rather than using a user defined functions. It perform better than user defined functions. So try to utilize in built SQL functions as much as you can. Some of the SQL functions really helps while writing complex SQLs.Most used SQL functions can be categorized as

1. Aggregate functions – Returns a single result from a  group of rows.
Examples :- AVG, MAX, MIN, COUNT,SUM etc.

2. Numeric functions – Accepts numeric input and returns numeric result.
Examples :- SIN, COS, TAN,  MOD, POWER, EXP etc.

3. Character functions – Returns character result from character input.
Examples :- CHR, LOWER, UPPER, TRIM etc.

4. Date functions – To manipulate date data.
Examples :- SYSDATE, ADD_MONTHS, TRUNC etc.

5. Conversion functions – To convert one data type to another data type
Examples :- TO_CHAR, TO_DATE, TO_NUMBER etc.

6. Analytical functions – It computes aggregate based on group of data
Examples :- RANK, DENSE_RANK, LAG etc.

Apart from these Oracle has few more functions like Collection functions, Large Object functions, XML functions, Data Mining functions, Hierarchical functions etc.

Some of the above listed functions which falls multiple categories. For example TRUNC is a Numeric as well as Date function. TRUNC(date) returns date value while TRUNC(number) returns numeric value.

Each individual SQL function explained in separate sections with examples.

 

Advertisements

Author: sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s