Oracle第9章模式對象.ppt

1、第9章 模式對象,本章內容,模式概念 表管理 索引管理 分區(qū)表與分區(qū)索引的管理 視圖、序列、同義詞、數(shù)據(jù)庫鏈接管理,本章要求,掌握表的管理 掌握索引的管理 掌握分區(qū)的概念及分區(qū)管理 了解視圖、序列、同義詞、數(shù)據(jù)庫鏈接等的管理,9.1模式,模式概念 模式與用戶的關系 模式選擇與切換,模式概念 是指一系列邏輯數(shù)據(jù)結構或對象的集合。 模式與用戶的關系 模式與用戶相對應，一個模式只能被一個數(shù)據(jù)庫用戶所擁有，并且模式的名稱與這個用戶的名稱相同。 通常情況下，用戶所創(chuàng)建數(shù)據(jù)庫對象都保存在與自己同名的模式中。 同一模式中數(shù)據(jù)庫對象的名稱必須惟一，而在不同模式中的數(shù)據(jù)庫對象可以同名。 默認情況下，用戶引用的對

2、象是與自己同名模式中的對象，如果要引用其他模式中的對象，需要在該對象名之前指明對象所屬模式。,模式選擇與切換 如果用戶以NORMAL身份登錄，則進入同名模式； 若以SYSDBA身份登錄，則進入SYS模式； 如果以SYSOPER身份登錄，則進入PUBLIC模式。,9.2 表,創(chuàng)建表 表約束的管理 表參數(shù)設置 修改表 刪除表 利用OEM管理表,9.2.1 創(chuàng)建表,表的創(chuàng)建 創(chuàng)建臨時表 利用子查詢創(chuàng)建表,（1）表的創(chuàng)建,表的規(guī)劃與設計 表名稱 列的數(shù)量 列名稱與類型 表約束 表內部數(shù)據(jù)的組織方式（標準表、索引化表、分區(qū)表） 表存儲位置、存儲空間分配等。,使用CREATE TABLE語句 創(chuàng)建表 CR

3、EATE TABLE table_name (column_name datatype column_level_constraint ，column_name datatype column_level_constraint ，table_level_constraint) parameter_list;,CREATE TABLE employee( empno NUMBER(5) PRIMARY KEY, ename VARCHAR2(15), deptno NUMBER(3) NOT NULL CONSTRAINT fk_emp REFERENCES dept（deptno） ) TAB

4、LESPACE USERS PCTFREE 10 PCTUSED 40 STORAGE(INITIAL 50K NEXT 50K MAXEXTENTS 10 PCTINCREASE 25);,表名（table_name） 必須是合法標識符，長度為130 字節(jié)，并且以字母開頭，可以包含字母(AZ，az)、數(shù)字（09），下劃線（_）、美元符號（$）和井號（#）。此外，表名稱不能是Oracle數(shù)據(jù)庫的保留字 數(shù)據(jù)類型(datatype) 字符類型 數(shù)字類型 日期類型 LOB類型 二進制類型 行類型,CHAR（n） 定長字符串，n的取值范圍為1-2000字節(jié) VARCHAR2（n） 可變字符串，n取值

5、范圍為1-4000字節(jié) 自動調整數(shù)據(jù)長度 NCHAR(n) 用來存儲Unicode類型字符串。 NVARCHAR2(n) 它用來存儲Unicode類型字符串。 LONG 可變長字符列，最大長度為2GB 用于不需設置成索引的字符，不常用,字符類型,數(shù)字類型,NUMBER（m，n） 用于存儲整數(shù)和實數(shù)。m表示數(shù)值的總位數(shù)（精度），取值范圍為138，默認為38；n表示小數(shù)位數(shù)，若為負數(shù)則表示把數(shù)據(jù)向小數(shù)點左邊舍入，默認值為0。,日期類型,DATE 可以存儲的日期范圍為公元前4712年1月1日到公元4712年1月1日，由世紀、年、月、日、時、分、秒組成?？梢栽谟脩舢斍皶捴惺褂脜?shù)NLS_DATE_F

6、ORMAT指定日期和時間的格式，或者使用TO_DATE函數(shù)，將表示日期和時間的字符串按特定格式轉換成日期和時間。 TIMESTAMP(n)：表示時間戳，是DATE 數(shù)據(jù)類型的擴展，允許存儲小數(shù)形式的秒值。n表示秒的小數(shù)位數(shù)，取值范圍為19，默認值為6。 TIMESTAMP(n) WITH TIME ZONE：通過存儲一個時區(qū)偏差來擴展TIMESTAMP類型。 時區(qū)偏差值為相對于通用協(xié)調時間（或稱UTC，以前稱為格林威治時間或GMT）的時差。,TIMESTAMP(n) WITH LOCAL TIME ZONE 與TIMESTAMP(n) WITH TIME ZONE的不同之處在于，存儲日期時直接

7、轉換為數(shù)據(jù)庫時區(qū)日期，而讀取日期時將數(shù)據(jù)庫時區(qū)日期轉換為用戶會話時區(qū)日期。 INTERVAL YEAR(n) TO MONTH 存儲以年份和月份表示的時間段。n是表示YEAR的最多數(shù)字位數(shù)，默認為2。例如，INTERVAL 2-11 YEAR TO MONTH表示2年11個月。 INTERVAL DAY(m) TO SECOND(n) 存儲以天數(shù)、小時數(shù)、分鐘數(shù)和秒數(shù)表示的時間段。m是表示DAY的最多數(shù)字位數(shù)，默認為2。n是表示SECOND的小數(shù)部分的位數(shù)，默認為6。例如，INTERVAL 5 10:30:40 DAY TO SECOND表示5天10小時30分40秒。,LOB類型,CLOB 用

8、于存儲可變長度的字符數(shù)據(jù)，如文本文件等，最大數(shù)據(jù)量為4 GB。 NCLOB 用于存儲可變長度的Unicode字符數(shù)據(jù)，最大數(shù)據(jù)量為4 GB。 BLOB 用于存儲大型的、未被結構化的可變長度的二進制數(shù)據(jù)（如二進制文件、圖片文件、音頻和視頻等非文本文件），最大數(shù)據(jù)量為4 GB。 BFILE 用于存儲指向二進制格式文件的定位器，該二進制文件保存在數(shù)據(jù)庫外部的操作系統(tǒng)中，文件最大為4 GB。,二進制類型,RAW(n) 用于存儲可變長度的二進制數(shù)據(jù)，n表示數(shù)據(jù)長度，取值范圍為12000字節(jié)； LONG RAW 用于存儲可變長度的二進制數(shù)據(jù)，最大存儲數(shù)據(jù)量為2 GB。,行類型,ROWID 行標識符，表中行

9、的物理地址的偽列類型。ROWID類型數(shù)據(jù)由18位十六進制數(shù)構成，包括對象編號、文件編號、數(shù)據(jù)塊編號和塊內行號。 UROWID 行標識符，用于表示索引化表中行的邏輯地址。,約束（constraint） 在Oracle數(shù)據(jù)庫中對列的約束包括主鍵約束、惟一性約束、檢查約束、外鍵約束和空/非空約束等5中，定義方法有表級約束和列級約束2種。 參數(shù)（parameter_list） 在定義表時，可以通過參數(shù)設置表存儲在哪一個表空間中，和存儲空間分配等。,（2）創(chuàng)建臨時表,臨時表中的數(shù)據(jù)在特定條件下自動釋放，但其結構將一直存在。 根據(jù)臨時表中數(shù)據(jù)被釋放的時間不同，臨時表分為事務級別的臨時表和會話級別的臨時表兩

10、類。 ON COMMIT DELETE ROWS /事務級臨時表 ON COMMIT PRESERVE ROWS /會話級臨時表 語法 使用CREATE GLOBAL TEMPORARY TABLE語句創(chuàng)建臨時表 使用ON COMMIT子句說明臨時表的類型，默認為事務級別的臨時表。,事務級別的臨時表是在事務提交時系統(tǒng)自動刪除表中所有記錄。 CREATE GLOBAL TEMPORARY TABLE tran_temp( ID NUMBER(2) PRIMARY KEY, name VARCHAR2(20) ) ON COMMIT DELETE ROWS;,會話級別的臨時表是在會話終止時系統(tǒng)自動

11、刪除表中所有記錄 。 CREATE GLOBAL TEMPORARY TABLE sess_temp( ID NUMBER(2) PRIMARY KEY, name VARCHAR2(20) ) ON COMMIT PRESERVE ROWS;,（3）利用子查詢創(chuàng)建表,語法 CREATE TABLE table_name (column_name column_level_constraint ，column_name column_level_constraint ，table_level_constraint) parameter_list AS subquery;,注意 通過該方法創(chuàng)建表

12、時，可以修改表中列的名稱，但是不能修改列的數(shù)據(jù)類型和長度； 源表中的約束條件和列的缺省值都不會復制到新表中； 子查詢中不能包含LOB類型和LONG類型列； 當子查詢條件為真時，新表中包含查詢到的數(shù)據(jù)；當查詢條件為假時，則創(chuàng)建一個空表。,創(chuàng)建一個標準表，保存工資高于3000的員工的員工號、員工名和部門號。語句為 CREATE TABLE emp_select(emp_no,emp_name,dept_no) AS SELECT empno,ename,deptno FROM employee WHERE sal3000;,創(chuàng)建一個會話級臨時表，保存部門號、部門人數(shù)和部門的平均工資。 CREATE

13、 GLOBAL TEMPORARY TABLE ept_temp ON COMMIT PRESERVE ROWS AS SELECT deptno,count(*) num,avg(sal) avgsal FROM emp GROUP BY deptno;,9.2.2 表約束,約束的類別 定義約束 添加和刪除約束 設置約束狀態(tài) 約束的延遲檢查 查詢約束信息,（1）約束的類別,約束作用 是在表中定義的用于維護數(shù)據(jù)庫完整性的一些規(guī)則。通過對表中列定義約束，可以防止在執(zhí)行DML操作時，將不符合要求的數(shù)據(jù)插入到表中。 約束類型 PRIMARY KEY UNIQUE CHECK FOREIGN KEY

14、NULL/NOT NULL,PRIMARY KEY,特點 定義主鍵，起惟一標識作用，其值不能為NULL，也不能重復； 一個表中只能定義一個主鍵約束； 建立主鍵約束的同時，在該列上建立一個惟一性索引，可以為它指定存儲位置和存儲參數(shù)； 主鍵約束可以是列級約束，也可以是表級約束。,UNIQUE,特點 定義為惟一性約束的某一列或多個列的組合的取值必須惟一； 如果某一列或多個列僅定義惟一性約束，而沒有定義非空約束，則該約束列可以包含多個空值； Oracle自動在惟一性約束列上建立一個惟一性索引，可以為它指定存儲位置和存儲參數(shù)； 惟一性約束可以是列級約束，也可以是表級約束。,在一個基本表中只能定義一個PR

15、IMARY KEY約束，但可定義多個UNIQUE約束； 對于指定為PRIMARY KEY的一個列或多個列的組合，其中任何一個列都不能出現(xiàn)空值，而對于UNIQUE所約束的唯一鍵，則允許為空。 不能為同一個列或一組列既定義UNIQUE約束，又定義PRIMARY KEY約束。,PRIMARY KEY與UNIQUE比較,CHECK,特點 檢查約束用來限制列值所允許的取值范圍，其表達式中必須引用相應列，并且表達式的計算結果必須是一個布爾值； 約束表達式中不能包含子查詢，也不能包含SYSDATE、USER等SQL函數(shù)，和ROWID、ROWNUM等偽列； 一個列可以定義多個檢查約束； 檢查約束可以是列級約束

16、，也可以是表級約束。,概念 FOREIGN KEY約束指定某一個列或一組列作為外部鍵，其中，包含外部鍵的表稱為從表，包含外部鍵所引用的主鍵或唯一鍵的表稱主表。 系統(tǒng)保證從表在外部鍵上的取值要么是主表中某一個主鍵值或唯一鍵值，要么取空值。以此保證兩個表之間的連接，確保了實體的參照完整性。,FOREIGN KEY,特點 定義外鍵約束的列的取值要么是主表參照列的值，要么為空； 外鍵列只能參照于主表中的主鍵約束列或惟一性約束列； 可以在一列或多列組合上定義外鍵約束； 外鍵約束可以是列級約束，也可以是表級約束。,NULL/NOT NULL,特點 在同一個表中可以定義多個NOT NULL約束； 只能是列級

17、約束。,（2）定義約束,列約束 對某一個特定列的約束，包含在列定義中，直接跟在該列的其他定義之后，用空格分隔,不必指定列名； 表約束 與列定義相互獨立，不包括在列定義中。通常用于對多個列一起進行約束，與列定義用,分隔。定義表約束時必須指出要約束的那些列的名稱。,定義列級約束的語法為： CONSTRAINT constraint_name constraint_type conditioin; 定義表級約束的語法為： CONSTRAINT constraint_name constraint_type(column1_name, column2_name,|condition); 注意 Orac

18、le約束通過名稱進行標識。在定義時可以通過CONSTRAINT關鍵字為約束命名。如果用戶沒有為約束命名，Oracle將自動為約束命名。,創(chuàng)建一個student表。 CREATE TABLE student( sno NUMBER(6) CONSTRAINT S_PK PRIMARY KEY, sname VARCHAR2(10) NOT NULL, sex CHAR(2) CONSTRAINT S_CK1 CHECK(sex in(M, F), sage NUMBER(6,2), CONSTRAINT S_CK2 CHECK( sage between 18 and 60) );,創(chuàng)建一個course表，同時為主鍵約束列上的唯一性索引設置存儲位置和存儲參數(shù)，語句為 CREATE TABLE course( cno NUMBER(6) PRIMARY KEY, cname CHAR(20) UNIQUE USING INDEX TABLESPACE indx STORAGE( INITIAL 64K NEXT 64K) );,創(chuàng)建一個SC表，語句為 CREATE TABLE SC( sno NUMBER(6) REFERENCES student(sno), cno NUMBER(6) REFERENCES cours