Oracle PL/SQL Tutorial/View/Create View — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 16:45, 26 мая 2010
Содержание
- 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
<source lang="sql">
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.</source>
Choose specific column from base table
<source lang="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> 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></source>
Create a view by joining two tables
<source lang="sql">
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></source>
create materialized view emp_dept build immediate refresh on demand enable query rewrite
<source lang="sql">
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;</source>
CREATE MATERIALIZED VIEW with rowid
<source lang="sql">
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></source>
CREATE MATERIALIZED VIEW with TABLESPACE
<source lang="sql">
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.</source>
Create view based on aggregate function
<source lang="sql">
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></source>
Create view based on user-defined function
<source lang="sql">
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></source>
Create view by join three tables
<source lang="sql">
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.</source>
Create view on single field of a type
<source lang="sql">
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></source>
Creating and Using a View
You create a view using CREATE VIEW , which has the following simplified syntax:
<source lang="sql">
CREATE [OR REPLACE] VIEW [{FORCE | NOFORCE}] VIEW view_name [(alias_name[, alias_name...])] AS subquery [WITH {CHECK OPTION | READ ONLY} CONSTRAINT constraint_name];</source>
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.
<source lang="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 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></source>
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.
<source lang="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> 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></source>
Creating a View with a READ ONLY Constraint
You can make a view read only by adding a READ ONLY constraint to the view.
<source lang="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 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></source>
Use view based on user-defind type
<source lang="sql">
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></source>
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>
