Oracle數(shù)據(jù)庫管理系統(tǒng)實(shí)驗(yàn)指導(dǎo)書

1、oracle數(shù)據(jù)庫管理系統(tǒng)實(shí)驗(yàn)指導(dǎo)書 編寫 主審計(jì)算機(jī)與信息技術(shù)學(xué)院信息科學(xué)系2011.1目 錄實(shí)驗(yàn)一 熟悉sql命令與sql*plus命令的應(yīng)用1實(shí)驗(yàn)二 sql語言中的數(shù)據(jù)操縱、事務(wù)控制和數(shù)據(jù)定義語句4實(shí)驗(yàn)三 pl/sql編程7實(shí)驗(yàn)四 數(shù)據(jù)庫觸發(fā)器、存儲(chǔ)過程和存儲(chǔ)函數(shù)11實(shí)驗(yàn)五 數(shù)據(jù)表的管理14實(shí)驗(yàn)六 索引的管理17實(shí)驗(yàn)七 安全管理19實(shí)驗(yàn)八 觸發(fā)器和游標(biāo)21前 言oracle數(shù)據(jù)庫是目前最為流行和成熟的幾種大型關(guān)系數(shù)據(jù)庫之一。oracle數(shù)據(jù)庫管理系統(tǒng)課程以oracle9i為主，在學(xué)習(xí)數(shù)據(jù)庫系統(tǒng)概論的基礎(chǔ)上，進(jìn)一步理解關(guān)系數(shù)據(jù)庫的概念、原理。通過對(duì)oracle數(shù)據(jù)庫系統(tǒng)的學(xué)習(xí)，使學(xué)生掌握數(shù)

2、據(jù)庫的基本理論和oracle數(shù)據(jù)庫操作的基本方法，熟悉sql基本命令的運(yùn)用,了解運(yùn)用oracle數(shù)據(jù)庫知識(shí)處理復(fù)雜問題的方法。使學(xué)生具有運(yùn)用oracle進(jìn)行數(shù)據(jù)庫服務(wù)器端的程序開發(fā)的基本能力。為了加強(qiáng)學(xué)生對(duì)oracle數(shù)據(jù)庫系統(tǒng)的操作能力，特編寫該實(shí)驗(yàn)指導(dǎo)書，希望給學(xué)生提供一定的指導(dǎo)。本指導(dǎo)書由李學(xué)貴編寫，馮亞麗教授主審，由于時(shí)間倉促，作者的水平有限，書中難免有不足之處，懇請(qǐng)廣大師生批評(píng)指正。編者2011年1月實(shí)驗(yàn)一 熟悉sql命令與sql*plus命令的應(yīng)用一、實(shí)驗(yàn)?zāi)康?1、熟悉sql語言交互式使用工具sql*plus的使用方法2、熟悉sql*plus常用命令的功能和使用方法3、掌握sql語

3、言中簡單查詢語句的功能和使用方法二、實(shí)驗(yàn)環(huán)境1、硬件設(shè)備：計(jì)算機(jī)局域網(wǎng)，服務(wù)器1臺(tái)，客戶機(jī)若干臺(tái)2、軟件系統(tǒng)：windows 2003 server 網(wǎng)絡(luò)操作系統(tǒng)，windows 2003/xp客戶機(jī)操作系統(tǒng)； oracle9i服務(wù)端數(shù)據(jù)庫系統(tǒng)，客戶端工具。三、實(shí)驗(yàn)內(nèi)容1、sql*plus的使用(1) 進(jìn)入（啟動(dòng)）sql*plus在windows環(huán)境下,雙擊sql*plus圖標(biāo)或從程序組找sql*plus ，出現(xiàn)登錄窗口輸入正確的數(shù)據(jù)庫用戶名、密碼和連接字符串后，若連接數(shù)據(jù)庫成功，則會(huì)出現(xiàn)如下提示符sql>(2) 退出 sql*plus sql>exit 則退回到windows桌面

4、。 (3) 創(chuàng)建表結(jié)構(gòu)：create table 創(chuàng)建部門登記表dept和雇員登記表emp的結(jié)構(gòu) sql> create table dept ( deptno number(2) not null， dname char(14)， loc char(13)；sql> create table emp ( empno number(4) not null， ename char(9)， job char(10), mgr number(4), hiredate date, sal number(7,2), comm number(7,2), deptno number(2) not

5、 null )； (4) 顯示表結(jié)構(gòu) 顯示部門登記表dept和雇員登記表emp的結(jié)構(gòu) sql>desc dept sql> desc emp (5) 向表中插入記錄 向部門登記表dept中插入一條記錄 sql> insert into dept values (60，computer，beijing)； sql> select * from dept; 向部門登記表dept中連續(xù)插入多條記錄sql> insert into dept (deptno，dname，loc) values ( &deptno，&dname，&loc); (6)

6、執(zhí)行sql緩沖區(qū)中的命令 sql>/ (直接執(zhí)行) sql>run (先顯示命令的內(nèi)容，再執(zhí)行 ) (7) 執(zhí)行磁盤上的命令文件 a. 先調(diào)入緩沖區(qū)，再運(yùn)行： sql>get f:oradept.sql sql>/ b. 用 或start命令將指定命令文件調(diào)入緩沖區(qū)并執(zhí)行。 sql>f:ora dept.sql 或 sql>start f:ora dept.sql 2、簡單查詢語句(1) 無條件簡單查詢：查表中所有記錄sql> select dname, deptno 2 from dept；對(duì)查詢結(jié)果進(jìn)行計(jì)算和統(tǒng)計(jì)sql> select cou

7、nt(*) all_emp， sum(sal) all_sal 2 from emp；(2) 有條件簡單查詢：查表中部分記錄 查在20號(hào)部門工作的雇員姓名和工資sql> select ename，sal，deptno 2 from emp 3 where deptno=20； 查找工種是職員或分析員的雇員姓名和工種sql> select ename，job 2 from emp 3 where job in (clerk，analyst)； 查找以“s”開頭的雇員姓名和所在部門 sql> select ename，deptno 2 from emp 3 where ename

8、 like s%；按工資升序排列20號(hào)部門的雇員sql> select ename，sal，deptno 2 from emp 3 where deptno=20 4 order by sal asc ； 查所有部門中工資大于2800美元的雇員 sql> select ename，sal from account where sal > 2800 union select ename，sal from research where sal > 2800 union select ename，sal from sales where sal > 2800 ； 查在所

9、有部門中都存在的工種sql> select job from account intersect select job from research intersect select job from sales； 查在account部門中有哪些職業(yè)，是sales部門中所沒有的 sql> select job from account minus select job from sales； 實(shí)驗(yàn)二 sql語言中的數(shù)據(jù)操縱、事務(wù)控制和數(shù)據(jù)定義語句一、實(shí)驗(yàn)?zāi)康?、掌握sql語言中數(shù)據(jù)操縱命令的功能及其使用方法2、掌握sql語言中事務(wù)控制命令的功能及其使用方法3、掌握sql語言中數(shù)據(jù)定義

10、命令的功能及其使用方法二、實(shí)驗(yàn)環(huán)境1、硬件設(shè)備：計(jì)算機(jī)局域網(wǎng)，服務(wù)器1臺(tái)，客戶機(jī)若干臺(tái)2、軟件系統(tǒng)：windows 2003 server 網(wǎng)絡(luò)操作系統(tǒng)，windows 2003/xp客戶機(jī)操作系統(tǒng)； oracle9i服務(wù)端數(shù)據(jù)庫系統(tǒng)，客戶端工具。三、實(shí)驗(yàn)內(nèi)容1、數(shù)據(jù)操縱命令的使用(1) 數(shù)據(jù)插入 向dept 表插入一行數(shù)據(jù)(插入所有列時(shí)可省略列名) 。 sql>insert into dept values (60，computer，beijing); 向dept表中一次插入多個(gè)紀(jì)錄(所有字段可用*代替) 。 sql> insert into dept (deptno，dname

11、，loc) values ( &deptno，&dname，&loc); 將dept表中所有記錄插入到空表department中。 sql> insert into department select * from dept;向emp表中插入一條與smith 內(nèi)容相同的記錄，但姓名改為richard，雇員號(hào)改為9999。 sql> insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) select 9999,richard,job,mgr,hiredate,sal,comm,deptno

12、from emp where ename=smith; (2) 數(shù)據(jù)修改將 martin 提升為經(jīng)理，工資加￥1000 。 sql> update emp set job = manager，sal=sal+1000 where ename= martin; 將30號(hào)部門所有雇員工資和獎(jiǎng)金，變?yōu)?0號(hào)部門雇員的最高工資和獎(jiǎng)金。 sql> update emp set (sal，comm)=( select max(sal)，max(nvl(comm，0) from emp where deptno =20) where deptno=30；(3) 數(shù)據(jù)刪除將 martin從公司刪除

13、 。 sql> delete from emp where ename= martin；2、事務(wù)控制命令的使用(1) 事務(wù)提交命令commitsql> commit；(2) 事務(wù)回退命令rollback sql> rollback ；3、數(shù)據(jù)定義命令的使用(1) 表操作手工創(chuàng)建表dept結(jié)構(gòu)。 sql> create table dept ( deptno number(2) not null， dname char(14)， loc char(13)； 通過復(fù)制創(chuàng)建表deptpartment結(jié)構(gòu)，其結(jié)構(gòu)與dept相同。sql> create table dept

14、partment as select * from dept where 1=2； 將dept 表中dname 列寬度加大到20個(gè)字符。 sql> alter table dept modify dname char (20) ； 為表dept增加一列all_emp 用來存放部門人數(shù) 。 sql> alter table dept add all_emp number(3)； 刪除dept表。 sql> drop table dept；(2) 視圖操作為20號(hào)部門的雇員創(chuàng)建一個(gè)視圖，包括姓名,工資,部門號(hào) 。 sql> create view emp_view(name

15、 ，salary ，no) as select ename ，sal ，deptno from emp where deptno=20 ；(3) 索引操作為emp表建立empno列上的唯一索引。 sql> create unique index empno_index on emp(empno); (4) 同義詞操作為gx1用戶的dept表建立同義詞new_dept 。 sql> create synonym new_dept for gx1.dept; (5) 序列生成器操作創(chuàng)建一個(gè)序列生成器deptno_seq，起始值為50,間隔為10 。 sql> create seq

16、uence deptno_seq start with 50 increment by 10； 列出deptno_seq的下次值(第一次使用時(shí)為50 ) sql> select deptno_seq.nextval next_val from dual；實(shí)驗(yàn)三 pl/sql編程 一、實(shí)驗(yàn)?zāi)康?、了解pl/sql塊的基本結(jié)構(gòu)與功能2、掌握pl/sql塊中各種sql命令的使用方法3、掌握pl/sql塊中流程控制語句的使用方法4、掌握pl/sql塊中游標(biāo)的使用方法5、掌握pl/sql塊中異常處理的使用方法二、實(shí)驗(yàn)環(huán)境1、硬件設(shè)備：計(jì)算機(jī)局域網(wǎng)，服務(wù)器1臺(tái)，客戶機(jī)若干臺(tái)2、軟件系統(tǒng)：window

17、s 2003 server 網(wǎng)絡(luò)操作系統(tǒng)，windows 2003/xp客戶機(jī)操作系統(tǒng)； oracle9i服務(wù)端數(shù)據(jù)庫系統(tǒng)，客戶端工具。三、實(shí)驗(yàn)內(nèi)容1、pl/sql塊中查詢命令的使用將7788號(hào)雇員的工資和獎(jiǎng)金作為smith的工資和獎(jiǎng)金。 sql> declare v_empno emp.empno%type:=7788； v_ename emp.ename%type:=smith； v_sal emp.sal%type； v_comm m%type； begin select sal，comm into v_sal，v_comm from emp where empno=v_empno

18、； update emp set sal=v_sal，comm=v_comm where ename= v_ename ； commit; end；查詢smith的情況 。 sql> declare emp_rec emp%rowtype； v_ename emp.ename%type：='smith'； begin select * into emp_rec from emp where ename=v_ename； - end； 2、pl/sql塊中數(shù)據(jù)操縱命令的使用向emp表插入一新雇員 。 sql> declare v_deptno dept.deptno%

19、type not null:=50； v_dname dept.dname%type:=computer； v_loc dept.loc%type:=beijing； begin insert into dept(deptno，dname，loc) values(v_deptno，v_dname，v_loc)； commit work; end；修改7788號(hào)雇員的工資 。 sql> declare v_empno emp.empno%type:=7788； v_addsal emp.sal%type； begin v_addsal:=1000； update emp set sal=s

20、al+v_addsal where empno=v_empno； commit； end； 從emp表中刪除7788號(hào)雇員 。 sql> declare begin delete from emp where empno=7788； commit； end； 3、pl/sql塊中流程控制語句的使用(1) 條件控制語句的使用將emp表中名為smith的雇員的工資進(jìn)行修改，若原工資大于$2000，則加$500，否則加$1000 。 sql>declare v_ename emp.ename%type:='smith'； v_addsal emp.sal%type； v_

21、sal emp.sal%type； begin select sal into v_sal from emp where ename=v_ename； if v_sal>20oo then v_addsal:=500； else v_addsal:=1000； end if； update emp set sall=sal+v_addsal where ename=v_ename； commit； end；(2) 循環(huán)控制語句的使用給10號(hào)部門增加新雇員，只確定雇員號(hào)，其它信息忽略 。 sql>declare v_empno emp.empno%type:=8000； begin

22、loop insert into emp(deptno,empno) values( 10，v_empno)； v_empno:=v_empno+l00； exit when v_empno>=9000； end loop； end； (3) goto控制語句的使用給10號(hào)部門增加新雇員，只確定雇員號(hào)，其它信息忽略 。 sql>declre v_empno emp.empno%type:=8000； begin lab1 insert into emp(deptno,empno) values(l0，v_empno)； v_empno:=v_empno+100； if v_empn

23、o<=9000 then goto lab1； end if； end； 4、pl/sql塊中游標(biāo)的使用取出10號(hào)部門雇員姓名和工資sql>declare v_deptno emp.deptno%type； v_ename emp.ename%type； v_sal emp.sal%type； cursor c1 is select ename，sal from emp where deptno=v_deptno； begin v_deptno:=10； open cl； fetch c1 into v_ename，v_sal； close c1 ； end；查詢10號(hào)部門所有雇員

24、姓名、工資，并插入到一臨時(shí)表tmp中 。 sql>declare v_deptno emp.deptno%type:=10； cursor c1 is select ename，sal from emp where deptno=v_deptno； emp_ec c1%rowtype； begin for emp_rec in c1 loop insert into tmp(ename，sal) values(emp_rec.ename，emp_rec.sal)； end loop； gommit work； end；5、pl/sql塊中異常處理的使用從emp表中刪去smith的信息 。

25、 sql>declare v_ename emp.ename%type:='smith'； begin delete from emp where ename=v_ename； commit work； exception when no_data_found then rollback work； insert into temp(message) values('smith is not found')； commit work； when too_many_rows then rollback work； insert into temp(messa

26、ge) values('found too many rows in emp')； commit work； when others then rollback work； insert into temp(message) values('other error occurred')； commit work； end； 實(shí)驗(yàn)四 數(shù)據(jù)庫觸發(fā)器、存儲(chǔ)過程和存儲(chǔ)函數(shù)一、實(shí)驗(yàn)?zāi)康?、掌握數(shù)據(jù)庫觸發(fā)器的功能與使用方法2、掌握存儲(chǔ)過程的功能與使用方法3、掌握存儲(chǔ)函數(shù)的功能與使用方法二、實(shí)驗(yàn)環(huán)境1、硬件設(shè)備：計(jì)算機(jī)局域網(wǎng)，服務(wù)器1臺(tái)，客戶機(jī)若干臺(tái)2、軟件系統(tǒng)：window

27、s 2003 server 網(wǎng)絡(luò)操作系統(tǒng)，windows 2003/xp客戶機(jī)操作系統(tǒng)； oracle9i服務(wù)端數(shù)據(jù)庫系統(tǒng)，客戶端工具。三、實(shí)驗(yàn)內(nèi)容1、數(shù)據(jù)庫觸發(fā)器的使用創(chuàng)建一個(gè)數(shù)據(jù)庫觸發(fā)器，當(dāng)任何時(shí)候某個(gè)部門從dept表中刪除時(shí)，該觸發(fā)器將從emp表中刪除該部門的所有雇員。 sql>create trigger del_emp_deptno_trig before delete on dept for each row begin delete from emp where deptno=:old.deptno; end del_emp_deptno_trig；2、存儲(chǔ)過程的使用創(chuàng)建一

28、個(gè)統(tǒng)計(jì)各部門雇員人數(shù)的據(jù)庫存儲(chǔ)過程。sql>create procedure count_proc(in_deptno in emp.deptno%type， out_num out number) as begin if in_deptno=10 then select count(deptno) into out_num from emp where deptno=in_deptno； elsif in_deptno=20 then select count(deptno) into out_num from emp where deptno= in_deptno ； elsif i

29、n_deptno=30 then select count(deptno) into out_num from emp where deptno= in_deptno ； else select count(deptno) into out_num from emp where deptno=40； end if； end count_proc ；3、存儲(chǔ)函數(shù)的使用創(chuàng)建一個(gè)統(tǒng)計(jì)不同部門雇員人數(shù)的據(jù)庫存儲(chǔ)函數(shù)sql>create function count _func( in_deptno emp.deptno%type) return number as out_num number;

30、 begin if in_deptno=10 then select count(deptno) into out_num from emp where deptno=in_deptno； elsif in_deptno=20 then select count(deptno) into out_num from emp where deptno= in_deptno ； elsif in_deptno=30 then select count(deptno) into out_num from emp where deptno= in_deptno ； else select count(d

31、eptno) into out_num from emp where deptno=40； end if； return(out_num )； end count_func； 用實(shí)際參數(shù)代替形式參數(shù)。 sql execute empdnum:= count_proc (10)；實(shí)驗(yàn)五 數(shù)據(jù)表的管理一、實(shí)驗(yàn)?zāi)康?、利用sql語句（2種）創(chuàng)建表；2、掌握在oem中創(chuàng)建表；3、掌握定義表的約束、添加、刪除和激活約束；4、掌握修改表的各種操作。二、實(shí)驗(yàn)環(huán)境1、硬件設(shè)備：計(jì)算機(jī)局域網(wǎng)，服務(wù)器1臺(tái)，客戶機(jī)若干臺(tái)2、軟件系統(tǒng)：windows 2003 server 網(wǎng)絡(luò)操作系統(tǒng)，windows 2003/x

32、p客戶機(jī)操作系統(tǒng)； oracle9i服務(wù)端數(shù)據(jù)庫系統(tǒng)，客戶端工具。三、實(shí)驗(yàn)內(nèi)容一、 實(shí)驗(yàn)內(nèi)容和步驟1、創(chuàng)建數(shù)據(jù)表 create table <表名> (<列名><數(shù)據(jù)類型>列級(jí)完整性約束條件 ，<列名> <數(shù)據(jù)類型>列級(jí)完整性約束條件 ，<表級(jí)完整性約束條件>) 參數(shù)設(shè)置;2、創(chuàng)建帶約束的數(shù)據(jù)表create table <表名> (<列名>列級(jí)完整性約束條件 ，<列名> 列級(jí)完整性約束條件 ，<表級(jí)完整性約束條件>) 參數(shù)設(shè)置as 子查詢  3 定義約束（1）列級(jí)約

33、束語法格式：constraint constraint_name constraint_type condition（2）表級(jí)約束語法格式：constraint constraint_name constraint_type column1_name,column2_name,|condition 3、表約束的修改alter table <table_name>add <constraint>modify <constraint >enable <constraint >disable <constraint >drop <co

34、nstraint >4、字段的的添加、刪除、修改alter table <表名>add <新列名><數(shù)據(jù)類型> <完整性約束定義modify <列名><數(shù)據(jù)類型> rename column oldname to newnameset unused column column /single columnset unused columns（column1，column2）drop column <col> /single columndrop < col1，col2 > /multi column

35、drop unused columns 5、利用oem創(chuàng)建表、刪除表、修改表和查詢表實(shí)驗(yàn)六 索引的管理一、實(shí)驗(yàn)?zāi)康?1、熟悉利用sql語句創(chuàng)建索引2、掌握在oem中創(chuàng)建索引3、掌握修改索引的各種操作二、實(shí)驗(yàn)環(huán)境1、硬件設(shè)備：計(jì)算機(jī)局域網(wǎng)，服務(wù)器1臺(tái)，客戶機(jī)若干臺(tái)2、軟件系統(tǒng)：windows 2003 server 網(wǎng)絡(luò)操作系統(tǒng)，windows 2003/xp客戶機(jī)操作系統(tǒng)； oracle9i服務(wù)端數(shù)據(jù)庫系統(tǒng)，客戶端工具。三、實(shí)驗(yàn)內(nèi)容1、使用命令創(chuàng)建索引 create unique bitmapindex <索引名>on <表名> (<列名> 次序 ,<

36、列名> 次序)reverseparameter_list 2、使用命令修改索引alter indexcoalescealter index rebuilder alter index monitoring usage alter indexrename to 3、刪除索引drop index 4、使用oem創(chuàng)建索引、刪除索引、修改索引和查詢索引實(shí)驗(yàn)七 安全管理一、實(shí)驗(yàn)?zāi)康?1、熟悉利用sql語句對(duì)用戶、權(quán)限和角色進(jìn)行管理2、掌握在oem中對(duì)用戶、權(quán)限和角色進(jìn)行管理二、實(shí)驗(yàn)環(huán)境1、硬件設(shè)備：計(jì)算機(jī)局域網(wǎng)，服務(wù)器1臺(tái)，客戶機(jī)若干臺(tái)2、軟件系統(tǒng)：windows 2003 server 網(wǎng)絡(luò)操作

37、系統(tǒng)，windows 2003/xp客戶機(jī)操作系統(tǒng)； oracle9i服務(wù)端數(shù)據(jù)庫系統(tǒng)，客戶端工具。三、實(shí)驗(yàn)內(nèi)容1、create user user_name identified by password | externally | globally as external_name default tablespace tablespace_name temporary tablespace temp_tablespace_name quota n k|m|unlimited on tablespace_name profile profile_name password expire a

38、ccount lock | unlock 2、alter user user_name identified by password | externally | globally as external_name default tablespace tablespace_name temporary tablespace temp_tablespace_name quota n k | m | unlimited on tablespace_name profile profile_name default role role_list | all except role_list | n

39、one password expire account lock | unlock 3、drop user user_name cascade ;4、create role role_name not identified identified by password 5、alter role role_name not identified identified by password ；6、drop role role_name;7、grant sys_priv_list to user_list|role_list public with admin option ;8、revoke s

40、ys_priv_list from user_list | role_list;9、grant obj_priv_list | all on schema.object to user_list | role_list with grant option;10、revoke obj_priv_list | all on schema.object from user_list|role_list;11、利用oem對(duì)用戶、權(quán)限和角色進(jìn)行管理。實(shí)驗(yàn)八 觸發(fā)器和游標(biāo)一、實(shí)驗(yàn)?zāi)康?、了解觸發(fā)器的概念。2、熟悉觸發(fā)器的基本用法。3、了解游標(biāo)的概念。4、熟悉游標(biāo)的基本用法。二、實(shí)驗(yàn)環(huán)境1、硬件設(shè)備：計(jì)算機(jī)

41、局域網(wǎng)，服務(wù)器1臺(tái)，客戶機(jī)若干臺(tái)2、軟件系統(tǒng)：windows 2003 server 網(wǎng)絡(luò)操作系統(tǒng)，windows 2003/xp客戶機(jī)操作系統(tǒng)； oracle9i服務(wù)端數(shù)據(jù)庫系統(tǒng)，客戶端工具。三、實(shí)驗(yàn)內(nèi)容1、聲明顯式游標(biāo)聲明一個(gè)游標(biāo)用來讀取基表emp中部門號(hào)是20且工作為分析員的職工：declare cursor c1 is select ename, sal, hiredate from emp where deptno = 20 and job = 'analyst'v_ename varchar2(10);v_sal number(7,2);v_hiredate date; begin open c1; fetch c1 into v_ename, v_sal, v_hiredate; close c1;end;2、使用游標(biāo)使用游標(biāo)屬性判斷游標(biāo)是否打開：if c1%open then fetch c1 into v_ename, v_sal, v_hiredate;else open c1;end if;利用循環(huán)讀取數(shù)據(jù)：loopfetch c1 into v_ename, v_sal, v_hir