-
Hello DBA gurus,
very urgent......................
what are the differences between sqlserver stored procedures and oracle stored procedures?
-
The main difference between Oracle stored procedures and MSSQL Server stored procedures is the use of PL/SQL in oracle stored scripts and special data types and environment variables.
PL/SQL is an extension of the standard SQL (92) of ANSI, It can use packages to group procedures and functions together.
You can read about it at:
http://otn.oracle.com/docs/products/...a96624/toc.htm
Special data types include LOB, REF Cursor etc.
In MSSQL there are other implementations (taken from sybase)
such as date manipulation (datepart function) etc.
cheers,
r.
-
rotem_fo gave some good examples, but it even confused me, so I'll try to clarify.
At the highest level, they are exactly the same. They are both used to store procedural code in the database.
SQLServer uses T-SQL as its procedural language and Oracle Uses PL/SQL as its procedural language.
The languages themselves have tons of differences. Now, I haven't written in T-SQL for about 5 years, and there have been several new releases since then, but at that time there were significant differences between the languages, and I imagine most of them still exist:
- No structured error-handling in T-SQL
- Much, much better temp table capabilities in T-SQL
- yada yada yada :)
However, you asked about the stored procedures, and not the languages themselves.
At that level the biggest differences are:
- In Oracle, you have packages. Packages allow you to group procedures into larger logical units. More than that, packages provide package-level variables and data-types. They have been called the 'poor-man's objects', and it's pretty true.
- In SQLServer, a procedure can return a record-set almost like a function. In Oracle, the methodology used to be really abysmal - the multiple table-type params. Now you can use Ref Cursors, which are much, much better. However, SQLServer has had a cleaner interface since back when it was Sybase ;). You simply do a SELECT in the code and the result set (or as many result sets as there are SELECTs in the proc) is returned to the calling program.
I'm sure there are more, but that's all that occurs to me now.
- Chris
