Oracle應(yīng)用開發(fā)

內(nèi)容概要

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

書籍目錄

理論篇...............................................................................................................................1
第1 章 關(guān)系型數(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 關(guān)系模型理論.................................................................................................................18
1.3.1 關(guān)系....................................................................................................................20
1.3.2 關(guān)系模式............................................................................................................23
1.3.3 關(guān)系的完整性....................................................................................................24
1.3.4 關(guān)系代數(shù)............................................................................................................26
1.4 規(guī)范化理論.....................................................................................................................34
1.4.1 規(guī)范化理論的提出............................................................................................34
1.4.2 函數(shù)依賴............................................................................................................37
1.4.3 函數(shù)依賴定義的碼............................................................................................40
1.4.4 范式及各范式的關(guān)系........................................................................................41
1.4.5 多值依賴............................................................................................................50
1.4.6 規(guī)范化思想........................................................................................................51
1.5 數(shù)據(jù)庫設(shè)計(jì).....................................................................................................................52
1.5.1 數(shù)據(jù)庫設(shè)計(jì)特點(diǎn)................................................................................................53
1.5.2 需求分析............................................................................................................55
1.5.3 數(shù)據(jù)流圖和數(shù)據(jù)字典........................................................................................57
1.6 概念結(jié)構(gòu)設(shè)計(jì).................................................................................................................58
1.6.1 概念結(jié)構(gòu)設(shè)計(jì)定義............................................................................................59
1.6.2 概念結(jié)構(gòu)設(shè)計(jì)的方法和步驟............................................................................60
1.6.3 數(shù)據(jù)抽象............................................................................................................61
1.6.4 E-R 圖的生成.....................................................................................................62
1.7 邏輯結(jié)構(gòu)設(shè)計(jì).................................................................................................................66
1.7.1 初始關(guān)系模式設(shè)計(jì).............................................................................................66
1.7.2 數(shù)據(jù)模型優(yōu)化的方法.........................................................................................68
1.7.3 模式評價(jià)與改進(jìn)................................................................................................68
小結(jié)....................................................................................................................70
練習(xí)..................................................................................................................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ù)庫的應(yīng)用結(jié)構(gòu).................................................................................81
2.2 Oracle 體系結(jié)構(gòu)..............................................................................................................83
2.3 Oracle 數(shù)據(jù)庫存儲結(jié)構(gòu)..................................................................................................84
2.3.1 物理存儲結(jié)構(gòu)....................................................................................................85
2.3.2 邏輯存儲結(jié)構(gòu)....................................................................................................88
2.4 Oracle 數(shù)據(jù)庫實(shí)例..........................................................................................................90
2.4.1 Oracle 實(shí)例概述.................................................................................................90
2.4.2 SGA ....................................................................................................................92
2.4.3 PGA ....................................................................................................................95
2.4.4 后臺進(jìn)程............................................................................................................97
小結(jié)..........................................................................................................101
練習(xí).....................................................................................................................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ā)與調(diào)試...................................................................................................................116
3.3.1 創(chuàng)建存儲過程..................................................................................................116
3.3.2 運(yùn)行存儲過程..................................................................................................119
3.3.3 調(diào)試存儲過程..................................................................................................120
3.4 導(dǎo)入與導(dǎo)出...................................................................................................................122
3.4.1 導(dǎo)出數(shù)據(jù)..........................................................................................................122
3.4.2 導(dǎo)入數(shù)據(jù)..........................................................................................................124
小結(jié)................................................................................................................127
練習(xí)................................................................................................................128
第4 章 SQL 應(yīng)用基礎(chǔ)........................................................................................129
4.1 SQL 語言概述...............................................................................................................131
4.1.1 SQL 語言特點(diǎn)..................................................................................................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 算術(shù)運(yùn)算符......................................................................................................136
4.2.4 排序..................................................................................................................137
4.2.5 聯(lián)合查詢..........................................................................................................137
4.3 多表查詢.......................................................................................................................139
4.3.1 內(nèi)連接..............................................................................................................139
4.3.2 外連接..............................................................................................................141
4.4 子查詢...........................................................................................................................143
4.4.1 單行和多行子查詢..........................................................................................145
4.4.2 多列子查詢......................................................................................................146
4.4.3 相關(guān)子查詢......................................................................................................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 事務(wù)操作.............................................................................................................176
小結(jié)..................................................................................................................181
練習(xí)...................................................................................................................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 維護(hù)索引..........................................................................................................194
5.2.3 索引使用原則..................................................................................................195
5.3 約束...............................................................................................................................196
5.3.1 創(chuàng)建約束..........................................................................................................196
5.3.2 維護(hù)約束..........................................................................................................201
小結(jié)..............................................................................................................203
練習(xí)................................................................................................................203
第6 章 視圖與序列..............................................................................................206
6.1 視圖概述.......................................................................................................................208
6.2 建立視圖.......................................................................................................................210
6.2.1 簡單視圖..........................................................................................................210
6.2.2 復(fù)雜視圖..........................................................................................................211
6.2.3 連接視圖..........................................................................................................212
6.2.4 只讀視圖..........................................................................................................213
6.2.5 CHECK 約束....................................................................................................213
6.3 維護(hù)視圖.......................................................................................................................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 維護(hù)序列..........................................................................................................217
小結(jié).........................................................................................................218
練習(xí)........................................................................................................218
第7 章 PL/SQL 基礎(chǔ).....................................................................................220
7.1 PL/SQL 簡介.................................................................................................................222
7.1.1 PL/SQL 概述....................................................................................................222
7.1.2 PL/SQL 結(jié)構(gòu)....................................................................................................222
7.1.3 PL/SQL 塊分類................................................................................................225
7.1.4 注釋..................................................................................................................226
7.2 基本數(shù)據(jù)類型...............................................................................................................227
7.2.1 標(biāo)量變量..........................................................................................................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 運(yùn)算符與表達(dá)式...........................................................................................................240
7.5.1 運(yùn)算符..............................................................................................................240
7.5.2 表達(dá)式..............................................................................................................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
小結(jié).............................................................................................................254
練習(xí)..............................................................................................................254
第8 章 PL/SQL 進(jìn)階.............................................................................................256
8.1 游標(biāo)...............................................................................................................................258
8.1.1 顯式游標(biāo)..........................................................................................................258
8.1.2 使用游標(biāo)..........................................................................................................260
8.1.3 游標(biāo)FOR 循環(huán)................................................................................................263
8.1.4 游標(biāo)修改和刪除操作......................................................................................264
8.2 游標(biāo)變量.......................................................................................................................266
8.2.1 游標(biāo)變量定義..................................................................................................266
8.2.2 游標(biāo)變量操作..................................................................................................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 維護(hù)存儲過程..................................................................................................278
8.4 自定義函數(shù)...................................................................................................................278
8.4.1 函數(shù)概述..........................................................................................................278
8.4.2 調(diào)用函數(shù)..........................................................................................................280
8.4.3 函數(shù)返回類型..................................................................................................280
8.4.4 維護(hù)函數(shù)..........................................................................................................283
8.5 包..................................................................................................................................283
8.5.1 包概述..............................................................................................................284
8.5.2 調(diào)用包..............................................................................................................285
8.5.3 使用集合類型..................................................................................................288
8.5.4 重載..................................................................................................................289
8.5.5 構(gòu)造過程..........................................................................................................290
8.5.6 維護(hù)包..............................................................................................................291
8.6 觸發(fā)器...........................................................................................................................292
8.6.1 語句觸發(fā)器......................................................................................................293
8.6.2 行觸發(fā)器..........................................................................................................294
8.6.3 維護(hù)觸發(fā)器......................................................................................................299
小結(jié)..............................................................................................................300
練習(xí)....................................................................................................................301
第9 章 事務(wù)管理與并發(fā)控制...........................................................................305
9.1 事務(wù)的概念...................................................................................................................307
9.1.1 事務(wù)概述..........................................................................................................307
9.1.2 事務(wù)的狀態(tài)......................................................................................................307
9.1.3 事務(wù)的特性與應(yīng)用...........................................................................................308
9.2 事務(wù)控制.......................................................................................................................310
9.2.1 搭建事務(wù)演示環(huán)境...........................................................................................310
9.2.2 事務(wù)的開始與結(jié)束...........................................................................................312
9.2.3 事務(wù)的提交......................................................................................................313
9.2.4 事務(wù)的回滾與保存點(diǎn).......................................................................................314
9.2.5 事務(wù)隔離級別..................................................................................................316
9.3 并發(fā)的概念...................................................................................................................318
9.3.1 并發(fā)概述..........................................................................................................319
9.3.2 鎖、鎖定和鎖定協(xié)議......................................................................................320
9.3.3 鎖的粒度..........................................................................................................325
9.4 鎖在Oracle 中的應(yīng)用...................................................................................................326
9.4.1 Oracle 中鎖的分類...........................................................................................326
9.4.2 Oracle 中鎖的級別...........................................................................................329
9.4.3 Oracle 中的鎖爭用的檢測與解決...................................................................331
小結(jié)...............................................................................................................336
練習(xí)....................................................................................................................337
第10 章 用戶管理與安全......................................................................................339
10.1 安全性概述.................................................................................................................341
10.1.1 計(jì)算機(jī)安全性概述........................................................................................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 中的權(quán)限管理..................................................................................................353
10.3.1 Oracle 系統(tǒng)權(quán)限.............................................................................................353
10.3.2 Oracle 對象權(quán)限.............................................................................................357
10.4 Oracle 中的角色管理..................................................................................................358
10.4.1 角色概述........................................................................................................358
10.4.2 Oracle 中的角色概述.....................................................................................359
10.4.3 Oracle 中的角色管理與使用.........................................................................360
小結(jié).........................................................................................................363
練習(xí)..............................................................................................................363
第11 章 Oracle 數(shù)據(jù)庫備份與恢復(fù)...............................................................365
11.1 數(shù)據(jù)庫恢復(fù)與備份.....................................................................................................367
11.1.1 數(shù)據(jù)庫恢復(fù)概述............................................................................................367
11.1.2 故障種類及恢復(fù)策略....................................................................................369
11.1.3 Oracle 數(shù)據(jù)庫的備份與恢復(fù).........................................................................371
11.2 Oracle 閃回技術(shù)..........................................................................................................374
11.2.1 閃回查詢........................................................................................................376
11.2.2 閃回版本查詢.................................................................................................381
11.2.3 閃回事務(wù)查詢.................................................................................................383
小結(jié)..........................................................................................................................385
練習(xí).......................................................................................................................386
實(shí)踐篇.....................................................................................................................387
實(shí)踐1 Oracle 11g 數(shù)據(jù)庫系統(tǒng)..............................................................................388
實(shí)踐指導(dǎo)..................................................................................................................388
實(shí)踐1.G.1.....................................................................................................................388
實(shí)踐1.G.2.....................................................................................................................402
實(shí)踐1.G.3.....................................................................................................................403
知識拓展...........................................................................................................405
數(shù)據(jù)庫配置助手（DBCA） .......................................................................................405
拓展練習(xí)...............................................................................................................410
練習(xí)1.E.1.....................................................................................................................410
練習(xí)1.E.2.....................................................................................................................411
練習(xí)1.E.3.....................................................................................................................411
實(shí)踐2 數(shù)據(jù)庫表對象...................................................................................................412
實(shí)踐指導(dǎo).................................................................................................................412
實(shí)踐2.G.1.....................................................................................................................412
實(shí)踐2.G.2.....................................................................................................................414
實(shí)踐2.G.3.....................................................................................................................416
實(shí)踐2.G.4.....................................................................................................................419
實(shí)踐2.G.5.....................................................................................................................421
實(shí)踐2.G.6.....................................................................................................................422
知識拓展...........................................................................................................426
1. 表信息.....................................................................................................................426
2. 索引信息.................................................................................................................429
3. 約束信息.................................................................................................................429
4. 多表插入.................................................................................................................430
拓展練習(xí)...............................................................................................................432
練習(xí)2.E.1.....................................................................................................................432
練習(xí)2.E.2.....................................................................................................................432
實(shí)踐3 視圖與序列.......................................................................................................433
實(shí)踐指導(dǎo)..............................................................................................................433
實(shí)踐3.G.1.....................................................................................................................433
實(shí)踐3.G.2.....................................................................................................................435
實(shí)踐3.G.3.....................................................................................................................438
知識拓展.............................................................................................................440
視圖信息........................................................................................................440
拓展練習(xí)............................................................................................................441
練習(xí)3.E.1.....................................................................................................................441
練習(xí)3.E.2.....................................................................................................................441
練習(xí)3.E.3.....................................................................................................................441
實(shí)踐4 PL/SQL 基礎(chǔ)................................................................................................442
實(shí)踐指導(dǎo).....................................................................................................442
實(shí)踐4.G.1.....................................................................................................................442
實(shí)踐4.G.2.....................................................................................................................444
知識拓展..........................................................................................................446
1. PL/SQL 代碼編寫規(guī)則...........................................................................................446
2. LOB 類型................................................................................................................447
3. 集合運(yùn)算.................................................................................................................448
4. 批量操作.................................................................................................................450
5. 異常處理函數(shù).........................................................................................................453
6. RAISE_APPLICATION_ERROR...........................................................................454
拓展練習(xí).......................................................................................................455
練習(xí)4.E.1.....................................................................................................................455
實(shí)踐5 PL/SQL 進(jìn)階....................................................................................456
實(shí)踐指導(dǎo)...................................................................................................456
實(shí)踐5.G.1.....................................................................................................................456
實(shí)踐5.G.2.....................................................................................................................457
實(shí)踐5.G.3.....................................................................................................................461
實(shí)踐5.G.4.....................................................................................................................465
實(shí)踐5.G.5.....................................................................................................................469
實(shí)踐5.G.6.....................................................................................................................471
知識拓展...............................................................................................................................472
1. 批量提取.................................................................................................................472
2. 包的純度級別.........................................................................................................474
3. 系統(tǒng)工具包.............................................................................................................475
4. INSTEAD OF 觸發(fā)器.............................................................................................477
5. 系統(tǒng)事件觸發(fā)器.....................................................................................................478
拓展練習(xí)........................................................................................................482
練習(xí)5.E.1.....................................................................................................................482
練習(xí)5.E.2.....................................................................................................................482
練習(xí)5.E.3.....................................................................................................................482
練習(xí)5.E.4.....................................................................................................................482
練習(xí)5.E.5.....................................................................................................................482
練習(xí)5.E.6.....................................................................................................................482
實(shí)踐6 Oracle 數(shù)據(jù)庫備份與恢復(fù).........................................................................483
實(shí)踐指導(dǎo)..........................................................................................................483
實(shí)踐6.G.1.....................................................................................................................483
實(shí)踐6.G.2.....................................................................................................................488
知識拓展........................................................................................................491
系統(tǒng)權(quán)限分類..............................................................................................................491
拓展練習(xí)....................................................................................................495
練習(xí)6.E.1.....................................................................................................................495
練習(xí)6.E.2.....................................................................................................................495
附錄A 常用SQL*Plus 命令...................................................................................496
附錄B Oracle 數(shù)據(jù)隱式轉(zhuǎn)換規(guī)則........................................................................505

圖書封面

評論、評分、閱讀與下載

---

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

---