Oracle PL/SQL Tutorial/View/Create View
Содержание
- 1 a user-defined type view
- 2 Choose specific column from base table
- 3 Create a view by joining two tables
- 4 create materialized view emp_dept build immediate refresh on demand enable query rewrite
- 5 CREATE MATERIALIZED VIEW with rowid
- 6 CREATE MATERIALIZED VIEW with TABLESPACE
- 7 Create view based on aggregate function
- 8 Create view based on user-defined function
- 9 Create view by join three tables
- 10 Create view on single field of a type
- 11 Creating and Using a View
- 12 Creating and Using Simple Views
- 13 Creating a View with a CHECK OPTION Constraint
- 14 Creating a View with a READ ONLY Constraint
- 15 Use view based on user-defind type
- 16 Views
a user-defined type view
SQL>
SQL>
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL>
SQL>
SQL>
SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );
Table created.
SQL>
SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK");
1 row created.
SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS");
1 row created.
SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO");
1 row created.
SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON");
1 row created.
SQL>
SQL> create or replace type emp_type
2 as object
3 (empno number(4),
4 ename varchar2(10),
5 job varchar2(9),
6 mgr number(4),
7 hiredate date,
8 sal number(7, 2),
9 comm number(7, 2)
10 );
11 /
Type created.
SQL>
SQL>
SQL> create or replace type emp_tab_type
2 as table of emp_type
3 /
Type created.
SQL>
SQL>
SQL> create or replace type dept_type
2 as object
3 ( deptno number(2),
4 dname varchar2(14),
5 loc varchar2(13),
6 emps emp_tab_type
7 )
8 /
Type created.
SQL>
SQL> create or replace view dept_or
2 of dept_type
3 with object identifier(deptno)
4 as
5 select deptno, dname, loc,
6 cast ( multiset (
7 select empno, ename, job, mgr, hiredate, sal, comm
8 from emp
9 where emp.deptno = dept.deptno )
10 as emp_tab_type )
11 from dept
12 /
View created.
SQL>
SQL> declare
2 l_emps emp_tab_type;
3 begin
4 select p.emps into l_emps from dept_or p where deptno = 10;
5
6 for i in 1 .. l_emps.count
7 loop
8 l_emps(i).ename := lower(l_emps(i).ename);
9 end loop;
10
11 update dept_or set emps = l_emps where deptno = 10;
12 end;
13 /
declare
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
ORA-06512: at line 11
SQL>
SQL> drop type dept_type;
Type dropped.
SQL>
SQL> drop type emp_tab_type;
Type dropped.
SQL>
SQL> drop type emp_type;
Type dropped.
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL> drop table dept;
Table dropped.
Choose specific column from base table
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL> CREATE VIEW employee_view AS
2 SELECT id, first_name, last_name
3 FROM employee;
View created.
SQL>
SQL> select * from employee_view;
ID FIRST_NAME LAST_NAME
---- ---------- ----------
01 Jason Martin
02 Alison Mathews
03 James Smith
04 Celia Rice
05 Robert Black
06 Linda Green
07 David Larry
08 James Cat
8 rows selected.
SQL>
SQL> drop view employee_view;
View dropped.
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
Create a view by joining two tables
SQL>
SQL>
SQL> create table courses
2 ( code VARCHAR2(6)
3 , description VARCHAR2(30)
4 , category CHAR(3)
5 , duration NUMBER(2)) ;
Table created.
SQL>
SQL>
SQL> insert into courses values("SQL","SQL course", "GEN",4);
1 row created.
SQL> insert into courses values("OAU","Oracle course", "GEN",1);
1 row created.
SQL> insert into courses values("JAV","Java course", "BLD",4);
1 row created.
SQL> insert into courses values("PLS","PL/SQL course", "BLD",1);
1 row created.
SQL> insert into courses values("XML","XML course", "BLD",2);
1 row created.
SQL> insert into courses values("ERM","ERM course", "DSG",3);
1 row created.
SQL> insert into courses values("PMT","UML course", "DSG",1);
1 row created.
SQL> insert into courses values("RSD","C# course", "DSG",2);
1 row created.
SQL> insert into courses values("PRO","C++ course", "DSG",5);
1 row created.
SQL> insert into courses values("GEN","GWT course", "DSG",4);
1 row created.
SQL>
SQL>
SQL>
SQL> create table course_schedule
2 ( course VARCHAR2(6)
3 , begindate DATE
4 , trainer NUMBER(4)
5 , location VARCHAR2(20)) ;
Table created.
SQL>
SQL>
SQL> insert into course_schedule values ("SQL",date "1999-04-12",1,"VANCOUVER" );
1 row created.
SQL> insert into course_schedule values ("OAU",date "1999-08-10",2,"CHICAGO");
1 row created.
SQL> insert into course_schedule values ("SQL",date "1999-10-04",3,"SEATTLE");
1 row created.
SQL> insert into course_schedule values ("SQL",date "1999-12-13",4,"DALLAS" );
1 row created.
SQL> insert into course_schedule values ("JAV",date "1999-12-13",5,"SEATTLE");
1 row created.
SQL> insert into course_schedule values ("XML",date "2000-02-03",6,"VANCOUVER" );
1 row created.
SQL> insert into course_schedule values ("JAV",date "2000-02-01",7,"DALLAS" );
1 row created.
SQL> insert into course_schedule values ("PLS",date "2000-09-11",8,"VANCOUVER" );
1 row created.
SQL> insert into course_schedule values ("XML",date "2000-09-18",NULL,"SEATTLE");
1 row created.
SQL> insert into course_schedule values ("OAU",date "2000-09-27",9,"DALLAS" );
1 row created.
SQL> insert into course_schedule values ("ERM",date "2001-01-15",10, NULL );
1 row created.
SQL> insert into course_schedule values ("PRO",date "2001-02-19",NULL,"VANCOUVER" );
1 row created.
SQL> insert into course_schedule values ("RSD",date "2001-02-24",8,"CHICAGO");
1 row created.
SQL>
SQL>
SQL> create or replace view crs_course_schedule as
2 select o.course as course_code, c.description, o.begindate
3 from course_schedule o
4 join
5 courses c
6 on (o.course = c.code);
View created.
SQL>
SQL> drop table course_schedule;
Table dropped.
SQL>
SQL> drop table courses;
Table dropped.
SQL>
SQL>
create materialized view emp_dept build immediate refresh on demand enable query rewrite
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2)
10 );
SQL>
SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
SQL>
SQL> CREATE TABLE DEPT(
2 DEPTNO NUMBER(2),
3 DNAME VARCHAR2(14),
4 LOC VARCHAR2(13)
5 );
SQL>
SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK");
SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS");
SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO");
SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON");
SQL>
SQL> create materialized view emp_dept
2 build immediate
3 refresh on demand
4 enable query rewrite
5 as
6 select dept.deptno, dept.dname, count (*)
7 from emp, dept
8 where emp.deptno = dept.deptno
9 group by dept.deptno, dept.dname
10 /
SQL>
SQL> begin
2 dbms_stats.set_table_stats( user, "EMP", numrows => 100000 );
3 dbms_stats.set_table_stats( user, "DEPT", numrows => 1000 );
4 end;
5 /
SQL>
SQL>
SQL> drop table emp;
SQL>
SQL> drop table dept;
CREATE MATERIALIZED VIEW with rowid
SQL> -- create demo table
SQL> create table emp(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 fname VARCHAR2(10 BYTE),
4 lname VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL> CREATE MATERIALIZED VIEW LOG ON emp
2 WITH ROWID (fname)
3 INCLUDING NEW VALUES;
Materialized view log created.
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>
CREATE MATERIALIZED VIEW with TABLESPACE
SQL> create table emp(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 fname VARCHAR2(10 BYTE),
4 lname VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> CREATE MATERIALIZED VIEW LOG ON emp
2 PCTFREE 5
3 TABLESPACE mv_logs
4 STORAGE (INITIAL 1M NEXT 1M) WITH ROWID;
SQL>
SQL>
SQL> drop view log;
SQL>
SQL> drop table emp;
Table dropped.
Create view based on aggregate function
SQL>
SQL>
SQL> create table registrations
2 ( attendee NUMBER(4)
3 , course VARCHAR2(6)
4 , begindate DATE
5 , evaluation NUMBER(1)) ;
Table created.
SQL>
SQL>
SQL> insert into registrations values (1, "SQL",date "1999-04-12",4 );
1 row created.
SQL> insert into registrations values (2, "SQL",date "1999-12-13",NULL);
1 row created.
SQL> insert into registrations values (3, "SQL",date "1999-12-13",NULL);
1 row created.
SQL> insert into registrations values (4, "OAU",date "1999-08-10",4 );
1 row created.
SQL> insert into registrations values (5, "OAU",date "2000-09-27",5 );
1 row created.
SQL> insert into registrations values (6, "JAV",date "1999-12-13",2 );
1 row created.
SQL> insert into registrations values (7, "JAV",date "2000-02-01",4 );
1 row created.
SQL> insert into registrations values (8, "JAV",date "2000-02-01",5 );
1 row created.
SQL> insert into registrations values (9, "XML",date "2000-02-03",4 );
1 row created.
SQL> insert into registrations values (10,"XML",date "2000-02-03",5 );
1 row created.
SQL> insert into registrations values (1, "PLS",date "2000-09-11",NULL);
1 row created.
SQL> insert into registrations values (2, "PLS",date "2000-09-11",NULL);
1 row created.
SQL> insert into registrations values (3, "PLS",date "2000-09-11",NULL);
1 row created.
SQL>
SQL>
SQL>
SQL> create or replace view avg_evaluations as
2 select course
3 , avg(evaluation) as avg_eval
4 from registrations
5 group by course;
View created.
SQL>
SQL>
SQL> drop table registrations;
Table dropped.
SQL>
SQL>
Create view based on user-defined function
SQL>
SQL>
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- -------------------- -------------------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL>
SQL> create or replace function f_emp_dsp (i_empNo VARCHAR)
2 return VARCHAR2 is v_out VARCHAR2 (256);
3 begin
4 DBMS_OUTPUT.put_line("Inside of F_EMP_DSP");
5 select initcap(first_name)||": "||initcap(last_name)
6 into v_out from employee where id = i_empNo;
7 return v_out;
8 end f_emp_dsp;
9 /
Function created.
SQL>
SQL> create or replace view v_emp as
2 select f_emp_dsp(id) emp_dsp
3 from employee;
View created.
SQL> /
View created.
SQL>
SQL> select * from v_emp;
EMP_DSP
--------------------------------------------------------------------------
Jason: Martin
Alison: Mathews
James: Smith
Celia: Rice
Robert: Black
Linda: Green
David: Larry
James: Cat
8 rows selected.
Inside of F_EMP_DSP
Inside of F_EMP_DSP
Inside of F_EMP_DSP
Inside of F_EMP_DSP
Inside of F_EMP_DSP
Inside of F_EMP_DSP
Inside of F_EMP_DSP
Inside of F_EMP_DSP
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
Create view by join three tables
SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key
3 , ename VARCHAR2(8)
4 , init VARCHAR2(5)
5 , job VARCHAR2(8)
6 , mgr NUMBER(4)
7 , bdate DATE
8 , sal NUMBER(6,2)
9 , comm NUMBER(6,2)
10 , deptno NUMBER(2) default 10
11 ) ;
Table created.
SQL> insert into emp values(1,"Tom","N", "TRAINER", 13,date "1965-12-17", 800 , NULL, 20);
1 row created.
SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30);
1 row created.
SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30);
1 row created.
SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20);
1 row created.
SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30);
1 row created.
SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30);
1 row created.
SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10);
1 row created.
SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10);
1 row created.
SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30);
1 row created.
SQL> insert into emp values(11,"Ana","AA", "TRAINER", 8,date "1966-12-30", 1100, NULL, 20);
1 row created.
SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30);
1 row created.
SQL> insert into emp values(13,"Fake","MG", "TRAINER", 4,date "1959-02-13", 3000, NULL, 20);
1 row created.
SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10);
1 row created.
SQL>
SQL> create table departments
2 ( deptno NUMBER(2) constraint D_PK primary key
3 , dname VARCHAR2(10)
4 , location VARCHAR2(8)
5 , mgr NUMBER(4)
6 ) ;
Table created.
SQL>
SQL> insert into departments values (10,"ACCOUNTING","NEW YORK",7);
1 row created.
SQL> insert into departments values (20,"TRAINING", "DALLAS", 4);
1 row created.
SQL> insert into departments values (30,"SALES", "CHICAGO", 6);
1 row created.
SQL> insert into departments values (40,"HR", "BOSTON", 9);
1 row created.
SQL>
SQL> create view empdept_v as
2 select e.empno
3 , e.ENAME
4 , e.init
5 , d.dname
6 , d.location
7 , m.ENAME as Designer
8 from emp e
9 join
10 departments d using (deptno)
11 join
12 emp m on (m.empno = d.mgr);
SQL> drop table emp;
Table dropped.
SQL> drop table departments;
Table dropped.
Create view on single field of a type
SQL> create type addressType as object
2 (Street VARCHAR2(50),
3 City VARCHAR2(25),
4 State CHAR(2),
5 Zip NUMBER);
6 /
SQL>
SQL> create type personType as object
2 (Name VARCHAR2(25),
3 Address addressType);
4 /
SQL>
SQL>
SQL> create or replace type personType as object
2 (Name VARCHAR2(25),
3 Address addressType);
4 /
SQL>
SQL>
SQL> create table myemp
2 (cid NUMBER,
3 Person personType);
SQL>
SQL>
SQL> insert into myemp values(1,personType("SomeName",addressType("StreetValue","CityValue","ST",11111)));
SQL>
SQL>
SQL> create index empview
2 on myemp(Person.Address.City);
SQL>
SQL>
SQL> drop table myemp;
SQL> drop type personType;
SQL> drop type addressType;
SQL>
SQL>
SQL>
SQL>
Creating and Using a View
You create a view using CREATE VIEW , which has the following simplified syntax:
CREATE [OR REPLACE] VIEW [{FORCE | NOFORCE}] VIEW view_name
[(alias_name[, alias_name...])] AS subquery
[WITH {CHECK OPTION | READ ONLY} CONSTRAINT constraint_name];
where
- OR REPLACE specifies the view is to replace an existing view if present.
- FORCE specifies the view is to be created even if the base tables don"t exist.
- NOFORCE specifies the view is not to be created if the base tables don"t exist; NOFORCE is the default.
- alias_name specifies the name of an alias for an expression in the subquery.
- There must be the same number of aliases as there are expressions in the subquery.
- subquery specifies the subquery that retrieves from the base tables.
- If you"ve supplied aliases, you can use those aliases in the list after the SELECT clause.
- WITH CHECK OPTION specifies that only the rows that would be retrieved by the subquery can be inserted, updated, or deleted.
- By default, rows are not checked that they are retrievable by the subquery before they are inserted, updated, or deleted.
- constraint_name specifies the name of the WITH CHECK OPTION or READ ONLY constraint.
- WITH READ ONLY specifies that rows may only read from the base tables.
There are two basic types of views:
Simple views, which contain a subquery that retrieves from one base table
Complex views, which contain a subquery that:
- Retrieves from multiple base tables
- Groups rows using a GROUP BY or DISTINCT clause
- Contains a function call
Quote from:
Oracle Database 10g SQL (Osborne ORACLE Press Series) (Paperback)
# Paperback: 608 pages
# Publisher: McGraw-Hill Osborne Media; 1st edition (February 20, 2004)
# Language: English
# ISBN-10: 0072229810
# ISBN-13: 978-0072229813
Creating and Using Simple Views
Simple views access one base table.
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL> CREATE VIEW my_view AS
2 SELECT *
3 FROM employee
4 WHERE id < 5;
View created.
SQL>
SQL> select * from my_view;
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
SQL>
SQL> drop view my_view;
View dropped.
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
SQL>
Creating a View with a CHECK OPTION Constraint
You can specify that DML operations on a view must satisfy the subquery by adding a CHECK OPTION constraint to the view.
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL> CREATE VIEW myView AS
2 SELECT *
3 FROM employee
4 WHERE id < 5
5 WITH CHECK OPTION CONSTRAINT myView;
View created.
SQL>
SQL> INSERT INTO myView (id) VALUES (0);
1 row created.
SQL>
SQL> INSERT INTO myView (id) VALUES (7);
INSERT INTO myView (id) VALUES (7)
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation
SQL>
SQL> select * from myView;
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
0
SQL>
SQL> drop view myView;
View dropped.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL>
Creating a View with a READ ONLY Constraint
You can make a view read only by adding a READ ONLY constraint to the view.
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL> CREATE VIEW myView AS
2 SELECT *
3 FROM employee
4 WITH READ ONLY CONSTRAINT my_view_read_only;
View created.
SQL>
SQL> INSERT INTO myView (id) VALUES (1);
INSERT INTO myView (id) VALUES (1)
*
ERROR at line 1:
ORA-01733: virtual column not allowed here
SQL>
SQL> drop view myView;
View dropped.
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
Use view based on user-defind type
SQL> create type addressType as object
2 (Street VARCHAR2(50),
3 City VARCHAR2(25),
4 State CHAR(2),
5 Zip NUMBER);
6 /
SQL> create type personType as object
2 (Name VARCHAR2(25),
3 Address addressType);
4 /
SQL>
SQL>
SQL> create table emp
2 (cid NUMBER primary key,
3 Name VARCHAR2(25),
4 Street VARCHAR2(50),
5 City VARCHAR2(25),
6 State CHAR(2),
7 Zip NUMBER);
SQL>
SQL> create view empView (cid, Person) as
2 select cid,personType(Name,addressType(Street, City, State, Zip))
3 from emp;
SQL>
SQL> drop view empView;
SQL> drop table emp;
SQL>
SQL> drop type personType;
SQL>
SQL> drop type addressType;
SQL>
SQL>
Views
- A view is a predefined query on one or more tables.
- Retrieving information from a view is done in the same manner as retrieving from a table.
- With some views you can also perform DML operations (delete, insert, update) on the base tables.
- Views don"t store data, they only access rows in the base tables.
- user_tables, user_sequences, and user_indexes are all views.
- View Only allows a user to retrieve data.
- view can hide the underlying base tables.
- By writing complex queries as a view, we can hide complexity from an end user.
- View only allows a user to access certain rows in the base tables.
8. 1. Create View 8. 1. 1. Views 8. 1. 2. <A href="/Tutorial/Oracle/0160__View/CreatingandUsingaView.htm">Creating and Using a View</a> 8. 1. 3. <A href="/Tutorial/Oracle/0160__View/CreatingandUsingSimpleViews.htm">Creating and Using Simple Views</a> 8. 1. 4. <A href="/Tutorial/Oracle/0160__View/Choosespecificcolumnfrombasetable.htm">Choose specific column from base table</a> 8. 1. 5. <A href="/Tutorial/Oracle/0160__View/CreatingaViewwithaCHECKOPTIONConstraint.htm">Creating a View with a CHECK OPTION Constraint</a> 8. 1. 6. <A href="/Tutorial/Oracle/0160__View/CreatingaViewwithaREADONLYConstraint.htm">Creating a View with a READ ONLY Constraint</a> 8. 1. 7. <A href="/Tutorial/Oracle/0160__View/Createviewbasedonuserdefinedfunction.htm">Create view based on user-defined function</a> 8. 1. 8. <A href="/Tutorial/Oracle/0160__View/Createaviewbyjoiningtwotables.htm">Create a view by joining two tables</a> 8. 1. 9. <A href="/Tutorial/Oracle/0160__View/Createviewbasedonaggregatefunction.htm">Create view based on aggregate function</a> 8. 1. 10. <A href="/Tutorial/Oracle/0160__View/auserdefinedtypeview.htm">a user-defined type view</a> 8. 1. 11. <A href="/Tutorial/Oracle/0160__View/CREATEMATERIALIZEDVIEWwithTABLESPACE.htm">CREATE MATERIALIZED VIEW with TABLESPACE</a> 8. 1. 12. <A href="/Tutorial/Oracle/0160__View/CREATEMATERIALIZEDVIEWwithrowid.htm">CREATE MATERIALIZED VIEW with rowid</a> 8. 1. 13. <A href="/Tutorial/Oracle/0160__View/Createviewbyjointhreetables.htm">Create view by join three tables</a> 8. 1. 14. <A href="/Tutorial/Oracle/0160__View/Createviewonsinglefieldofatype.htm">Create view on single field of a type</a> 8. 1. 15. <A href="/Tutorial/Oracle/0160__View/creatematerializedviewempdeptbuildimmediaterefreshondemandenablequeryrewrite.htm">create materialized view emp_dept build immediate refresh on demand enable query rewrite</a> 8. 1. 16. <A href="/Tutorial/Oracle/0160__View/Useviewbasedonuserdefindtype.htm">Use view based on user-defind type</a>
