---

Functions

---

Functions is sub program which is used to perform some operations

Functions are classified into two type:

1: System Defined Functions 2: User Defined Functions

System Defined Functions A Functions defined by the system is called system Defined Functions.

User Defined Functions A Functions defined by the user is called User Defined Functions.

In Oracle there are three types of Functions.

1: Single Row Functions: Single Row Functions return a value for every row that is processed in a query.

2: Group Functions: The Group functions are the group of rows of data based on the values returned by the query.

The Group Functions are used to calculate aggregate values like total or average, which return just one total or one average values after processing a group of rows.

Group Functions or Multiple Row Functions These are classified into following type

1. SUM()

2. AVG()

3. MIN()

4. MAX()

5. COUNT()

3: Null Functions: These functions are used on NULL values.

1: Single Row Functions

2: Numeric Functions

3: Character Function

4: Date Functions

5: Conversion Functions

Null Functions: These functions are used on NULL values (NVL)

NVL Function ( NVL() ) This Function is used in cases where we want to consider Null values as Zero.

Example:

NVL(expression1, expression2)

If expression1 is null NVL will return expression2. If expression2 is null NVL will return expression1.

If expression1 and 2 to the data type of expression1 and then compares it.

Select empno, nvl(deptno, 0)from emp;
Select empno, nvl(comm, 0)from emp;

Null values and zeros are not equivalent.

---