| Team Fly | |
Views are database objects that are based on one or more tables. They allow the user to create a pseudo-table that has no data. The view consists solely of an SQL query that retrieves specific columns and rows. The data that is retrieved by a view is presented like a table.
Views can provide a level of security, making only certain rows and columns from one or more tables available to the end user. We could hide the underlying tables, CUSTOMERS and SALES from all the users in our organization and only make available the data for states they are entitled to see. In the following example, we are creating a view to only show specific details about Utah-based customers sales:
SQL> create view utah_sales 2 as 3 select c.cust_id ID, 4 substr(cust_last_name,1,20) Name, 5 substr(cust_city,1,20) City, 6 substr(cust_state_province,1,5) State, 7 sum(amount_sold) Total 8 from customers c, sales s 9 where c.cust_id = s.cust_id 10 and cust_state_province = 'UT' 11 group by c.cust_id, 12 substr(cust_last_name,1,20), 13 substr(cust_city,1,20), 14 substr(cust_state_province,1,5); View created.
The create view statement names the view and then uses keywords as select to define the select list, tables, and selection criteria that the view will be based upon. The following code listing issues a desc statement to demonstrate that the view looks just like a table. Notice that the column names have been changed from their original ones and were instead created using the column aliases from the select statement in the preceding view creation DDL.
SQL> desc utah_sales Name Null? Type ----------------------------------------- -------- ------------------- ID NOT NULL NUMBER NAME VARCHAR2(20) CITY VARCHAR2(20) STATE VARCHAR2(5) TOTAL NUMBER
The view looks like a table as demonstrated by the preceding code listing, so let's now issue a couple of queries against it. The first one that follows selects all
| Team Fly | |