Oracle Database View

Oracle Database View

• 1.
  Oracle Database View ErykBudi Pratama – Dimas Aryo Anggoro Advanced Database Lab Informatics Engineering –Bakrie University
• 2.
  Definition • A databaseview is a logical or virtual table based on a query. • It is useful to think of a view as a stored query. • Views are created through use of a CREATE VIEW command that incorporates use of the SELECT statement. • Views are queried just like tables.
• 3.
  View Syntax CREATE [ORREPLACE] [FORCE|NOFORCE] VIEW <view name> [(column alias name….)] AS <query> [WITH [CHECK OPTION] [READ ONLY] [CONSTRAINT]];
• 4.
  Example CREATE VIEW student_list (id,last_name, first_name, email) AS SELECT std_id, std_last_name, std_first_name, std_email FROM student ORDER BY std_first_name;
• 5.
  Display View SELECT *FROM <view name>; • Only columns in the query are those defined as part of the view
• 6.
  • OR REPLACEoption is used to create a view that already exists. This option is useful for modifying an existing view without having to drop or grant the privileges that system users have acquired with respect to the view . • If you attempt to create a view that already exists without using the OR REPLACE option, Oracle will return the ORA-00955: name is already used by an existing object error message and the CREATE VIEW command will fail.
• 7.
  • The FORCEoption allows a view to be created even if a base table that the view references does not already exist • NO FORCE option allows a system user to create a view if they have the required permissions to create a view, and if the tables from which the view is created already exist. (Default Option)
• 8.
  • WITH READONLY option allows creation of a view that is read-only • WITH CHECK OPTION clause allows rows that can be selected through the view to be updated • CONSTRAINT clause is used in conjunction with the WITH CHECK OPTION clause to enable a database administrator to assign a unique name to the CHECK OPTION
• 9.
  View Stability • Aview does not actually store any data. The result table is only stored temporarily. • If a table that underlies a view is dropped, then the view is no longer valid. Attempting to query an invalid view will produce an ORA-04063: view "VIEW_NAME" has errors error message.
• 10.
  INSERTING, UPDATING,ANDDELETING TABLEROWSTHROUGHVIEWS • Youcan insert a row if the view in use is one that is updateable (not read-only). • A view is updateable if the INSERT command does not violate any constraints on the underlying tables. • This rule concerning constraint violations also applies to UPDATE and DELETE commands.
• 11.
  Example CREATE OR REPLACEVIEW std_view AS SELECT std_no, std_name FROM student; INSERT INTO std_view VALUES (01, ‘Student 01'); INSERT INTO std_view VALUES (02, ‘Student 02'); SELECT * FROM std_view;
• 12.
  Example UPDATE std_view SETstd_name = ‘Student 13‘ WHERE std_no = 13; SELECT * FROM student WHERE std_no >= 5;
• 13.
  Example DELETE std_view WHERE std_no= 15 OR std_no = 17; SELECT * FROM student;
• 14.
  Dropping View •A DBAor view owner can drop a view with the DROP VIEW command. The following command drops a view named std_view. DROP VIEW std_view;
• 15.
  Summary • A viewdoes not store data, but a view does display data through a SELECT query as if the data were stored in the view. • A view definition as provided by the CREATE VIEW statement is stored in the database. • A view can simplify data presentation as well as provide a kind of data security by limiting access to data based on a "need to know."
• 16.
  Summary • A viewcan display data from more than one table. • Views can be used to update the underlying tables. Views can also be limited to read-only access. • Views can change the appearance of data. For example, a view can be used to rename columns from tables without affecting the base table. • A view that has columns from more than one table cannot be modified by an INSERT, DELETE, or UPDATE command if a grouping function, GROUP BY clause is part of the view definition.
• 17.
  Q&A