Stored Procedure

A stored procedure is a named block of procedural code stored in the database and executed on the server. It groups multiple SQL statements and logic behind a single callable name.

Procedures keep business logic close to the data (one round trip instead of many), centralize and reuse logic, and improve security by exposing an interface instead of raw tables. In Oracle they are written in PL/SQL; in SQL Server, in T-SQL.

A procedure performs actions and may return values through OUT parameters; a function is similar but returns a single value and can be used inside a query.

Example

CREATE PROCEDURE raise_salary (p_id IN NUMBER, p_pct IN NUMBER) AS
BEGIN
  UPDATE employees SET salary = salary * (1 + p_pct/100)
  WHERE  employee_id = p_id;
END;