Oracle VIEWS - The Basics
You can think of a view as a lens looking at one or more tables. A view is really nothing more than a logical representation of one or more tables in a database. View offer the following benefits and functionality.
- Many different perspectives of the same table.
- Can hide certain columns in a table. For example you may want to allow employees to see other employees to see the phone number column, but only certain employees to be able to access an employees salary column!
- Can provide huge time savings in writing queries by already having a group of frequently accessed tables joined together in a view.
- Views allow you to use functions and manipulate data in ways that meet your requirements. For example, you store a persons birth date, but you like to calculate this to determine their age.
Creating a view, by example
The following is an example of a simple Oracle view using the Student table. Note: You may have to update some of the rows to add data to see the effects of this view.
CREATE OR REPLACE VIEW vw_STUDENTS1 AS SELECT FIRSTNAME, LASTNAME, BIRTH_DTTM, (FIRSTNAME || ' ' || LASTNAME) "FULLNAME_FL", trunc(months_between(sysdate,BIRTH_DTTM)/12) "AGE" FROM COURSEREGISTRATION.STUDENTS /
In general, you should adopt some naming standard for your views. This standard is vw_<name of view>. The name should be somewhat reflective of the purpose of the view. You can clearly see that I did not do such a good job with this. Sometimes coming up with a short descriptive name for your view is easier said than done.
The syntax for creating a view is...
CREATE OR REPLACE VIEW `<your_view_name>`
...followed by a normal SQL SELECT. This SELECT can include a WHERE clause or anything else for that matter that can be put into a SELECT statement. The scenarios are endless. It really depends on the purpose of the view.
As you can see in our view we are formatting the first and last name. This is a pretty common thing to do, By having a view that already does this we save having to write that function in every query where this is a requirement. You can also see that we have take the birth date column and calculated age.
Executing a View
Execute an SQL View
The example below shows all of the code from the view. You could also do a SELECT *, or further restrict the columns you want to see. You can also add additional row restriction to the view as we have done.
SELECT FIRSTNAME , LASTNAME , BIRTH_DTTM , FULLNAME_FL , AGE FROM VW_STUDENTS1 WHERE AGE IS NOT NULL /
Creating a View containing one or more SQL Tables
Another key advantage of a view is that it allows us to join multiple tables together.
CREATE OR REPLACE VIEW vw_OCCUPIED_SEATS_BY_CLASS AS SELECT c.COURSE_DESIGNATER_FK AS "COURSE", b.SEAT_NUM , (a.FIRSTNAME || ' ' || a.LASTNAME) AS "STUDENT" FROM STUDENTS a JOIN CLASSREGISTRATION b ON a.STUDENT_ID = b.STUDENT_ID_FK JOIN CLASSES c ON c.CLASSES_NUM = b.CLASSES_NUM /
Above is a simple view that provides us with a listing of occupied/unoccupied seats for our classes. As you can see from the examples below, we can use this view in a variety of different ways. Note that for each scenario that we did not need to join any tables. The grunt work is already done.
Using our View
View a single class
SELECT COURSE , SEAT_NUM , STUDENT FROM VW_OCCUPIED_SEATS_BY_CLASS WHERE COURSE = 'Perl100' and STUDENT <> '1' /
COURSE SEAT_NUM STUDENT ---------------------------------- Perl100 1 Madge Lowdown Perl100 2 Robert Frapples Perl100 3 Mary Lamacker Perl100 4 Helga Joens Perl100 5 Maggie Jomomma Perl100 6 Mary Meigh Perl100 7 JONES Perl100 8 Bob JONES Perl100 9 Ted Applebee Perl100 10 Jon Nesbitt Perl100 11 Mary Lamacker Perl100 12 Mark Jackson
Count open seats by class
SELECT COURSE , COUNT(SEAT_NUM) "# Open Seats" FROM VW_OCCUPIED_SEATS_BY_CLASS GROUP BY COURSE /
COURSE # Open Seats ---------------------------- dbOrchestra100 16 Perl100 12
Column name considerations
The column name MUST be unique in a view. Note the following example.
CREATE OR REPLACE View vw_NAME_CONFLICT AS SELECT a.CLASSES_NUM , b.CLASSES_NUM FROM CLASSES a JOIN CLASSESREGISTRATION b ON a.CLASSES_NUM = b.CLASSES_NUM /
Duplicate column name 'classes_num'
Here is how to resolve this issue. Create a unique name using "as".
CREATE OR REPLACE VIEW vw_NAME_CONFLICT AS SELECT a.CLASSES_NUM "CLASSES_CLASSES_NUM" , b.CLASSES_NUM "CLASSREGISTRATION_CLASSES_NUM" FROM CLASSES a JOIN CLASSREGISTRATION b ON a.CLASSES_NUM = b.CLASSES_NUM /
Drop a View
DROP VIEW COURSEREGISTRATION.VW_NAME_CONFLICT /