[Oracle] Creating & Using Database Objects

上一個部份提到 COMMIT 與 ROLLBACK，有一點需要注意的地方，就是 CREATE TABLE、ALTER TABLE、DROP TABLE …. 等語法，是隱含著 COMMIT 指令的，因此是無法被 ROOLBACK 的喔!

資料型態

Oracle 提供的資料型態很多，僅針對比較常用的來筆記。

VARCHAR2

這是處理字元資料最有效率的資料型態，長度範圍介於 1~4000；另外比較需要注意的是 NVARCHAR2，是用來儲存 Unicode 的字元。

CLOB (Character Large Object)

儲存超大型的字元資料，最大可以到 (4 -1) GB * database block size。

NUMBER

除了用來儲存整數外，浮點數也可以!

BINARY_FLOAT & BINARY_DOUBLE

比 NUMBER 更進階的浮點數型態，精準度更高，計算速度更快，儲存所需的空間也較少。

DATE

日期型態，支援許多不同的顯示格式。

TIMESTAMP

常用來追蹤事件發生的順序之用。

資料表(Table)

create table

<br />CREATE TABLE EVALUATIONS ( <br /> EVALUATION_ID NUMBER(8, 0), <br /> EMPLOYEE_ID NUMBER(6, 0), <br /> EVALUATION_DATE DATE, <br /> JOB_ID VARCHAR2(10), <br /> MANAGER_ID NUMBER(6, 0), <br /> DEPARTMENT_ID NUMBER(4, 0), <br /> TOTAL_SCORE NUMBER(3, 0) <br />);<br />

alter table

<br />-- 修改 PERFORMANCE_PARTS 表格 <br />-- 將 WEIGHT 欄位設定為 NOT NULL <br />ALTER TABLE PERFORMANCE_PARTS <br />MODIFY WEIGHT NOT NULL; <br /><br />--將 EVALUATION_ID 與 PERFORMANCE_ID 欄位設為 UNIQUE <br />ALTER TABLE SCORES <br />ADD CONSTRAINT "SCORES_EVAL_PERF_UNIQUE" UNIQUE("EVALUATION_ID", "PERFORMANCE_ID"); <br /><br />--設定 PRIMARY KEY <br />ALTER TABLE PERFORMANCE_PARTS <br />ADD CONSTRAINT "PERF_PERF_ID_PK" PRIMARY KEY("PERFORMANCE_ID"); <br /><br />--設定 PRIMARY KEY (不加雙引號也可以) <br />ALTER TABLE EVALUATIONS <br />ADD CONSTRAINT EVAL_EVAL_ID_PK PRIMARY KEY(EVALUATION_ID); <br /><br />--設定 FOREIGN KEY <br />--SCORE(EVALUATION_ID) -> EVALUATION(EVALUATION_ID) <br />ALTER TABLE SCORES <br />ADD CONSTRAINT "SCORES_EVAL_FK" FOREIGN KEY("EVALUATION_ID") <br /> REFERENCES hr.EVALUATIONS("EVALUATION_ID") ENABLE; <br />ALTER TABLE SCORES <br />ADD CONSTRAINT "SCORES_PERF_FK" FOREIGN KEY("PERFORMANCE_ID") <br /> REFERENCES hr.PERFORMANCE_PARTS("PERFORMANCE_ID") ENABLE; <br /><br />--設定 CHECK <br />ALTER TABLE SCORES <br />ADD CONSTRAINT "SCORE_VALID" CHECK(SCORE >= 0 AND SCORE <= 9) ENABLE;

管理 table index (索引)

當 table 中有 primary key 時，其實 Oracle 就會自動將 primary key 的欄位建立索引了!

create index

<br />--建立 INDEX <br />--在 EVALUATION TABLE 中的 JOB_ID 欄位建立 INDEX <br />--INDEX 建立的方式為 ASC <br />CREATE INDEX EVAL_JOB_IDX ON EVALUATIONS(JOB_ID ASC) NOPARALLEL;<br />

modify index

<br />--比較快的方法是將 index DROP 以後再 CREATE <br />DROP INDEX EVAL_JOB_IDX; <br />CREATE INDEX EVAL_JOB_IDX ON EVALUATIONS(JOB_ID DESC) NOPARALLEL;<br />

更詳細的 index 管理的語法，可以參考官方網站的文件：

• CREATE INDEX
  
  
• ALTER INDEX
  
  
• DROP INDEX

檢視表(View)

以下是從 WIKI 上找來關於 View 的的資料：

===== 開始 =====

檢視表 (View) 是在關聯式資料庫中，將一組查詢指令構成的結果集組合成可查詢的資料表的一種資料庫物件。與資料表不同的是，資料表是一種實體結構(Physical Structure)，但檢視表是一種虛擬結構(Virtual Structure)，在實體資料表中的改變都可以立刻反應在檢視表中，不過部份資料庫管理系統也支援具更新能力的檢視表(Updatable View)。

檢視表具有下列的好處：

• 可以將實體資料表隱藏起來，讓外部程式的設計師無法得知實際的資料結構，降低資料庫被攻擊的風險。
  
  
• 在多數的情況下，檢視表是唯讀的，外部程式無法直接透過檢視表修改資料(具更新能力的檢視表除外)。
  
  
• 簡化查詢，資料庫管理員可以將高度複雜的查詢，包裝在檢視表中，外部程式只需要直接存取該檢視表即可取出需要的資料。
  
  
• 在檢視表中先行執行運算。
  
  
• 檢視表可視為資料表，具有 JOIN 的能力。
  
  
• 資料庫中只需要儲存定義，無須儲存資料。

===== 結束 =====

VIEW 的建立

<br />--CREATE VIEW 範例語法 1 <br />CREATE VIEW SALESFORCES AS <br /> SELECT FIRST_NAME || ' ' || LAST_NAME AS "NAME", <br /> SALARY * 12 AS "ANNUAL_SALARY" <br /> FROM EMPLOYEES <br /> WHERE DEPARTMENT_ID = 80;<br /><br /><br />--CREATE VIEW 範例語法 2 <br />CREATE VIEW EMP_LOCATIONS AS <br /> SELECT e.EMPLOYEE_ID, <br /> e.LAST_NAME || ' ' || e.FIRST_NAME AS "NAME", <br /> d.DEPARTMENT_NAME AS "DEPARTMENT", <br /> l.CITY AS "CITY", <br /> c.COUNTRY_NAME AS "COUNTRY" <br /> FROM EMPLOYEES e, <br /> DEPARTMENTS d, <br /> LOCATIONS l, <br /> COUNTRIES c <br /> WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID AND <br /> d.LOCATION_ID = l.LOCATION_ID AND <br /> l.COUNTRY_ID = c.COUNTRY_ID <br /> ORDER BY LAST_NAME;<br />

VIEW 的修改

<br />--修改 VIEW <br />CREATE OR REPLACE VIEW SALESFORCES AS <br /> SELECT FIRST_NAME || ' ' || LAST_NAME AS "NAME", <br /> SALARY * 12 AS "ANNUAL_SALARY" <br /> FROM EMPLOYEES <br /> WHERE DEPARTMENT_ID = 80 OR DEPARTMENT_ID = 20;<br />

VIEW 的更名

<br />--修改 VIEW 的名稱 <br />RENAME "SALESFORCES" TO "SALES_MARKETING";<br />

刪除 VIEW

<br />--移除 VIEW <br />DROP VIEW SALES_MARKETING;<br />

Sequence

Sequence 就像 MS SQL Server 中的流水號一樣，是 Oracle 用來管理流水號的資料庫物件，目的是用來產生循序且唯一的值

Sequence 的值如何使用? 主要是要透過兩個虛擬欄位，分別為：

• CURRVAK
  
  此欄位代表目前 Sequence 的值，但在 Sequence 尚未初始化(呼叫 NEXTVAL)之前是沒辦法使用的。
  
  
• NEXTVAL
  
  此欄位代表 Sequence 下一個會傳回的值；第一次使用代表進行 Sequence 的初始化。

需要注意的是，在 MS SQL Server 中，流水號是依附在特定 table 的特定欄位上；但在 Oracle 中，sequence 跟任何的 database object 都沒有關係。

因此若要拿 sequence 來產生流水號給 table 當 primary key，在 sequence 在命名時可以加入 table 名稱作為方便辨識之用。

建立 SEQUENCE

<br />--建立 sequence <br />--從 1 開始計數，每次加 1 <br />CREATE SEQUENCE EVALUATIONS_SEQ INCREMENT BY 1 START WITH 1 ORDER;<br /><br />--建立 sequence <br />--從 5 開始計數，每次加 5 <br />CREATE SEQUENCE TEST_SEQ INCREMENT BY 5 START WITH 5 ORDER;<br />

刪除 SEQUENCE

<br />--刪除 sequence <br />DROP SEQUENCE TEST_SEQ;<br />

參考資料

• [ 永遠的UNIX > ORACLE SEQUENCE的簡單介紹 ]
  
  
• Acinom的胡言亂語 - Oracle 的流水號
  
  
• [Oracle] Oracle 產生自動編號方式
  
  
• 快速掌握Oracle數據庫中sequence的用法-che,oracle,增加-北方網-ＩＴ浪潮

Synonym

synonym 其實就是 schema object 的別名，除了可以用來協助簡化 SQL 語法外，還可以協助隱藏真正的 database object(基於某些安全考量)。

假設當 database 名稱被修改時，可以透過 synonym 的方式來避免應用程式的修改。

建立 SYNONYM

<br />--建立 SYNONYM <br />--Object based -> JOBS <br />CREATE SYNONYM POSITIONS FOR JOB;<br />

移除 SYNONYM

<br />--移除 SYNONYM <br />DROP SYNONYM POSITIONS;<br />

更詳細的資料，可以參考官方網站的文件：

• CREATE SYNONYM
  
  
• DROP SYNONYM

參考資料

• 敗家貓:有關synonym的討論 - 樂多日誌
  
  
• Oracle的同義詞（synonyms）總結
  
  
• [藍森林-自由軟件] － [轉]synonym的使用- Oracle