What is the difference between a view and a stored Procedure in SQL? [duplicate]
I have come across views in SQL and also stored procedures. Would you like to understand better, what is the difference between a view and a stored procedure and what are its purposes?
Would it be possible to provide 1 case study for each situation?
3 answers
A view is treated "as if it were" a table by the database.
Basically it's a select saved in the bank. It is used to save a preview (select) to the bank.
Already stored procedure as the name says, it is a sequence of procedures, which can include "DML" commands such as select, update and insert or "DDL", like create table for example. A stored procedure May or may not return data.
Its use is much wider than a view, since it allows you to perform a multitude of SQL language commands.
From the point of view of select, its behavior is similar: it has a saved query, and optimize the performance, but if you are thinking of the book of the select is a stored procedure allows you to pass parameters, you may perform a filtered query (where for example), it's a view does not accept any parameters.
VIEWS
A VIEW is basically a sql query, where we build a query, usually more complex than a simple SELECT, and then we can use it as if it were a table.
Example
CREATE VIEW V_Exemplo
AS
SELECT T1.Id
, T1.Data
, T2.Texto
FROM T1
INNER JOIN T2 ON T2.IdT1 = T1.Id
The above example will create a VIEW by joining the table T1 and T2 and returning results from the columns Id and Data of T1 and Texto of T2.
The VIEWS must be created when a specific query is invoked multiple times, and to execute it is enough:
SELECT * FROM V_Exemplo
Instead of running the same query again, which can be tricky to manage.
Attention , the VIEWS do not allow the use of parameters, so we will not be able to restrict the query in the same way as, say, in a Stored Procedure.
Stored Procedure
A Stored Procedure is basically a parameterizable method where we can include logic.
Is much more flexible than VIEW, not only because it allows the use of of input parameters but also because there we can execute [practically] everything.
Let's imagine that in the query we put above we needed to filter the date to restrict results:
DELIMITER //
CREATE PROCEDURE SP_Exemplo (IN Data DATETIME)
BEGIN
SELECT T1.Id
, T1.Data
, T2.Texto
FROM T1
INNER JOIN T2 ON T2.IdT1 = T1.Id
WHERE T1.Data >= Data
END //
DELIMITER
In this case we have already managed to obtain, for example, only 1,000 results from a table with 1,000,000, something that is not possible only with VIEW.
The VIEWS and the SPs have completely different implementations, and should be used depending on the scenario in cause.
The indications I gave are somewhat simplistic, but I hope I helped.
View is a query stored in your database, whereas stored procedure is a stored procedure. In the View, vc has a query that returns some result. Already the stored procedure it contains a process, which can be backup start, pointers, while's and, depending on the configuration, and the DBMS, functions and processes of the own operating system.