• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 404
  • Last Modified:

new to stored procedures

I was going through oracle PL SQL reference manual book at following link
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/overview.htm#sthref259

I was trying to find material about stored procedure but when i search on that i could not see anything related to that. I see something like subprograms etc.

I would like to learn stored procedures from scratch. what are the best places to start learn quickly, effectively with code sample.


Any ideas, resources,sample code,links,  highly appreciated. thanks in advance.
0
gudii9
Asked:
gudii9
  • 5
  • 4
  • 3
  • +2
4 Solutions
 
sdstuberCommented:
Steven Feuerstein's   "Oracle PL/SQL Programming" is a great reference to learn from

He also has online tutorials and articles on his "pl/sql obsession" page

http://www.toadworld.com/Knowledge/DatabaseKnowledge/StevenFeuersteinsPLSQLObsession/tabid/153/Default.aspx


also, you can learn here on EE, if you have a specific question about pl/sql, just ask it in the pl/sql zone and we'll be glad to help

0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
gudii9Author Commented:
any other links or resources with good notes, powerpoints, more examples. please advise
0
 
sdstuberCommented:
Feuerstein covers pretty much everything there is to know about pl/sql from total beginner to advanced developers.

Do you have questions about anything in the links above?
0
 
gudii9Author Commented:
i see stored procedures with multiple begins multiple if if else for loops. I am trying to understand those kind of stored procedures. but could not find similar material.please advise
0
 
gudii9Author Commented:
i have lot of cursors also in between same procedure
0
 
slightwv (䄆 Netminder) Commented:
Something I heard a long time ago but never personally confirmed (they swore by it):  PL/SQL strongly resembles and follows the old ADA programming language.  If the others were right, you might look into ADA guides.

For example: packaged with public and private functions and procedures.  The code syntax is also supposed to be close if not exact.

Hopefully you understand the nested it-then-else construct.  PL/SQL doesn't differ much from other languages in that aspect.

What languages do you know?  We might be able to compare features between PL/SQL and that language.

I'm sure the links above cover the 'begin-end' aspect somewhere but in a nutshell:

PL/SQL allows to 'block' off sections of code and handle them specially.  This is typically done to perform special error or EXCEPTION handling.  Exceptions are trapped and handled in the same block where they occur.  a 'block' is the code between a 'begin-end'.

So I can do something like:

begin
   --do some logic
   begin
       --special code
       exception
           when NO_DATA_FOUND then
                 --handle this error
           when others then
                 --trap/handle unexpected error
   end;
   -- do this logic regardless of the error in the above block
   exception
        when -- trap exceptions in the main code (not from the internal blocks)
end;

Cursors are a completely different story.  What do you find confusing about them?
0
 
gudii9Author Commented:
>>>begin
   --do some logic
   begin
       --special code
       exception
           when NO_DATA_FOUND then
                 --handle this error
           when others then
                 --trap/handle unexpected error
   end;
   -- do this logic regardless of the error in the above block
   exception
        when -- trap exceptions in the main code (not from the internal blocks)
end;

i have not understood this line

>>>   -- do this logic regardless of the error in the above block

does that means iner begin loop is bypassed to come here or how this logic executed regardless of the error in the above block.

I am from java programmming background.
0
 
slightwv (䄆 Netminder) Commented:
It means that no matter the outcome of the inner block, if exceptions are properly handled, the main block continues executing.
0
 
gudii9Author Commented:
any good video tutorial link on stored procedures, PL/SQL. please advise
0
 
sdstuberCommented:
same link I posted above above  in http:#35354265

Feuerstein's PL/SQL Obsession site has videos on it as well as sample code and articles.


He also has a new PLSQLChannel website, it has a free trial on it but pay for full access.
The Obsession site is free


http://tutorials.plsqlchannel.com/
0
 
slightwv (䄆 Netminder) Commented:
There were a lot of links posted above.  Did those not help you?

A quick Google came up with a new one:  http://www.plsql-tutorial.com/

It doesn't appear to be a full-blown course but does seem to cover the basics.
0
 
slightwv (䄆 Netminder) Commented:
Thanks for the points.

>>best places to start learn quickly, effectively

Just remember, its an old programming adage but works in many places.  Typically you never find all three, pick two:  Good, Fast, Cheap.


0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 5
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now