Oracle數(shù)據(jù)庫管理系統(tǒng)實驗指導書

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

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

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

4、。 (3) 創(chuàng)建表結構：create table 創(chuàng)建部門登記表dept和雇員登記表emp的結構 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) 顯示表結構 顯示部門登記表dept和雇員登記表emp的結構 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 (先顯示命令的內容，再執(zhí)行 ) (7) 執(zhí)行磁盤上的命令文件 a. 先調入緩沖區(qū)，再運行： sql>get f:oradept.sql sql>/ b. 用 或start命令將指定命令文件調入緩沖區(qū)并執(zhí)行。 sql>f:ora dept.sql 或 sql>start f:ora dept.sql 2、簡單查詢語句(1) 無條件簡單查詢：查表中所有記錄sql> select dname, deptno 2 from dept；對查詢結果進行計算和統(tǒng)計sql> select cou

7、nt(*) all_emp， sum(sal) all_sal 2 from emp；(2) 有條件簡單查詢：查表中部分記錄 查在20號部門工作的雇員姓名和工資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號部門的雇員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； 實驗二 sql語言中的數(shù)據(jù)操縱、事務控制和數(shù)據(jù)定義語句一、實驗目的1、掌握sql語言中數(shù)據(jù)操縱命令的功能及其使用方法2、掌握sql語言中事務控制命令的功能及其使用方法3、掌握sql語言中數(shù)據(jù)定義

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

11、，loc) values ( &deptno，&dname，&loc); 將dept表中所有記錄插入到空表department中。 sql> insert into department select * from dept;向emp表中插入一條與smith 內容相同的記錄，但姓名改為richard，雇員號改為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號部門所有雇員工資和獎金，變?yōu)?0號部門雇員的最高工資和獎金。 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、事務控制命令的使用(1) 事務提交命令commitsql> commit；(2) 事務回退命令rollback sql> rollback ；3、數(shù)據(jù)定義命令的使用(1) 表操作手工創(chuàng)建表dept結構。 sql> create table dept ( deptno number(2) not null， dname char(14)， loc char(13)； 通過復制創(chuàng)建表deptpartment結構，其結構與dept相同。sql> create table dept

14、partment as select * from dept where 1=2； 將dept 表中dname 列寬度加大到20個字符。 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號部門的雇員創(chuàng)建一個視圖，包括姓名,工資,部門號 。 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)建一個序列生成器deptno_seq，起始值為50,間隔為10 。 sql> create seq

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

17、s 2003 server 網(wǎng)絡操作系統(tǒng)，windows 2003/xp客戶機操作系統(tǒng)； oracle9i服務端數(shù)據(jù)庫系統(tǒng)，客戶端工具。三、實驗內容1、pl/sql塊中查詢命令的使用將7788號雇員的工資和獎金作為smith的工資和獎金。 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號雇員的工資 。 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號雇員 。 sql> declare begin delete from emp where empno=7788； commit； end； 3、pl/sql塊中流程控制語句的使用(1) 條件控制語句的使用將emp表中名為smith的雇員的工資進行修改，若原工資大于$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號部門增加新雇員，只確定雇員號，其它信息忽略 。 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號部門增加新雇員，只確定雇員號，其它信息忽略 。 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塊中游標的使用取出10號部門雇員姓名和工資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號部門所有雇員

24、姓名、工資，并插入到一臨時表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ù)據(jù)庫觸發(fā)器、存儲過程和存儲函數(shù)一、實驗目的1、掌握數(shù)據(jù)庫觸發(fā)器的功能與使用方法2、掌握存儲過程的功能與使用方法3、掌握存儲函數(shù)的功能與使用方法二、實驗環(huán)境1、硬件設備：計算機局域網(wǎng)，服務器1臺，客戶機若干臺2、軟件系統(tǒng)：window

27、s 2003 server 網(wǎng)絡操作系統(tǒng)，windows 2003/xp客戶機操作系統(tǒng)； oracle9i服務端數(shù)據(jù)庫系統(tǒng)，客戶端工具。三、實驗內容1、數(shù)據(jù)庫觸發(fā)器的使用創(chuàng)建一個數(shù)據(jù)庫觸發(fā)器，當任何時候某個部門從dept表中刪除時，該觸發(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、存儲過程的使用創(chuàng)建一

28、個統(tǒng)計各部門雇員人數(shù)的據(jù)庫存儲過程。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、存儲函數(shù)的使用創(chuàng)建一個統(tǒng)計不同部門雇員人數(shù)的據(jù)庫存儲函數(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ù)。 sql execute empdnum:= count_proc (10)；實驗五 數(shù)據(jù)表的管理一、實驗目的1、利用sql語句（2種）創(chuàng)建表；2、掌握在oem中創(chuàng)建表；3、掌握定義表的約束、添加、刪除和激活約束；4、掌握修改表的各種操作。二、實驗環(huán)境1、硬件設備：計算機局域網(wǎng)，服務器1臺，客戶機若干臺2、軟件系統(tǒng)：windows 2003 server 網(wǎng)絡操作系統(tǒng)，windows 2003/x

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

33、束語法格式：constraint constraint_name constraint_type condition（2）表級約束語法格式：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)建表、刪除表、修改表和查詢表實驗六 索引的管理一、實驗目的 1、熟悉利用sql語句創(chuàng)建索引2、掌握在oem中創(chuàng)建索引3、掌握修改索引的各種操作二、實驗環(huán)境1、硬件設備：計算機局域網(wǎng)，服務器1臺，客戶機若干臺2、軟件系統(tǒng)：windows 2003 server 網(wǎng)絡操作系統(tǒng)，windows 2003/xp客戶機操作系統(tǒng)； oracle9i服務端數(shù)據(jù)庫系統(tǒng)，客戶端工具。三、實驗內容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)建索引、刪除索引、修改索引和查詢索引實驗七 安全管理一、實驗目的 1、熟悉利用sql語句對用戶、權限和角色進行管理2、掌握在oem中對用戶、權限和角色進行管理二、實驗環(huán)境1、硬件設備：計算機局域網(wǎng)，服務器1臺，客戶機若干臺2、軟件系統(tǒng)：windows 2003 server 網(wǎng)絡操作

37、系統(tǒng)，windows 2003/xp客戶機操作系統(tǒng)； oracle9i服務端數(shù)據(jù)庫系統(tǒng)，客戶端工具。三、實驗內容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對用戶、權限和角色進行管理。實驗八 觸發(fā)器和游標一、實驗目的1、了解觸發(fā)器的概念。2、熟悉觸發(fā)器的基本用法。3、了解游標的概念。4、熟悉游標的基本用法。二、實驗環(huán)境1、硬件設備：計算機

41、局域網(wǎng)，服務器1臺，客戶機若干臺2、軟件系統(tǒng)：windows 2003 server 網(wǎng)絡操作系統(tǒng)，windows 2003/xp客戶機操作系統(tǒng)； oracle9i服務端數(shù)據(jù)庫系統(tǒng)，客戶端工具。三、實驗內容1、聲明顯式游標聲明一個游標用來讀取基表emp中部門號是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、使用游標使用游標屬性判斷游標是否打開：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