Oracle procedures vs MS procedures vs Oracle packages

I was told by someone that when you create procedures in Oracle you should create a Package with procedures in it. Is this a true statement开发者_StackOverflow社区? Are procedures in MS the same as Oracle?

---

You do not have to put your procedures and functions inside a package, but it is generally considered a best practice. Bundle them by function and they are a lot easier to organize.

One exception to this is the AUTHID clause. That can only be specified for an entire package or a standalone procedure/function. Different procedures inside a single package cannot have different privileges.

---

Procedures in Oracle and MS are similar, but yes, in Oracle, you make a package declaring the procedure, then you define the procedure in the package body

This has some good info on how to build an Oracle package

---

Stored procedures and functions are very similar between Oracle and SQL Server (or MySQL, PostgreSQL, etc). A SQL function is intended to always return a SQL data type; a stored procedure can return a SQL data type but doesn't by default.

An Oracle package is an awesome tool.

• SQL Server has assemblies, but they aren't as immediately accessible as Oracle packages.
• Besides being a container for functions and stored procedures, Oracle packages support variable declaration when other alternatives would only expose via functions.
• Oracle packages also allow for logical grouping of functionality, without needing to setup schemas (for namespacing). Because of what's contained in a package, you deploy the package -- not the functions/stored procedures individually.
• My only regret was that IME (Oracle 9i, 10g), you could not drill into the specific function/stored procedure/line when tracing references.

There's nothing requiring you to use Oracle Packages, but you really stand to benefit by using them vs not. I don't understand why other vendors haven't reproduced the functionality in their own way.

---

There are a ton of reasons to use packages over standalone procedures/functions, and a few situations when you wouldn't use packages. Instead of trying to rehash them from memory, I'll point you to an excellent article from pl/sql guru Steve Feuerstein.

Note: The article is several years old, but the rationale still holds imo