Difference Between View and Stored Procedure
Views and stored procedures are two different kinds of database objects. Gathering data from one or more tables, view is a type of stored query. One of the major differences between the two is that view works as a virtual table and can be used directly in the closing of SQL queries. Stored procedure, on the other hand, cannot be used from the close of queries. Just like their body, views just possess a selective statement; whereas, stored procedures can have variable assignments, variable declarations, loops, SQL queries and control statements. Other functions, procedures and packages act as the body of the stored procedures.
Views do not accept parameters; whereas, stored procedures accept parameters. Another big difference between the two is that views can be used as a building block in large queries but the stored procedures cannot be used in large queries. Views only have the capacity to contain just a single select query but stored procedures can contain multiple statements like if, else, loop etc. Another difference between the two is that views are not able to modify any table and stored procedures can modify one or more tables. At times, views can be used as the target for update, insert or delete queries but stored procedures cannot do the same job. Inside a view select statement, SQL hints can be used to enhance the execution plan. However, stored procedures cannot facilitate from SQL hints. Using %ROWTYPE, record types can be created but this is not the case with stored procedures.
Instructions
-
1
View
A virtual table which hides a select statement inside a body is called a view. The select statement is not easy to understand for the common man as it takes data from a number of other tables and views. According to some experts, a view can be referred to as a named select statement stored in the database. Logic behind the table relations can be hidden from the end users through a view. This is possible because a view does not keep any data as it itself is a result of a stored query.
- Image courtesy: docs.oracle.com
-
2
Stored Procedure
Named programming blocks are called stored procedures. A stored procedure must have a separate identity. They process according to the logic behind the procedure, give results and accept parameters as user input.
- Image courtesy: xunitpatterns.com
