Oracle應用開發(fā)

內容概要

本書從數(shù)據(jù)庫的基本概念出發(fā)，以Oracle 11g 數(shù)據(jù)庫為背景詳細地介紹了數(shù)據(jù)庫的原理和數(shù)據(jù)庫系統(tǒng)的開發(fā)技術。理論篇共分為11 章，分別介紹了關系型數(shù)據(jù)庫的概念和理論、Oracle 數(shù)據(jù)庫體系結構、SQL Developer 工具的使用、SQL 語句、函數(shù)、表、數(shù)據(jù)維護、視圖、PL/SQL 語法、游標、函數(shù)異常、序列、觸發(fā)器、數(shù)據(jù)恢復、并發(fā)控制以及數(shù)據(jù)庫安全等內容。書中通過SQL Developer 和SQL *Plus 工具實現(xiàn)對Oracle的數(shù)據(jù)查詢，并詳細介紹如何通過SQL 語句實現(xiàn)表格、索引、約束、視圖、存儲過程、函數(shù)、觸發(fā)器、序列以及PL/SQL 的編寫。
本書重點突出、偏重應用，結合理論篇的實例和實踐篇對貫穿案例的講解、剖析及實現(xiàn)，使讀者能迅速理解并掌握知識，全面提高動手能力。
本書適應面廣，可作為本科計算機科學與技術、軟件外包專業(yè)、高職高專計算機軟件、計算機網(wǎng)絡、計算機信息管理、電子商務和經(jīng)濟管理等專業(yè)的程序設計課程的教材。

書籍目錄

理論篇...............................................................................................................................1
第1 章 關系型數(shù)據(jù)庫.........................................................................................................2
1.1 數(shù)據(jù)庫概述.......................................................................................................................4
1.1.1 數(shù)據(jù)庫基本概念..................................................................................................4
1.1.2 數(shù)據(jù)庫發(fā)展史......................................................................................................6
1.1.3 數(shù)據(jù)庫特征..........................................................................................................9
1.1.4 常見數(shù)據(jù)庫..........................................................................................................9
1.2 數(shù)據(jù)模型......................................................................................................................... 11
1.2.1 概念模型............................................................................................................ 11
1.2.2 邏輯數(shù)據(jù)模型....................................................................................................14
1.3 關系模型理論.................................................................................................................18
1.3.1 關系....................................................................................................................20
1.3.2 關系模式............................................................................................................23
1.3.3 關系的完整性....................................................................................................24
1.3.4 關系代數(shù)............................................................................................................26
1.4 規(guī)范化理論.....................................................................................................................34
1.4.1 規(guī)范化理論的提出............................................................................................34
1.4.2 函數(shù)依賴............................................................................................................37
1.4.3 函數(shù)依賴定義的碼............................................................................................40
1.4.4 范式及各范式的關系........................................................................................41
1.4.5 多值依賴............................................................................................................50
1.4.6 規(guī)范化思想........................................................................................................51
1.5 數(shù)據(jù)庫設計.....................................................................................................................52
1.5.1 數(shù)據(jù)庫設計特點................................................................................................53
1.5.2 需求分析............................................................................................................55
1.5.3 數(shù)據(jù)流圖和數(shù)據(jù)字典........................................................................................57
1.6 概念結構設計.................................................................................................................58
1.6.1 概念結構設計定義............................................................................................59
1.6.2 概念結構設計的方法和步驟............................................................................60
1.6.3 數(shù)據(jù)抽象............................................................................................................61
1.6.4 E-R 圖的生成.....................................................................................................62
1.7 邏輯結構設計.................................................................................................................66
1.7.1 初始關系模式設計.............................................................................................66
1.7.2 數(shù)據(jù)模型優(yōu)化的方法.........................................................................................68
1.7.3 模式評價與改進................................................................................................68
小結....................................................................................................................70
練習..................................................................................................................70
第2 章 Oracle 數(shù)據(jù)庫........................................................................................76
2.1 Oracle 數(shù)據(jù)庫概述..........................................................................................................78
2.1.1 Oracle 數(shù)據(jù)庫簡介.............................................................................................78
2.1.2 Oracle 發(fā)展史.....................................................................................................79
2.1.3 Oracle 數(shù)據(jù)庫的應用結構.................................................................................81
2.2 Oracle 體系結構..............................................................................................................83
2.3 Oracle 數(shù)據(jù)庫存儲結構..................................................................................................84
2.3.1 物理存儲結構....................................................................................................85
2.3.2 邏輯存儲結構....................................................................................................88
2.4 Oracle 數(shù)據(jù)庫實例..........................................................................................................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 開發(fā)工具..............................................................................103
3.1 SQL Developer 概述....................................................................................................105
3.1.1 SQL Developer 簡介........................................................................................105
3.1.2 SQL Developer 下載........................................................................................105
3.1.3 創(chuàng)建數(shù)據(jù)庫連接..............................................................................................107
3.2 SQL Developer 基本操作.............................................................................................109
3.2.1 數(shù)據(jù)操作..........................................................................................................110
3.2.2 表的創(chuàng)建、修改..............................................................................................112
3.3 開發(fā)與調試...................................................................................................................116
3.3.1 創(chuàng)建存儲過程..................................................................................................116
3.3.2 運行存儲過程..................................................................................................119
3.3.3 調試存儲過程..................................................................................................120
3.4 導入與導出...................................................................................................................122
3.4.1 導出數(shù)據(jù)..........................................................................................................122
3.4.2 導入數(shù)據(jù)..........................................................................................................124
小結................................................................................................................127
練習................................................................................................................128
第4 章 SQL 應用基礎........................................................................................129
4.1 SQL 語言概述...............................................................................................................131
4.1.1 SQL 語言特點..................................................................................................131
4.1.2 SQL 分類..........................................................................................................132
4.1.3 SQL 語句編寫規(guī)則..........................................................................................132
4.2 數(shù)據(jù)查詢.......................................................................................................................133
4.2.1 列查詢..............................................................................................................134
4.2.2 條件查詢..........................................................................................................135
4.2.3 算術運算符......................................................................................................136
4.2.4 排序..................................................................................................................137
4.2.5 聯(lián)合查詢..........................................................................................................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 系統(tǒng)函數(shù).......................................................................................................................148
4.5.1 單行函數(shù)..........................................................................................................148
4.5.2 數(shù)據(jù)分組..........................................................................................................165
4.6 數(shù)據(jù)操作.......................................................................................................................170
4.6.1 插入數(shù)據(jù)..........................................................................................................170
4.6.2 更新數(shù)據(jù)..........................................................................................................173
4.6.3 刪除數(shù)據(jù)..........................................................................................................175
4.7 DML 事務操作.............................................................................................................176
小結..................................................................................................................181
練習...................................................................................................................181
第5 章 數(shù)據(jù)表對象.......................................................................................................184
5.1 表..................................................................................................................................186
5.1.1 創(chuàng)建表..............................................................................................................186
5.1.2 修改表..............................................................................................................190
5.1.3 刪除表..............................................................................................................191
5.2 索引...............................................................................................................................192
5.2.1 創(chuàng)建索引..........................................................................................................193
5.2.2 維護索引..........................................................................................................194
5.2.3 索引使用原則..................................................................................................195
5.3 約束...............................................................................................................................196
5.3.1 創(chuàng)建約束..........................................................................................................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 基本數(shù)據(jù)類型...............................................................................................................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 可變數(shù)組..........................................................................................................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 循環(huán)語句..........................................................................................................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 循環(huán)................................................................................................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 帶輸入?yún)?shù)的存儲過程...................................................................................271
8.3.4 帶輸出參數(shù)的存儲過程...................................................................................274
8.3.5 帶輸入/輸出參數(shù)的存儲過程.........................................................................277
8.3.6 維護存儲過程..................................................................................................278
8.4 自定義函數(shù)...................................................................................................................278
8.4.1 函數(shù)概述..........................................................................................................278
8.4.2 調用函數(shù)..........................................................................................................280
8.4.3 函數(shù)返回類型..................................................................................................280
8.4.4 維護函數(shù)..........................................................................................................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 觸發(fā)器...........................................................................................................................292
8.6.1 語句觸發(fā)器......................................................................................................293
8.6.2 行觸發(fā)器..........................................................................................................294
8.6.3 維護觸發(fā)器......................................................................................................299
小結..............................................................................................................300
練習....................................................................................................................301
第9 章 事務管理與并發(fā)控制...........................................................................305
9.1 事務的概念...................................................................................................................307
9.1.1 事務概述..........................................................................................................307
9.1.2 事務的狀態(tài)......................................................................................................307
9.1.3 事務的特性與應用...........................................................................................308
9.2 事務控制.......................................................................................................................310
9.2.1 搭建事務演示環(huán)境...........................................................................................310
9.2.2 事務的開始與結束...........................................................................................312
9.2.3 事務的提交......................................................................................................313
9.2.4 事務的回滾與保存點.......................................................................................314
9.2.5 事務隔離級別..................................................................................................316
9.3 并發(fā)的概念...................................................................................................................318
9.3.1 并發(fā)概述..........................................................................................................319
9.3.2 鎖、鎖定和鎖定協(xié)議......................................................................................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 數(shù)據(jù)庫安全性控制........................................................................................343
10.2 Oracle 中的用戶管理..................................................................................................346
10.2.1 數(shù)據(jù)庫系統(tǒng)用戶概述....................................................................................346
10.2.2 Oracle 用戶概述.............................................................................................347
10.2.3 Oracle 用戶管理.............................................................................................348
10.3 Oracle 中的權限管理..................................................................................................353
10.3.1 Oracle 系統(tǒng)權限.............................................................................................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 數(shù)據(jù)庫備份與恢復...............................................................365
11.1 數(shù)據(jù)庫恢復與備份.....................................................................................................367
11.1.1 數(shù)據(jù)庫恢復概述............................................................................................367
11.1.2 故障種類及恢復策略....................................................................................369
11.1.3 Oracle 數(shù)據(jù)庫的備份與恢復.........................................................................371
11.2 Oracle 閃回技術..........................................................................................................374
11.2.1 閃回查詢........................................................................................................376
11.2.2 閃回版本查詢.................................................................................................381
11.2.3 閃回事務查詢.................................................................................................383
小結..........................................................................................................................385
練習.......................................................................................................................386
實踐篇.....................................................................................................................387
實踐1 Oracle 11g 數(shù)據(jù)庫系統(tǒng)..............................................................................388
實踐指導..................................................................................................................388
實踐1.G.1.....................................................................................................................388
實踐1.G.2.....................................................................................................................402
實踐1.G.3.....................................................................................................................403
知識拓展...........................................................................................................405
數(shù)據(jù)庫配置助手（DBCA） .......................................................................................405
拓展練習...............................................................................................................410
練習1.E.1.....................................................................................................................410
練習1.E.2.....................................................................................................................411
練習1.E.3.....................................................................................................................411
實踐2 數(shù)據(jù)庫表對象...................................................................................................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 代碼編寫規(guī)則...........................................................................................446
2. LOB 類型................................................................................................................447
3. 集合運算.................................................................................................................448
4. 批量操作.................................................................................................................450
5. 異常處理函數(shù).........................................................................................................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. 系統(tǒng)工具包.............................................................................................................475
4. INSTEAD OF 觸發(fā)器.............................................................................................477
5. 系統(tǒng)事件觸發(fā)器.....................................................................................................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 數(shù)據(jù)庫備份與恢復.........................................................................483
實踐指導..........................................................................................................483
實踐6.G.1.....................................................................................................................483
實踐6.G.2.....................................................................................................................488
知識拓展........................................................................................................491
系統(tǒng)權限分類..............................................................................................................491
拓展練習....................................................................................................495
練習6.E.1.....................................................................................................................495
練習6.E.2.....................................................................................................................495
附錄A 常用SQL*Plus 命令...................................................................................496
附錄B Oracle 數(shù)據(jù)隱式轉換規(guī)則........................................................................505

圖書封面

評論、評分、閱讀與下載

---

    Oracle應用開發(fā) PDF格式下載

---