《Oracle套用開發》是2013年電子工業出版社出版的圖書，作者是青島東合信息技術有限公司。
基本介紹
- 作者：青島東合信息技術有限公司
- ISBN：9787121209093
- 頁數：524
- 定價：69
- 出版社：電子工業出版社
- 出版時間：2013-7
- 裝幀：平裝
- 副標題：高等院校軟體專業方向系列教材
內容介紹,作品目錄,
內容介紹
本書從資料庫的基本概念出發，以Oracle 11g 資料庫為背景詳細地介紹了資料庫的原理和資料庫系統的開發技術。理論篇共分為11 章，分別介紹了關係型資料庫的概念和理論、Oracle 資料庫體系結構、SQL Developer 工具的使用、SQL 語句、函式、表、數據維護、視圖、PL/SQL 語法、游標、函式異常、序列、觸發器、數據恢復、並發控制以及資料庫安全等內容。書中通過SQL Developer 和SQL *Plus 工具實現對Oracle的數據查詢，並詳細介紹如何通過SQL 語句實現表格、索引、約束、視圖、存儲過程、函式、觸發器、序列以及PL/SQL 的編寫。
本書重點突出、偏重套用，結合理論篇的實例和實踐篇對貫穿案例的講解、剖析及實現，使讀者能迅速理解並掌握知識，全面提高動手能力。
本書適應面廣，可作為本科計算機科學與技術、軟體外包專業、高職高專計算機軟體、計算機網路、計算機信息管理、電子商務和經濟管理等專業的程式設計課程的教材。
作品目錄
理論篇...............................................................................................................................1
第1 章 關係型資料庫.........................................................................................................2
1.1 資料庫概述.......................................................................................................................4
1.1.1 資料庫基本概念..................................................................................................4
1.1.2 資料庫發展史......................................................................................................6
1.1.3 資料庫特徵..........................................................................................................9
1.1.4 常見資料庫..........................................................................................................9
1.2 數據模型......................................................................................................................... 11
1.2.1 概念模型............................................................................................................ 11
1.2.2 邏輯數據模型....................................................................................................14
1.3 關係模型理論.................................................................................................................18
1.3.1 關係....................................................................................................................20
1.3.2 關係模式............................................................................................................23
1.3.3 關係的完整性....................................................................................................24
1.3.4 關係代數............................................................................................................26
1.4 規範化理論.....................................................................................................................34
1.4.1 規範化理論的提出............................................................................................34
1.4.2 函式依賴............................................................................................................37
1.4.3 函式依賴定義的碼............................................................................................40
1.4.4 範式及各範式的關係........................................................................................41
1.4.5 多值依賴............................................................................................................50
1.4.6 規範化思想........................................................................................................51
1.5 資料庫設計.....................................................................................................................52
1.5.1 資料庫設計特點................................................................................................53
1.5.2 需求分析............................................................................................................55
1.5.3 數據流圖和數據字典........................................................................................57
1.6 概念結構設計.................................................................................................................58
1.6.1 概念結構設計定義............................................................................................59
1.6.2 概念結構設計的方法和步驟............................................................................60
1.6.3 數據抽象............................................................................................................61
1.6.4 E-R 圖的生成.....................................................................................................62
1.7 邏輯結構設計.................................................................................................................66
1.7.1 初始關係模式設計.............................................................................................66
1.7.2 數據模型最佳化的方法.........................................................................................68
1.7.3 模式評價與改進................................................................................................68
小結....................................................................................................................70
練習..................................................................................................................70
第2 章 Oracle 資料庫........................................................................................76
2.1 Oracle 資料庫概述..........................................................................................................78
2.1.1 Oracle 資料庫簡介.............................................................................................78
2.1.2 Oracle 發展史.....................................................................................................79
2.1.3 Oracle 資料庫的套用結構.................................................................................81
2.2 Oracle 體系結構..............................................................................................................83
2.3 Oracle 資料庫存儲結構..................................................................................................84
2.3.1 物理存儲結構....................................................................................................85
2.3.2 邏輯存儲結構....................................................................................................88
2.4 Oracle 資料庫實例..........................................................................................................90
2.4.1 Oracle 實例概述.................................................................................................90
2.4.2 SGA ....................................................................................................................92
2.4.3 PGA ....................................................................................................................95
2.4.4 後台進程............................................................................................................97
小結..........................................................................................................101
練習.....................................................................................................................102
第3 章 SQL Developer 開發工具..............................................................................103
3.1 SQL Developer 概述....................................................................................................105
3.1.1 SQL Developer 簡介........................................................................................105
3.1.2 SQL Developer 下載........................................................................................105
3.1.3 創建資料庫連線..............................................................................................107
3.2 SQL Developer 基本操作.............................................................................................109
3.2.1 數據操作..........................................................................................................110
3.2.2 表的創建、修改..............................................................................................112
3.3 開發與調試...................................................................................................................116
3.3.1 創建存儲過程..................................................................................................116
3.3.2 運行存儲過程..................................................................................................119
3.3.3 調試存儲過程..................................................................................................120
3.4 導入與導出...................................................................................................................122
3.4.1 導出數據..........................................................................................................122
3.4.2 導入數據..........................................................................................................124
小結................................................................................................................127
練習................................................................................................................128
第4 章 SQL 套用基礎........................................................................................129
4.1 SQL 語言概述...............................................................................................................131
4.1.1 SQL 語言特點..................................................................................................131
4.1.2 SQL 分類..........................................................................................................132
4.1.3 SQL 語句編寫規則..........................................................................................132
4.2 數據查詢.......................................................................................................................133
4.2.1 列查詢..............................................................................................................134
4.2.2 條件查詢..........................................................................................................135
4.2.3 算術運算符......................................................................................................136
4.2.4 排序..................................................................................................................137
4.2.5 聯合查詢..........................................................................................................137
4.3 多表查詢.......................................................................................................................139
4.3.1 內連線..............................................................................................................139
4.3.2 外連線..............................................................................................................141
4.4 子查詢...........................................................................................................................143
4.4.1 單行和多行子查詢..........................................................................................145
4.4.2 多列子查詢......................................................................................................146
4.4.3 相關子查詢......................................................................................................147
4.5 系統函式.......................................................................................................................148
4.5.1 單行函式..........................................................................................................148
4.5.2 數據分組..........................................................................................................165
4.6 數據操作.......................................................................................................................170
4.6.1 插入數據..........................................................................................................170
4.6.2 更新數據..........................................................................................................173
4.6.3 刪除數據..........................................................................................................175
4.7 DML 事務操作.............................................................................................................176
小結..................................................................................................................181
練習...................................................................................................................181
第5 章 數據表對象.......................................................................................................184
5.1 表..................................................................................................................................186
5.1.1 創建表..............................................................................................................186
5.1.2 修改表..............................................................................................................190
5.1.3 刪除表..............................................................................................................191
5.2 索引...............................................................................................................................192
5.2.1 創建索引..........................................................................................................193
5.2.2 維護索引..........................................................................................................194
5.2.3 索引使用原則..................................................................................................195
5.3 約束...............................................................................................................................196
5.3.1 創建約束..........................................................................................................196
5.3.2 維護約束..........................................................................................................201
小結..............................................................................................................203
練習................................................................................................................203
第6 章 視圖與序列..............................................................................................206
6.1 視圖概述.......................................................................................................................208
6.2 建立視圖.......................................................................................................................210
6.2.1 簡單視圖..........................................................................................................210
6.2.2 複雜視圖..........................................................................................................211
6.2.3 連線視圖..........................................................................................................212
6.2.4 唯讀視圖..........................................................................................................213
6.2.5 CHECK 約束....................................................................................................213
6.3 維護視圖.......................................................................................................................214
6.3.1 修改視圖..........................................................................................................214
6.3.2 重新編譯視圖..................................................................................................214
6.3.3 刪除視圖..........................................................................................................215
6.4 序列...............................................................................................................................215
6.4.1 序列概述..........................................................................................................215
6.4.2 使用序列..........................................................................................................216
6.4.3 維護序列..........................................................................................................217
小結.........................................................................................................218
練習........................................................................................................218
第7 章 PL/SQL 基礎.....................................................................................220
7.1 PL/SQL 簡介.................................................................................................................222
7.1.1 PL/SQL 概述....................................................................................................222
7.1.2 PL/SQL 結構....................................................................................................222
7.1.3 PL/SQL 塊分類................................................................................................225
7.1.4 注釋..................................................................................................................226
7.2 基本數據類型...............................................................................................................227
7.2.1 標量變數..........................................................................................................227
7.2.2 使用%TYPE.....................................................................................................230
7.3 記錄類型.......................................................................................................................230
7.3.1 自定義PL/SQL 記錄類型...............................................................................231
7.3.2 %ROWTYPE 記錄類型...................................................................................232
7.4 集合類型.......................................................................................................................233
7.4.1 PL/SQL 一維表................................................................................................233
7.4.2 PL/SQL 多維表................................................................................................235
7.4.3 嵌套表..............................................................................................................235
7.4.4 可變數組..........................................................................................................236
7.4.5 集合方法..........................................................................................................237
7.5 運算符與表達式...........................................................................................................240
7.5.1 運算符..............................................................................................................240
7.5.2 表達式..............................................................................................................241
7.6 流程控制語句...............................................................................................................242
7.6.1 條件分支語句..................................................................................................242
7.6.2 CASE 語句.......................................................................................................245
7.6.3 循環語句..........................................................................................................246
7.6.4 GOTO 語句......................................................................................................248
7.7 異常處理.......................................................................................................................249
7.7.1 異常簡介..........................................................................................................249
7.7.2 異常處理..........................................................................................................250
小結.............................................................................................................254
練習..............................................................................................................254
第8 章 PL/SQL 進階.............................................................................................256
8.1 游標...............................................................................................................................258
8.1.1 顯式游標..........................................................................................................258
8.1.2 使用游標..........................................................................................................260
8.1.3 游標FOR 循環................................................................................................263
8.1.4 游標修改和刪除操作......................................................................................264
8.2 游標變數.......................................................................................................................266
8.2.1 游標變數定義..................................................................................................266
8.2.2 游標變數操作..................................................................................................268
8.3 存儲過程.......................................................................................................................269
8.3.1 存儲過程概述..................................................................................................269
8.3.2 無參存儲過程..................................................................................................270
8.3.3 帶輸入參數的存儲過程...................................................................................271
8.3.4 帶輸出參數的存儲過程...................................................................................274
8.3.5 帶輸入/輸出參數的存儲過程.........................................................................277
8.3.6 維護存儲過程..................................................................................................278
8.4 自定義函式...................................................................................................................278
8.4.1 函式概述..........................................................................................................278
8.4.2 調用函式..........................................................................................................280
8.4.3 函式返回類型..................................................................................................280
8.4.4 維護函式..........................................................................................................283
8.5 包..................................................................................................................................283
8.5.1 包概述..............................................................................................................284
8.5.2 調用包..............................................................................................................285
8.5.3 使用集合類型..................................................................................................288
8.5.4 重載..................................................................................................................289
8.5.5 構造過程..........................................................................................................290
8.5.6 維護包..............................................................................................................291
8.6 觸發器...........................................................................................................................292
8.6.1 語句觸發器......................................................................................................293
8.6.2 行觸發器..........................................................................................................294
8.6.3 維護觸發器......................................................................................................299
小結..............................................................................................................300
練習....................................................................................................................301
第9 章 事務管理與並發控制...........................................................................305
9.1 事務的概念...................................................................................................................307
9.1.1 事務概述..........................................................................................................307
9.1.2 事務的狀態......................................................................................................307
9.1.3 事務的特性與套用...........................................................................................308
9.2 事務控制.......................................................................................................................310
9.2.1 搭建事務演示環境...........................................................................................310
9.2.2 事務的開始與結束...........................................................................................312
9.2.3 事務的提交......................................................................................................313
9.2.4 事務的回滾與保存點.......................................................................................314
9.2.5 事務隔離級別..................................................................................................316
9.3 並發的概念...................................................................................................................318
9.3.1 並發概述..........................................................................................................319
9.3.2 鎖、鎖定和鎖定協定......................................................................................320
9.3.3 鎖的粒度..........................................................................................................325
9.4 鎖在Oracle 中的套用...................................................................................................326
9.4.1 Oracle 中鎖的分類...........................................................................................326
9.4.2 Oracle 中鎖的級別...........................................................................................329
9.4.3 Oracle 中的鎖爭用的檢測與解決...................................................................331
小結...............................................................................................................336
練習....................................................................................................................337
第10 章 用戶管理與安全......................................................................................339
10.1 安全性概述.................................................................................................................341
10.1.1 計算機安全性概述........................................................................................341
10.1.2 資料庫安全性控制........................................................................................343
10.2 Oracle 中的用戶管理..................................................................................................346
10.2.1 資料庫系統用戶概述....................................................................................346
10.2.2 Oracle 用戶概述.............................................................................................347
10.2.3 Oracle 用戶管理.............................................................................................348
10.3 Oracle 中的許可權管理..................................................................................................353
10.3.1 Oracle 系統許可權.............................................................................................353
10.3.2 Oracle 對象許可權.............................................................................................357
10.4 Oracle 中的角色管理..................................................................................................358
10.4.1 角色概述........................................................................................................358
10.4.2 Oracle 中的角色概述.....................................................................................359
10.4.3 Oracle 中的角色管理與使用.........................................................................360
小結.........................................................................................................363
練習..............................................................................................................363
第11 章 Oracle 資料庫備份與恢復...............................................................365
11.1 資料庫恢復與備份.....................................................................................................367
11.1.1 資料庫恢復概述............................................................................................367
11.1.2 故障種類及恢復策略....................................................................................369
11.1.3 Oracle 資料庫的備份與恢復.........................................................................371
11.2 Oracle 閃回技術..........................................................................................................374
11.2.1 閃回查詢........................................................................................................376
11.2.2 閃回版本查詢.................................................................................................381
11.2.3 閃回事務查詢.................................................................................................383
小結..........................................................................................................................385
練習.......................................................................................................................386
實踐篇.....................................................................................................................387
實踐1 Oracle 11g 資料庫系統..............................................................................388
實踐指導..................................................................................................................388
實踐1.G.1.....................................................................................................................388
實踐1.G.2.....................................................................................................................402
實踐1.G.3.....................................................................................................................403
知識拓展...........................................................................................................405
資料庫配置助手（DBCA） .......................................................................................405
拓展練習...............................................................................................................410
練習1.E.1.....................................................................................................................410
練習1.E.2.....................................................................................................................411
練習1.E.3.....................................................................................................................411
實踐2 資料庫表對象...................................................................................................412
實踐指導.................................................................................................................412
實踐2.G.1.....................................................................................................................412
實踐2.G.2.....................................................................................................................414
實踐2.G.3.....................................................................................................................416
實踐2.G.4.....................................................................................................................419
實踐2.G.5.....................................................................................................................421
實踐2.G.6.....................................................................................................................422
知識拓展...........................................................................................................426
1. 表信息.....................................................................................................................426
2. 索引信息.................................................................................................................429
3. 約束信息.................................................................................................................429
4. 多表插入.................................................................................................................430
拓展練習...............................................................................................................432
練習2.E.1.....................................................................................................................432
練習2.E.2.....................................................................................................................432
實踐3 視圖與序列.......................................................................................................433
實踐指導..............................................................................................................433
實踐3.G.1.....................................................................................................................433
實踐3.G.2.....................................................................................................................435
實踐3.G.3.....................................................................................................................438
知識拓展.............................................................................................................440
視圖信息........................................................................................................440
拓展練習............................................................................................................441
練習3.E.1.....................................................................................................................441
練習3.E.2.....................................................................................................................441
練習3.E.3.....................................................................................................................441
實踐4 PL/SQL 基礎................................................................................................442
實踐指導.....................................................................................................442
實踐4.G.1.....................................................................................................................442
實踐4.G.2.....................................................................................................................444
知識拓展..........................................................................................................446
1. PL/SQL 代碼編寫規則...........................................................................................446
2. LOB 類型................................................................................................................447
3. 集合運算.................................................................................................................448
4. 批量操作.................................................................................................................450
5. 異常處理函式.........................................................................................................453
6. RAISE_APPLICATION_ERROR...........................................................................454
拓展練習.......................................................................................................455
練習4.E.1.....................................................................................................................455
實踐5 PL/SQL 進階....................................................................................456
實踐指導...................................................................................................456
實踐5.G.1.....................................................................................................................456
實踐5.G.2.....................................................................................................................457
實踐5.G.3.....................................................................................................................461
實踐5.G.4.....................................................................................................................465
實踐5.G.5.....................................................................................................................469
實踐5.G.6.....................................................................................................................471
知識拓展...............................................................................................................................472
1. 批量提取.................................................................................................................472
2. 包的純度級別.........................................................................................................474
3. 系統工具包.............................................................................................................475
4. INSTEAD OF 觸發器.............................................................................................477
5. 系統事件觸發器.....................................................................................................478
拓展練習........................................................................................................482
練習5.E.1.....................................................................................................................482
練習5.E.2.....................................................................................................................482
練習5.E.3.....................................................................................................................482
練習5.E.4.....................................................................................................................482
練習5.E.5.....................................................................................................................482
練習5.E.6.....................................................................................................................482
實踐6 Oracle 資料庫備份與恢復.........................................................................483
實踐指導..........................................................................................................483
實踐6.G.1.....................................................................................................................483
實踐6.G.2.....................................................................................................................488
知識拓展........................................................................................................491
系統許可權分類..............................................................................................................491
拓展練習....................................................................................................495
練習6.E.1.....................................................................................................................495
練習6.E.2.....................................................................................................................495
附錄A 常用SQL*Plus 命令...................................................................................496
附錄B Oracle 數據隱式轉換規則........................................................................505
第1 章 關係型資料庫.........................................................................................................2
1.1 資料庫概述.......................................................................................................................4
1.1.1 資料庫基本概念..................................................................................................4
1.1.2 資料庫發展史......................................................................................................6
1.1.3 資料庫特徵..........................................................................................................9
1.1.4 常見資料庫..........................................................................................................9
1.2 數據模型......................................................................................................................... 11
1.2.1 概念模型............................................................................................................ 11
1.2.2 邏輯數據模型....................................................................................................14
1.3 關係模型理論.................................................................................................................18
1.3.1 關係....................................................................................................................20
1.3.2 關係模式............................................................................................................23
1.3.3 關係的完整性....................................................................................................24
1.3.4 關係代數............................................................................................................26
1.4 規範化理論.....................................................................................................................34
1.4.1 規範化理論的提出............................................................................................34
1.4.2 函式依賴............................................................................................................37
1.4.3 函式依賴定義的碼............................................................................................40
1.4.4 範式及各範式的關係........................................................................................41
1.4.5 多值依賴............................................................................................................50
1.4.6 規範化思想........................................................................................................51
1.5 資料庫設計.....................................................................................................................52
1.5.1 資料庫設計特點................................................................................................53
1.5.2 需求分析............................................................................................................55
1.5.3 數據流圖和數據字典........................................................................................57
1.6 概念結構設計.................................................................................................................58
1.6.1 概念結構設計定義............................................................................................59
1.6.2 概念結構設計的方法和步驟............................................................................60
1.6.3 數據抽象............................................................................................................61
1.6.4 E-R 圖的生成.....................................................................................................62
1.7 邏輯結構設計.................................................................................................................66
1.7.1 初始關係模式設計.............................................................................................66
1.7.2 數據模型最佳化的方法.........................................................................................68
1.7.3 模式評價與改進................................................................................................68
小結....................................................................................................................70
練習..................................................................................................................70
第2 章 Oracle 資料庫........................................................................................76
2.1 Oracle 資料庫概述..........................................................................................................78
2.1.1 Oracle 資料庫簡介.............................................................................................78
2.1.2 Oracle 發展史.....................................................................................................79
2.1.3 Oracle 資料庫的套用結構.................................................................................81
2.2 Oracle 體系結構..............................................................................................................83
2.3 Oracle 資料庫存儲結構..................................................................................................84
2.3.1 物理存儲結構....................................................................................................85
2.3.2 邏輯存儲結構....................................................................................................88
2.4 Oracle 資料庫實例..........................................................................................................90
2.4.1 Oracle 實例概述.................................................................................................90
2.4.2 SGA ....................................................................................................................92
2.4.3 PGA ....................................................................................................................95
2.4.4 後台進程............................................................................................................97
小結..........................................................................................................101
練習.....................................................................................................................102
第3 章 SQL Developer 開發工具..............................................................................103
3.1 SQL Developer 概述....................................................................................................105
3.1.1 SQL Developer 簡介........................................................................................105
3.1.2 SQL Developer 下載........................................................................................105
3.1.3 創建資料庫連線..............................................................................................107
3.2 SQL Developer 基本操作.............................................................................................109
3.2.1 數據操作..........................................................................................................110
3.2.2 表的創建、修改..............................................................................................112
3.3 開發與調試...................................................................................................................116
3.3.1 創建存儲過程..................................................................................................116
3.3.2 運行存儲過程..................................................................................................119
3.3.3 調試存儲過程..................................................................................................120
3.4 導入與導出...................................................................................................................122
3.4.1 導出數據..........................................................................................................122
3.4.2 導入數據..........................................................................................................124
小結................................................................................................................127
練習................................................................................................................128
第4 章 SQL 套用基礎........................................................................................129
4.1 SQL 語言概述...............................................................................................................131
4.1.1 SQL 語言特點..................................................................................................131
4.1.2 SQL 分類..........................................................................................................132
4.1.3 SQL 語句編寫規則..........................................................................................132
4.2 數據查詢.......................................................................................................................133
4.2.1 列查詢..............................................................................................................134
4.2.2 條件查詢..........................................................................................................135
4.2.3 算術運算符......................................................................................................136
4.2.4 排序..................................................................................................................137
4.2.5 聯合查詢..........................................................................................................137
4.3 多表查詢.......................................................................................................................139
4.3.1 內連線..............................................................................................................139
4.3.2 外連線..............................................................................................................141
4.4 子查詢...........................................................................................................................143
4.4.1 單行和多行子查詢..........................................................................................145
4.4.2 多列子查詢......................................................................................................146
4.4.3 相關子查詢......................................................................................................147
4.5 系統函式.......................................................................................................................148
4.5.1 單行函式..........................................................................................................148
4.5.2 數據分組..........................................................................................................165
4.6 數據操作.......................................................................................................................170
4.6.1 插入數據..........................................................................................................170
4.6.2 更新數據..........................................................................................................173
4.6.3 刪除數據..........................................................................................................175
4.7 DML 事務操作.............................................................................................................176
小結..................................................................................................................181
練習...................................................................................................................181
第5 章 數據表對象.......................................................................................................184
5.1 表..................................................................................................................................186
5.1.1 創建表..............................................................................................................186
5.1.2 修改表..............................................................................................................190
5.1.3 刪除表..............................................................................................................191
5.2 索引...............................................................................................................................192
5.2.1 創建索引..........................................................................................................193
5.2.2 維護索引..........................................................................................................194
5.2.3 索引使用原則..................................................................................................195
5.3 約束...............................................................................................................................196
5.3.1 創建約束..........................................................................................................196
5.3.2 維護約束..........................................................................................................201
小結..............................................................................................................203
練習................................................................................................................203
第6 章 視圖與序列..............................................................................................206
6.1 視圖概述.......................................................................................................................208
6.2 建立視圖.......................................................................................................................210
6.2.1 簡單視圖..........................................................................................................210
6.2.2 複雜視圖..........................................................................................................211
6.2.3 連線視圖..........................................................................................................212
6.2.4 唯讀視圖..........................................................................................................213
6.2.5 CHECK 約束....................................................................................................213
6.3 維護視圖.......................................................................................................................214
6.3.1 修改視圖..........................................................................................................214
6.3.2 重新編譯視圖..................................................................................................214
6.3.3 刪除視圖..........................................................................................................215
6.4 序列...............................................................................................................................215
6.4.1 序列概述..........................................................................................................215
6.4.2 使用序列..........................................................................................................216
6.4.3 維護序列..........................................................................................................217
小結.........................................................................................................218
練習........................................................................................................218
第7 章 PL/SQL 基礎.....................................................................................220
7.1 PL/SQL 簡介.................................................................................................................222
7.1.1 PL/SQL 概述....................................................................................................222
7.1.2 PL/SQL 結構....................................................................................................222
7.1.3 PL/SQL 塊分類................................................................................................225
7.1.4 注釋..................................................................................................................226
7.2 基本數據類型...............................................................................................................227
7.2.1 標量變數..........................................................................................................227
7.2.2 使用%TYPE.....................................................................................................230
7.3 記錄類型.......................................................................................................................230
7.3.1 自定義PL/SQL 記錄類型...............................................................................231
7.3.2 %ROWTYPE 記錄類型...................................................................................232
7.4 集合類型.......................................................................................................................233
7.4.1 PL/SQL 一維表................................................................................................233
7.4.2 PL/SQL 多維表................................................................................................235
7.4.3 嵌套表..............................................................................................................235
7.4.4 可變數組..........................................................................................................236
7.4.5 集合方法..........................................................................................................237
7.5 運算符與表達式...........................................................................................................240
7.5.1 運算符..............................................................................................................240
7.5.2 表達式..............................................................................................................241
7.6 流程控制語句...............................................................................................................242
7.6.1 條件分支語句..................................................................................................242
7.6.2 CASE 語句.......................................................................................................245
7.6.3 循環語句..........................................................................................................246
7.6.4 GOTO 語句......................................................................................................248
7.7 異常處理.......................................................................................................................249
7.7.1 異常簡介..........................................................................................................249
7.7.2 異常處理..........................................................................................................250
小結.............................................................................................................254
練習..............................................................................................................254
第8 章 PL/SQL 進階.............................................................................................256
8.1 游標...............................................................................................................................258
8.1.1 顯式游標..........................................................................................................258
8.1.2 使用游標..........................................................................................................260
8.1.3 游標FOR 循環................................................................................................263
8.1.4 游標修改和刪除操作......................................................................................264
8.2 游標變數.......................................................................................................................266
8.2.1 游標變數定義..................................................................................................266
8.2.2 游標變數操作..................................................................................................268
8.3 存儲過程.......................................................................................................................269
8.3.1 存儲過程概述..................................................................................................269
8.3.2 無參存儲過程..................................................................................................270
8.3.3 帶輸入參數的存儲過程...................................................................................271
8.3.4 帶輸出參數的存儲過程...................................................................................274
8.3.5 帶輸入/輸出參數的存儲過程.........................................................................277
8.3.6 維護存儲過程..................................................................................................278
8.4 自定義函式...................................................................................................................278
8.4.1 函式概述..........................................................................................................278
8.4.2 調用函式..........................................................................................................280
8.4.3 函式返回類型..................................................................................................280
8.4.4 維護函式..........................................................................................................283
8.5 包..................................................................................................................................283
8.5.1 包概述..............................................................................................................284
8.5.2 調用包..............................................................................................................285
8.5.3 使用集合類型..................................................................................................288
8.5.4 重載..................................................................................................................289
8.5.5 構造過程..........................................................................................................290
8.5.6 維護包..............................................................................................................291
8.6 觸發器...........................................................................................................................292
8.6.1 語句觸發器......................................................................................................293
8.6.2 行觸發器..........................................................................................................294
8.6.3 維護觸發器......................................................................................................299
小結..............................................................................................................300
練習....................................................................................................................301
第9 章 事務管理與並發控制...........................................................................305
9.1 事務的概念...................................................................................................................307
9.1.1 事務概述..........................................................................................................307
9.1.2 事務的狀態......................................................................................................307
9.1.3 事務的特性與套用...........................................................................................308
9.2 事務控制.......................................................................................................................310
9.2.1 搭建事務演示環境...........................................................................................310
9.2.2 事務的開始與結束...........................................................................................312
9.2.3 事務的提交......................................................................................................313
9.2.4 事務的回滾與保存點.......................................................................................314
9.2.5 事務隔離級別..................................................................................................316
9.3 並發的概念...................................................................................................................318
9.3.1 並發概述..........................................................................................................319
9.3.2 鎖、鎖定和鎖定協定......................................................................................320
9.3.3 鎖的粒度..........................................................................................................325
9.4 鎖在Oracle 中的套用...................................................................................................326
9.4.1 Oracle 中鎖的分類...........................................................................................326
9.4.2 Oracle 中鎖的級別...........................................................................................329
9.4.3 Oracle 中的鎖爭用的檢測與解決...................................................................331
小結...............................................................................................................336
練習....................................................................................................................337
第10 章 用戶管理與安全......................................................................................339
10.1 安全性概述.................................................................................................................341
10.1.1 計算機安全性概述........................................................................................341
10.1.2 資料庫安全性控制........................................................................................343
10.2 Oracle 中的用戶管理..................................................................................................346
10.2.1 資料庫系統用戶概述....................................................................................346
10.2.2 Oracle 用戶概述.............................................................................................347
10.2.3 Oracle 用戶管理.............................................................................................348
10.3 Oracle 中的許可權管理..................................................................................................353
10.3.1 Oracle 系統許可權.............................................................................................353
10.3.2 Oracle 對象許可權.............................................................................................357
10.4 Oracle 中的角色管理..................................................................................................358
10.4.1 角色概述........................................................................................................358
10.4.2 Oracle 中的角色概述.....................................................................................359
10.4.3 Oracle 中的角色管理與使用.........................................................................360
小結.........................................................................................................363
練習..............................................................................................................363
第11 章 Oracle 資料庫備份與恢復...............................................................365
11.1 資料庫恢復與備份.....................................................................................................367
11.1.1 資料庫恢復概述............................................................................................367
11.1.2 故障種類及恢復策略....................................................................................369
11.1.3 Oracle 資料庫的備份與恢復.........................................................................371
11.2 Oracle 閃回技術..........................................................................................................374
11.2.1 閃回查詢........................................................................................................376
11.2.2 閃回版本查詢.................................................................................................381
11.2.3 閃回事務查詢.................................................................................................383
小結..........................................................................................................................385
練習.......................................................................................................................386
實踐篇.....................................................................................................................387
實踐1 Oracle 11g 資料庫系統..............................................................................388
實踐指導..................................................................................................................388
實踐1.G.1.....................................................................................................................388
實踐1.G.2.....................................................................................................................402
實踐1.G.3.....................................................................................................................403
知識拓展...........................................................................................................405
資料庫配置助手（DBCA） .......................................................................................405
拓展練習...............................................................................................................410
練習1.E.1.....................................................................................................................410
練習1.E.2.....................................................................................................................411
練習1.E.3.....................................................................................................................411
實踐2 資料庫表對象...................................................................................................412
實踐指導.................................................................................................................412
實踐2.G.1.....................................................................................................................412
實踐2.G.2.....................................................................................................................414
實踐2.G.3.....................................................................................................................416
實踐2.G.4.....................................................................................................................419
實踐2.G.5.....................................................................................................................421
實踐2.G.6.....................................................................................................................422
知識拓展...........................................................................................................426
1. 表信息.....................................................................................................................426
2. 索引信息.................................................................................................................429
3. 約束信息.................................................................................................................429
4. 多表插入.................................................................................................................430
拓展練習...............................................................................................................432
練習2.E.1.....................................................................................................................432
練習2.E.2.....................................................................................................................432
實踐3 視圖與序列.......................................................................................................433
實踐指導..............................................................................................................433
實踐3.G.1.....................................................................................................................433
實踐3.G.2.....................................................................................................................435
實踐3.G.3.....................................................................................................................438
知識拓展.............................................................................................................440
視圖信息........................................................................................................440
拓展練習............................................................................................................441
練習3.E.1.....................................................................................................................441
練習3.E.2.....................................................................................................................441
練習3.E.3.....................................................................................................................441
實踐4 PL/SQL 基礎................................................................................................442
實踐指導.....................................................................................................442
實踐4.G.1.....................................................................................................................442
實踐4.G.2.....................................................................................................................444
知識拓展..........................................................................................................446
1. PL/SQL 代碼編寫規則...........................................................................................446
2. LOB 類型................................................................................................................447
3. 集合運算.................................................................................................................448
4. 批量操作.................................................................................................................450
5. 異常處理函式.........................................................................................................453
6. RAISE_APPLICATION_ERROR...........................................................................454
拓展練習.......................................................................................................455
練習4.E.1.....................................................................................................................455
實踐5 PL/SQL 進階....................................................................................456
實踐指導...................................................................................................456
實踐5.G.1.....................................................................................................................456
實踐5.G.2.....................................................................................................................457
實踐5.G.3.....................................................................................................................461
實踐5.G.4.....................................................................................................................465
實踐5.G.5.....................................................................................................................469
實踐5.G.6.....................................................................................................................471
知識拓展...............................................................................................................................472
1. 批量提取.................................................................................................................472
2. 包的純度級別.........................................................................................................474
3. 系統工具包.............................................................................................................475
4. INSTEAD OF 觸發器.............................................................................................477
5. 系統事件觸發器.....................................................................................................478
拓展練習........................................................................................................482
練習5.E.1.....................................................................................................................482
練習5.E.2.....................................................................................................................482
練習5.E.3.....................................................................................................................482
練習5.E.4.....................................................................................................................482
練習5.E.5.....................................................................................................................482
練習5.E.6.....................................................................................................................482
實踐6 Oracle 資料庫備份與恢復.........................................................................483
實踐指導..........................................................................................................483
實踐6.G.1.....................................................................................................................483
實踐6.G.2.....................................................................................................................488
知識拓展........................................................................................................491
系統許可權分類..............................................................................................................491
拓展練習....................................................................................................495
練習6.E.1.....................................................................................................................495
練習6.E.2.....................................................................................................................495
附錄A 常用SQL*Plus 命令...................................................................................496
附錄B Oracle 數據隱式轉換規則........................................................................505
