Database Programming

1. Database Programming Sections 11 – Creating, and Managing Views, Sequences, Indexes, and Synonymns

2. What is a View? • A view is a query stored as a SELECT statement in the data dictionary. A table of logical subsets or combinations of data based on a table or another view. • A ”window” into the database for convenience/per/permission • Presents data from one or more tables in one place • Two Types of Views • Simple • Complex Marge Hohly

3. Example of a View • CREATE VIEW view_employees AS SELECT first_name, last_name, emailFROM employeesWHERE employee_id BETWEEN 100 and 124; • SELECT * FROM view_employees; Marge Hohly

4. Why use view? • Views restrict access to base table data because the view can display selective columns from the table • Views can be used to reduce the complexity of executing queries based on more complicated SELECT statements. For example, the creator of the view can construct join statements that retrieve data from multiple tables. The user of the view neither sees the underlying code nore have to create it. The user, through the view, interacts with the database using simple queries. Marge Hohly

5. Why use Views? Cont. • Views can be used to retrieve data from several tables, providing data independence for users. Users can view the same data in different ways. • Views provide groups of users with access to data according to their particular permissions of criteria. Marge Hohly

6. The Syntax For Creating a View • CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view_name [(alias [,alias]....)]AS subquery[WITH CHECK OPTION [CONSTRAINT constraint_name]][WITH READ ONLY [CONSTRAINT constraint_name]]; • OR REPLACE – recreates the view if it already exists • FORCE – creates the view regardless of whether or not the base tables exist • NO FORCE – creates the view only if the base tables exist (DEFAULT) • view_name – name of the view • alias – specifies a name of each expression selected by the view’s query • subquery – a complete SELECT statement (you can use aliases for the columns in the SELECT list) • WITH CHECK OPTION – specifies that only rows accessible to the view can be inserted or updated • WITH READ ONLY – ensures that NO DML operations can be performed on this view Marge Hohly

7. Guidelines for Creating a View • The subquery that defines the view can contain complex SELECT syntax • The subquery that defines the view cannot contain an ORDER BY clause • You can use the OR REPLACE option to change the definition of the view without having to drop or re-grant object privileges previously granted • Aliases can be used for the column names in the subquery Marge Hohly

8. Aliases in a View • Column names in the SELECT statement can have aliases as shown below. Note that aliases can also be listed after the CREATE VIEW statement and before the SELECT subquery • CREATE VIEW view_copy_d_cdsAS SELECT cd_number AS “Number”, title AS “Title”, year AS “Year Recorded”FROM d_cds; • CREATE VIEW view_copy_d_cds(Number, Title, Year Recorded)AS SELECT cd_number, title, yearFROM d_cds; Marge Hohly

9. Simple vs. Complex Marge Hohly

10. Simple View • CREATE VIEW view1_copy_d_cdsAS SELECT cd_number, title, producer, yearFROM d_cds; Marge Hohly

11. Simple View Example • CREATE VIEW view2_copy_d_cds AS SELECT cd_number “Number”, title AS “Title”, year AS “Year Recorded”)FROM copy_d_cds; • CREATE VIEW view3_copy_d_cds(Number, Title, Year_Recorded) AS SELECT cd_number, title, year FROM d_cds; Marge Hohly

12. Simple View • Can create a view whether or not a base table exists • User word FORCE in CREATE VIEW statement • NOFORCE is the default Marge Hohly

13. Complex View Example • CREATE VIEW view_dj_on_demand (LAST_NAME, PHONE, EVENT, DATE_HELD) AS SELECT c.last_name, c.phone, e.name, TO_CHAR(e.event_date, ‘Month dd, YYYY’)FROM d_clients c, d_events eWHERE c.client_number = e.client_number; Marge Hohly

14. Complex View • Group functions can be added to complex-view statements • CREATE VIEW view_dj_cds(Title, Song, Min_Year, Max_year)AS SELECT c.title, t.song_id, MIN(c.year), MAX(c.year)FROM d_cds c, d_track_listings tWHERE c.cd_number = t.cd_numberGROUP BY c.cd_number, c.title, t.song_id; Marge Hohly

15. Modifying a View • To modify a view, use the [OR REPLACE] option • The old view will be replaced by the new version • CREATE OR REPLACE VIEW view_copy_d_cdsAS SELECT cd_number, producer, title, yearFROM copy_d_cds; Marge Hohly

16. DML Operations on a View • DML operations (INSERT, UPDATE, and DELETE) can be performed on a simple view • Data in the underlying base tables can be changed also • To prevent unintended changes, the DBA can control data access using the WITH CHECK OPTION and WITH READ ONLY constraints Marge Hohly

17. WITH CHECK OPTION CONSTRAINT • CREATE OR REPLACE VIEW view_dept50 AS SELECT department_id, employee_id, first_name, last_name, salaryFROM employeesWHERE department_id = 50WITH CHECK OPTION CONSTRAINT view_dept50_check; • UPDATE view_dept50SET department_id = 190WHERE employee_id = 141;NOTE: ORA-01402: view WITH CHECK OPTION where-clause violation Marge Hohly

18. WITH READ ONLY CONSTRAINT • CREATE OR REPLACE VIEW view_dept50 ASSELECT department_id, employee_id, first_name, last_name, salaryFROM employeesWHERE department_id = 50WITH READ ONLY CONSTRAINT view_dept50_read; • DELETE FROM view_dept50WHERE employee_id = 144;ORA-01752: cannot delete from view without exactly one key-preserved table Marge Hohly

19. DML Restrictions on a View • You cannot REMOVE a row from an underlying base table if the view contains any of the following: • Group functions • A GROUP BY clause • The pseudocolumn ROWNUM keyword • The DISTINCT keyword • ROWNUM is just a number value given to each row in the result set. For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on. • You can use ROWNUM to limit the number of rows returned by a query, as in this example:SELECT * FROM employees WHERE ROWNUM < 10; Marge Hohly

20. DML Restrictions on a View • You cannot MODIFY data through a view if the view contains: • Group functions • A GROUP BY clause • The pseudocolumn ROWNUM keyword • The DISTINCT keyword • Columns defined by expressions Marge Hohly

21. DML Restrictions on a VIEW • You cannot ADD data through a view if the view • includes group functions • includes a GROUP BY clause • includes the pseudocolumn ROWNUM keyword • includes the DISTINCT keyword • includes columns defined by expressions • does not include NOT NULL columns in the base tables – the user of the view must know which column in the base table are NOT NULL – these columns must be in the view. Marge Hohly

22. Deleting a View • DROP VIEW viewname; • Removing a view does not effect the data in the underlying tables • If the view was used to manipulate data in the past, these changes to the base tables remain • Only the creator or users with the DROP ANY VIEW privilege can remove a view Marge Hohly

23. What is an Inline View? • Also known as queries in the FROM clause • The view is created “on the fly” instead of saving the view as a separate object • A common use for in-line views in Oracle SQL is the simplify complex queries by removing join operations and condensing several separate queries into a single query. Marge Hohly

24. Inline View Example • SELECT e.name, e.description, p.maxrange, p.codeFROM d_events e, (SELECT code, max(high_range) maxrange FROM d_packages GROUP BY code) pWHERE e.package_code = p.codeAND e.cost < p.maxrange; • The data returned by the subquery is given an alias, which is then used in conjunction with the main query to return selected columns from both query sources. Marge Hohly

25. Inline View Example • SELECT code, max(high_range) maxrangeFROM d_packagesGROUP BY code; Marge Hohly

26. Top-N Analysis • A SQL operation used to rank results • Add a pseudocolumn called ROWNUM • ROWNUM is used to select the top “n” (number) of rowsSELECT ROWNUM as top, name, costFROM (SELECT name, cost FROM d_events ORDER BY cost DESC)WHERE ROWNUM <= 2; Marge Hohly

27. Examples • SELECT ROWNUM as RANK, year, titleFROM (SELECT year, title FROM d_cds ORDER BY year)WHERE ROWNUM <=4; • SELECT ROWNUM as TOP, last_name, first_name, salary FROM (SELECT last_name, first_name, salary FROM employees ORDER BY salary DESC)WHERE ROWNUM <=5; Marge Hohly