SQL Functions are developed into Oracle Database and are available to use in various appropriate SQL Statements. Functions in SQL and User defined function in Pl/SQL both are different.
In query, if you call a SQL function with an argument with different datatype other than expected by the function, then Oracle will convert the argument datatype to the expected datatype before performing the SQL function and if you call function with null argument, it will return null.
Categories of Functions:
1. Single_row_function 2. Aggregate_function 3. Analytic_function 4. Model_function 5. User_defined_function
Some of these functions are explained as following below.
- Single row function:
Single row functions are those functions which return a single result row for each row of queried table or view. This functions exists in Select lists, WHERE clause, START WITH, CONNECT BY clause and HAVING clause.
- Aggregate function:
While using Aggregate function it will returns single row result based on group of rows, instead of single rows.Aggregate function appears in Select lists and ORDER BY and HAVING clause.They are usually used with GROUP BY clause and SELECT Statements.
If you use GROUP BY clause, then Oracle applies aggregate function in the select list to all the rows in the queried table or view.
All the Aggregate functions except GROUPING and COUNT(*) ignores null values. You can also use NVL function in the argument to an aggregate function to substitute a value in place of null.
You can also nest aggregate functions.For example:-
SELECT AVG(MAX(salary) FROM employees GROUP BY department_id AVG(MAX(salary)) ---------------- 10925
Most frequently used Aggregate functions are AVG, COUNT, DENSE_RANK, MAX, MIN, RANK, SUM.
- Analytic function:
Analytic function calculate aggregate value based on group of rows.Difference between Analytic function and Aggregate function is they return multiple rows for each group. The group of rows is termed as window and it is defined by analytic_clause.
Analytic function are the last set of operations performed in a query except the final ORDER BY clause.
- Model Functions:
SELECTstatements, Model Functions can be used with model_clause.
Model Functions are:
- User defined function:
You can use User defied functions in PL/SQL or Java to provide functionality that is not available in SQL or SQL built in functions. SQL functions and User defined functions can be appear anywhere, that is, wherever an expression occur.
For example, It can be used in:
- Select list of
- Condition of
CONNECT BY, ORDER BY, START WITHand
Basically we use
CREATEFunction to create User defined functions in SQL.
- Select list of
GeeksforGeeks has prepared a complete interview preparation course with premium videos, theory, practice problems, TA support and many more features. Please refer Placement 100 for details
- Categories of End Users in DBMS
- Categories of Two Phase Locking (Strict, Rigorous & Conservative)
- SQL | Functions (Aggregate and Scalar Functions)
- Functions in PL/SQL
- SQL | Advanced Functions
- SQL | Numeric Functions
- SQL | String functions
- SQL | Date Functions (Set-1)
- SQL | Date Functions (Set-2)
- Aggregate functions in SQL
- SQL | Date functions
- SQL | NULL functions
- MySQL | Ranking Functions
- Mathematical functions in MySQL
- Useful Date and Time Functions in PL/SQL
If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to email@example.com. See your article appearing on the GeeksforGeeks main page and help other Geeks.
Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.