Re: Oracle Stored Procedures VERSUS SQL Server Stored Procedures

jrefactors_at_hotmail.com wrote:
> I want to know the differences between SQL Server 2000 stored
> procedures and oracle stored procedures? Do they have different
> syntax? The concept should be the same that the stored procedures
> execute in the database server with better performance?

Concept is the same (performance wise). Everything else is different. PL/SQL is formal programming language like Java or C/C++ or Delphi.

It has OO support - so you can create classes and subclasses. You have various APIs. You can talk to TCP servers (telnet, ftp), LDAP servers, SMTP servers, HTTP servers. In other words, in PL/SQL you have a web browser, an e-mail client, a TCP client, a LDAP client. Then there are APIs for flat file I/O, encryption and decryption, IPC (Inter Process Communication) via database pipes and message queues, data capture streams, and many more.

PL/SQL is nothing like Transact-SQL. Transact-SQL is a macro language at best. Which has its uses. But PL/SQL is as much a programming language as any other formal development language today.

Case in point. The Oracle Application product suite consists of over a million lines of PL/SQL. Oracle's replication is written in PL/SQL (and not C).

I have a PL/SQL procedure that maps incoming router traffic to the source and then inserts it into a table. This procedure is called by a process that gets the network traffic data from various Cisco routers. The stored proc is executed in excess of 300 times per second. Its SQL statements (in the Oracle shared pool) typically shows (indvidually) 1.5 billion (and more) executions over time.

I also use PL/SQL as the application tier as oppose to Java/J2EE - Oracle is both my app server and database server. This not only scales better, but is faster to develop, easier to maintain, has less moving parts and a lot cheaper than the traditional Java app server setup.

PL/SQL is the fastest and most optimal way to deal with data in Oracle. Period.

> Please advise good references for Oracle stored procedures also.

PL/SQL Reference Guide. Application Developer Fundementals Guide. Application Developer Object Relational Features Guide. PL/SQL Packages and Types Guide.

The best is however to get your feet wet and start writing PL/SQL code and getting to grips with the features and fundementals of PL/SQL and Oracle.

--
Billy