PL/SQL versus stored procedures

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?
Please advise. thanks!!On 13 Oct 2005 22:15:44 -0700, apngss@.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?
>Please advise. thanks!!
1 PL/SQL is Oracle's language to implement stored procedures, T-SQL is
MS language to implement stored procedures. T-SQL is only supported in
MS and maybe Sybase. 'Stored procedure' is a *concept*, not a
*language*
2 Like any proper procedural language
3 PL/SQL can be used for anonymous blocks : ie code which is not
stored, and for procedures/functions/packages which are stored
4 the syntax for T-SQL and Pl/SQL is akin, but dissimilar.
T-SQL procedures won't work in Oracle and vice versa, moreover Oracle
works completely different, and too many people mistakenly think
Oracle is sqlserver sold by a different vendor. It is not, it works
completely different.
Sybrand Bakker, Senior Oracle DBA|||PL/SQL is to Oracle as T-SQL is to SQL Server. Both have their programming
constructs and data structures. You can "implement" stored procedures using
PL/SQL much like you can do the same with T-SQL.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
<apngss@.yahoo.com> wrote in message
news:1129266944.512419.283400@.o13g2000cwo.googlegroups.com...
>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?
> Please advise. thanks!!
>|||apngss@.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)
- anonymous code block
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.
Billy|||Good Explanation Billy
Madhivanan|||That was a very good explanation. Thank you.
Ivy