In a (relational) database, a stored procedure is a piece of code that is stored in the database and executed by the database server.
A stored procedure is usually written in an SQL-like language, for example PL/SQL on Oracle, but some servers also support Java (for example Oracle) or C (compiled).
Usually, a stored procedure cannot return a value, but it can pass data back to the caller using out or in out parameters. On some servers, you can create stored functions, which do return a value.
Advantages of using stored procedures:
- They are executed by the database server, which prevents unnecessary data transfer if the procedure takes data from one or more tables, does something with it and then puts it in a table again, and the operation cannot be done by a single SQL statement.
- They are usually saved in a compiled state in the database, so executing an SQL statement in a procedure can be faster because the server does not have to parse the statement.
Disadvantages:
- The programming language depends on the server, which makes it harder to support multiple databases.