html5-img
1 / 12

More on views

More on views. Please refer to speaker notes for additional information!. Student view. In this slide, I am creating a view with information from the student00 table and the major00 table. SQL> CREATE VIEW stuview1 2 AS 3 SELECT name, s.majorcode, majorname

honora
Télécharger la présentation

More on 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. 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. SQL> 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. SQL> DESC stuview1; Name Null? Type ------------------------------- -------- ---- NAME VARCHAR2(20) MAJORCODE VARCHAR2(2) MAJORNAME VARCHAR2(30) SQL> 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. SQL> 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. SQL> DESC stuview1 Name Null? Type ------------------------------- -------- ---- NAME VARCHAR2(20) MAJORCODE VARCHAR2(2) MAJORNAME VARCHAR2(30) ENROLLED DATE SQL> 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 SQL> 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 > 30000; View created. SQL> DESC payview1 Name Null? Type ------------------------------- -------- ---- EMPNO VARCHAR2(4) EMPNAME VARCHAR2(20) EMPJOBCODE CHAR(2) EMPSALARY NUMBER(9,2) EMPBONUS NUMBER(5) SQL> 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. SQL> 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 > 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. SQL> 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 SQL> 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. SQL> 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 SQL> 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. SQL> 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. SQL> 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 SQL> 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. SQL> CREATE VIEW empdeptx 2 AS 3 SELECT idno, name, empx.dept, deptname 4 FROM empx, deptx 5 WHERE empx.dept = deptx.dept; View created. SQL> 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 SQL> UPDATE empx 2 SET name = 'John Adams' 3 WHERE idno = '111'; 1 row updated. SQL> 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 SQL> 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 > 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. SQL> CREATE VIEW new_payview2 2 AS 3 SELECT * FROM new_first_pay 4 WHERE salary > 30000 5 WITH CHECK OPTION CONSTRAINT sal30K_ck; SQL> 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 SQL> UPDATE new_payview2 2 SET salary = 30500 3 WHERE pay_id = '9999'; 1 row updated. SQL> 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 SQL> UPDATE new_first_pay 2 SET SALARY = 29500 3 WHERE pay_id = '9999'; 1 row updated. SQL> 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. SQL> 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 > 30000) is no longer there.

  11. Read only SQL> CREATE VIEW new_payview3 2 AS 3 SELECT * FROM new_first_pay 4 WITH READ ONLY; View created. SQL> 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. SQL> 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 SQL> DROP VIEW new_payview3; View dropped. SQL> SELECT * FROM new_payview3; SELECT * FROM new_payview3 * ERROR at line 1: ORA-00942: table or view does not exist

More Related