（十八）其他数据库对象，视图，序列

视图

SQL> ed
已写入 file afiedt.buf

  1  create view empincomeview
  2  as
  3  select e.empno,e.ename,e.sal,e.sal*12 annualsal,e.sal*12+nvl(comm,0) income,d.dname
  4  from emp e,dept d
  5* where e.deptno=d.deptno
SQL> /
create view empincomeview
            *
第 1 行出现错误:
ORA-01031: 权限不足

 

 

 

SQL> ed
已写入 file afiedt.buf

  1  create or replace view empincomeview
  2  as
  3  select e.empno,e.ename,e.sal,e.sal*12 annualsal,e.sal*12+nvl(comm,0) income,d.dname
  4  from emp e,dept d
  5*  where e.deptno=d.deptno
SQL> /

视图已创建。

SQL> drop view empincomeview;

视图已删除。

总结：不通过视图做insert, update, delete 操作。因为视图提供的目的就是为了简化查询

序列

 

SQL> create sequence myseq;

序列已创建。

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$QdtFwotIQY2TnKXqPH7lHw==$0 TABLE
BONUS                          TABLE
DEPT                           TABLE
EMP                            TABLE
EMPINCOME                      TABLE
SALGRADE                       TABLE
STUDENT                        TABLE
TB_DEPT                        TABLE
TEST7                          TABLE
TEST99                         TABLE
TESTSP                         TABLE

已选择11行。

SQL> create table tableA(tid number,tname varchar2(20));

表已创建。

SQL> select myseq.currval from dual;
select myseq.currval from dual
       *
第 1 行出现错误:
ORA-08002: 序列 MYSEQ.CURRVAL 尚未在此会话中定义


SQL> select myseq.nextval from dual;

   NEXTVAL
----------
         1

SQL> select myseq.currval from dual;

   CURRVAL
----------
         1

SQL> insert into tableA values(myseq.nextval,'aaa');

已创建 1 行。

SQL> insert into tableA values(myseq.nextval,'bbb');

已创建 1 行。

SQL> insert into tableA values(myseq.nextval,&name);
输入 name 的值:  ccc
原值    1: insert into tableA values(myseq.nextval,&name)
新值    1: insert into tableA values(myseq.nextval,ccc)
insert into tableA values(myseq.nextval,ccc)
                                        *
第 1 行出现错误:
ORA-00984: 列在此处不允许


SQL> insert into tableA values(myseq.nextval,&name);
输入 name 的值:  'ccc'
原值    1: insert into tableA values(myseq.nextval,&name)
新值    1: insert into tableA values(myseq.nextval,'ccc')

已创建 1 行。

SQL> select * from tableA;

       TID TNAME
---------- --------------------
         3 aaa
         4 bbb
         5 ccc

SQL> select * from user_sequences;

SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ---------- ------------ - - ---------- -----------
MYSEQ                                   1 1.0000E+28            1 N N         20          21

SQL> drop sequence myseq;

序列已删除。

SQL>