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,
CREATE PROCEDURE us_customers AS SELECT customer_id, first_name FROM Customers WHERE Country = 'USA';
CREATE PROCEDURE us_customers () LANGUAGE SQL AS $$ SELECT customer_id, first_name FROM Customers WHERE Country = 'USA'; $$;
DELIMITER // CREATE PROCEDURE us_customers () BEGIN SELECT customer_id, first_name FROM Customers WHERE Country = 'USA'; END // DELIMITER ;
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
first_name columns of those customers who live in the USA from the
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
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