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

a、什么是序列?

  • 序列(sequence):是一种用于自动生成唯一数字的数据库对象。主要用于提供主键值。
    Oralce专题10之Oracle的其他数据库对象

    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 hh24: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;