-----------------------------------------------视图 view （用来封装复杂的查询语句）
SQL> create or replace view myview ---创建视图
2 as
3 select * from emp;
View created
SQL> select * from myview; -----查询视图
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
14 rows selected
SQL> insert into myview(empno,ename) values(1,'liwu'); ----插入一条记录后，查询视图和表，结果一样
1 row inserted
SQL> select * from myview;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
1 liwu
15 rows selected
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
1 liwu
15 rows selected
-------------------------------------------------------
SQL> create or replace view myview ----创建视图 此视图有where条件
2 as
3 select * from emp where sal <1000;
View created
SQL> select * from myview;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
SQL> insert into myview values(2,'abcd');
insert into myview values(2,'abcd')
ORA-00947: not enough values ----插入报错，说明有where条件的视图，插入时要把字段写全
SQL> insert into myview values(8000,'john','java',null,null,6000,null,10);
1 row inserted
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
1 liwu
8000 john java 6000.00 10
17 rows selected
SQL> select * from myview; ---视图结果没有显示插入的记录是因为视图中的where条件
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7999 john java 600.00 20
-------------------------------------------------
SQL> create or replace view myview
2 as
3 select * from dept where deptno >30
4 with check option ; ------创建视图时添加此限制语句，在视图添加操作时判断是否满足视图里面的where条件
View created
SQL> select * from myview;
DEPTNO DNAME LOC
------ -------------- -------------
40 OPERATIONS BOSTON
SQL> insert into myview values(5,'test','sss'); ----视图插入记录 不满足where条件
insert into myview values(5,'test','sss')
ORA-01402: view WITH CHECK OPTION where-clause violation ----报错
----------------------------------------
SQL> select e.empno,e.ename,d.dname,d.loc from emp e,dept d where e.deptno = d.deptno;
EMPNO ENAME DNAME LOC
----- ---------- -------------- -------------
7369 SMITH RESEARCH DALLAS
7499 ALLEN SALES CHICAGO
7521 WARD SALES CHICAGO
7566 JONES RESEARCH DALLAS
7654 MARTIN SALES CHICAGO
7698 BLAKE SALES CHICAGO
7782 CLARK ACCOUNTING NEW YORK
7788 SCOTT RESEARCH DALLAS
7839 KING ACCOUNTING NEW YORK
7844 TURNER SALES CHICAGO
7876 ADAMS RESEARCH DALLAS
7900 JAMES SALES CHICAGO
7902 FORD RESEARCH DALLAS
7934 MILLER ACCOUNTING NEW YORK
8000 john ACCOUNTING NEW YORK
15 rows selected
SQL> create or replace view v_emp_dept -----创建视图封装查询语句
2 as
3 select e.empno,e.ename,d.dname,d.loc from emp e,dept d where e.deptno = d.deptno;
View created
SQL> select * from v_emp_dept;
EMPNO ENAME DNAME LOC
----- ---------- -------------- -------------
7369 SMITH RESEARCH DALLAS
7499 ALLEN SALES CHICAGO
7521 WARD SALES CHICAGO
7566 JONES RESEARCH DALLAS
7654 MARTIN SALES CHICAGO
7698 BLAKE SALES CHICAGO
7782 CLARK ACCOUNTING NEW YORK
7788 SCOTT RESEARCH DALLAS
7839 KING ACCOUNTING NEW YORK
7844 TURNER SALES CHICAGO
7876 ADAMS RESEARCH DALLAS
7900 JAMES SALES CHICAGO
7902 FORD RESEARCH DALLAS
7934 MILLER ACCOUNTING NEW YORK
8000 john ACCOUNTING NEW YORK
15 rows selected
两个基表或以上的查询语句组成的试图是不能够进行 视图的增操作的或其他操作，
解决办法是 利用oracle的替代触发器
----------------------------------------------
SQL> create or replace view v_read ---创建视图
2 as
3 select * from dept
4 with read only; -----限制语句，此视图为只读的，有的视图里面的查询语句虽然没有此限制语句但是select语 句中有group by ,distinct等函数，那么视图也是只读的，不能修改
View created
--------------------------------------------
SQL> create sequence myseq -----创建序列sequence
2 start with 1 --从几开始
3 increment by 1 --递增
4 order --排序
5 nocycle; --不循环
Sequence created
SQL> select myseq.nextval from dual;
NEXTVAL
----------
1
SQL> select myseq.nextval from dual;
NEXTVAL
----------
2
SQL> select myseq.nextval from dual; --查询下一个序列值
NEXTVAL
----------
3
SQL> select myseq.currval from dual; --查询当前的序列值
CURRVAL
----------
3
SQL> alter sequence myseq ---修改序列的递增值 可以修改递增量，但不能修改当前的序列值
2 increment by 3;
Sequence altered
SQL> select myseq.nextval from dual;
NEXTVAL
----------
6
SQL> select myseq.nextval from dual;
NEXTVAL
----------
9
SQL> select myseq.nextval from dual;
NEXTVAL
----------
12