Orac1e轆庫(kù)系統(tǒng)

教案

目錄

目錄

專(zhuān)題-Oracle入

門(mén)................................................................................

......................................1

1.教學(xué)目

標(biāo)..................................................................................

..................................1

2.工作任

務(wù)..................................................................................

..................................1

3.相關(guān)實(shí)踐知

識(shí)..................................................................................

...........................1

3.1Oracle10g的安裝和卸

?..................................................

3.2用NetConfigurationAssistant配置客戶端效勞

名.............................................10

3.3啟動(dòng)Oracle效

勞.......................................................................................

..............16

3.4認(rèn)識(shí)Oracle常用工

具........................................................................................

.......18

3.5創(chuàng)立數(shù)據(jù)庫(kù)和表空

問(wèn)..........................................................................................

......24

3.6Oracle中的用戶管

理........................................................................................

.........32

4.實(shí)

驗(yàn)..........................................................................................

.......................................34

5.課后作

業(yè)..........................................................................................

................................35

專(zhuān)題二鎖和表分

區(qū)..........................................................................................

...................................36

1.教學(xué)目

標(biāo)..........................................................................................

................................36

2.工作任

務(wù)..........................................................................................

................................36

3.相關(guān)實(shí)踐知

識(shí)..........................................................................................

.........................36

3.1使用行級(jí)鎖和時(shí)間戳來(lái)保證數(shù)據(jù)完整

性...............................................................36

3.2使用表級(jí)

鎖..........................................................................................

....................40

3.3使用表分

區(qū)..........................................................................................

....................43

4.提

高..........................................................................................

.......................................49

5.實(shí)

驗(yàn)..........................................................................................

.......................................50

6.課后作

業(yè)..........................................................................................

............................................................................50

專(zhuān)題三SQL語(yǔ)句和SQL函

數(shù)..........................................................................................

.............................................52

教學(xué)目

標(biāo)..........................................................................................

......................................................................................52

案例一Oracle數(shù)據(jù)庫(kù)中的DDL、

DML和DCI.........................................................................................................................................

53

1.教學(xué)目

標(biāo)..........................................................................................

............................................................................53

2.工作任

務(wù)..........................................................................................

............................................................................53

3.相關(guān)實(shí)踐知

識(shí)..........................................................................................

............................................................53

4.實(shí)

驗(yàn)..........................................................................................

.............................................................................................67

5.課后作

業(yè)..........................................................................................

............................................................................67

案例二Oracle數(shù)據(jù)庫(kù)中的函數(shù)及子查

詢...............................................................................

68

1.教學(xué)目

標(biāo)..........................................................................................

............................................................................68

2.工作任

務(wù)..........................................................................................

............................................................................68

3.相關(guān)實(shí)踐知

識(shí)..........................................................................................

............................................................68

3.1綜合使用各種函

數(shù)..........................................................................................

.......................68

3.2字符函

數(shù)..........................................................................................

........................................................78

3.3數(shù)字函

數(shù)..........................................................................................

........................................................81

3.4日期函

數(shù)..........................................................................................

........................................................84

3.5轉(zhuǎn)換函

數(shù)..........................................................................................

........................................................85

4.實(shí)

驗(yàn)..........................................................................................

.............................................................................................86

目錄

5.課后作

業(yè)..........................................................................................

............................................................................86

專(zhuān)題四數(shù)據(jù)庫(kù)左

象..........................................................................................

...................................................................................87

1.教學(xué)目

標(biāo)..........................................................................................

............................................................................87

2.工作任

務(wù)..........................................................................................

............................................................................87

3.相關(guān)實(shí)踐知

識(shí)..........................................................................................

............................................................87

3.1使用同義

詞..........................................................................................

...............................................87

3.2使用序列實(shí)現(xiàn)自增主

鍵..........................................................................................

....89

3.3使用視

圖..........................................................................................

........................................................92

3.4使用索引加快表的查

詢..........................................................................................

....99

4.實(shí)

驗(yàn)..........................................................................................

.........................................................................................108

5.課后作

業(yè)..........................................................................................

........................................................................108

專(zhuān)題五PL/SQL編

程.......................................................................................

.....................................................................................110

1.教學(xué)目

標(biāo)..........................................................................................

................................................................................110

2.工作任

務(wù)..........................................................................................

................................................................................110

3.相關(guān)實(shí)踐知

識(shí)..........................................................................................

.........................................................................................................................................................................................110

3.1用PL/SQL塊來(lái)查詢表內(nèi)信

息................................................................................111

3.2用條件控制語(yǔ)句來(lái)實(shí)現(xiàn)為員工加

薪.....................................................................116

3.3使用循環(huán)控制語(yǔ)

句..........................................................................................

.....................118

3.4用順序控制語(yǔ)句實(shí)現(xiàn)更新員工工

資.....................................................................H9

3.5PL/SQL異常處理機(jī)制的應(yīng)

用120

4.提

高

..................................................................................................124

5.實(shí)

驗(yàn)..........................................................................................

..................................................................................................125

6.課后作

業(yè)..........................................................................................

................................................................................126

專(zhuān)題六游

標(biāo)..........................................................................................

..................................................................................................................127

1.教學(xué)目

標(biāo)..........................................................................................

................................................................................127

2.工作任

務(wù)..........................................................................................

................................................................................127

3.相關(guān)實(shí)踐知

識(shí)..........................................................................................

..............................................................127

3.1使用游標(biāo)屬

性..........................................................................................

.......................................127

3.2用游標(biāo)生成員工報(bào)

表..................................................................................

............129

3.3用游標(biāo)生成分部門(mén)員工報(bào)

表............................................................................133

3.4用游標(biāo)更新員工工

資..................................................................................

............135

3.5使用REF游標(biāo)動(dòng)態(tài)返回結(jié)果

集.......................................................................137

4.提

高..................................................................................

..................................................................................................138

5.實(shí)

臉..................................................................................

..................................................................................................140

6.課后作

業(yè)..................................................................................

................................................................................140

專(zhuān)題七過(guò)程、函數(shù)和程序

包..................................................................................

..................................................141

1.教學(xué)目

標(biāo)..................................................................................

................................................................................141

2.工作任

務(wù)..................................................................................

................................................................................141

3.相關(guān)實(shí)踐知

識(shí)

.........................................................................................................................................................................................141

3.1無(wú)參的顯示“Hell。World!"的過(guò)

程..............................................................Ml

3.2用帶輸入?yún)?shù)的過(guò)程向表中插入記

錄...........................................................142

3.3用帶輸出參數(shù)的過(guò)程查詢表中的記錄

數(shù).......................................................144

-11-

目錄

3.4使用帶輸入輸出參數(shù)的過(guò)程查詢記錄是否存

在...........................................144

3.5使用函數(shù)查詢部門(mén)信

息..................................................................................

145

3.6使用程序包封裝過(guò)程和函

數(shù)................................................................................147

4.提

高..................................................................................

..................................................................................................150

4.1用過(guò)程返回結(jié)果

集..................................................................................

.....................150

4.2在企業(yè)管理器中管理過(guò)程、函數(shù)和程序

包...................................................151

5.實(shí)

驗(yàn)..................................................................................

..................................................................................................153

6.課后作

業(yè)..................................................................................

................................................................................153

專(zhuān)題八觸發(fā)

器..................................................................................

.........................................................................................................155

1.教學(xué)目

標(biāo)..................................................................................

................................................................................155

2.工作任

務(wù)..................................................................................

................................................................................155

3.相關(guān)實(shí)踐知

識(shí)..................................................................................

..............................................................155

3.1BEFORE行級(jí)觸發(fā)

器................................................................................

................156

3.2AFTER行級(jí)觸發(fā)

器................................................................................

.......................160

3.3BEFORE語(yǔ)句級(jí)觸發(fā)

器................................................................................

....161

3.4AFTER語(yǔ)句級(jí)觸發(fā)

器................................................................................

..............162

3.5INSTEADOF觸發(fā)

器..................................................................................

..............164

3.6DDL觸發(fā)

器................................................................................

..................................................167

3.7數(shù)據(jù)庫(kù)啟動(dòng)和關(guān)閉觸發(fā)

器................................................................................

168

3.8用戶登錄和退出觸發(fā)

器..................................................................................

..169

3.9管理觸發(fā)

器..................................................................................

................................................171

4.實(shí)

驗(yàn)..................................................................................

..................................................................................................172

5.課后作

業(yè)..................................................................................

................................................................................173

專(zhuān)題九數(shù)據(jù)庫(kù)高級(jí)管

理..................................................................................

.....................................................................174

1.教學(xué)目

標(biāo)..................................................................................

................................................................................174

2.工作任

務(wù)..................................................................................

................................................................................174

3.相關(guān)實(shí)踐知

識(shí)..................................................................................

..............................................................174

3.1用戶和角色的創(chuàng)立和使

用................................................................................

174

3.2調(diào)整數(shù)據(jù)庫(kù)從非歸檔模式到歸檔模

式...........................................................179

3.3數(shù)據(jù)導(dǎo)入導(dǎo)

出..................................................................................

.......................................184

4.實(shí)

驗(yàn)..................................................................................

..................................................................................................193

5.課后作

業(yè)..................................................................................

................................................................................193

II

專(zhuān)題

Oracle入門(mén)

專(zhuān)題一.Oracle入門(mén)

1.教學(xué)目標(biāo)

1.1了解Oracle10g的安裝和卸載

1.2學(xué)會(huì)配置Oracle客戶端

1.3學(xué)會(huì)使用Oracle常用工具：企業(yè)管理器、iSQL*Plus和SQL*Plus

1.4掌握創(chuàng)立數(shù)據(jù)庫(kù)和表空間的方法，了解Oracle的體系結(jié)構(gòu)

1.5掌握Oracle中的根本用戶管理

2.工作任務(wù)

2.1在Windows環(huán)境下安裝和卸載Oracle10g

2.2配置Oracle客戶端

2.3學(xué)習(xí)使用Oracle常用工具：企業(yè)管理器、iSQL*Plus和SQL*Plus

2.4創(chuàng)立數(shù)據(jù)庫(kù)和表空間

2.5創(chuàng)立用戶并為之授權(quán)，修改用戶口令，刪除用戶

3.相關(guān)實(shí)踐知識(shí)

3.1Oracle10g的安裝和卸載

1.在Windows環(huán)境下，將Oracle10g安裝盤(pán)放入光盤(pán)驅(qū)動(dòng)器，安裝

程序會(huì)自動(dòng)運(yùn)行，

顯示如如圖1T所示的窗口。

理論知識(shí)：

Oracle簡(jiǎn)介

Oracle公司，中文翻譯成甲骨文公司，是全球最大的信息管理軟件及效勞供給商。該

公司成立于1977年，總部位于美國(guó)加州的紅木海岸城。目前，Oracle產(chǎn)品覆蓋了大、中、

小型機(jī)等幾十種機(jī)型，Oracle數(shù)據(jù)庫(kù)已成為世界上使用最廣泛的數(shù)據(jù)庫(kù)系統(tǒng)之一，Oracle

公司已成為這一領(lǐng)域的領(lǐng)軍者與標(biāo)準(zhǔn)制訂者。經(jīng)過(guò)30多年的不懈開(kāi)展，Oracle數(shù)據(jù)庫(kù)已經(jīng)

可以應(yīng)用于從支持成千上萬(wàn)用戶的分布式聯(lián)機(jī)事務(wù)處理系統(tǒng)到擁有數(shù)萬(wàn)億字節(jié)的用于決策支

持?jǐn)?shù)據(jù)倉(cāng)庫(kù)的廣泛領(lǐng)域。Oracle公司推出的Oracle數(shù)據(jù)庫(kù)系統(tǒng)始終占據(jù)著數(shù)據(jù)庫(kù)市場(chǎng)

龍

頭的地位9

Oracle公司敢為人先，始終引領(lǐng)著數(shù)據(jù)庫(kù)開(kāi)展的潮流。在號(hào)稱第三代互聯(lián)網(wǎng)技術(shù)一

“網(wǎng)格計(jì)算”技術(shù)蓬勃興起之時(shí)，Oracle公司推出了最新的支持網(wǎng)格環(huán)境的數(shù)據(jù)庫(kù)解決方

案----Oracle10g,其中的g代表網(wǎng)格（grid）。

Oracle數(shù)據(jù)庫(kù)教案_

Oracle10g數(shù)據(jù)庫(kù)是第一個(gè)為企業(yè)級(jí)網(wǎng)格計(jì)算而設(shè)計(jì)的數(shù)據(jù)庫(kù)。Oracle10g在Ora

de9i的基礎(chǔ)上，提供了針對(duì)網(wǎng)格計(jì)算更多的特性，如更大的規(guī)模、可管理性、高可用

性

及業(yè)務(wù)智能等。

在過(guò)去的幾年中，Oracle數(shù)據(jù)庫(kù)環(huán)境已成為世界上最流行的數(shù)據(jù)庫(kù)平臺(tái)之一。據(jù)有關(guān)

資料所述，目前Oi'acle已經(jīng)占領(lǐng)了46%以上的數(shù)據(jù)庫(kù)市場(chǎng)份額，并且有進(jìn)一步擴(kuò)大的趨勢(shì)。

在高端數(shù)據(jù)庫(kù)領(lǐng)域，Oracle更是無(wú)所匹敵，因此，掌握Oracle數(shù)據(jù)庫(kù)技術(shù)是廣闊IT人員

的一項(xiàng)根本要求。盡管Oracle數(shù)據(jù)庫(kù)的主要目標(biāo)依舊是效勞于那些要求海量數(shù)據(jù)處理能力

的大型公司或政府部門(mén)，但Oracle已經(jīng)推出了許多適合較小組織的新產(chǎn)品，如Personal

Oracle等。在各種操作系統(tǒng)平臺(tái)上，都有相應(yīng)的數(shù)據(jù)庫(kù)產(chǎn)品。將Oracle集成到各種類(lèi)

型

的企業(yè)和組織的方案正在進(jìn)行中，并已經(jīng)取得了巨大的成就，這意味著不只是大企業(yè)需要0-

racle數(shù)據(jù)庫(kù)，廣闊中小企業(yè)同樣需要Oracle數(shù)據(jù)庫(kù)。

圖1T光盤(pán)自動(dòng)安裝界面

2.單擊“開(kāi)始安裝"，啟動(dòng)安裝向?qū)?，進(jìn)入“選擇安裝方法〃界面，如圖1-2所示。

-2-

專(zhuān)題

Oracle入門(mén)

圖卜2選擇安裝方法

3.可以選擇“根本安裝”或"高級(jí)安裝”，我們選"根本安裝〃，同時(shí)選中創(chuàng)立啟動(dòng)數(shù)

據(jù)庫(kù)，輸入創(chuàng)立的全局?jǐn)?shù)據(jù)庫(kù)（能唯一標(biāo)識(shí)一個(gè)數(shù)據(jù)庫(kù)的名稱，Oracle10g之前的版本要

求

全局?jǐn)?shù)據(jù)庫(kù)名為數(shù)據(jù)庫(kù)名+數(shù)據(jù)庫(kù)域名，Oracle10g對(duì)此沒(méi)有要求）的名稱（例如"orcP）

和口令。

圖1-3選擇安裝類(lèi)型

-3-

Oracle數(shù)據(jù)庫(kù)教

案

注意：

Orac1e10gRelease2規(guī)定，口令不能為

“CHANGE_ON」NSTALL",

“MANAGER”,"DBSNMP","SYS7IAN"。這些口令分別為“SYS”

“SYSTEM”"SYSMAN”"DBSNMP”四個(gè)數(shù)據(jù)庫(kù)用戶的默認(rèn)口令。

理論知識(shí)：

Oracle10g數(shù)據(jù)庫(kù)核心產(chǎn)品；

Oi'aclc數(shù)據(jù)庫(kù)10g標(biāo)準(zhǔn)版1(Oi'acleDatabase10gStandardEdition

One)

Oi'acle數(shù)據(jù)庫(kù)10g標(biāo)準(zhǔn)版（OracleDatabase10gStandardEdition)

Oracle數(shù)據(jù)庫(kù)10g企業(yè)版（OracleDatabase10gEnterpriseEdition)

Oi'acle數(shù)據(jù)庫(kù)10g個(gè)人版（OracleDatabase10gPersonalEdition)

一般的部門(mén)級(jí)別的應(yīng)用，比方一個(gè)部門(mén)的考勤管理，標(biāo)準(zhǔn)版足夠用，只有大型的企業(yè)級(jí)

應(yīng)用，比方一個(gè)大型制造企業(yè)的ERP系統(tǒng)，需要數(shù)據(jù)分布式的存儲(chǔ)和計(jì)算，才選擇企業(yè)版，

個(gè)人版本一般個(gè)人學(xué)習(xí)用。

4.選擇“安裝類(lèi)型〃。Oracle10gRelease2的安裝類(lèi)型可為：企業(yè)版、標(biāo)準(zhǔn)

版和個(gè)人版，

可根據(jù)需要進(jìn)行選擇，默認(rèn)為企業(yè)版，單擊“下一步”按鈕，開(kāi)始準(zhǔn)備安裝。

圖1-4準(zhǔn)備安裝

接著安裝程序自動(dòng)進(jìn)行“產(chǎn)品特定的先決條件檢查"，如圖15所示。

5.安裝程序自動(dòng)對(duì)當(dāng)前系統(tǒng)進(jìn)行檢查，檢查其是否符合安裝要求，只有最終出現(xiàn)“檢查

完成。此次檢查的總體結(jié)果為：通過(guò)"（如圖1-5）,才能繼續(xù)安裝，否則檢查相應(yīng)的檢查項(xiàng)，

逐一改正，直到“總體結(jié)果為：通過(guò)〃。單擊“下一步"按鈕，繼續(xù)安裝，顯示“概要〃對(duì)話

框，如圖1-6所示。

專(zhuān)題

Oracle入門(mén)

圖1-5產(chǎn)品特定的先決條件檢查