Polymorphism (Overloading):-
· Defining multiple local subprograms with the very same name but by differing number, order and data types of parameters.
Create or replace package pack is Procedure p(x number);
Procedure p(x number, y number);
Procedure P(x number, y varchar2);
Procedure p(y varchar2, x number);
End pack;
Note:- The data types should not be same family while comparing to procedures.
Creating or replace package body pack is procedure p(x number) is
Begin
DOPL(x);
End p;
Procedure p(x nmber, y number) is
Begin
DOPL(x||y);
End p;
Procedure p(x number, y varchar2) is
Begin DOPL(x||’ ‘||y);
End p;
Procedure p(y varchar2, x number)is
Begin
DOPL(y||’ ‘||x);
End p;
End;
Calling environment:-
SQL> exec p(100);
SQL> exec p(‘a’,200);
SQL> exec p(200,’a’);
Note:- Overloading is not possible for standard alone program (schema level programs).
· V$parameter=>all DB related parameters will store
· PL/SQL-optimize-level-initialization parameter.
Where level-0-ideal
Level-1
Level-2-preferrable
Level-3(11g)-aggressive much more faster.
Tuning the PL/SQL code:-
optimizing the PL/SQL code:-
·
We will tune the PL/SQL code based on initialisation parameter
i.e PL/SQL-optimize-level in this we will set the levels from 0 to 3. Level 2
is default which allows us
1.
In lining (11g) a program.
2.
Avoiding the in lining program.
·
Level 0 and 1 won’t allow us to in lining program.
·
Level 3 won’t allow us to inline a program but allow us to avoid
in lining program.
Declare
Sdate number:=0;
Edate number:=0;
D_code number:=0; //dead
code no use in program
L_r number:=0;
Function f(x number)
return number is
Begin
Return x;
End f;
Begin
Sdate:=dbms_utility.get_time; //here we are assigning to one
variable
so
call it as function.
For I in 1..1000000
D_code:=0;
L_r:=l_r+f(i); //calling
End loop;
Edate:=dbms_utility.get_time;
DOPL(edate_sdate);
End;
SQL> alter session set PLSQL_optimize_level=0;
Time:23session set PLSQL_optimize_level=1;
Time:22session set PLSQL_optimize_level=2;
Time:21session set PLSQL_optimize_level=3;
Time:9session set PLSQL_optimize_level=4;
·
Dbms_utility.put_line;
·
Get_time;
SQL> show parameter
PLSQL_optimize_level;
Understanding the NOT NULL constraint for a PL/SQL variable:-
Providing NOT NULL
constraint for a PL/SQL variable is not preferable doing so, will degrades the
performance because nullity will be checked through one virtual variable
created implicitly by the engine.
Declare
Sdate
number:=0;
Edate
number:=0;
V
number not null:=0
Begin
Sdate:=dbms_utility.get_time;
For
i in 1..100000000 loop
V:=I;
End
loop;
Edate:=dbms_utility.get_time;
DOPL(edate_sdate);
End;
Output:
Time
:- 72 minutes.
Note: Internally it takes one virtual variable and it checks v number
NOT NULL(it is not preferable more time it will take if you use coading in
program it is better like if v is not null).
Same as above program
but variable using without NOTNULL.
Output:
Time: 44
Using pls integer is
preferiable than the number since it is faster:-
V
pls_integer:=0; //we use these only in
PL/SQL and remaining is same as above.
Time:-12
Using simple integer is
more refereable than pls_integer:-
Simple integer is a
derivate of pls_integer , but avoids overflow error and allow null values.
Note: Up to 10g every
datatype allows null values but n 11g simple integer won’t allow null
value. Pls_integer size-2147483648 to 2147483647
Note: Pls_integer can store
max of 2147483647, if you assign above of this value throws an error i.e
overflow error, to overcome this error we have a datatype called
“simple-integer”(11g) and even simple integer won’t allow “null” values
(NOT NULL datatype).
Declare
V pls_integer:=214783646;
Begin
V:=v+1;
DOPL(v);
V:=v+1;
DOPL(v);
End;
Out put: Numeric overflow
error.
To overcome this
Declare
V
simple_integer:=2147483646;
Begin
V:=v+1;
DOPL(v);
V:=v+1;
DOPL(v);
End;
Out put:
2147483647
-2147483648
Pragma Autonomous Transaction (Tx):-
It is an independent
Tx happens individually irrespective of parent Tx.
Limitations:-
Package specification won’t allow pragma; we can apply for
packeged procedures and packages.
Create or replace
procedure p is pragma autonomous_transaction;
Begin
Insert into nestab values(10);
End p;
Create table
nestab(sno number(5));
Select * form nestab;
Calling environment:
Begin
Insert into nstab
values(11);
P;
Insert into nestab
values(12);
Rollback;
End;
Output: 10
·
While using pragma autonomus Tx we need to mention commit, rollback
as mandatory.
·
DDl- autocommit commands
·
DML- non-autocommit.
Create or replace procedure p is pragma autonomius_transaction;
Begin
Insert into nestab values(10);
Rollback;
End p;
Begin
Insert into nestab values(11);
P;
Insert into nestab values(12);
Commit;
End;
Select * from nestab;
Out put:-
11
10
12
Create or replace procedure p is Begin insert into nestab
values(10);
Rollback;
End p;
Begin insert into nestab values(11);
P;
Insert into nestab values(12);
Commit;
End p;
Out put:
12
Note: We avoiding mutating error using pragma.atonomous_transaction.
Pragma inline:- (11g)
·
Which is used to including the programs(11g).
·
Pragma inlining will enhance the performance.
Deff: Inlining a program
means replacing the procedure call with actual executable code copy of a
program.
Note: Programs which are
having static code and frequently used programs (or) subject to the pragma
inline (preferrable).
Syntax:
Pragma inline(‘procedure name’,{‘yes/no’});
Declare
Stime integer;
Etime integer;
V number;
Function f(x number) return number is
Begin
Return x;
End f;
Begin
Pragma inline(‘f’,’yes’);
Stime :=dbms_utility.get_time;
For I in 1..10000 loop
V:=f(i);
End loop;
Etime:=dbms_utility.get_time;
DOPL(etime-stime);
Pragma inline(‘f’,’no’);
Stime:=dbms_utility.get_time;
For I in 1..10000 loop
V:=f(i);
End loop;
Etime:=dbms_utility.get_time;
DOPL(etime-stime);
End;
No comments:
Post a Comment