Function in Oracle
- Subprogram that returns a value
- Have a RETURN clause
- Stored in database and can be invoked or called by any anonymous block
- Generally take parameters
- Datatype specifier in parameter declaration must be unconstrained
- Has two parts
- Specifiction
begins with keyword FUNCTION, ends with RETURN clause
- Body
begins with keyword IS, ends with keyword END followed by optional function name
Syntax –
CREATE [OR REPLACE] FUNCTION <functionname> [(argument1, … argumentN)] RETURN datatype IS
[local declarations]
BEGIN
executable statements;
[EXCEPTION
exception handlers]
END [<functionname>];
argument stands for
variablename [IN|OUT|IN OUT] datatype [{:= | DEFAULT} value]
Format –
CREATE FUNCTION day_fn(mday NUMBER) RETURN CHAR IS
disp_day CHAR(15);
BEGIN
executable statements;
RETURN disp_day;
EXCEPTION
statements;
END day_fn;
Calling a Function
Can call a function as a PL/SQL statement
– Example
chardays := day_fn(3);
Can call a function as part of an expression
– Example
IF day_fn(3) = ‘TUESDAY’ THEN
statements;
END IF;
The RETURN Statement
Immediately completes execution of subprogram and returns control to caller
In procedures
– cannot contain an expression
– returns control to the caller before normal end of procedure is reached
In functions
– must contain an expression, evaluated when it is executed
Examples of Functions –
1)
a)
create or replace function eo ( n number)
RETURN char
is
disp char(30);
Begin
If Mod(n,2) = 0 Then
disp := 'Number is even';
else
disp := 'Number is odd';
End If;
dbms_output.put_line(disp);
RETURN disp;
End;
Select eo(9) from dual;
OR
Declare
x number;
y char(30);
Begin
x := &x;
/*Calling the function eo, supplying the parameter x and giving the value to y*/
y := eo(x);
end;
2) Database example
a) create or replace function Remarks ( x number)
RETURN char is
disp char(30);
Begin
If x >= 70 Then
disp := 'Distinction';
Elsif x >= 60 Then
disp := 'First Class';
Elsif x >= 50 Then
disp := 'Second Class';
Elsif x >= 35 Then
disp := 'Passed';
Else
disp := 'Failed';
End If;
RETURN disp;
End;
c) Using this function to insert values for a column of a table
create table st
(name varchar2(10),
marks number,
result char(30));
Directly calling the function inside Insert statement
Insert into st
values(‘John’, 90, Remarks(90)); -- Directly calling the function in the values clause
b) Calling a function in select statement
create or replace function raise_sal(s number)
return number is
begin
return s + 8000;
end;
Select ename,deptno,job,sal, raise_sal(sal)
From emp;
create or replace function f1(s number)
return number
is
begin
delete from emp;
return 0;
end;
Wrong usage of functions
select ename,sal,f1(sal) from emp;
Function having select statement in it’s body can get called in the select statement of the same table or any different table.
create or replace function highest
return number
is
x number;
begin
select max(sal) into x from emp;
return x;
end;
select ename, sal, highest, highest - sal "Diff" from emp;
Applicable for displaying aggregate values alongwith non groupbale columns.
3) Returning more than one value using OUT parameter
a)
create or replace function getdetails(no number,name OUT varchar2)
RETURN varchar2 as
vloc varchar2(30);
begin
select dname,loc into name,vloc
from dept
where deptno = no;
RETURN vloc;
End;
b)
-- First declare two bind variables location and deptname
--SQL> variable deptname varchar2(100) (size is imp)
--SQL> variable location varchar2(100)
Begin
:location := getdetails(30, :deptname);
End;
-- To see both the values
-- print deptname location
-------------------------------------------------------------------------
Discussion about this article
| Author | Body |
admin
8/24/2009 2:25 PM
|
Please provide feedback about this article here.
To participate in this discussion Sign up for free membership of 24x7code.
To Signup click on Login , Use create user link & the follow the instructions.
Thank you
|