Compare the Difference Between Similar Terms

Difference Between

Home / Technology / IT / Database / Difference Between View and Stored Procedure

Difference Between View and Stored Procedure

November 14, 2011 Posted by Admin

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.

 

Related posts:

Difference Between Triggers and Stored Procedures Difference Between View and Materialized View Difference Between Stack and Queue Difference Between Entity and Attribute Difference Between SAP and ORACLE

Filed Under: Database Tagged With: database objects, named programming blocks, non updatable views, pre compiled SQL command set, stored procedure, stored procedure vs, stored procedures, stored procedures vs, stored queries, updatable views, View, view vs, views, views vs, virtual table

About the Author: Admin

Coming from Engineering cum Human Resource Development background, has over 10 years experience in content developmet and management.

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Request Article

Featured Posts

Difference Between Coronavirus and Cold Symptoms

Difference Between Coronavirus and Cold Symptoms

Difference Between Coronavirus and SARS

Difference Between Coronavirus and SARS

Difference Between Coronavirus and Influenza

Difference Between Coronavirus and Influenza

Difference Between Coronavirus and Covid 19

Difference Between Coronavirus and Covid 19

You May Like

Difference Between Dependent and Productive Population

Difference Between Dependent and Productive Population

Difference Between Legislation and Regulation

What is the Difference Between Online Education and Traditional Education

What is the Difference Between Online Education and Traditional Education

Difference Between Flavonoids and Polyphenols

Difference Between Flavonoids and Polyphenols

Difference Between In and On

Difference Between In and On

Latest Posts

  • What is the Difference Between Cybrids and Hybrids
  • What is the Difference Between Hapten and Adjuvant
  • What is the Difference Between Omphalocele and Gastroschisis
  • What is the Difference Between Autonomic and Somatic Reflexes
  • What is the Difference Between Plagiocephaly and Craniosynostosis
  • What is the Difference Between Coconut Oil and Virgin Coconut Oil
  • Home
  • Vacancies
  • About
  • Request Article
  • Contact Us

Copyright © 2010-2018 Difference Between. All rights reserved. Terms of Use and Privacy Policy: Legal.