A Stored Procedure is a program (or procedure) which is physically stored within a database. They are usually written in a proprietary database language like PL/SQL for Oracle database or PL/PgSQL for PostgreSQL. The advantage of a stored procedure is that when it is run, in response to a user request, it is run directly by the database engine, which usually runs on a separate database server. As such, it has direct access to the data it needs to manipulate and only needs to send its results back to the user, doing away with the overhead of communicating large amounts of data back and forth.
A user-defined function is a routine that encapsulates useful logic for use in other queries. While views are limited to a single
SELECTstatement, user-defined functions can have multiple
SELECTstatements and provide more powerful logic than is possible with views.
User defined functions have three main categories: