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.
LVL 7
gudii9Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.