More on views

1
More on views

• Please refer to speaker notes for additional
  information!

2
Student view
In this slide, I am creating a view with
information from the student00 table and the
major00 table.
SQLgt CREATE VIEW stuview1 2 AS 3 SELECT
name, s.majorcode, majorname 4 FROM
student00 s, major00 m 5 WHERE s.majorcode
m.majorcode View created. SQLgt DESC
stuview1 Name Null?
Type ------------------------------- --------
---- NAME
VARCHAR2(20) MAJORCODE
VARCHAR2(2) MAJORNAME
VARCHAR2(30) SQLgt SELECT FROM
stuview1 NAME MA
MAJORNAME -------------------- --
------------------------------ Stephen Daniels
BU Business Administration Jennifer Ames
CI Computer Information Systems Carl Hersey
BU Business Administration Mary Stanton
CI Computer Information Systems John Richards
CI Computer Information Systems
3
Student view
This allows you to create a new view or modify an
existing view by replacing the specifications.
SQLgt CREATE OR REPLACE VIEW stuview1 2 AS 3
SELECT name, s.majorcode, majorname, enrolled
4 FROM student00 s, major00 m 5 WHERE
s.majorcode m.majorcode View created. SQLgt
DESC stuview1 Name
Null? Type -------------------------------
-------- ---- NAME
VARCHAR2(20) MAJORCODE
VARCHAR2(2) MAJORNAME
VARCHAR2(30) ENROLLED
DATE SQLgt SELECT FROM
stuview1 NAME MA MAJORNAME
ENROLLED -------------------- --
------------------------------ --------- Stephen
Daniels BU Business Administration
09-SEP-00 Jennifer Ames CI Computer
Information Systems 02-SEP-00 Carl Hersey
BU Business Administration
02-SEP-00 Mary Stanton CI Computer
Information Systems 05-SEP-00 John Richards
CI Computer Information Systems 06-SEP-00
4
Pay view
SQLgt CREATE VIEW payview1 2 (empno, empname,
empjobcode, empsalary, empbonus) 3 AS 4
SELECT pay_id, name, jobcode, salary, bonus 5
FROM first_pay 6 WHERE salary gt
30000 View created. SQLgt DESC payview1 Name
Null? Type
------------------------------- -------- ----
EMPNO
VARCHAR2(4) EMPNAME
VARCHAR2(20) EMPJOBCODE
CHAR(2) EMPSALARY
NUMBER(9,2) EMPBONUS
NUMBER(5) SQLgt SELECT FROM
payview1 EMPN EMPNAME EM EMPSALARY
EMPBONUS ---- -------------------- -- ---------
--------- 1111 Linda Costa CI 45000
1000 2222 John Davidson IN 40000
1500 4444 Stephen York CM 42000
2000 5555 Richard Jones CI 50000
2000 6666 Joanne Brown IN 48000
2000 7777 Donald Brown CI 45000 8888
Paula Adams IN 45000 2000 7
rows selected.
New names are given to the columns/fields in the
view. Pay_id will be come empno, name will
become empname etc.
5
SQLgt CREATE VIEW new_payview1 2 (empno,
empname, empjobcode, empsalary, empbonus) 3
AS 4 SELECT pay_id, name, jobcode, salary,
bonus 5 FROM new_first_pay 6 WHERE
salary gt 30000
Pay view for update
I decided to modify new_first_pay, so I recreated
the view from the previous slide and named it
new_payview1.
SQLgt SELECT FROM new_first_pay PAY_ NAME
JO STARTDATE SALARY BONUS ----
-------------------- -- --------- ---------
--------- 1111 Linda Costa CI 15-JAN-97
45000 1000 2222 John Davidson IN
25-SEP-92 40000 1500 3333 Susan Ash
AP 05-FEB-00 25000 500 4444
Stephen York CM 03-JUL-97 42000
2000 5555 Richard Jones CI 30-OCT-92
50000 2000 6666 Joanne Brown IN
18-AUG-94 48000 2000 7777 Donald Brown
CI 05-NOV-99 45000 8888 Paula Adams
IN 12-DEC-98 45000 2000 9999 Joseph
Souza IN 35000
SQLgt SELECT FROM new_payview1 EMPN EMPNAME
EM EMPSALARY EMPBONUS ----
-------------------- -- --------- --------- 1111
Linda Costa CI 45000 1000 2222
John Davidson IN 40000 1500 4444
Stephen York CM 42000 2000 5555
Richard Jones CI 50000 2000 6666
Joanne Brown IN 48000 2000 7777
Donald Brown CI 45000 8888 Paula
Adams IN 45000 2000 9999 Joseph
Souza IN 35000
6
SQLgt SELECT FROM new_payview1 EMPN EMPNAME
EM EMPSALARY EMPBONUS ----
-------------------- -- --------- --------- 1111
Linda Costa CI 45000 1000 2222
John Davidson IN 40000 1500 4444
Stephen York CM 42000 2000 5555
Richard Jones CI 50000 2000 6666
Joanne Brown IN 48000 2000 7777
Donald Brown CI 45000 8888 Paula
Adams IN 45000 2000 9999 Joseph
Souza IN 35000 1500 8 rows
selected.
Updating view
SQLgt UPDATE new_payview1 2 SET empbonus
1500 3 WHERE empno '9999' 1 row updated.
The update statement updates the data in
new_payview1 as shown. When the view is updated,
it also updates the data in the original table.
SQLgt SELECT FROM new_first_pay PAY_ NAME
JO STARTDATE SALARY BONUS ----
-------------------- -- --------- ---------
--------- 1111 Linda Costa CI 15-JAN-97
45000 1000 2222 John Davidson IN
25-SEP-92 40000 1500 3333 Susan Ash
AP 05-FEB-00 25000 500 4444
Stephen York CM 03-JUL-97 42000
2000 5555 Richard Jones CI 30-OCT-92
50000 2000 6666 Joanne Brown IN
18-AUG-94 48000 2000 7777 Donald Brown
CI 05-NOV-99 45000 8888 Paula Adams
IN 12-DEC-98 45000 2000 9999 Joseph
Souza IN 35000 1500 9
rows selected.
7
SQLgt SELECT FROM empx IDN NAME
DE --- -------------------- -- 111 John Doe
AP 222 Mary Jones AR 333 David
Souza AP 444 Susan Brooks AR 555
Michael Brown IN SQLgt SELECT FROM
deptx DE DEPTNAME -- ---------- AP Acct Pay AR
Acct Recv IN Inventory
View with multiple tables
I created the two tables shown and then combined
the information from the two tables into a view
called empdeptx.
SQLgt CREATE VIEW empdeptx 2 AS 3 SELECT
idno, name, empx.dept, deptname 4 FROM empx,
deptx 5 WHERE empx.dept deptx.dept View
created. SQLgt SELECT FROM empdeptx IDN NAME
DE DEPTNAME --- -------------------
- -- ---------- 111 John Doe AP Acct
Pay 333 David Souza AP Acct Pay 222 Mary
Jones AR Acct Recv 444 Susan Brooks
AR Acct Recv 555 Michael Brown IN
Inventory
8
Update view
SQLgt UPDATE empx 2 SET name 'John Adams' 3
WHERE idno '111' 1 row updated. SQLgt SELECT
FROM empx IDN NAME DE ---
-------------------- -- 111 John Adams
AP 222 Mary Jones AR 333 David Souza
AP 444 Susan Brooks AR 555 Michael
Brown IN SQLgt SELECT FROM
empdeptx IDN NAME DE
DEPTNAME --- -------------------- --
---------- 111 John Adams AP Acct
Pay 333 David Souza AP Acct Pay 222 Mary
Jones AR Acct Recv 444 Susan Brooks
AR Acct Recv 555 Michael Brown IN
Inventory
In this example, I updated the table empx. The
change in the table is also shown in the view.
9
View constraints
I created a view with a constraint that did not
allow a salary to be in the view that was not gt
30000. Only records that met that criteria were
originally placed in the view. When I tried to
alter a record in the view to come in below the
criteria, it was rejected as a check option
violation.
SQLgt CREATE VIEW new_payview2 2 AS 3 SELECT
FROM new_first_pay 4 WHERE salary gt 30000
5 WITH CHECK OPTION CONSTRAINT sal30K_ck
SQLgt UPDATE new_payview2 2 SET salary 29500
3 WHERE pay_id '9999' SET salary 29500
ERROR at line 2 ORA-01402 view WITH
CHECK OPTION where-clause violation SQLgt UPDATE
new_payview2 2 SET salary 30500 3 WHERE
pay_id '9999' 1 row updated.
SQLgt SELECT FROM new_payview2 PAY_ NAME
JO STARTDATE SALARY BONUS ----
-------------------- -- --------- ---------
--------- 1111 Linda Costa CI 15-JAN-97
45000 1000 2222 John Davidson IN
25-SEP-92 40000 1500 4444 Stephen York
CM 03-JUL-97 42000 2000 5555
Richard Jones CI 30-OCT-92 50000
2000 6666 Joanne Brown IN 18-AUG-94
48000 2000 7777 Donald Brown CI
05-NOV-99 45000 8888 Paula Adams IN
12-DEC-98 45000 2000 9999 Joseph Souza
IN 30500 1500 8 rows
selected.
Here the new salary is above 30000, so the change
is allowed.
10
Update
SQLgt UPDATE new_first_pay 2 SET SALARY
29500 3 WHERE pay_id '9999' 1 row updated.
SQLgt SELECT FROM new_first_pay PAY_ NAME
JO STARTDATE SALARY BONUS ----
-------------------- -- --------- ---------
--------- 1111 Linda Costa CI 15-JAN-97
45000 1000 2222 John Davidson IN
25-SEP-92 40000 1500 3333 Susan Ash
AP 05-FEB-00 25000 500 4444
Stephen York CM 03-JUL-97 42000
2000 5555 Richard Jones CI 30-OCT-92
50000 2000 6666 Joanne Brown IN
18-AUG-94 48000 2000 7777 Donald Brown
CI 05-NOV-99 45000 8888 Paula Adams
IN 12-DEC-98 45000 2000 9999 Joseph
Souza IN 29500 1500 9
rows selected. SQLgt SELECT FROM
new_payview2 PAY_ NAME JO
STARTDATE SALARY BONUS ----
-------------------- -- --------- ---------
--------- 1111 Linda Costa CI 15-JAN-97
45000 1000 2222 John Davidson IN
25-SEP-92 40000 1500 4444 Stephen York
CM 03-JUL-97 42000 2000 5555
Richard Jones CI 30-OCT-92 50000
2000 6666 Joanne Brown IN 18-AUG-94
48000 2000 7777 Donald Brown CI
05-NOV-99 45000 8888 Paula Adams IN
12-DEC-98 45000 2000 7 rows selected.
In this example, I updated the table
new_first_pay. The update was successful, but it
put 9999 below the criteria for the view
new_payview2. When I did a select on that view,
the record with pay_id 9999 that now has a salary
below the view criteria (salary gt 30000) is no
longer there.
11
Read only
SQLgt CREATE VIEW new_payview3 2 AS 3 SELECT
FROM new_first_pay 4 WITH READ ONLY View
created. SQLgt SELECT FROM new_payview3 PAY_
NAME JO STARTDATE SALARY
BONUS ---- -------------------- -- ---------
--------- --------- 1111 Linda Costa CI
15-JAN-97 45000 1000 2222 John Davidson
IN 25-SEP-92 40000 1500 3333 Susan
Ash AP 05-FEB-00 25000
500 4444 Stephen York CM 03-JUL-97
42000 2000 5555 Richard Jones CI
30-OCT-92 50000 2000 6666 Joanne Brown
IN 18-AUG-94 48000 2000 7777
Donald Brown CI 05-NOV-99 45000 8888
Paula Adams IN 12-DEC-98 45000
2000 9999 Joseph Souza IN
29500 1500 9 rows selected. SQLgt UPDATE
new_payview3 2 SET salary 42000 3 WHERE
pay_id '2222' SET salary 42000 ERROR
at line 2 ORA-01733 virtual column not allowed
here
Since this view was created as read only, it
cannot be updated.
12
Drop view
SQLgt DROP VIEW new_payview3 View dropped. SQLgt
SELECT FROM new_payview3 SELECT FROM
new_payview3 ERROR at line
1 ORA-00942 table or view does not exist