Introduce me to Oracle PL/SQL extensions

Question

I'm an accomplished user of SQL; I'm confident creating schema and optimizing queries in ANSI-SQL, for Sybase, MS SQL Server, MySQL, or postgresql. I understand joins, subqueries, indexes, etc., so I don't need to recapitulate any of that that isn't different under Oracle.

I'll be taking a job that'll require using Oracle. Point me to online resources designed not as an "Intro to SQL for Oracle" but ones that explain "PL/SQL for people who already understand SQL".

I'm especially interested in the following: a concise guide to PL/SQL extensions, and optimizing Oracle queries.

Answer 1

Oracle® Database PL/SQL User's Guide and Reference (10g) Really, what more could you want?

If you are new to Oracle, I'd also suggest you spend a bit of time learning its transaction model, as it is subtly different to SQL Server, which could bite you. Here is a good article on it. The other poster who suggested reading Kyte is spot on.

Answer 2

asktom.oracle.com would be useful to browse.

I suggest you investigate the following powerful features:-

• analytic functions
• materialized views
• pipelined PL/SQL functions

Answer 3

Read for the concepts: http://download.oracle.com/docs/cd/B28359_01/server.111/b28318/toc.htm

Answer 4

You should be aware that PL/SQL is a more-or-less complete programming language. Crazy people like me create applications where most of the work is done in PL/SQL packages on the server.

Answer 5

Read articles and books by Steve Feuerstein, he is one of the main evangelists of PL/SQL.

Focus on their PL/SQL data structures like cursors, associative arrays, tables(PL/SQL not SQL), etc.

One thing to keep in mind with googling Oracle code, you will run into old code a lot more than you will new code. Try to learn new rather than old.

For example the old way of looping over a query is to create a cursor and then perform a while loop over it until it returns nothing. Now days you can implicitly create a loop like this:

FOR Test IN (Select Ct from TableCT) LOOP
 Sum := Sum + Test.Ct;
END LOOP;

For optimizing queries, get SQL Developer and run the explain plan. It may take a while to understand, but it's the best way that I found. Also, if you haven't run into it yet, the WITH clause works wonders for optimizing queries.