1 / 22

Creating Views

Creating Views. Objectives. Explain the concept of a view. Create simple and complex views. Retrieve data through a view. Alter the definition of a view. Insert, Update, Delete data through a view. Drop a view. What Is a View?. ID LAST_NAME FIRST_NAME TITLE DEPT_ID

rianne
Télécharger la présentation

Creating Views

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Creating Views

  2. Objectives • Explain the concept of a view. • Create simple and complex views. • Retrieve data through a view. • Alter the definition of a view. • Insert, Update, Delete data through a view. • Drop a view.

  3. What Is a View? ID LAST_NAME FIRST_NAME TITLE DEPT_ID -- ------------ ---------- -------------------- ------- 1 Velasquez Carmen President 50 2 Ngao LaDoris VP, Operations 41 3 Nagayama Midori VP, Sales 31 4 Quick-To-See Mark VP, Finance 10 5 Ropeburn Audry VP, Administration 50 6 Urguhart Molly Warehouse Manager 41 7 Menchu Roberta Warehouse Manager 42 8 Biri Ben Warehouse Manager 43 9 Catchpole Antoinette Warehouse Manager 44 10 Havel Marta Warehouse Manager 45 11 Magee Colin Sales Representative 31 12 Giljum Henry Sales Representative 32 13 Sedeghi Yasmin Sales Representative 33 14 Nguyen Mai Sales Representative 34 15 Dumas Andre Sales Representative 35 16 Maduro Elena Stock Clerk 41 17 Smith George Stock Clerk 41 18 Nozaki Akira Stock Clerk 42 19 Patel Vikram Stock Clerk 42 20 Newman Chad Stock Clerk 43 21 Markarian Alexander Stock Clerk 43 22 Chang Eddie Stock Clerk 44 23 Patel Radha Stock Clerk 34 24 Dancs Bela Stock Clerk 45 25 Schwartz Sylvie Stock Clerk 45 EMP Table EMPVU45 View ID LAST_NAME TITLE -- ----------- --------------- 10 Havel Warehouse Manager 24 Dancs Stock Clerk 25 Schwartz Stock Clerk

  4. Why Use Views? • To restrict database access • To make complex queries easy • To allow data independence • To present different views of the same data

  5. Simple Views and Complex Views Simple Views One No No Yes Complex Views One or more Yes Yes Not always Number of tables Contain functions Contain groups of data DML through view

  6. Creating a View: Syntax • Embed a subquery within the CREATE VIEW statement. • The subquery can contain complex SELECT syntax. • The subquery cannot contain an ORDER BY clause. CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY]

  7. Creating a View: Syntax CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view [(alias[, alias]...)] AS subquery [WITH CHECK OPTION [CONSTRAINT constraint]] [WITH READ ONLY] • OR REPLACE – re-create the view if it already exists. • FORCE – create the view regardless whether the base table exists or not. • WITH CHECK OPTION – specifies that only rows accessible to the view can be inserted or updated. • WITH READ ONLY – no DML can be performed.

  8. Creating a View: Example • Create the EMPVU10 view, which contains the employee number, last name, and job title for employees in department 10. • Describe the structure of the view by using the SQL*Plus DESCRIBE command. • Display data from the view by entering a SELECT statement against the view. SQL> CREATE VIEW empvu10 2 AS SELECT empno, ename, job 3 FROM emp 4 WHERE deptno = 10; View created.

  9. Creating a View: Example • Create a view by using column aliases in the subquery. • Select the columns from this view by the given alias name. SQL> CREATE VIEW salvu30 2 AS SELECT empno EMPLOYEE_NUMBER, ename FIRST_NAME, 3 sal MONTHLY_SALARY 4 FROM emp 5 WHERE deptno = 30; View created.

  10. Modifying a View: Example • Modify the EMPVU10 view by using CREATE OR REPLACE. Add an alias for each column name. • Column aliases in the CREATE VIEW clause are listed in the same order as the columns in the subquery. SQL> CREATE OR REPLACE VIEW empvu10 2 (id_number, employee, title) 3 AS SELECT empno, ename, job 4 FROM emp 5 WHERE deptno = 10; View created.

  11. Creating a Complex View: Example Create a complex view that contains group functions to display values from two tables. SQL> CREATE VIEW dept_sum_vu 2 (name, minsal, maxsal, avgsal) 3 AS SELECT d.dname, MIN(e.sal), 4 MAX(e.sal), AVG(e.sal) 5 FROM emp e, dept d 6 WHERE e.deptno = d.deptno 7 GROUP BY d.dname; View created.

  12. Rules for Performing DML Operations on a View • You can perform DML operations on simple views. • You cannot remove a row if the view contains • Group functions. • A GROUP BY clause. • The DISTINCT keyword.

  13. Rules for Performing DML Operations on a View continued • You cannot modify data in a view if it contains • Any of the above conditions. • Columns defined by expressions. • The ROWNUM pseudocolumn. • You cannot add data if the view contains • Any of the above conditions. • Any NOT NULL columns in the base table not selected by the view.

  14. Using the WITH CHECK OPTION Clause • Ensure that DML on the view stays within the domain of the view. • If you attempt to change the department number for any rows in the view, the statement will fail because it violates the CHECK OPTION constraint. SQL> CREATE OR REPLACE VIEW empvu20 2 AS SELECT * 3 FROM emp 4 WHERE deptno = 20 5 WITH CHECK OPTION CONSTRAINT empvu20_ck; View created.

  15. Using the WITH CHECK OPTION Clause • Without check option, employee with empno = 7788 will be removed from the view. SQL> UPDATE empvu20 2 SET deptno = 10 3 WHERE empno = 7788; Update empvu20 * ERROR at line 1: ORA-01402: view WITH CHECK OPTION where-clause violation

  16. Denying DML Operations • Ensure that no DML operations occur by adding the WITH READ ONLY option to your view definition. • If you attempt to perform a DML on any rows in the view, you will see the Oracle Server error. SQL> CREATE OR REPLACE VIEW empvu10 2 (id_number, employee, title) 3 AS SELECT empno, ename, job 4 FROM emp 5 WHERE deptno = 10 6 WITH READ ONLY; View created.

  17. Confirming Views The USER_VIEWS data dictionary table contains the name of the view and the view definition. SQL> SELECT view_name, text 2 FROM user_views;

  18. Removing a View: Example Remove a view without losing data because a view is based on underlying tables in the database. SQL> DROP VIEW empvu10; View dropped.

  19. Summary • A view is derived from data in other tables or other views. • A view is like a window to the underlying data. • A view provides the following advantages: • Restrict database access • Simplify queries • Provide data independence • Allow multiple views of the same data • Can be dropped without removing the underlying data

  20. Practice Overview • Creating a simple view • Creating a complex view • Creating a view with a check constraint • Attempting to modify data in the view • Displaying view definitions • Removing views

  21. Practice 1 • Create a view called DEPT20 that contains the employee number, name and department number for all employees in department 20 from EMP table. Label the view column EMPLOYEE_ID, EMPLOYEE and DEPARTMENT_ID. Do not allow an employee to be reassigned to another department through the view. • Display the structure and contents of the DEPT20 view. • Select the view name and text from the data dictionary USER_VIEWS. • Attempt to reassign Smith to department 30.

  22. Practice 2 • Create a view called SALARY_VU based on the employee name, depart name, salary and salary grade for all employees, using tables EMP, DEPT and SALGRADE. Label the columns Employee, Department, Salary and Grade respectively.

More Related