当前位置: 代码迷 >> SQL >> qlsql 学习范例代码3
  详细解决方案

qlsql 学习范例代码3

热度:54   发布时间:2016-05-05 13:47:34.0
qlsql 学习实例代码3
-----------------------------------------------视图 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