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

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

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.

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.

6
The Syntax For Creating a View

• CREATE OR REPLACE FORCENOFORCE VIEW
  view_name (alias ,alias....)AS
  subqueryWITH CHECK OPTION CONSTRAINT
  constraint_nameWITH 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 views 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

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

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

9
Simple vs. Complex
Feature Simple View Complex View
Number of tables used to derive data One One or more
Can contain functions No Yes
Can contain groups of data No Yes
Can perform DML operations (insert, update, Delete) through a view Yes Not always
10
Simple View

• CREATE VIEW view1_copy_d_cdsAS SELECT cd_number,
  title, producer, yearFROM d_cds

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

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

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

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

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

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

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 141NOTE ORA-01402 view WITH
  CHECK OPTION where-clause violation

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
  144ORA-01752 cannot delete from view without
  exactly one key-preserved table

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 exampleSELECT
  FROM employees WHERE ROWNUM lt 10

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

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.

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

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.

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 lt 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.

25
Inline View Example

• SELECT code, max(high_range) maxrangeFROM
  d_packagesGROUP BY code

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 lt 2

27
Examples

• SELECT ROWNUM as RANK, year, titleFROM (SELECT
  year, title FROM d_cds
  ORDER BY year)WHERE ROWNUM lt4
• SELECT ROWNUM as TOP, last_name, first_name,
  salary FROM (SELECT last_name, first_name,
  salary FROM employees ORDER
  BY salary DESC)WHERE ROWNUM lt5