SQL functions are built into Oracle and are available for use in various appropriate SQL statements. You can also create your own function using PL/SQL.
Single-Row Functions
Single-row functions return a single result row for every row of a queried table or view. These functions can appear in select lists, WHERE clauses, START WITH and CONNECT BY clauses, and HAVING clauses.
Number Functions
Number functions accept numeric input and return numeric values. Most of these functions return values that are accurate to 38 decimal digits.
The number functions available in Oracle are:
ABS ACOS ASIN ATAN ATAN2 BITAND CEIL COS COSH EXP FLOOR LN LOG
MOD POWER ROUND (number) SIGN SIN SINH SQRT TAN TANH TRUNC (number)
MOD POWER ROUND (number) SIGN SIN SINH SQRT TAN TANH TRUNC (number)
ABS
ABS returns the absolute value of n.
The following example returns the absolute value of -87:
SELECT ABS(-87) "Absolute" FROM DUAL;
Absolute
----------
87
ACOS
ACOS returns the arc cosine of n. Inputs are in the range of -1 to 1, and outputs are in the range of 0 to pi and are expressed in radians.
The following example returns the arc cosine of .3:
SELECT ACOS(.3)"Arc_Cosine" FROM DUAL;
Arc_Cosine
----------
1.26610367
Similar to ACOS, you have ASIN (Arc Sine), ATAN (Arc Tangent) functions.
CIEL
Returns the lowest integer above the given number.Example:
The following function return the lowest integer above 3.456;select ciel(3.456) “Ciel” from dual;
Ciel
---------
4
FLOOR
Returns the highest integer below the given number.
Example:
The following function return the highest integer below 3.456;
select floor(3.456) “Floor” from dual;
Floor
------------
3
COS
Returns the cosine of an angle (in radians).
Example:
The following example returns the COSINE angle of 60 radians.
select cos(60) “Cosine” from dual;
SIN
Returns the Sine of an angle (in radians).
Example:
The following example returns the SINE angle of 60 radians.
select SIN(60) “Sine” from dual;
TAN
Returns the Tangent of an angle (in radians).
Example:
The following example returns the tangent angle of 60 radians.
select Tan(60) “Tangent” from dual;
Similar to SIN, COS, TAN functions hyperbolic functions SINH, COSH, TANH are also available in oracle.
MOD
Returns the remainder after dividing m with n.
Example
The following example returns the remainder after dividing 30 by 4.
Select mod(30,4) “MOD” from dual;
MOD
---------
2
POWER
Returns the power of m, raised to n.
Example
The following example returns the 2 raised to the power of 3.
select power(2,3) “Power” from dual;
POWER
---------
8
EXP
Returns the e raised to the power of n.
Example
The following example returns the e raised to power of 2.
select exp(2) “e raised to 2” from dual;
E RAISED TO 2
-------------
LN
Returns natural logarithm of n.
Example
The following example returns the natural logarithm of 2.
select ln(2) from dual;
LN
------------
LOG
Returns the logarithm, base m, of n.
Example
The following example returns the log of 100.
select log(10,100) from dual;
LOG
---------
2
ROUND
Returns a decimal number rounded of to a given decimal positions.
Example
The following example returns the no. 3.4573 rounded to 2 decimals.
select round(3.4573,2) “Round” from dual;
Round
------------
3.46
TRUNC
Returns a decimal number Truncated to a given decimal positions.
Example
The following example returns the no. 3.4573 truncated to 2 decimals.
select round(3.4573,2) “Round” from dual;
Round
------------
3.45
SQRT
Returns the square root of a given number.
Example
The following example returns the square root of 16.
select sqrt(16) from dual;
SQRT
---------
4
Character Functions
Character functions operate on values of dataype CHAR or VARCHAR.
LOWER
Returns a given string in lower case.
select LOWER(‘SAMI’) from dual;
LOWER
-------------
sami
UPPER
Returns a given string in UPPER case.
select UPPER(‘Sami’) from dual;
UPPER
------------------
SAMI
INITCAP
Returns a given string with Initial letter in capital.
select INITCAP(‘mohammed sami’) from dual;
INITCAP
------------------
Mohammed Sami
LENGTH
Returns the length of a given string.
select length(‘mohammed sami’) from dual;
LENGTH
------------
13
SUBSTR
Returns a substring from a given string. Starting from position p to n characters.
For example the following query returns “sam” from the string “mohammed sami”.
select substr('mohammed sami',10,3) from dual;
Substr
--------
sam
INSTR
Tests whether a given character occurs in the given string or not. If the character occurs in the string then returns the first position of its occurrence otherwise returns 0.
Example
The following query tests whether the character “a” occurs in string “mohammed sami”
select instr('mohammed sami','a') from dual;
INSTR
--------
4
REPLACE
Replaces a given set of characters in a string with another set of characters.
Example
The following query replaces “mohd” with “mohammed” .
select replace('ali mohd khan','mohd','mohammed') from dual;
REPLACE
---------
ali mohammed khan
TRANSLATE
This function is used to encrypt characters. For example you can use this function to replace characters in a given string with your coded characters.
Example
The following query replaces characters A with B, B with C, C with D, D with E,...Z with A, and a with b,b with c,c with d, d with e ....z with a.
select translate('interface','ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz','BCDEFGHIJKLMNOPQRSTUVWXYZAbcdefghijklmnopqrstuvwxyza') “Encrypt” from dual;
Encrypt
-----------
joufsgbdf
SOUNDEX
This function is used to check pronounciation rather than exact characters. For example many people write names as “smith” or “smyth” or “smythe” but they are pronounced as smith only.
Example
The following example compare those names which are spelled differently but are pronouced as “smith”.
Select ename from emp where soundex(ename)=soundex('smith');
ENAME
---------
Smith
Smyth
Smythe
RPAD
Right pads a given string with a given character to n number of characters.
Example
The following query rights pad ename with '*' until it becomes 10 characters.
select rpad(ename,'*',10) from emp;
Ename
----------
Smith*****
John******
Mohammed**
Sami******
LPAD
Left pads a given string with a given character upto n number of characters.
Example
The following query left pads ename with '*' until it becomes 10 characters.
select lpad(ename,'*',10) from emp;
Ename
----------
*****Smith
******John
**Mohammed
******Sami
LTRIM
Trims blank spaces from a given string from left.
Example
The following query returns string “ Interface “ left trimmed.
select ltrim(' Interface ') from dual;
Ltrim
--------------
Interface
RTRIM
Trims blank spaces from a given string from Right.
Example
The following query returns string “ Interface “ right trimmed.
select rtrim(' Interface ') from dual;
Rtrim
------------
Interface
TRIM
Trims a given character from left or right or both from a given string.
Example
The following query removes zero from left and right of a given string.
Select trim(0 from '00003443500') from dual;
Trim
----------
34435
CONCAT
Combines a given string with another string.
Example
The following Query combines ename with literal string “ is a “ and jobid.
Select concat(concat(ename,' is a '),job) from emp;
Concat
----------------
Smith is a clerk
John is a Manager
Sami is a G.Manager
Miscellaneous Single Row Functions
COALESCE
Coalesce function returns the first not null value in the expression list.
Example.
The following query returns salary+commision, if commission is null then returns salary, if salary is also null then returns 1000.
select empno,ename,salary,comm,coalesce(salary+comm,salary,1000) “Net Sal” from emp;
ENAME SALARY COMM NET SAL
----- ------ ---- -------
SMITH 1000 100 1100
SAMI 3000 3000
SCOTT 1000
RAVI 200 1000
DECODE
DECODE(expr, searchvalue1, result1,searchvalue2,result2,..., defaultvalue)
Decode functions compares an expr with search value one by one. If the expr does not match any of the search value then returns the default value. If the default value is omitted then returns null.
Example
The following query returns the department names according the deptno. If the deptno does not match any of the search value then returns “Unknown Department”
select decode(deptno,10,'Sales',20,'Accounts,30,'Production,
40,'R&D','Unknown Dept') As DeptName from emp;
DEPTNAME
----------
Sales
Accounts
Unknown Dept.
Accounts
Production
Sales
R&D
Unknown Dept.
GREATEST
GREATEST(expr1, expr2, expr3,expr4...)
Returns the greatest expr from a expr list.
Example
select greatest(10,20,50,20,30) from dual;
GREATEST
--------
50
select greatest('SAMI','SCOTT','RAVI','SMITH','TANYA') from dual;
GREATEST
--------
TANYA
LEAST
LEAST(expr1, expr2, expr3,expr4...)
It is simillar to greatest. It returns the least expr from the expression list.
select least(10,20,50,20,30) from dual;
LEAST
--------
10
select least('SAMI','SCOTT','RAVI','SMITH','TANYA') from dual;
LEAST
--------
RAVI
NVL
NVL2(expr1,expr2)
This function is oftenly used to check null values. It returns expr2 if the expr1 is null, otherwise returns expr1.
Example
The following query returns commission if commission is null then returns 'Not Applicable'.
Select ename,nvl(comm,'Not Applicable') “Comm” from dual;
ENAME COMM
------ ----
Scott 300
Tiger 450
Sami Not Applicable
Ravi 300
Tanya Not Applicable
NVL2
NVL2(expr1,expr2,expr3)
NVL2 returns expr2 if expr1 is not null, otherwise return expr3.
Example
The following query returns salary+comm if comm is not null, otherwise just returns salary.
select salary,comm,nvl2(comm,salary+comm,salary) “Income” from emp;
SALARY COMM INCOME
------ ---- ------
1000 100 1100
2000 2000
2300 200 2500
3400 3400
NULLIF
NULLIF(expr1, expr2)
Nullif compares expr1 with expr2. If they are equal then returns null, otherwise return expr1.
Example.
The following query shows old jobs of those employees who have changed their jobs in the company by comparing the current job with old job in oldemp table.
Select ename,nullif(e.job,o.job) “Old Job” from emp e, oldemp o where e.empno=o.empno;
ENAME OLD JOB
----- -------
SMITH CLERK
SAMI
SCOTT MANAGER
UID
Returns the current session ID of user logged on.
Example
select uid from dual;
UID
----
20
USER
Returns the username of the current user logged on.
select user from dual;
USER
---------
SCOTT
SYS_CONTEXT
SYS_CONTEXT returns the value of parameter associated with the context namespace. You can use this function in both SQL and PL/SQL statements.
EXAMPLE
The following query returns the username of the current user.
Select sys_context('USERENV','SESSION_USER') “Username” from dual;
USERNAME
---------
SCOTT
Similar to SESSION_USER parameter for namespace USERENV the other important parameters are
ISDBA :To check whether the current user is having DBA privileges or not.
HOST :Returns the name of host machine from which the client is connected.
INSTANCE :The instance identification number of the current instance
IP_ADDRESS: IP address of the machine from which the client is connected.
DB_NAME :Name of the database as specified in the DB_NAME initialization parameter
VSIZE
VSIZE(expr)
Returns the internal representation of expr in bytes.
Example
The following query return the representation of ename in bytes.
select ename,vsize(ename) as Bytes from emp;
ENAME BYTES
------ ------
SCOTT 5
SAMI 4
RAVI 4
KIRAN 5
Aggregate Functions
Aggregate functions return a single value based on groups of rows, rather than single value for each row. You can use Aggregate functions in select lists and in
ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle divides the rows of a queried table or view into groups.The important Aggregate functions are :
Avg Sum Max Min Count Stddev Variance
AVG
AVG( ALL /DISTINCT expr)
Returns the average value of expr.
Example
The following query returns the average salary of all employees.
select avg(sal) “Average Salary” from emp;
Average Salary
------------------------
2400.40
SUM
SUM(ALL/DISTINCT expr)
Returns the sum value of expr.
Example
The following query returns the sum salary of all employees.
select sum(sal) “Total Salary” from emp;
Total Salary
------------------------
26500
MAX
MAX(ALL/DISTINCT expr)
Returns maximum value of expr.
Example
The following query returns the max salary from the employees.
select max(sal) “Max Salary” from emp;
Maximum Salary
------------------------
4500
MIN
MIN(ALL/DISTINCT expr)
Returns minimum value of expr.
Example
The following query returns the minimum salary from the employees.
select min(sal) “Min Salary” from emp;
Minimum Salary
------------------------
1200
COUNT
COUNT(*) OR COUNT(ALL/DISTINCT expr)
Returns the number of rows in the query. If you specify expr then count ignore nulls. If you specify the asterisk (*), this function returns all rows, including duplicates and nulls.
COUNT never returns null.Example
The following query returns the number of employees.
Select count(*) from emp;
COUNT
------
14
The following query counts the number of employees whose salary is not null.
Select count(sal) from emp;
COUNT
------
12
STDDEV
STDDEV(ALL/DISTINCT expr)
STDDEV returns sample standard deviation of expr, a set of numbers.Example
The following query returns the standard deviation of salaries.
select stddev(sal) from emp;
Stddev
-------
1430
VARIANCE
VARIANCE(ALL/DISTINCT expr)
Variance returns the variance of
expr.Example
The following query returns the variance of salaries.
select variance(sal) from emp;
Variance
-------
1430
No comments:
Post a Comment