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.
|