Difference Between View and Stored Procedure

View vs Stored Procedure
 

Views and stored procedures are two types of database objects. Views are kind of stored queries, which gather data from one or more tables. Here, is the syntax to create a view

create or replace view viewname

as

select_statement;

A stored procedure is a pre compiled SQL command set, which is stored in the database server. Each stored procedure has a calling name, which is used to call them inside other packages, procedures and functions. This is the syntax (in ORACLE) to create a stored procedure,

create or replace procedure procedurename (parameters)

is

begin

statements;

exception

exception_handling

end;

View

A View acts as a virtual table. It hides a select statement inside its body. This select statement can be a very complex one, which takes data from several tables and views. Therefore, in other words, a view is a named select statement, which is stored in the database. A view can be used to hide the logic behind the table relations from end users. Since a view is a result of a stored query, it does not keep any data. It gathers data from the base tables and shows. Views play an important role in data security, as well. When the table owner needs to show only a set of data to end users, creating a view is a good solution. Views can be divided in to two categories

• Updatable views (Views those can be used for INSERT, UPDATE and DELETE)
• Non-Updatable views (Views those cannot be used for INSERT, UPDATE and DELETE)

Updatable views cannot include followings,

Set Operators (INTERSECT, MINUS, UNION, UNION ALL)

DISTINCT

Group Aggregate Functions (AVG, COUNT, MAX, MIN, SUM, etc.)

GROUP BY Clause

ORDER BY Clause

CONNECT BY Clause

START WITH Clause

Collection Expression in a Select List

Sub query in A Select List

Join Query 

Stored Procedure

Stored procedures are named programming blocks. They must have a name to call. Stored procedures accept parameters as user input and process according to the logic behind the procedure and give the result (or perform a specific action). Variable declarations, variable assignments, control statements, loops, SQL queries and other functions/procedure/package calls can be inside the body of procedures. 

What is the difference between View and Stored Procedure? Let us see the differences between these two. • Views act as virtual tables. They can be used directly in from close of SQL queries (select), but procedures cannot be used in from close of queries. • Views have only a select statement as their body, but procedures can have Variable declarations, variable assignments, control statements, loops, SQL queries and other functions/procedure/package calls as its body. • Procedure accepts parameters to execute, but views do not want parameters to execute. • Record types can be created from views using % ROWTYPE, but using procedures, record types cannot be created. • SQL hints can be used inside view select statement, to optimize the execution plan, but SQL hints cannot be used in stored procedures. • DELETE, INSERT, UPDATE, SELECT, FLASHBACK, and DEBUG can be granted on views, but only EXECUTE and DEBUG can be granted on procedures.