How To Use General Function in Oracle
General Functions
- Greatest(expr1, [expr2]…)
- Least(expr1, [expr2]…)
- NVL(col, value)
- Translate(char, find, new)
- Decode(c, v1, s1, v2, s2,….,d)
- UID
- User
Greatest(expr1, [expr2]…)
This function returns the greatest expression of its arguments. Each expression is implicitly converted to the type of expr1 before the comparisons are made. If expr1 is a character type, non-blank-padded character comparisons are used and the result has a data type varchar2.
Example
- Select Greatest(9, ‘6’, -2)
- from dual;
Least(expr1, [expr2]…)
This function returns the least value in the list of the expressions.
Example
- Select Least('pqrs', 'PQRS', 'p', 'XYZ') "Least"
- From dual;
NVL(col, value)
The NVL function helps in substituting a value in place of a null. The data type of the value to be substituted must match with the col data type.
Example
- Select emp_name, salary, salary+NVL(salary, 0) ”new gross”
- from employee;
Translate(char, find, new)
This function returns ‘char’ with each ‘find’ changed to ‘new’.
Example
- Select Emp_name, Translate(emp_name, ‘e’, ‘1’)
- from employee;
Decode(c, v1, s1, v2, s2,…d)
This function substitutes on a value-by-value basis, unlike translate.
Example
- Select emp_name, emp_job, decode(Emp_job, ‘salesmen’,’clerk’,’president’,’hr’)
- from employee;
UID
This functions returns an integer, which uniquely identifies the current database user. UID takes no arguments.
Example
- select UID
- From dual;
User
This function returns a varchar2 value, which contains the name of the current Oracle user. User takes no arguments.
Example
- Select user
- from dual;
Summary
Thus we learnt, general functions work with any data type and pertain to use nulls. We learnt, how to use these general functions in Oracle with examples.