7.1 How do I…Create a view? Problem

I want to create views to improve the security of my data and to represent complex queries simply. I understand that views can be created to represent subsets of data or queries on tables and other views in a virtual table. How do I create views?

Technique

The CREATE VIEW statement creates views. The statement will show compilation errors when run, if they exist, but the view will still be created in the data dictionary. Use the CREATE VIEW statement to make a subset of data, or to join several tables, all invisible to the user accounts. This How-To will create a table, insert sample data into it, create a view limiting the data, and then retrieve the data through the view.

Steps

1. Run SQL*Plus and connect as the WAITE user account. Load CHP7_1.SQL into the SQL buffer. The file contains a CREATE TABLE statement to build the table that will be referenced in the first sample view.

SQL> GET CHP7_1.sql

1 CREATE TABLE DEPT07 (

2 DEPT_NO NUMBER(10),

3 DNAME VARCHAR2(30),

4* MGR_ID NUMBER(10))

The DEPT07 table contains all of the columns that will be referenced by the sample view. If it did not, the view would be invalid when created.

2. Execute the statement to create the table.

SQL> /

Table created.

3. Load the CHP7_2.SQL into the SQL buffer and run it. The file contains a CREATE VIEW statement to create a view on the DEPT07 table.

SQL> GET CHP7_2.sql

1 CREATE VIEW

2 DEPT_VIEW07

3 AS SELECT

4 DEPT_NO,

5 DNAME,

6 MGR_ID

7 FROM DEPT07

8 WHERE DEPT_NO=4

SQL> /

View created.

The DEPT_VIEW07 view will show only records where the DEPT_NO=4. This type of view is useful for restricting data that users may see, on a record level.

4. Insert sample records into the DEPT07 table by running CHP7_3.SQL, which is shown in Figure 7.1.

SQL> START CHP7_3.sql

1 row created.

1 row created.

1 row created.

1 row created.

SQL>

5. Now, select everything from the view by running CHP7_4.SQL, as shown in Figure 7.2. You will see how the data is selected based on the restriction that the DEPT_NO=4.

How It Works

Steps 1 and 2 create the DEPT07 table that will be referenced by the view. Step 3 creates a view using a CREATE VIEW statement, based upon the DEPT07 table, restricting data to the records where DEPT_NO = 4. Step 4 inserts sample records into the DEPT07 table to demonstrate how a view may limit data seen by a user account. Step 5 selects records from the view, demonstrating that the view limits data returned to those records whose DEPT_NO = 4.

Views can be created to restrict data in any manner, or to join several tables in a simple or complex fashion. In a more advanced setup, views can also be used to select data across several databases, and the user accounts would view the data as if it were local.

Comments

Object views are useful when dealing with abstract data types and object-relational data. See How-To 7.5 to learn more about object views. Also, views that contain several tables may be inserted, updated, or deleted. How-To 7.4 shows how to create an updatable join view.

It is also possible to create a view that contains an invalid query. The query cannot contain syntax errors, but tables or columns referenced in the query do not have to exist. The FORCE option in the CREATE VIEW statement allows invalid views to be created.