Functions

 Functions are very powerful feature of SQL used to manipulate data

items .
 SQL functions are built into oracle database and are operated for use
in various appropriate SQL statements.
 If you call a SQL function with a null argument, then the SQL function
automatically returns null. The only SQL functions that do not
necessarily follow this behavior are CONCAT, NVL, REPLACE,
and REGEXP_REPLACE.
 Functions are similar to operators in that they manipulate data items
and return a result.

Prof. Sunny Bhadlawala

 SQL FUNCTION

Function
s
Arg 1 Function performs
action
arg2 Resullt
value

Arg n

Prof. Sunny Bhadlawala

Advantages of function

 Function can be used to perform complex calculations

on data.
 Functions can modify individual data items
 Function can very easily manipulate output for groups
of rows. Function can manipulate character as well as
numeric type of data.
 function can alter date formats for display

Prof. Sunny Bhadlawala

 TYPES OF FUNCTION
 There are two types of function:
 Single row functions (Scalar functions)
 Multiple row functions (Group Functions)

Prof. Sunny Bhadlawala

 Single row function
 These function operate on single rows only and
return one value for ach row, column name or an
expression. Single-row functions can be used in
SELECT. WHERE and ORDER by clauses.
 Syntax of using a single-row function is
function_name [(arg1, arg2,…..)]

 Where, function_name is the name of the

function. arg1,arg2 is any argument to be used by
the function. This can be represented by a user-
supplied constant value, variable value, column
name or an expression.
Prof. Sunny Bhadlawala
Types of single row functions

 There are different types of single row function:

 Character functions
 Number functions/arithmatic functions
 Date functions
 Conversion functions
 General functions
 Aggregate functions

Prof. Sunny Bhadlawala

Prof. Sunny Bhadlawala
String/character function

 1.LOWER:- returns char, with all letters in lowercase

Syntax:-lower(char)
e.g. select lower(‘IVAN BAYROSS’)”Lower” from dual;
Output=ivan bayross

2 .INITCAP:- returns a string with the first letter of each word

in upper case.
Syntax:- initcap(char)
e.g. select initcap(‘IVAN BAYROSS’)”Title case” from dual;
Output=Ivan Bayross

Prof. Sunny Bhadlawala

 3.UPPER:- returns char, with all letters in uppercase.
syntax:- upper(char)
e.g. select upper(‘ivan bayross’)”capitalized” from dual;
Output= IVAN BAYROSS

4.SUBSTR:-returns a portion of characters beginning at

character m, and going up to character n. if n is omitted the
result returned is up to the last character in the string. The first
position of char is 1.
Syntax:- substr(<string>,<start_position>,[<length>])

Prof. Sunny Bhadlawala

 Where string is source string
 start_position is the position for extraction. The first position in
the string is always 1.
 Length is the number of character is extract.
e.g. select substr(“secure”,3,4) ”Substring” from dual;
Output= cure

Prof. Sunny Bhadlawala

5.ASCII:-returns the number code that represents the specified
character. If more than one character is entered, the function
will return the value for the first character and ignore all the
characters after the first.
syntax:-ascii(character)
e.g. select ascii(‘a’) “Ascii 1”, ascii(‘A’)”ascii 2”,
ascii(‘cure’)”ascii “ from dual;
ouput= 97 65 99

Prof. Sunny Bhadlawala

 6 .COMPOSE :- return a unicode string. It can be a char,
ncahr, nvchar2, clob or nclob.
Syntax:-compose(<single>)
Below it is a listing of unistring values that can be combined
with other characters in compose function.
unistring value resulting character
UNISTR(‘\0300’) grave accent(‘)
UNISTR(‘\0301’) acute accent(`)
UNISTR(‘\0302’) circumflex(^)
UNISTR(‘\0303’) tilde(~)

Example Query : select compose(unistr('\0300')) from dual;

Prof. Sunny Bhadlawala

7. LENGTH:- returns a length of a word.
Syntax:- length(word)
e.g. select length(‘sharanam’) “length” from dual;
Output= 8

Prof. Sunny Bhadlawala

 8.LTRIM:- returns characters from the left of char with
initial characters removed upto the first character not in set.
Syntax:-ltrim(char[,set])
e.g. select ltrim(‘nisha’,’n’)”ltrim” from dual;
Output= isha

9. RTRIM:- returns char, with final characters removed after

the last character not in set. ‘set’ is optional, it defaults to
spaces.
Syntax:- rtim(char[,set])
e.g. select rtrim(‘sunila’,’a’)”rtrim” from dual;
Output= sunil

Prof. Sunny Bhadlawala

 10. TRIM:- remove all specified character either from
beginning or the ending of a string.
Syntax:- trim([leading|trailing|both[<trim_charac ter>
from]]<string>)
e.g. select trim(‘ hansel ‘)”trim both side” from dual;
Output=hansel
e.g. select trim(leading ‘x’ from ‘xxxhanselxxx’)”remove
prefixes” from dual;
Output= hanselxxx
e.g. select trim(both ‘x’ from ‘xxxhanselxxx’) from dual;
Output=hansel

Prof. Sunny Bhadlawala

 11.LPAD:- returns char1, left-papped to length n with the
sequence of character specified in char2.
Syntax:- lpad(‘char1,n[,char2])
E.g. select lpad(‘page1’,10,’*’)”lpad” from dual;
Output=*****page1

12. RPAD:- returns char1, right papped to length n with the

character specified in char2.
Syntax:- rpad(char1,n[,char2])
e.g. select rpad(ivan,10,’x’)”rpad” from dual;
Output=ivanxxxxxx

Prof. Sunny Bhadlawala

NUMERIC FUNCTIONS…..

• 1. ABS:- returns the absolute value of ‘n’.

syntax:- ABS(-15)
e.g. Select ABS(-15) “absolute” from dual;

• 2.POWER:- returns m raised to the nth power. n must be

an integer else an error is returned.
syntax:-power(m,n)
e.g. Select power(3,2)”raised” from dual;

Prof. Sunny Bhadlawala

 3. Round :-returns n, rounded to m places to the right of the
decimal point. If m is omitted, n is rounded to 0 places, m can
be negative to round off digits to the left of the decimal point.
m must be an integer
syntax:-round(n,[m])
e.g. select round(15.91,1) from dual;
output=15.9

4.SQRT:- returns square root of n.

syntax:-sqrt(n)
e.g. select sqrt(25) from dual;
output=5

Prof. Sunny Bhadlawala

 5.EXP:-returns e raised t the nth power where e=2.71828183
syntax:- exp(n)
E.g. select exp(5) from dual;
Output=148.413159

• 6.TRUNC:- returns a number truncated to a certain no. of

decimal places. The decimal place value is must be an integer.
Syntax:- trunc(no,[decimal_places])
e.g. select trunc(125.815,1)”trunc1” from dual;
Output= 125.8

Prof. Sunny Bhadlawala

DATE FUNCTIONS
 Oracle database stores date in an internal numeric format, representing
the century , Year, month, day hours, minutes, and seconds. The default
date display format is DD_MON_YY.
 Date function operates on oracle dates. These are the function that
takes values of DATE datatype as input and return values of date datatype
as output, except for the MONTHS_BETWEEN function, which returns a
number as output. Few date functions are as given below.

Prof. Sunny Bhadlawala

 SYSDATE
SYSDATE is a pseudo-column that returns the system’s current
date and time of type DATE. The SYSDATE can be used just as any
other column name. it takes no arguments. When used in distributed
SQL statements, SYSDATE returns the date and time of the local
database.
Example:
SELECT SYSDATE FROM DUAL;
output: O3-SEP-13

 ADD_MONTH(d,n)

This function adds or subtract months to or from date, it returns a date as result.
Example:
SELECT SYSDATE, ADD_MONTHS(SYSDATE,4) FROM DUAL;
OUTPUT:
SYSDATE ADD_MONTHS
------------ ------------------
03-APR-13
Prof. Sunny Bhadlawala 03-AUG-13
CONVERSION FUNCTIOSNS
 These are functions that help us to convert a value in one
form to another form. For example: a null value into an
actual value, or a value from one datatype to another
datatype . Few of the conversion functions available in
oracle are:
 TO CHAR(d,f)
This function converts the date ’d’ to character format ‘f’.
Example:
SELECT SYSDATE, TO_CHAR(SYSDATE,’DAY’) FROM
DUAL;
OUTPUT:
SYSDATE TO _CHAR(S
-------------- ------------- ----------------------------------------

03-SEP-13 TUESDAY
Prof. Sunny Bhadlawala
 TO_DATE(char,f)

This function converts the character string representing date into a date format
according to ‘f’ format specified. If no format is specified, then the default format
is DD-MON-YY.
Example:
SELECT SYSDATE, TO_DATE(‘JAN2007’,’MON YYYY’) FROM DUAL;
Output:
SYSDATE TO_DATE(
------------------- ---------------------------

03-SEP-13 01-JAN-07

 NVL(col,value)

This function helps in substituting a value in place of a null value. The data
type of the value to substitute must match with the col data type.
Example:
Select nvl(null,101) from dual;
Output:
Nul(null.101)
-------------------
101

Prof. Sunny Bhadlawala

General functions
 The general comparison functions determine the greatest
and least value from a set of values. Some general functions
also help to find the detail of current database user. Few of
the general functions available in oracle are:
Greatest(exp1,exp2,exp3….)
 This function returns the greatest value in the list of
expressions. Each expression is implicitly converted to the
type of expression (exp1) before the comparison are made .
 If the first expression is numeric, then the oracle determines
the argument with the highest numeric precedence,
implicitly converts the remaining arguments to that data
type before the comparison , and return that data type.
 If the first expression(exp1) is not numeric, then each
expression after the first is implicitly converted to the data
type of the first expression before the comparison.
Prof. Sunny Bhadlawala
Example:
SELECT GREATEST(33,55,66) FROM DUAL;
OUTPUT:
GREATEST(33,55,66)
-------------------------------
66
EXAMPLE:
SELECT GREATEST (‘R’,’A’,’Z’) FROM DUAL;
OUTPUT:
G
-----------

Z
EXAMPLE :
GREATEST(‘HARD’,’HARRY’,’HAROLD’) FROM DUAL;
OUTPUT:
GREAT
-----------

HARRY
Prof. Sunny Bhadlawala
 Least(exp1,exp2,exp3…)
This function returns the least value in the list of expressions. LEAST function behaves same like
Greatest , in which all expressions are implicitly converted to the data type of the first.
EXAMPLE:
Select least(44,22,7) from dual;
Output:
Least(44,22,7)
--------------------------
7

UID
THIS FUNCTION RETURNS AN INTEGER THAT UNIQUELY IDENTIFIES THE CURRENT DATABASE USER. UID
TAKES NO ARGUMENTS.
EXAMPLE:
SELECT UID FROM DUAL;
OUTPUT:
UID
------
57

Prof. Sunny Bhadlawala

 USER

This function returns a vaharchar2 value containing the

name of the current oracle user. User function takes no
arguments.
EXAMPLE:
SELECT USER FROM DUAL;
OUTPUT:
USER
-------_- - - - - - - - - - - - - - - - - - - - - - - - - - ---

SCOTT

Prof. Sunny Bhadlawala

AGGREGATE FUNCTIONS.....

1.AVG :- returns the average value

syntax:- Select avg(sal) from emp;

2.MIN :- return the minimum value of expr.

syntax :-select min(sal) from emp;

3.COUNT :- returns the no. of rows where expr. Is not null

syntax:-select count(acct_no) from acct_mstr;

Prof. Sunny Bhadlawala

4.COUNT(*) :- Returns the no. of rows in a table
including duplicates and those with null.
syntax:- select count(*)”no of records” from
acct_mstr;

5.MAX:- Returns the minimum value of expr.

syntax:-select max(curbal) from acct_mstr;

6.SUM:-Returns the sum of the value of ‘n’

syntax:-select sum(curbal) from acct_mstr;

Prof. Sunny Bhadlawala

Multiple row functions (Group Functions)

 SQL GROUP Functions

 Group functions are built-in SQL functions that operate
on groups of rows and return one value for the entire
group. These functions are: COUNT, MAX, MIN, AVG,
SUM, DISTINCT
 SQL COUNT (): This function returns the number of rows
in the table that satisfies the condition specified in the
WHERE condition. If the WHERE condition is not
specified, then the query returns the total number of
rows in the table.

Prof. Sunny Bhadlawala

 SELECT COUNT (*) FROM employee
WHERE dept = 'Electronics';
 SELECT COUNT (*) FROM employee;
 SELECT DISTINCT dept FROM employee;
 SELECT COUNT (DISTINCT name) FROM employee;
 SELECT MAX (salary) FROM employee;
 SELECT MIN (salary) FROM employee;
 SELECT AVG (salary) FROM employee;
 SELECT SUM (salary) FROM employee;

Prof. Sunny Bhadlawala