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. It is also called as Mathematical functions.
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.
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. It is also called as Mathematical functions.
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.
No comments:
Post a Comment