More on Views
A view is the logical collection of data from one table or multiple tables. I should emphasize
the word logical because a view is not a physical collection of data because it contains no
data, the data is gathered from base tables and the view itself is kept in the data dictionary
as a SELECT statement. When the view is executed it will gather the data needed to display the
view. This means the data will be current, representing any changes made to the base tables.
The create view statement itself can have a subquery embedded inside that can contain many
complex SELECT syntax but the subquery cannot have an ORDER BY clause, the order by has to be
used when you display the view.
If I want to create a view with different names then the data in the original tables, I can use
the alias to accomplish this goal.
SQL CODE:
1 CREATE VIEW disord1 AS
2 SELECT l.itemno ITEM_NO, itemname ITEM_NAME, numord NUMBER_ORDERED
3 FROM ordline l, inven i
4* WHERE l.itemno = i.itemno
SQL> /
View created.
SQL> DESC disord1;
Name Null? Type
------------------------------- -------- ----
ITEM_NO VARCHAR2(4)
ITEM_NAME VARCHAR2(15)
NUMBER_ORDERED NUMBER(3)
SQL> SELECT * FROM disord1;
ITEM ITEM_NAME NUMBER_ORDERED
---- --------------- --------------
1111 Good Night Moon 3
1212 Heidi 1
2121 Teddy Bear 1
2345 Doll House 1
3333 Basketball 1
3333 Basketball 2
3456 Net/Hoop 1
7 rows selected.
If you want to modify a view, you use the CREATE OR REPLACE clause which allows the view to be
created as a new view or an old version of the view to be replaced. This means you can change
the view without first deleting it, but it also means you have to be careful or you may loose a
view that you wanted to retain.
SQL CODE:
SQL> CREATE OR REPLACE VIEW disord1
2 AS
3 SELECT l.itemno ITEM_NO, itemname ITEM_NAME, price ITEM_PRICE, numord NUMBER_ORDERED
4 FROM ordline l, inven i
5 WHERE l.itemno = i.itemno;
View created.
SQL> DESC disord1;
Name Null? Type
------------------------------- -------- ----
ITEM_NO VARCHAR2(4)
ITEM_NAME VARCHAR2(15)
ITEM_PRICE NUMBER(6,2)
NUMBER_ORDERED NUMBER(3)
SQL> SELECT * FROM disord1;
ITEM ITEM_NAME ITEM_PRICE NUMBER_ORDERED
---- --------------- ---------- --------------
1111 Good Night Moon 12.99 3
1212 Heidi 14.99 1
2121 Teddy Bear 19.95 1
2345 Doll House 55.98 1
3333 Basketball 17.99 1
3333 Basketball 17.99 2
3456 Net/Hoop 27.95 1
7 rows selected.
The example below shows another way to assign names to the view that are different from the
names in the table(s). In this example, I am only using one table and taking selected fields
from that table and giving them a new name. Note that the alisas list is in the same order as
the column list in the subquery. The where cost > 15 means that only rows where the cost
is > 15 will appear in the new view.
SQL CODE:
1 CREATE VIEW disord2
2 (itmno, itmnam, itmcost, itmprice)
3 AS
4 SELECT itemno, itemname, cost, price
5 FROM inven
6* WHERE COST > 15
SQL> /
View created.
SQL> DESC disord2;
Name Null? Type
------------------------------- -------- ----
ITMNO VARCHAR2(4)
ITMNAM VARCHAR2(15)
ITMCOST NUMBER(6,2)
ITMPRICE NUMBER(6,2)
SQL> SELECT * FROM disord2;
ITMN ITMNAM ITMCOST ITMPRICE
---- --------------- --------- ---------
2222 Building Blocks 23 27.98
2345 Doll House 45 55.98
3456 Net/Hoop 25 27.95
You can update data on the table and in the view through the view. In this case I am updating
the view and changing the cost of item 2222. Notice that the change takes place in both the
view and the base table behind the view. In the second example, I update the table. Notice
that the changes take place in both the table and the view.
SQL CODE:
SQL> SELECT * FROM disord2;
ITMN ITMNAM ITMCOST ITMPRICE
---- --------------- --------- ---------
2222 Building Blocks 23 27.98
2345 Doll House 45 55.98
3456 Net/Hoop 25 27.95
SQL> UPDATE disord2
2 SET itmcost = 48
3 WHERE itmno = '2222';
1 row updated.
SQL> SELECT * FROM disord2;
ITMN ITMNAM ITMCOST ITMPRICE
---- --------------- --------- ---------
2222 Building Blocks 48 27.98
2345 Doll House 45 55.98
3456 Net/Hoop 25 27.95
SQL> SELECT * FROM inven;
ITEM ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA
---- --------------- --------- --------- --------- --------- --------- -- -- ----
1111 Good Night Moon 24 30 40 8 12.99 BK BY X100
1212 Heidi 12 25 25 10 14.99 BK CH X112
1234 Adven Reddy Fox 5 0 10 9 14.75 BK CH X100
2121 Teddy Bear 5 20 40 15 19.95 TY CH X115
2222 Building Blocks 4 0 15 48 27.98 TY CH Z200
2345 Doll House 2 5 10 45 55.98 TY CH Z212
3333 Basketball 24 25 50 14 17.99 SP BK Y200
3456 Net/Hoop 12 0 25 25 27.95 SP BK Y200
8 rows selected.
SQL> UPDATE inven
2 SET price = 51.99
3 WHERE itemno = '2222';
1 row updated.
SQL> SELECT * FROM inven;
ITEM ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA
---- --------------- --------- --------- --------- --------- --------- -- -- ----
1111 Good Night Moon 24 30 40 8 12.99 BK BY X100
1212 Heidi 12 25 25 10 14.99 BK CH X112
1234 Adven Reddy Fox 5 0 10 9 14.75 BK CH X100
2121 Teddy Bear 5 20 40 15 19.95 TY CH X115
2222 Building Blocks 4 0 15 48 51.99 TY CH Z200
2345 Doll House 2 5 10 45 55.98 TY CH Z212
3333 Basketball 24 25 50 14 17.99 SP BK Y200
3456 Net/Hoop 12 0 25 25 27.95 SP BK Y200
8 rows selected.
SQL> SELECT * from disord2;
ITMN ITMNAM ITMCOST ITMPRICE
---- --------------- --------- ---------
2222 Building Blocks 48 51.99
2345 Doll House 45 55.98
3456 Net/Hoop 25 27.95
In the example below, I am putting a constraint on the view which means that through the view
I cannot change any cost so that it falls below the criteria for the view which is that
itmcost > 15. After setting the constraint, first I inserted a new row with a valid cost.
Then, I tried to change the cost of the new record in the view to fall below 15, note the
response. Next, I update the inventory file and changed cost. This time when I showed the
view, this record was not selected because it did not meet the criteria.
SQL CODE:
1 CREATE VIEW disord3
2 AS
3 SELECT *
4 FROM disord2
5 WHERE itmcost > 15
6* WITH CHECK OPTION CONSTRAINT disord2_cost_ck
SQL> /
View created.
SQL> SELECT * FROM disord3;
ITMN ITMNAM ITMCOST ITMPRICE
---- --------------- --------- ---------
2222 Building Blocks 48 51.99
2345 Doll House 45 55.98
3456 Net/Hoop 25 27.95
SQL> DESC disord3;
Name Null? Type
------------------------------- -------- ----
ITMNO VARCHAR2(4)
ITMNAM VARCHAR2(15)
ITMCOST NUMBER(6,2)
ITMPRICE NUMBER(6,2)
SQL> INSERT into disord3
2 VALUES ('6789', 'BAT/BALL', 18, 21.99);
1 row created.
SQL> SELECT * FROM disord3;
ITMN ITMNAM ITMCOST ITMPRICE
---- --------------- --------- ---------
2222 Building Blocks 48 51.99
2345 Doll House 45 55.98
3456 Net/Hoop 25 27.95
6789 BAT/BALL 18 21.99
SQL> UPDATE DISORD3
2 SET itmcost = 14.99
3 WHERE itmno = '6789';
UPDATE DISORD3
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
SQL> UPDATE inven
2 SET cost = 14.99
3 WHERE itemno = '6789';
1 row updated.
SQL> SELECT * from inven;
ITEM ITEMNAME ONHAND ONORDER REORDPT COST PRICE DE IT LOCA
---- --------------- --------- --------- --------- --------- --------- -- -- ----
1111 Good Night Moon 24 30 40 8 12.99 BK BY X100
1212 Heidi 12 25 25 10 14.99 BK CH X112
1234 Adven Reddy Fox 5 0 10 9 14.75 BK CH X100
2121 Teddy Bear 5 20 40 15 19.95 TY CH X115
2222 Building Blocks 4 0 15 48 51.99 TY CH Z200
2345 Doll House 2 5 10 45 55.98 TY CH Z212
3333 Basketball 24 25 50 14 17.99 SP BK Y200
3456 Net/Hoop 12 0 25 25 27.95 SP BK Y200
6789 BAT/BALL 14.99 21.99
9 rows selected.
SQL> SELECT * FROM disord3;
ITMN ITMNAM ITMCOST ITMPRICE
---- --------------- --------- ---------
2222 Building Blocks 48 51.99
2345 Doll House 45 55.98
3456 Net/Hoop 25 27.95
In this example, I created a view that was labeled as READ ONLY, this means that no data base
maintenance operations can be performed through this view.
SQL CODE:
SQL> CREATE VIEW disord4 AS
2 SELECT * FROM disord3
3 WITH READ ONLY;
View created.
SQL> SELECT * FROM disord4;
ITMN ITMNAM ITMCOST ITMPRICE
---- --------------- --------- ---------
2222 Building Blocks 48 51.99
2345 Doll House 45 55.98
3456 Net/Hoop 25 27.95
SQL> INSERT INTO disord4
2 VALUES ('7890', 'Mother Goose', 15.25, 16.99);
INSERT INTO disord4
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
To drop a view from the data dictionary use the command DROP VIEW followed by the name of the
view.
SQL CODE:
SQL> DROP VIEW disporder1;
View dropped.