2023/12/211Oracle數據庫應用

與開發案例教程第五章表管理2023/12/21Oracle數據庫應用與開發案例教程2第五章表管理5.1表和方案5.2創建表5.3維護表5.4維護約束條件5.5序列和同義詞2023/12/21Oracle數據庫應用與開發案例教程35.1表和方案常用數據類型在創建表的時候，不僅需要指定表名、列名，而且要根據情況為每個列選擇合適的數據類型并定義其長度，用來指定該列可以存取哪種類型的數據。Oracle中常用的數據類型如下所示：字符數據類型數字數據類型日期和時間數據類型二進制數據類型大對象數據類型行數據類型2023/12/21Oracle數據庫應用與開發案例教程45.1表和方案用戶與方案方案是指用戶所擁有一系列邏輯數據結構或數據庫對象的集合，在Oracle數據庫中對象是以用戶來組織的，一個方案對應一個數據庫用戶，每個用戶都有一個單獨的方案。用戶與方案名稱相同，而且是一一對應的關系。【例5-1】在數據庫ZCGL中創建用戶ZCGL_OPER，密碼是admin，默認表空間是zcgl_tbs1，臨時表空間是zcgl_temp1，并為該用戶在表空間zcgl_tbs1中分配50M的限額。并將DBA角色授予該用戶。2023/12/21Oracle數據庫應用與開發案例教程55.2

創建表創建表的一般語法所謂創建表，實際上就是在數據庫中定義表的結構。表的結構主要包括表與列的名稱、列的數據類型，以及建立在表或列上的約束。基本語法格式如下所示：CREATETABLE[schema.]table_name(column1datatype1[DEFAULTexpl][column1constraint],column2datatype2[DEFAULTexp2][column2constraint],…)[TABLESPACEtablespace_name];2023/12/21Oracle數據庫應用與開發案例教程65.2

創建表語法說明1）schema：指定表所屬的用戶方案名稱。如果用戶在自己的方案中創建表，要求該用戶必須具有CREATETABLE系統權限；如果要在其他方案中創建表，則要求該用戶必須具有CREATEANYTABLE系統權限。2）table_name：指定要創建的表的名稱。3）columnldatatypel：用于指定列的名稱和及其數據類型。4）DEFAULTexpl：用于指定列的默認值。5）columnlconstraint：用于為約束命名。如果不使用columnlconstraint子句，Oracle將自動為約束建立默認的約束名。6）TABLESPACEtablespace_name：用于指定存儲表或索引的表空間。2023/12/21Oracle數據庫應用與開發案例教程75.2

創建表創建標準表【例5-2】在用戶ZCGL_OPER的方案中創建一個名為ZICHANMINGXI的標準表。【例5-3】使用DESC命令來顯示ZICHANMINGXI表的結構。2023/12/21Oracle數據庫應用與開發案例教程85.2

創建表創建臨時表如果在創建表時用關鍵字TEMPORARY或GLOBALTEMPORARY指定了表的類型，Oracle會生成一個臨時表。臨時表用于存放事務或會話的私有數據，其表結構會一直存在，但其數據只是在當前事務內或會話內有效。臨時表分為事務臨時表和會話臨時表兩種。【例5-4】創建事務臨時表temp1。【例5-5】創建會話臨時表temp2。2023/12/21Oracle數據庫應用與開發案例教程95.2

創建表基于已有的表創建新表通過在CREATETABLE語句中嵌套SELECT子查詢就可以基于已有的表或視圖來創建新表，這種創建表的語法格式是：CREATETABLEtable_name[(column1,column2,……)]ASSELECT<query>語法說明：1）column1,column2,……：定義新表的字段，新表中所有列的數據類型和長度都與原表中相應的列一樣；如果省略該子句，那么新表的字段名與查詢結果集中的字段名同名。2）ASSELECT<query>：在子查詢中可以引用一個或多個表（或視圖），查詢結果集中包含的列即是新表中定義的列，并且查詢到的記錄都會插入到新表中。2023/12/21Oracle數據庫應用與開發案例教程105.2

創建表基于已有的表創建新表【例5-6】通過復制表ZICHANMINGXI的結構和數據來創建NEW_ZICHANMINGXI新表。【例5-7】創建新表NEW_ZICHANMINGXI_1，其表結構同ZICHANMINGXI表，但不含有該表的任何記錄。【例5-8】通過復制表ZICHANMINGXI的ZCID，FLID，BMID，ZTID，YHID和ZCMC等列的結構和數據，來創建新表NEW_ZICHANMINGXI_2，并將列ZCMC改名NAME。2023/12/21Oracle數據庫應用與開發案例教程115.3維護表表的結構創建完成以后，如果發現有不滿意或者不符合實際需要的地方，可以對表的結構進行修改：可以添加或刪除表中的列、修改表中列的名稱、數據類型和長度等，還可以對表進行重新命名和重新組織。普通用戶只能對自己方案中的表進行更改，而具有ALTERANYTABLE系統權限的用戶可以修改任何方案中的表。2023/12/21Oracle數據庫應用與開發案例教程125.3維護表字段操作：添加列向一個現有的表中添加一個新列的語法格式是：ALTERTABLE[schema.]table_nameADD(column_definition1,column_definition2﹍);語法說明：新添加的列總是位于表的末尾。column_definition部分包括列名、列的數據類型及其默認值。【例5-9】在ZICHANMINGXI表中添加一個新列：規格型號GGXH。2023/12/21Oracle數據庫應用與開發案例教程135.3維護表字段操作：更改列如果需要調整一個表中某些列的數據類型、長度和默認值，就需要更改這些列的屬性。更改表中現有列的語法格式是：ALTERTABLE[schema.]table_nameMODIFY(column_name1new_attributesl,column_name2new_attributes2﹍);【例5-10】將ZICHANMINGXI表的BMID列的數據類型改為CHAR(8)，將BZ列的數據類型改為VARCHAR2(90)。2023/12/21Oracle數據庫應用與開發案例教程145.3維護表字段操作：刪除列當不再需要某些列時，可以將其刪除。刪除列時Oracle將刪除表中每條記錄內的相應列的值，并釋放其占用的存儲空間。在被刪除列上建立的索引和約束也會被刪除，如果被刪除的列是一個多列約束的組成部分，那么就必須指定CASCADECONSTRAINTS選項，這樣才會刪除相關的約束。直接刪除列的語法是：ALTERTABLE[schema.]table_nameDROP(column_name1,column_name2﹍)[CASCADECONSTRAINTS];說明：可以在括號中使用多個列名，每個列名用逗號分隔。【例5-11】下面的語句刪除ZICHANMINGXI表中的GGXH列和BZ列 2023/12/21Oracle數據庫應用與開發案例教程155.3維護表字段操作：將列標記為UNUSED狀態為了避免在數據庫使用高峰期間由于執行刪除列的操作而占用過多系統資源，可以暫時通過ALTERTABLE--SETUNUSED語句將要刪除的列設置為UNUSED狀態。當表中某列被設置為UNUSED狀態后，用戶將無法對該列進行各種DML操作，實際上Oracle并沒有釋放該列占用的存儲空間，該列還是存在于數據庫中。該語句的語法格式為：ALTERTABLE[schema.]table_nameSETUNUSED(column_name1,column_name2﹍)[CASCADECONSTRAINTS];

2023/12/21Oracle數據庫應用與開發案例教程165.3維護表字段操作：將列標記為UNUSED狀態【例5-12】將ZICHANMINGXI表中的ZCYZ列和GRSJ列標記為UNUSED狀態： 【例5-13】在數據字典視圖USER_UNUSED_COL_TABS、ALL-_UNUSED_COL_TABS和DBA_UNUSED_COL_TABS中可以查看數據庫中被標記為UNUSED狀態的列所在表的信息。【例5-14】將ZICHANMINGXI表中的被標記為UNUSED狀態的列刪除：2023/12/21Oracle數據庫應用與開發案例教程175.3維護表重命名表當重新命名表時，Oracle會自動把舊表上的視圖、對象權限和約束條件轉換到新表名上，但所有與舊表相關的視圖、同義詞、存儲過程和函數等對象會失效。可以使用兩種方法來重新命名表。 1.RENAME語句使用RENAME語句來修改一個表、視圖、序列、專用同義詞名稱的語法格式為：RNAMEold_tablenameTOnew_tablename【例5-15】把ZICHANMINGXI表的名稱更改為ZICHANMINGXI_CHANGE：2023/12/21Oracle數據庫應用與開發案例教程185.3維護表重命名表 2.ALTERTABLE語句用RENAME語句只能更改自己方案中對象的名字，而不能重命名其他用戶方案中對象的名字。可以使用帶有RENAMETO子句的ALTERTABLE語句來重命名其他用戶方案中對象的名字。但用戶必須對該表擁有ALTER權限或者ALTERANYTABLE系統權限才能實現這一操作。【例5-16】把ZICHANMINGXI_CHANGE表的名稱更改為ZICHANMINGXI：2023/12/21Oracle數據庫應用與開發案例教程195.3維護表刪除表 當不再需要某個表時，可以將該表刪除，刪除表時該表中的數據也將被刪除。其語法格式如下：DROPTABLE[schema.]table_name[CASCADECONSTRAINTS];說明：如果在table_name前面沒有加schema，則表示刪除自己方案中的表。如果要刪除其他方案中的表，則必須加schema，并且還必須具有DROPANYTABLE系統權限。【例5-17】刪除ZICHANMINGXI表。【例5-18】刪除表ZICHANMINGXI，同時刪除所有引用該表的視圖，約束，索引和觸發器。2023/12/21Oracle數據庫應用與開發案例教程205.3維護表移動表 在創建表的時候如果沒有指定表所屬的表空間，Oracle將把該表存儲到默認表空間中。使用ALTERTABLE…MOVE語句將表從一個表空間移動到另一個表空間中。其語法格式為：ALTERTABLEtable_nameMOVETABLESPACEtablespace_name;【例5-19】將ZICHANMINGXI表從當前的表空間移動到USERS表空間中。2023/12/21Oracle數據庫應用與開發案例教程215.3維護表查看表信息 創建表時，Oracle會將表的定義存放到數據字典中，常用數據字典有：1）DBA_TABLES：描述數據庫中所有關系表。2）ALL_TABLES：描述用戶可以訪問的所有表。3）USER_TABLES：描述當前用戶擁有的表。【例5-20】查詢用戶ZCGL_OPER的表ZICHANMINGXI信息：2023/12/21Oracle數據庫應用與開發案例教程225.4維護約束條件數據庫的完整性是指維護數據的正確性、一致性和安全性。例如，當用戶執行INSERT、DELETE和UPDATE等操作時，如果將無效的數據添加到數據庫的表中就破壞了數據庫的完整性。約束條件是在表中定義的，用于維護數據庫完整性的一些規則。可以在創建表時在單個表中或多個表之間定義一些約束條件來防止將錯誤的數據插入到表中，并可以保證數據的一致性。 Oracle數據庫的完整性約束主要包括主鍵約束（PRIMARYKEY），非空約束（NOTNULL），唯一性約束（UNIQUE），檢查約束（CHECK）和外鍵約束（FOREIGNKEY）。如果某約束只作用于一列，該約束可以定義為列約束或表約束；如果某約束作用于多個列，那么該約束應該定義為表約束。2023/12/21Oracle數據庫應用與開發案例教程235.4維護約束條件約束條件的定義：非空約束NOTNULL NOTNULL即非空約束，該約束只能定義為列約束，用于限定一個字段的取值不能為空。如果在某列上定義了NOTNULL約束，插入數據時，必須為該列提供數據。同一個表中可以有多個列被定義為NOTNULL約束。【例5-21】在BUMEN表的BMID列上定義NOTNULL約束。【例5-22】在BUMEN表的BMMC列上定義NOTNULL約束。2023/12/21Oracle數據庫應用與開發案例教程245.4維護約束條件約束條件的定義：唯一性約束UNIQUE UNIQUE約束即唯一性約束，用于要求所約束的列中不能有重復值。可以為一列或者多列組合定義UNIQUE約束。Oracle會自動為具有UNIQUE約束的列建立一個唯一索引（UniqueIndex)，對同一個列，可以同時定義UNIQUE約束和NOTNULL約束。【例5-23】在BUMEN表的BMMC列上定義UNIQUE約束。

2023/12/21Oracle數據庫應用與開發案例教程255.4維護約束條件約束條件的定義：檢查約束CHECKCHECK約束即檢查約束，用于強制在約束中的列必須滿足指定的條件表達式。該條件表達式需要引用表中的一列或多列，其計算結果是一個布爾值。定義CHECK約束列的值需要滿足條件表達式，但可以是NULL。CHECK可以定義為列約束或者表約束。在單個列上，可以定義多個CHECK約束。【例5-24】在表BUMEN的BMID列上定義CHECK約束“CK”。2023/12/21Oracle數據庫應用與開發案例教程265.4維護約束條件約束條件的定義：主鍵約束PRIMARYKEYPRIMARYKEY約束為主鍵約束，用于唯一地標識出表中的每行數據。PRIMARYKEY約束列的值不能重復，并且也不能為NULL。定義PRIMARYKEY約束時，Oracle會自動為具有PRIMARYKEY約束的列建立一個唯一索引和一個NOTNULL約束。一個表中只能有一個PRIMARYKEY約束。【例5-25】在BUMEN表的BMID列上定義主鍵約束“BUMEN_PK”。2023/12/21Oracle數據庫應用與開發案例教程275.4維護約束條件約束條件的定義：外鍵約束FOREIGNKEYFOREIGNKEY約束即外鍵約束，用于與其他表（父表）中的列（參照列）建立連接，定義主從表之間的聯系。將父表中具有PRIMARYKEY約束或UNIQUE約束的列包含在另一個表（子表）中，這些列就構成了子表的外鍵。定義了FOREIGNKEY約束的列中只能包含相應的、在其他表中引用的列的值，或者為NULL值。在單個列上，可以同時定義FOREIGNKEY約束和NOTNULL約束。同樣可以為一列定義FOREIGNKEY約束，也可以為多列的組合定義FOREIGNKEY約束。【例5-26】在BUMEN表中，將BMID列定義為SJBM列的外鍵約束。2023/12/21Oracle數據庫應用與開發案例教程285.4維護約束條件約束的狀態1）ENABLEVALIDATE(啟用驗證狀態）在定義或增加約束時如果不指定約束狀態，則ENABLEVALIDATE為約束的默認狀態。當將約束轉變為此狀態后，Oracle會對新、舊數據進行約束檢查。這種狀態要求表中所有的記錄都滿足約束規則。2）ENABLENOVALIDATE(啟用非驗證狀態)當將約束轉變為此狀態后，Oracle會對新數據進行約束檢查，但已存在的舊數據可以不滿足約束規則。2023/12/21Oracle數據庫應用與開發案例教程295.4維護約束條件約束的狀態3）DISABLEVALIDATE(禁用驗證狀態）當將約束轉變為此狀態后，約束被禁用，不允許對表進行任何DML操作。但是表中已存在的數據仍然滿足約束規則。4）DISABLENOVALIDATE(禁用非驗證狀態）當將約束轉變為此狀態后，Oracle對新、舊數據都不進行約束檢查。這種狀態下，表中所有的數據都可以不滿足約束規則。2023/12/21Oracle數據庫應用與開發案例教程305.4維護約束條件約束的狀態【例5-27】對表BUMEN禁用主碼。【例5-28】通過約束名稱來改變約束狀態。【例5-29】對BUMEN表禁用主碼并將對該主碼的外鍵引用刪除。2023/12/21Oracle數據庫應用與開發案例教程315.4維護約束條件添加和刪除約束：添加約束為已經建立的表添加新的約束可以使用ALTERTABLE…ADD語句完成，其添加約束語法格式是：ALTERTABLEtable_nameADD[CONSTRAINTconstraint_name]constraint_type(column1,column2,…)[condition]語法說明：1）CONSTRAINT關鍵字：用于指定約束名，如果沒有為約束指定名稱，Oracle會自動為約束命名為SYS_C。2）constraint_type和condition：分別用于指定約束類型和約束條件。3）column1等：用于指定添加約束的列。2023/12/21Oracle數據庫應用與開發案例教程325.4維護約束條件添加和刪除約束：添加約束【例5-30】為BUMEN表添加主鍵約束。【例5-31】為表ZICHANMINGXI的BMID列添加外鍵約束：外鍵為BUMEN表的BMID列。【例5-32】為表BUMEN的name列添加UNIQUE約束。2023/12/21Oracle數據庫應用與開發案例教程335.4維護約束條件添加和刪除約束：刪除約束刪除約束是通過執行ALTERTABLE…DROP語句來完成的。刪除約束的命令格式為：ALTERTABLEtable_nameDROP[CONSTRAINTconstraint_name];【例5-33】刪除BUMEN表的BMMC列上的UNIQUE約束。【例5-34】刪除BUMEN表的約束的名稱CKK。【例5-35】刪除BUMEN表的主鍵約束。2023/12/21Oracle數據庫應用與開發案例教程345.4維護約束條件查看約束信息視圖描述DBA_CONSTRAINTS查看數據庫中所有約束信息。ALL_CONSTRAINTS查看當前用戶可以訪問的所有約束信息。USER_CONSTRAINTS查看當前用戶的所有約束信息。DBA_CONS_COLUMNS查看數據庫中所有約束對應列的信息。ALL_CONS_COLUMNS查看當前用戶可以訪問的所有約束的列的信息2023/12/21Oracle數據庫應用與開發案例教程355.4維護約束條件查看約束信息【例5-36】通過查詢視圖USER_CONSTRAINTS來查看當前用戶的BUMEN表的所有約束。【例5-37】通過查詢視圖USER_CONS_COLUMNS來查看表BUMEN的約束都定義在哪些列上。2023/12/21Oracle數據庫應用與開發案例教程365.5序列和同義詞創建和使用序列序列(sequence)是定義在數據字典中用于生成一個整數序列的數據庫對象，用來為表中數據類型的主鍵提供有序的唯一值。多個用戶可以共享序列中的序號。序列不占用實際的存儲空間，在數據字典中只存儲序列的定義描述。2023/12/21Oracle數據庫應用與開發案例教程375.5序列和同義詞創建和使用序列：創建序列為了在自己的方案中創建序列，要求用戶必須具有CREATESEQUENCE系統權限；如果要在其它用戶中創建序列，則用戶必須具有CREATEANYSEQUENCE系統權限。創建序列使用CREATESEQUENCE語句完成，其語法格式是：CREATESEQUENCEsequence_name[STARTWITHn1][INCREMENTBYn2][{MAXVALUEn3｜NOMAXVALUE}][{MINVALUEn4｜NOMINVALUE}][{CACHEn5｜NOCACHE}][{CYCLE｜NOCYCLE}][ORDER];2023/12/21Oracle數據庫應用與開發案例教程385.5序列和同義詞創建和使用序列：創建序列語法說明：1）sequence_name：用于指定序列名。2）nl、n2、n3、n4、n5：表示整數。3）STARTWITH：用于指定序列中的序號從哪個數字開始，默認值為序列號的最小值。4）INCREMENTBY：用于指定序號的增量，序列號可以遞增也可以遞減。5）MAXVALUE：用來指定序列中的最大序列號，如果沒有最大序列號，可用NOMAXVALUE代替。同樣，MINVALUE用于指定序列中的最小序列號，其最小值必須小于或等于STARTWITH中的開始值nl。2023/12/21Oracle數據庫應用與開發案例教程395.5序列和同義詞創建和使用序列：創建序列語法說明：6）CACHE：用于緩沖預分配的序列號個數n5。如果不需要對預分配的序列號個數進行緩沖，則可以使用NOCACHE代替。7）CYCLE：用于使序列中的序號可以循環使用，默認為NOCYCLE。8）ORDER：用于指定按順序生成序列號。【例5-38】SCOTT用戶創建ZICHANMINGXI_SEQ序列。2023/12/21Oracle數據庫應用與開發案例教程405.5序列和同義詞創建和使用序列：使用序列序列提供了NEXTVAL與CURRVAL兩個偽列，用來訪問序列中的序號。NEXTVAL偽列返回序列生成的下一個值，而CURRVAL返回序列的當前值。在首次使用序列中的序號時，必須引用一次序列的NEXTVAL偽列，用于初始化序列的值，否則會出現錯誤提示。【例5-39】對序列ZICHANMINGXI_SEQ執行SELECT語句查看其當前序列值。【例5-40】對于已創建的序列ZICHANMINGXI_SEQ執行SELECT語句。【例5-41】創建序列zcseq，然后使用序列zcseq向ZICHANMINGXI表中插入幾條記錄，最后查詢ZICHANMINGXI表。2023/12/21Oracle數據庫應用與開發案例教程415.5序列和同義詞創建和使用序列：修改和刪除序列當需要修改序列的時候，可以使用ALTERSEQUENCE命令完成。如果用戶要修改其他方案的序列，則該用戶必須具有ALTERANYSEQUENCE系統權限。【例5-42】對ZICHANMINGXI_SEQ序列進行更改。【例5-43】刪除ZICHANMINGXI_SEQ序列。2023/12/21Oracle數據庫應用與開發案例教程425.5序列和同義詞同義詞同義詞（synonym）是為表、索引和視圖等數據庫對象定義的別名，使用同義詞可以簡化SQL語句的書寫。同義詞的定義存儲在數據字典中，不占用任何實際的存儲空間。同義詞分為以下兩