best+practices+in+pl sql
Post on 14-Apr-2018
219 Views
Preview:
TRANSCRIPT
-
7/27/2019 Best+Practices+in+PL SQL
1/15
-
7/27/2019 Best+Practices+in+PL SQL
2/15
.
Why best practices?
Many ways of writing a code
Code
Will give solution (some how) Time taken and resource consumed ?
Best code Use optimal resource
Provides quicker solution
-
7/27/2019 Best+Practices+in+PL SQL
3/15
.
Modularized Design
Bad design Dump your logic in a single procedure
Having lots of selects inserts updatesand deletes.etc
Good design Break your logic into small blocks
Grouping related logic as a single blockor program
Spend more time in designLess time in coding
-
7/27/2019 Best+Practices+in+PL SQL
4/15
.
Modularize
Modularize will reduce complexity
make your tasks manageable make your resulting code maintainable
Use Packages For each major functionality
With repeated DML as procedure
With repeated select as functions
-
7/27/2019 Best+Practices+in+PL SQL
5/15
.
Naming convention
Follow a standard throughout yourcode Easy to understand
Easy for maintain and change
Example Local variable l_var_name
Procedure parameter p_var_name
Global variable g_var_name
Follow the standard throughoutyour code and application
-
7/27/2019 Best+Practices+in+PL SQL
6/15
.
Avoid hard coding
CREATEORREPLACEPROCEDURE GEN_SWIP( an_document_number IN
asap.serv_req_si.document_number%TYPE,an_serv_item_id IN
asap.serv_req_si.serv_item_id%TYPE,an_srsi_ip_addr_seq INasap.srsi_ip_addr.srsi_ip_addr_seq
%TYPE,)asbeginnull;end;
CREATEORREPLACEPROCEDURE GEN_SWIP( an_document_number IN number,an_serv_item_id IN number,
an_srsi_ip_addr_seq IN varchar(20))As
beginselect d_no into an_document_numberfrom task;
end;
What happens if the d_no column changed tovarchar2 type ?
-
7/27/2019 Best+Practices+in+PL SQL
7/15
.
Avoid SQL !
SQL is equivalent to hard-coding Drag the performance down
Difficult to maintain
Encapsulate your SQL Selects inside functions
DMLs inside procedures
And call these inside your business logic
We will see an examplefor this later.
-
7/27/2019 Best+Practices+in+PL SQL
8/15
.
Repeated SQL as functions
This is quintessential for performance Avoid repeating the SQL in different
places
Hard parsing will be avoided
Identify at the time of designingSo spend more time in design
rather than coding
-
7/27/2019 Best+Practices+in+PL SQL
9/15
.
Exception handling
A common package for Error handling Error logging Log as more a data as possible
(debugging)
Normally
Date of occurrence Error number , name Program name and program dataetc
-
7/27/2019 Best+Practices+in+PL SQL
10/15
-
7/27/2019 Best+Practices+in+PL SQL
11/15
.
Use the FORALL Statement
Instead of the individual FOR operations,you can do this:
Used to reduce the context switchingbetween SQL and PL/SQL engine
PROCEDURE update_employee (p_name customer.name%TYPE)ISBEGINFORALL cust_dt IN (select cust_name,cust_id,cust_age from customer where cust_name=p_name )LoopBegin--bussiness logicEnd;END;
-
7/27/2019 Best+Practices+in+PL SQL
12/15
.
Oracle server
PL/SQL Runtime Engine SQL Engine
PL/SQL blockProcedural
statement
executorSQL
statement
executor
FOR aDept IN deptlist.FIRST..deptlist.LAST
LOOPDELETE empWHERE deptno = deptlist(aDept);
END LOOP;
Performance penaltyPerformance penaltyfor many contextfor many context
switchesswitches
Conventional Bind
-
7/27/2019 Best+Practices+in+PL SQL
13/15
.
Enter the Bulk Bind
Oracle server
PL/SQL Runtime Engine SQL Engine
PL/SQL blockProcedural
statement
executorSQL
statement
executor
FORALL aDept IN deptlist.FIRST..deptlist.LASTDELETE empWHERE deptno = deptlist(aDept);
Much less overheadMuch less overheadfor context switchingfor context switching
-
7/27/2019 Best+Practices+in+PL SQL
14/15
.
Points to remember
Take more time in designing
Follow coding standard
Avoid hard coding Avoid writing more SQL
Write tiny chunk of code
Dont repeat anything
-
7/27/2019 Best+Practices+in+PL SQL
15/15
.
Books/ Materials
Beginning Oracle Programming
Sean Dillon, Christopher Beck ,
Thomas Kyte http://asktom.oracle.com
http://www.toadworld.com/sf
Code Complete by Steve McConnell
http://asktom.oracle.com/http://www.toadworld.com/sfhttp://www.toadworld.com/sfhttp://www.toadworld.com/sfhttp://asktom.oracle.com/
top related