In SQL, a stored procedure is a set of statement(s) that perform some defined actions. We make stored procedures so that we can reuse statements that are used frequently.
Stored procedures are thus similar to functions in programming. They can perform specified operations when we call them.
Creating a Procedure
We create stored procedures using the CREATE PROCEDURE command followed by SQL commands. For example,
SQL Server
CREATE PROCEDURE us_customers AS
SELECT customer_id, first_name
FROM Customers
WHERE Country = 'USA';
PostgreSQL
CREATE PROCEDURE us_customers ()
LANGUAGE SQL
AS $$
SELECT customer_id, first_name
FROM Customers
WHERE Country = 'USA';
$$;
MySQL
DELIMITER //
CREATE PROCEDURE us_customers ()
BEGIN
SELECT customer_id, first_name
FROM Customers
WHERE Country = 'USA';
END //
DELIMITER ;
Oracle
CREATE PROCEDURE us_customers
AS res SYS_REFCURSOR;
BEGIN
open res for
SELECT customer_id, first_name
FROM Customers
WHERE country = 'USA';
DBMS_SQL.RETURN_RESULT(res);
END;
The commands above create a stored procedure named us_customers in various DBMS. This procedure selects the customer_id and first_name columns of those customers who live in the USA from the Customers table.
Executing Stored Procedure
Now, whenever we want to fetch all customers who live in the USA, we can simply call the procedure mentioned above. For example,
SQL Server, Oracle
EXEC us_customers;
PostgreSQL, MySQL
CALL us_customers();
Drop Procedure
We can delete stored procedures by using the DROP PROCEDURE command. For example,
SQL Server, PostgreSQL, MySQL
DROP PROCEDURE us_customers;
Here, the SQL command deletes the us_customers procedure which we created previously.
Recommended Reading: SQL Parameterized Procedures