Re: Oracle Stored Procedures VERSUS SQL Server Stored Procedures

On Thu, 07 Jul 2005 20:38:52 -0700, jrefactors interested us by writing:

>
> I want to know the differences between SQL Server 2000 stored
> procedures and oracle stored procedures? Do they have different
> syntax?

They are completely different languages.

Oracle PL/SQL uses the robust Ada language as it's template.

Not sure what Sybase used as it's template - seems like a mix of Pascal and Basic. Very reasonable in it's own way as well. IIRC, Microsoft has not extended the basic too much when they turned the core into SQL Server - lots of nice add-ons though.

More to the point, because of the differences in database internals, such as locking strategies, the way you need to approach your code is significantly different. For example, in SQL Server it's reasonable to create temp tables on the fly to hold intermediate results, whereas in ORacle you use a predefined global temp table. In Oracle, readers don't block writers and writers don't block readers so far less explicit locking occurs in Oracle in general. And in Oracle, the fastest way to kill your scalability is to build your SQL statement on the fly for each client - bind variables are very, very useful.

> Please advise good references for Oracle stored procedures also.
>

All Oracle docco is online, free of charge, at http://docs.oracle.com ... recommended reading for an app developer is (in order):

  Oracle Concepts manual
  Oracle Application Developer's Guide - Fundamentals   Oracle SQL Reference
  Oracle PL/SQL User's Guide and Reference   Oracle Supplied PL/SQL Packages and Types Reference   

Note that Oracle supplies a whole pile of added things in the database, most at no added charge. Some examples in this category include: message queueing; text indexing and search; doc indexing and search; multimedia storage and retrieval; workflow; OLAP; XML. (Yes many also occur in SQL Server.)

To avoid duplicating what is provided, I recommend selecting from the remaining manuals and guides for API and examples.

The following books are, IMO, mandatory in your library:

  O'Reilly "Mastering Oracle SQL"

• there's a lot that doesn't need PL/SQL APress "Mastering Oracle PL/SQL"
• bible! Oracle Press "Effective Oracle by Design"
• a MUST read if you care about performance
• goes a LONG way to explaining the differences in code style

Have fun

-- 
Hans Forbrich                           
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com   
*** I no longer assist with top-posted newsgroup queries ***