Oralce专题10之Oracle的其他数据库对象

1、Oracle的数据库对象：序列

a、什么是序列？

• 序列（sequence）：是一种用于自动生成唯一数字的数据库对象。主要用于提供主键值。
  

  b、怎样创建序列？

• 创建序列的语法格式：

  CREATE SEQUENCE     sequence 
  [INCREMENT BY n] --每次增长的数值（步长），默认值为1（n为正数，则自增；n为负数，则自减）
  [START WITH n] --从哪个值开始（初始值），默认值为1
  [{MAXVALUE n | NOMAXVALUE}] -- 默认值为 NOMAXVALUE
  [{MINVALUE n | NOMINVALUE}]-- 默认值为 NOMINVALUE
  [{CYCLE | NOCYCLE}] -- 是否循环，默认值为NOCYCLE
  [{CACHE n | NOCACHE}] --是否缓存，默认为不缓存

• 示例：

  SQL> create sequence deptno_seq start with 50 increment by 10 maxvalue 70 cache 3;
  序列已创建。

  c、怎样使用序列？

• 当使用序列时，必须通过伪列NEXTVAL和CURRVAL来引用序列。
• NEXTVAL用于引用返回下一个序列值。例如：deptno_seq.nextval。
• CURRVAL用于引用返回当前序列值。例如：deptno_seq.currval。
• 示例1：插入数据时，使用序列（需要注意的是，当第一次插入序列时，只能使用nextval伪列，不能使用currval伪列）

      SQL> create table deptnew as select * from dept;
      表已创建。
      SQL> insert into deptnew(deptno, dname, loc) values(deptno_seq.nextval, 'test_dname','test_loc');
      已创建 1 行。
      SQL> select * from deptnew;
          DEPTNO DNAME          LOC
                  10 ACCOUNTING     NEW YORK
                  20 RESEARCH       DALLAS
                  30 SALES          CHICAGO
                  40 OPERATIONS     BOSTON
                  50 test_dname     test_loc

• 查看当前序列值：

  SQL> select deptno_seq.currval from dual;
       CURRVAL
                  50

  ---

  - 附加说明：Oracle中的dual表

• dual表是一个虚拟表，用来构成select的语法规则，oracle保证dual里面永远只有一条记录。我们可以用它来做很多事情，如下：
  1、查看当前用户，可以在SQLPlus中执行下面语句：select user from dual;
  2、用来调用系统函数：select to_char(sysdate,'yyyy-mm-dd hh34:mi:ss') from dual; -- 获得当前系统时间
• select SYS_CONTEXT('USERENV','TERMINAL') from dual;--获得主机名
• select SYS_CONTEXT('USERENV','language') from dual;--获得当前locale
• select dbms_random.random from dual;--获得一个随机数
  3、获得序列的下一个值或者当前值，用下面语句：
• select your_sequence.nextval from dual;--获得序列your_sequence的下一个值

• select your_sequence.currval from dual;--获得序列your_sequence的当前值
  4、可用作计算器：select 7*9 from dual;

  d、怎样查询序列？

• 查询数据字典视图USER_SEQUENCES获取序列定义信息：SELECT sequence_name, min_value, max_value, increment_by, cycle_flag, cache_size, last_number FROM user_sequences;
• LAST_NUMBER列有两种情况，如果指定NOCACHE情况，则LAST_NUMBER显示的是序列当中的下一个值；如果指定CACHE情况，则LAST_NUMBER显示的是序列缓存当中最后一个序列号的下一个值。

      SQL> SELECT sequence_name, min_value, max_value, increment_by, cycle_flag, cache_size, last_number from user_sequences;
      SEQUENCE_NAME   MIN_VALUE  MAX_VALUE INCREMENT_BY   C    CACHE_SIZE LAST_NUMBER 
      DEPTNO_SEQ                1                 70                      10                            N              3                     80

  e、修改序列

• 修改序列的命令：ALTER SEQUENCE。

  ALTER SEQUENCE sequence 
  [INCREMENT BY n] --每次增长的数值
  [{MAXVALUE n | NOMAXVALUE}]
  [{MINVALUE n | NOMINVALUE}]
  [{CYCLE | NOCYCLE}] -- 是否需要循环
  [{CACHE n | NOCACHE}] --是否需要缓存

• 需要注意的是，序列的初始值不能够修改。

  SQL> alter sequence deptno_seq maxvalue 90;
  序列已更改。

  f、怎样删除序列？

• 删除序列的命令：DROP SEQUENCE。
• 语法格式为：DROP SEQUENCE sequence;

  SQL> DROP SEQUENCE deptno_seq;
  序列已删除。

  g、序列注意事项

• 如果指定cache值，可提高访问效率，但是使用cache也会出现跳号的可能。（即序列出现缺口）
• 序列在下列情况下回出现序列缺口（裂缝）：回滚、系统异常、多个表同时使用同一序列。

  2、Oracle的数据库对象：索引

  a、什么是索引？

• 索引是为了加速对表中的数据行的检索而创建的一种存储结构。

  b、索引的分类

• 按索引列的个数：单列索引、复合索引。
• 按索引列值的唯一性：唯一索引、非唯一索引。

  c、怎么创建索引？

• 在一个或者多个列上创建索引。语法格式为：CREATE INDEX index ON table (column[, column] ...);
• 创建单列索引示例：

  SQL> create index idx_ename on emp(ename);
  索引已创建。

• 创建复合索引示例：

  SQL> create index idx_deptno on emp(deptno, job);
  索引已创建。

• 创建唯一索引（索引的列值不能重复）

  SQL> create unique index idx_dname on dept(dname);
  索引已创建。

• 创建非唯一索引

  SQL> create index idx_job on emp(job);
  索引已创建。

  d、什么时候创建索引？

• 以下情况可以创建索引：
  1、where子句经常引用的表列上。
  2、为了提高多表连接的性能，应该在连接列上建立索引。
  3、经常排序的列上创建索引，可以加快数据排序的速度。
• 什么时候不能创建索引：
  1、表很小。
  2、列不经常作为连接条件或者出现在WHERE子句中。
  3、表经常更新。

  e、怎么查询索引？

• 可以使用数据字典视图USER_INDEXES（索引信息）和 USER_IND_COLUMNS（索引列信息）查看索引的信息。
• 示例：SELECT uic.index_name, uic.column_name, uic.column_position, ui.unqueness FROM user_indexes ui, user_ind_columns uic WHERE uic.index_name = ui.index_name AND ui.table_name = 'EMP';

      SQL> SELECT uic.index_name, uic.column_name, uic.column_position, ui.uniqueness FROM user_indexes ui, user_ind_columns uic WHERE uic.index_name = ui.index_name AND ui.table_name = 'EMP';
      INDEX_NAME         COLUMN_NAME         COLUMN_POSITION   UNIQUENES
      IDX_JOB                JOB                              1                                NONUNIQUE
      ......
      PK_EMP                  EMPNO                      1                                UNIQUE

  f、怎样删除索引？

• 使用DROP INDEX命令删除索引：DROP INDEX index;

  SQL> DROP INDEX idx_ename;
  索引已删除。

  3、Oracle的数据库对象-同义词

  a、什么是同义词？

• 同义词是数据库方案对象的一个别名。
• 方案对象：表、索引、视图、触发器、序列、同义词、存储过程等；非方案对象：表空间、用户、角色等。
• 比如：stu是表studuent_info的同义词。

  b、同义词的作用

  1、简化对象访问。
  2、提供对象访问的安全性：多用户协同开发中，可以屏蔽对象的名字及其持有者。

  c、同义词的分类

• 包括公共同义词和私有同义词。
• 公共同义词：是指数据库中所有的用户都可以使用；私有同义词：只能被创建它的用户所拥有，其他用户在引用时必须带有方案名。

  d、创建同义词

• 创建公共同义词：CREATE PUBLIC SYNONYM synonym FOR [schema.]object;
• 创建私有同义词：CREATE SYNONYM synonym FOR [schema.]object;
• 注意：schema指的是方案名。（同用户名）；创建同义词时用户需要有创建同义词的权限。
• 授予scott用户创建同义词的权限示例：（创建公共同义词和私有同义词）

  SQL> conn sys/02000059 as sysdba;
  已连接。
  SQL> grant create public synonym to scott;
  授权成功。
  SQL> grant create synonym to scott;
  授权成功。

• 给scott用户下的deptnew表创建公共同义词dn示例：

  SQL> conn scott/02000059
  已连接。
  SQL> create public synonym dn for scott.deptnew;
  同义词已创建。
  SQL> select * from dn;
          DEPTNO DNAME          LOC
                  10 ACCOUNTING     NEW YORK
                  20 RESEARCH       DALLAS
                  30 SALES          CHICAGO
                  40 OPERATIONS     BOSTON
                  50 test_dname     test_loc

• 给scott用户下的emp01表创建私有同义词em示例：

  SQL> create synonym em for scott.emp01;
  同义词已创建。
  SQL> select * from em;
           EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM
          DEPTNO
              7951 EASON      ANALYST         7566 01-12月-17           3000
                  20
              7369 G_EASON    CLERK           7902 17-12月-80            800
                  20
              ......
              7934 MILLER     CLERK           7782 23-1月 -82           1300
                  10
  已选择9行。

  e、查看同义词

• 当建立同义词时，Oracle会将同义词的信息存放到数据字典中。通过查询数据字典视图USER_SYNONYMS，可以显示当前用户所有同义词的详细信息。
• 示例：SELECT synonym_name, table_owner, table_name FROM user_synonyms WHERE synonym_name = 'EM';

  SQL> SELECT synonym_name, table_owner, table_name FROM user_synonyms WHERE synonym_name = 'EM';
  SYNONYM_NAME            TABLE_OWNER               TABLE_NAME
  EM                                     SCOTT                           EMP01

  f、Oracle对下列数据字典视图提供的同义词

• 序列，数据字典：USER_SEQUENCES，对应的同义词：SEQ。
• 索引，数据字典：USER_INDEXES，对应的同义词：IND。
• 同义词：数据字典：USER_SYNONYMS ，对应的同义词：SYN。
• 示例查看私有同义词：

  SQL> select * from syn where synonym_name = 'EM';
  SYNONYM_NAME     TABLE_OWNER               TABLE_NAME        DB_LINK
  EM                             SCOTT                            EMP01

• 示例查看公共同义词：

  SQL> SELECT synonym_name, table_owner, table_name FROM all_synonyms WHERE synonym_name = 'EM';
  SQL> select * from syn where synonym_name = 'EM';
  SYNONYM_NAME     TABLE_OWNER               TABLE_NAME      
  EM                             SCOTT                            EMP01

  g、删除同义词

• 删除公共同义词：DROP PUBLIC SYNONYM synonym;
• 删除私有同义词：DROP SYNONYM synonym;