*
The moose likes JDBC and the fly likes stored procedure Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "stored procedure" Watch "stored procedure" New topic
Author

stored procedure

thomas davis
Ranch Hand

Joined: Feb 01, 2003
Posts: 207
I have some insert statements,I would like to convert these insert statement into stored procedure and make the query most efficient how to go about it?
If anybody worked on stored procedures and converting simple statements to sotred procedures please reply with converted stored procedure and how to make the below statement most efficient by optimizing the joins.?
Please go through the below code and try to give stored procedure for it(oracle)?
public void loadCupPartUpdateAttach() throws Exception {
displayMsg( "loadCupPartUpdateAttach ..." );
String[] purposes = {"P", "O", "M", "S", "D", "U", "V"};
String[] titles = { "Picture", "Overview", "MSDS", "Tech Spec", "Drawing",
"WebSite", "Video" };
String[] locations = {"c.PicName", "c.OverView", "c.MSDS", "c.TechSpec",
"c.Drawing", "c.SupplierURL", "c.Video" };
String sql = null;
Statement stmt = connection.createStatement();
for ( int i = 0 ; i < purposes.length; i++ ){
try{
sql = " insert into Cup_PartUpdateAttach " +
" (Cup_PartUpdateAttachID, Title, PurPose, Location , Cup_PartUpdateInfoID, " +
" Locale ) " +
" select rownum " + (i == 0 ? "" : " + ( select max(Cup_PartUpdateAttachID) from Cup_PartUpdateAttach ) " )+
" as id, '" + titles[i] + "' as title, '" + purposes[i] + "' as purpose, lower(" + locations[i] +
") , info.Cup_PartUpdateInfoID, c.locale " +
" from cup c, cup_partupdateinfo info, cup_partupdate up where " +
locations[i] + " is not null " +
" and c.SPN = up.PARTNUM " +
" and info.CUP_PARTUPDATEID = up.CUP_PARTUPDATEID ";
stmt.executeUpdate(sql);
} catch (Exception e )
{
e.printStackTrace();
}
}
displayMsg( "Completed loadPartCupUpdateAttach" );
}
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Thomas,
I don't have time to do your work for you, ( unfortunately I have to do my own! ), but have a look at the Oracle9i JDBC Developer's Guide and Reference - Stored Procedure Calls in JDBC Programs for help in calling a stored procedure. If you want to know how to write an Oracle PL/SQL stored procedure, then visit the PL/SQL User's Guide and Reference. This is some of the best documentation out there, so take advantage of it!
Jamie
thomas davis
Ranch Hand

Joined: Feb 01, 2003
Posts: 207
Given below are two insertion queries which I change it to more understandable way and I just want to convert these querries into a stored procedure and also would like to optimize the query if you have sometime to spare,please try to give the converted query.
1)INSERTION QUERY FOR CUP_PARTUPDATEDESC
INSERT INTO cup_partupdatedesc
SELECT rownum as id, b.CUP_PartUpdateInfoID, a.locale, a.desc1, a.desc2
FROM
cup a, cup_partupdateinfo b, cup_partupdate c
WHERE
a.spn = c.partnum and a.PartNumExt = c.PartNumExt and c.cup_partupdateid = b.cup_partupdateid "

2)INSERTION QUERY FOR CUP_PARTUPDATEATTACH
String[] purposes = {"P", "O", "M", "S", "D", "U", "V"};
String[] titles = { "Picture", "Overview", "MSDS", "Tech Spec", "Drawing","WebSite", "Video" };
String[] locations = {"c.PicName", "c.OverView", "c.MSDS", "c.TechSpec","c.Drawing", "c.SupplierURL", "c.Video" };
for ( int i = 0 ; i < purposes.length; i++ ){
INSERT INTO Cup_PartUpdateAttach
(Cup_PartUpdateAttachID, Title, PurPose, Location , Cup_PartUpdateInfoID, Locale )
select rownum (i == 0 ? "" : ( select max(Cup_PartUpdateAttachID) from Cup_PartUpdateAttach ) as id,
titles[i] as title, purposes[i] as purpose, lower(locations[i]) , info.Cup_PartUpdateInfoID, c.locale
FROM
cup c, cup_partupdateinfo info, cup_partupdate up
WHERE
locations[i] is not null and c.SPN = up.PARTNUM and info.CUP_PARTUPDATEID = up.CUP_PARTUPDATEID
Thnaks,
Thomas
thomas davis
Ranch Hand

Joined: Feb 01, 2003
Posts: 207
Please answer my question,it is quite urgent...
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

Originally posted by thomas davis:
Please answer my question,it is quite urgent...

Use the PL/SQL link I provided. There is a chapter with just examples. You can easily adjust the code so that it fits your needs, without knowing a lick about PL/SQL. If you have any problems, let us know and we'll help you with your problem.
Jamie
thomas davis
Ranch Hand

Joined: Feb 01, 2003
Posts: 207
Can I use BATCH UPDATE FOR OPTIMIZING THE THE STORED PROCEDURE?If yes,How can I implement it on abovesaid queries?
Pradeep bhatt
Ranch Hand

Joined: Feb 27, 2002
Posts: 8919

Thomas,
How are you?
Here is an example of batch update
http://java.sun.com/docs/books/tutorial/jdbc/jdbc2dot0/batchupdates.html


Groovy
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

So thomas, is your actual question "My queries/updates are taking too long, how can I make things happen faster?" Because writing a stored procedure probably won't help you in this case. Batch updating will increase performance if you have numberous updates to do in a group. If you are looking at performance tuning the JDBC portion of your application, have a read through Java Programming with Oracle JDBC - Chapter 19 Performance. It is very helpful in explaining the strengths and weaknesses ( performance wise ) and when you should use each one.
Jamie
 
 
subject: stored procedure