| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL versus stored procedures
apngss_at_yahoo.com wrote:
> I want to know what's the differences between PL/SQL and stored
> procedures,
> the followings are my analysis, please comment and advise.
>
> 1) PL/SQL is Oracle specific, stored procedures are supported in
> Oracle, MS-SQL Server, or other databases.
>
> 2) PL/SQL has 2 types: procedures and functions
>
> 3) PL/SQL procedure = stored procedure ??
>
> 4) Oracle stored procedure and MS-SQL stored procedure have
> different syntax. I think they are slightly different, I could
> find the syntax for Oracle stored procedure, but not MS-SQL stored
> procedure. In other words, can we put MS-SQL stored procedure
> and put in Oracle without any changes?
PL/SQL = name of a Oracle's embedded programming language (based on another programming language called ADA). PL/SQL is also very similar in syntax and structure to Pascal.
PL/SQL programs can be :
- procedures - functions - packages (program units containing both procedures and packages,similar to a Pascal unit)
As procedures, functions and packages are stored (in source code and pre-compiled format) inside the database, these are often collectively refered to as "stored procedures".
Anonymous blocks are PL/SQL code blocks constructed by the client and transmitted to Oracle. Oracle parses and compiles these and then execute them. These are obviously not stored in the database and therefore not considers as "stored procedures".
Other databases implements their own embedded programming languages. SQL-Server's is called Transact-SQL (or simply T-SQL).
Why not simple use SQL? SQL is not Turing Complete and despite its power and flexibility, lacks at doing complex structural processing. Thus most databases implement an embedded programming language, which they tightly integrate with their SQL engine - providing seamless SQL access from within this embedded language. Note that not all databases have embedded programming language - some only recently started doing this (like mySQL).
Embedded programming language look similar. So yes, there are similarities between T-SQL and PL/SQL. But these are very superficial. PL/SQL is a formal declarative procedural language with object-orientated features. It is capable of doing what you can do in other languages, like Java, C/C++, Delphi/Pascal, Visual Basic, etc. (besides, Oracle and SQL-Sever work conceptually very different and what is "good practise" in one database, is a performance killer in the other)
There are limitations in PL/SQL however. It is a server-side language. It thus lacks I/O devices such as screen, keyboard, mouse, printer and so on. It is not an interactive language (it cannot interact with the end-user). As it is embedded in a database it cannot natively access the operating system kernel API. Etc.
However, these "limitations" are common to embedded languages. The advantage is that PL/SQL can be run on any Oracle database on any platform. Thus you can develop a PL/SQL application on Windows in Oracle and have a customer use your application in their Oracle database on an IBM mainframe. Similar to Java, it is fully portable across Oracle platforms. Unlike Java, it is not an issue of write-once-and-debug-everywhere as the PL/SQL engine is consistant.
Oracle's Replicator is written in PL/SQL. Oracle Applications (a commercial product suite) consists of over 1 million lines of PL/SQL source code. I myself, has written numerous server-side systems (including a custom replicator and web applicatiom tiers) in PL/SQL. It is a very capable language - and, as a statement of fact, the *best* programming language to use when dealing with Oracle data.
-- BillyReceived on Fri Oct 14 2005 - 02:13:21 CDT
 |
 |