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;