Functions
o
They are the PL/SQL program units which allow parameters similar
to that of procedures.
o
But unlike procedures they return value by default.
o
Functions are useful for calculation purpose and for data
manipulation purpose (DML).
o
Functions makes the queries simple, readable and also enhance
the performance.
Note:- Providing out and
in out parameters’ in functions is not preferable.
Syntax:- Create or replace
function (Para) return datatype
[pipelined|aggregate|parallelenabled|deterministic|authiduser] is/as
Begin
Code;
Return
statement;
Exception
------
End[procedure
name];
Calling
Functions:-
1. SQL/Plus
2. Blocks
3. SQl
Developer
4. Apps
5. Select
statement
6. Objects
Functions
without any parameter:-
create or replace function fun return number is
v number(5):=&n;
begin
return v; // we can give
assign value/exp/literals/collections.
end;
block:-
declare
a number(5);
begin
a:= fun;
DOPL(a);
End;
SQL/Plus:-
Var a number;
Exec :a:=fun; //where ‘a’ holds the value
Print a;
Create or replace procedure p is
B number(5);
Begin
B:=fun;
DOPL(b);
End p;
Create or
replace function f1 return number is
Begin
Return fun;
End f1;
Select fun
from dual;
Note:- In the return
statement we can mention value directly, expression, another function, cursor
variables, index by table (collection), Boolean value, and so on…..
----------------------------------------------------------------------------------
Function
with IN parameter:-
Create or
replace function fin(X number) return variable is V name emp.ename%type;
Begin
Select ename into vname from emp where
empno:=X;
End fin;
SQL/Plus:-
Var a carchar2(10);
Var b number;
Exec :b:=7788;
Exec :a:=fin(:b);
Print a;
Blocks:-
Declare
A
varchar2(10);
B
number(5):=&n;
Begin
A:=fin(b);
DOPL(a);
End;
Select fin(empno) from emp;
Create
or replace function fn(X varchar2) return number is Vno number(5);
Begin
Select empno into vno from
emp where ename=X;
Return vno;
End fn;
SQL> select
fn(fin(empno)) from emp;
Function can contain
‘n’ no of ‘return’ statements but alwaya executes only one return statements.
Using
multiple return statements in Functions:-
Create or
replace function f(X number) return number is vnumber(5):=1000;
Begin
If x>100 then
Return(x);
Else
Return v;
End if;
End;
Create or replace function f return….
Begin
Return X;
Return Y;
End;
o
If control looks first return statement the control
automatically comes out of function them it won’t goes to second return
statement.
o
DOPL won’t supportsT(or) F
SQL*Plus:-
Var a
number;
Exec
:a:=F(500);
Print a;
------------------------------------------------------------------------------------
Function without
parameters:-
Returning
Boolean value from the function:-
Create or replace function f return Boolean is v number(5):=100;
Begin
Return null;
End;
Blocks:-
Declare
A Boolean;
Begin
A:=f;
DOCL(‘value
‘||a);
End;
SQL won’t supports Boolean datatype.
EX for out:-
Create or replace function f(X out number) return number is v
number(5):=100;
Begin
X:=v+500;
Return v;
End;
SQL*Plus:-
var a number;
var b number;
exec :a:=f(:b);
print a;
100
Print b;
600
Blocks:-
Declare
A nmber(5);
B number(5);
Begin
A:=f(b);
DOPL(a||’ ‘||b);
End;
SQL>
select f(:b) from dual;
Note: It is not possible to
call the function which are having out and in out parameters in ‘select
statement’.
Function
with in out parameter:-
Create or replace function f(X inout number) return number is v
umber(5):=100;
Begin
X: =v+500+X;
Return v;
End;
SQL*Plus:-
Var a number;
Var b number;
Exec: b: =500;
Exec: a: =f (: b);
Print a;
Print b;
Blocks:-
Declare
A number (5);
B number (5);
Begin
A: =f (b);
DOPL (a||’ ‘||b);
End;
Using
Functions:-
We can use the
functions in the following areas
i.
Where clause
ii.
Start with
iii.
Connect by
iv.
Having
v.
Group by
vi.
Order by
vii.
Select
viii.
Update set clause
ix.
Insert value clause
x.
From clause
We cannot use the
functions in the following areas
i.
Default (alter and create)
ii.
Check constraint
Restrictions
of Function:-
o In the
above diagram select statements DML is not possible and select is possible.
o In the DML
statements DML will not possible on same function table. But it will possible
on different function tables.
o
In DML statements select possible on different (or) same
function table.
NOTE:-
o
We can use select statement in DML operations.
o Using DML
operations in select statements is not passible either directly (or)
indirectly.
o Functions/procedures
i.e DML
o Select f
(DML) from dual;
o Select f
(select) //invalid
o Update t1
set val=f(DML(T1)) //invalid
o Update t2
set val =f(DML(T2)) //valid
o
Generally SQL statements won’t allow us to call the functions
which are having DML operations. If you do so, resulting into error. If you
won’t to restrict the user not to provide DML operations in a function which
are frequently used in select statements,
we need to provide “program restrict reference” while creating a
function, in this manner we will eliminate the impurities.
Create or replace function f
return number is
Begin
Null;
End f;
Note:-
We can define a function without return statement but at the
time of calling it throws error as such in above eg.
Var
a number;
Exec :a:=f; //throws
an error.
-------------------------------------------------------------------
Diff between procedure and function?
Procedure:-
o
Procedure may (or) may not return a value.
o
Not passable to call in SQL statements.
o
It acts as a PL/SQL statement execution (for statements
execution purpose).
Functions:-
o
By default function returns single value.
o
It is possible to call in SQL statements.
o
For calculations (or) computing purpose.
o
Using functions in select statement will enhance the performance
and simplifies the coding.
---------------------------------------------------------------------
Forward declaration:-
o
Basically if you want to call a program it has to be get defined
very previously.
o
Whenever you call a program before defining throws an error.
o
For mutual exchange purpose sometimes we have a need to call a
procedure before defining.
o
To fulfill this requirement we will declare the procedure very
previously which is calling before defining.
o
If you want to call a procedure before defining you have to
declare those procedures vary previously.
Declare
V number
(5):=5;
Procedure
p2(Y input number); //forward
declaration
Procedure
p1(X input number) is
Begin
If X>0
then
Docl(X)
X: =X-1;
P2(X);//calling
End if;
End p1;
Procedure
p2(Y input number) is //defining
Begin
P1(Y);
End p2;
Begin
P2 (v); // calling and executable section first
compiler comes here.
First define then call
any procedure without defining ifYou need to make a call at least we need to
declare.
End;
Create or replace procedure p3 is
(1)
End p3;
Exec p3;
Note:-
A
procedure can optionally contain ‘return’ statement but it won’t through any
value as in the given eg.
Create or
replace procedure p3 isV number(5):=100;
Begin
DOCL(v);
Return v;
DOPL(v);
End p3;
--------------------------------------------------------------------------------------------------------------------
Diff between procedure and
function?
Procedure:-
o
Procedure may (or) may not returns a value.
o
Not passable to call in SQL statements.
o
It acts as a PL/SQL statement execution(for statements execution
purpose).
Functions:-
·
By default function returns single value.
·
It is possible to call in SQL statements.
·
For calculations (or) computing purpose.
· Using functions in select statement will enhance the performance
and simplifies the coding.
No comments:
Post a Comment