![]() ![]() ![]() Next, if a view is created as the result of a JOIN operation (a join view), the UPDATE and INSERT statements can only modify or insert rows into one of the base tables at a time. Also, the view cannot have any derived columns in the SELECT list. When you execute an UPDATE, DELETE, or INSERT DML statement on a view, you are actually manipulating the data rows for the base table or tables on which the view is defined.There are restrictions on the use of UPDATE, DELETE, and INSERT statements with views.First, to use the UPDATE, DELETE, or INSERT statement with a view, the view must be updateable.A view is updateable if the SELECT clause does not specify any aggregate function in the SELECT listing.Additionally, the view could not have been created through use of a GROUP BY, DISTINCT, or UNION clause or clauses.It is permissible for aggregate functions to be used in a SELECT subquery in a FROM clause. DML operations on a ViewĭML operations can be easily exercised on simple views.As stated earlier, the insert, update and delete operations actually happen on the base table. Columns are listed in the same sequence as in the view definition. SELECT department_name, AVG (salary) avg_sal, COUNT (first_name) countĭESCRIBE describes the view structure. CREATE OR REPLACE VIEW v_emp_devĬomplex view: The below example shows the department name, average salary drawn in the department and the count of employees working in it. Simple View: The below simple view select employee name, department id and salary for the employees with JOB ID as DEV. Types of ViewsĪ Simple view is created on top of one table only.It is a simple SELECT query with no functions or group clause,but just selection of columns from the table without any transformation.If a DML is performed on the view, it is straightaway reflected in the base table.Ī Complex view is created on multiple tables using joins.It can contain SQL functions,Group by functions.But since the view is on multiple data and selection of columns is also not simple, it does not allow DML operation on it. The WITH CHECK OPTION clause allows the update of rows that can be selected through the view.It also enables you to specify constraints on values.The CONSTRAINT clause works in conjunction with the WITH CHECK OPTION clause to enable a database administrator to assign a unique name to the CHECK OPTION.If a database administrator omits the CONSTRAINT clause,Oracle will automatically assign the constraint a system-generated name that will not be very meaningful. The WITH READ ONLY option allows creation of a view that is read-only.You cannot use the DELETE,INSERT,or UPDATE commands to modify data for a read-only view. The NOFORCE option is the opposite of FORCE and allows a system user to create a view if they have the required privileges to create a view, and if the tables from which the view is created already exist. The FORCE option allows a view to be created even if a base table that the view references does not already exist.This option is used to create a view prior to the actual creation of the base tables and accompanying data. ViewsĪ database view is a logical or virtual table based on a query.Views are queried just like tables.This means that from your perspective as a developer or from a database system user's perspective, a view looks like a table.The definition of a view as an object is stored within a database's data dictionary however,a view stores no data itself.A database also stores the execution plan for creating a view-this means that data can be retrieved rapidly through use of a view even though the actual data presented by a SELECT query of a view is not stored as part of a view.Rather,the data is "gathered together" each time that a view is queried from the database tables for which a view is defined-these are termed base tables. Synonyms are simply alias names for database objects.Synonyms also simplify query writing and provide an element of system security by disguising the actual name of a database object.Sequences are special database objects that support the automatic generation of integer values,and are often used to generate primary key values for tables.Indexes are created on table columns to facilitate the rapid retrieval of information from tables. Apart from tables, other essential schema objects are view, sequences,indexes and synonyms.A view is a logical or virtual table. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |