3493949142《數(shù)據(jù)庫原理及應(yīng)用》實(shí)驗(yàn)指導(dǎo)書

《3493949142《數(shù)據(jù)庫原理及應(yīng)用》實(shí)驗(yàn)指導(dǎo)書》由會員分享，可在線閱讀，更多相關(guān)《3493949142《數(shù)據(jù)庫原理及應(yīng)用》實(shí)驗(yàn)指導(dǎo)書（24頁珍藏版）》請?jiān)谘b配圖網(wǎng)上搜索。

1、 《數(shù)據(jù)庫原理及應(yīng)用-ORACLE》 實(shí) 驗(yàn) 指 導(dǎo) 書 信息科學(xué)與工程學(xué)院 2014年9月 作者：何小衛(wèi) 目 錄 實(shí)驗(yàn)一ORACLE平臺下基本SQL語言的應(yīng)用 實(shí)驗(yàn)二ORACLE基本操作與用戶權(quán)限基本管理 實(shí)驗(yàn)三 數(shù)據(jù)字典視圖 實(shí)驗(yàn)四 PL-SQL語言設(shè)計(jì) 實(shí)驗(yàn)五 存儲過程 實(shí)驗(yàn)六 游標(biāo) 實(shí)驗(yàn)七 數(shù)據(jù)庫觸發(fā)器 實(shí)驗(yàn)一：ORACLE平臺下基本SQL語言的應(yīng)用 實(shí)驗(yàn)?zāi)康? 在oracle下熟練運(yùn)用SQL語言來完成基本表

2、的管理、索引的建立和刪除、數(shù)據(jù)查詢、數(shù)據(jù)更新、視圖建立和刪除等等操作。 實(shí)驗(yàn)要求 1、 件基本配置：Intel PentiumIII以上級別的CPU，大于512MB的內(nèi)存。 2、 軟件要求：Window XP操作系統(tǒng)，ORACLE 9i 3、 實(shí)驗(yàn)學(xué)時(shí)：4學(xué)時(shí)。 4、 實(shí)驗(yàn)報(bào)告。 實(shí)驗(yàn)準(zhǔn)備 ORACLE 9i 實(shí)驗(yàn)內(nèi)容 上機(jī)題1：利用Create Table 創(chuàng)建scott用戶下的3個(gè)表，包括每個(gè)表的碼和外碼的定義。 EmpC員工表字段內(nèi)容如下: empno員工號（主碼），ename員工姓名，job工作，mgr上級編號（外碼），hiredate受雇日期，sal薪金，c

3、omm傭金，deptno部門編號（外碼）； create table Empc(empno number,ename varchar(10),job varchar(20),mgr number,hiredate date,sal number,comm number,deptno number,primary key(empno),foreign key(mgr) references Empc(empno)); alter table empc add constraint deptno foreign key(deptno) references deptc(deptno); De

4、ptC部門表字段內(nèi)容如下： deptno 部門號（主碼），dname部門名稱，loc辦公地方 create table Deptc(deptno number,ename varchar(10),loc varchar(10),primary key(deptno)); BonusC獎(jiǎng)金表字段內(nèi)容如下： ename員工姓名，job 工作名稱，sal薪金，comm傭金 create table bounsc(ename varchar(10),job varchar(10),sal number,comm number); 上機(jī)題2：用Alter Table向表中BonusC增加co

5、mm的約束為0-3000元之間。 alter table bounsc add constraint comm check(comm between 0 and 3000); 上機(jī)題3：將一個(gè)員工記錄插入到Emp中（每個(gè)屬性值自定）； insert into emp values(102,zhangsan,manager,102,to_date(2010-05-06,yyyy-mm-dd),null,null,10); 上機(jī)題4：刪除所有張三職工記錄； Delete from emp where ename=’zhangsan’; 上機(jī)題5：創(chuàng)建如下的視圖：創(chuàng)建“research

6、”部門的職工情況，包含如下字段的視圖view_research：包括empno、ename、sal、comm。 create view research as select empno,ename,sal,comm from emp; 上機(jī)題6：使用SELECT語句創(chuàng)建查詢： 1、列出至少有一個(gè)員工的全部部門及人數(shù)。 select count(empno),deptno from emp group by deptno having count(ename)>1; 2、列出薪金比“SMITH”多的全部員工。 select ename,sal from emp where sal

7、>(select sal from emp where ename=SMITH); 3、列出全部員工的姓名及其直接上級的姓名 select table1.ename,table2.ename from emp table1,emp table2 where table1.mgr=table2.empno; 4、列出受雇日期早于其直接上級的全部員工。 select a.ename from emp a,emp b where a.mgr=b.empno and a.hiredate

8、elect emp.*,dept.dname from emp right outer join dept on dept.deptno=emp.deptno; 6、列出全部 “CLERK”（做事員）的姓名及其部分名稱。 select ename,dname from emp,dept where emp.deptno=dept.deptno and emp.job=CLERK; 7、列出最低薪金大于1500的種種工作 。 select job from emp group by job having min(sal)>1500; 8、列出在部分門“SALES”（販賣部）做事的員

9、工的姓名，假定不知道販賣部的部門編號。 select ename from (select deptno from dept where dname=SALES)a,emp where a.deptno=emp.deptno; 9、列出薪金高于公司平均薪金的全部員工。 select emp.* from emp where sal>(select avg(sal)from emp); 10、列出與“SCOTT”從事相同工作的全部員工。 select emp.* from emp where job in(select job from emp where ename=SCOTT

10、); 11、列出薪金是部門30中員工的薪金的全部員工的姓名和薪金。 select ename,sal from emp where sal in (select sal from emp where deptno=30); 12、列出薪金高于在部門30做事的全部員工的薪金的員工姓名和薪金。 select ename,sal from emp where sal >(select max(sal) from emp where deptno=30); 13、列出在每個(gè)部門做事的員工數(shù)量 、均勻收入、平均做事限期。 select count(ename),avg(sal),fl

11、oor(sysdate-hiredate/365) from emp group by(deptno); 14、列出全部員工的姓名、部門名稱和收入。 select ename,dname,sal from emp,dept where emp.deptno=dept.deptno; 15、列出從事同一種工作但屬于不同部門的員工。 select a.ename from emp a,emp b where a.job=b.job and a.deptno<>b.deptno; 16、列出全部部門的具體信息和部門人數(shù)。 select * from dept a left join

12、(select deptno.count(*)from emp group by dept(no) b on a.deptno=b.deptno; 17、列出種種工作的最低收入。 select job,min(sal) from emp group by job; 18、列出各個(gè)部門的MANAGER（司理）的最低薪金。 select min(sal) from emp where job=MANAGERgroup by deptno; 19、列出全部員工的年收入 ,按年薪從低到高排序。 select ename,sal*12 a from emp order by a as

13、c; 20、找出傭金高于薪金的60%的員工. select ename from emp where comm>sal*0.6; 21、找出部門10中所有經(jīng)理(MANAGER),部門20中所有辦事員(CLERK),既不是經(jīng)理又不是辦事員但其薪金大于或等于2000的所有員工的詳細(xì)資料. Select * name emp where (deptno=’10’ and job=’MANAGER’) or (deptno=’20’ and job=’CLERK’) or (job not in (‘MANAGER’,’ CLERK’) and sal>=2000) 22、找出不收取傭金

14、或收取的傭金低于100的員工. select * from emp where comm<100 or comm=0; 23、找出各月倒數(shù)第3天受雇的所有員工. select * from emp where hiredate=last day(hiredate)-2; 24、顯示正好為5個(gè)字符的員工的姓名. select ename from emp where ename like ; 25、顯示不帶有"R"的員工的姓名 select ename from emp where ename not like%R%;. 26、顯示所有員工姓名的前三個(gè)字符.

15、 select substr(ename,1,3)from emp; 27、顯示所有員工的姓名、工作和薪金,按工作的降序排序,若工作相同則按薪金排序. select ename,sal,job from emp order by job desc,sal; 28、對于每個(gè)員工,顯示其加入公司的天數(shù). select ename,round(sysdate-hiredate) emp_date from emp; 29、顯示姓名字段的任何位置包含"A"的所 有員工的姓名. select ename from emp where ename like%A%; 30、查詢和“李

16、建國”是同一部門的職工姓名。（使用子查詢） select ename from emp where deptno=(select deptno from emp where ename=李建國); 31、查詢“Research”和”Manager”部門的職工姓名（UNION） select ename from emp where job=RESEARCH or job=MANAGER; 實(shí)驗(yàn)二ORACLE基本操作與用戶權(quán)限基本管理 實(shí)驗(yàn)?zāi)康? 熟悉ORACLE環(huán)境下基本操作和基本用戶權(quán)限管理 實(shí)驗(yàn)要求 1、 硬件基本配置：Intel PentiumIII以上級別

17、的CPU，大于512MB的內(nèi)存。 2、 軟件要求：Window XP操作系統(tǒng)，ORACLE 9i 3、 實(shí)驗(yàn)學(xué)時(shí)：2學(xué)時(shí) 4、 實(shí)驗(yàn)報(bào)告 實(shí)驗(yàn)內(nèi)容 上機(jī)題1： 連接命令 connect username/password@服務(wù)名[as sysdba, as sysoper], 當(dāng)用特權(quán)用戶連接時(shí)候，必須帶上as sysdba或as sysoper。 Sys均可以；conn System/manager disconnect：斷開與當(dāng)前數(shù)據(jù)庫的連接；SQL/plus中運(yùn)用OK password 說明:該命令用于修改用戶的密碼，如果要想修改其它用戶的密碼，需要用sys/sy

18、stem 登錄。 show user 說明: 顯示當(dāng)前用戶名 exit：該命令會斷開與數(shù)據(jù)庫的連接，同時(shí)會退出sql*plus disconnect; connect system/sys as dydsba; show user; 上機(jī)題2：創(chuàng)建用戶和系統(tǒng)權(quán)限 create user xiaozhang identified by xiao; grant create session to xiaozhang; grant create table to xiaozhang; grant unlimited tablespace to xiaozhang; sele

19、ct * from user_sys_privs; 上機(jī)題3：限制用戶和密碼修改 用戶加鎖：alter user 用戶名 account lock 用戶解鎖：alter user 用戶名 account unlock 用戶口令即刻失效：alter user 用戶名 password expire 刪除用戶：drop user 用戶名 [cascade] cascade 用在當(dāng)被刪除的用戶下還有未刪除的對象（如一些表）時(shí)，強(qiáng)制級聯(lián)刪除。它表示刪除用戶所有對象。 密碼修改：用SYS (或SYSTEM)用戶登錄: CONN SYS/PASS_WORD AS SYSDBA; 使用如下語

20、句修改用戶的密碼: ALTER USER user_name IDENTIFIED BY "newpass"; alter user xiaozhang account lock; alter user xiaozhang account unlock; alter user xiaozhang password expire; alter user xiaozhang identified by zhang; 上機(jī)題4：對象權(quán)力的管理: Grant,Revoke運(yùn)用 grant select on mytab to xiaozhang; grant update,select,

21、delete on mytab to xiaozhang; grant all on mytab to xiaozhang; connect scott/tiger; grant select on emp to xiaozhang with grant option; connect xiaozhang/zhang; select * from scott.emp; grant select on scott.emp to lisi; connect lisi/lisi; select * from scott.emp; 上機(jī)題5：權(quán)限的傳遞 sys用戶把一些系統(tǒng)權(quán)限授權(quán)

22、給xiaozhang用戶. grant alter any table to xiaozhang with admin option 查看當(dāng)前用戶的對象權(quán)限：select * from user_tab_privs grant unlimited tablespace to xiaozhang with admin option; create user lisi identified by lisi; grant create session to lisi; grant unlimited tablespace to lisi; select * from user_tab_

23、privs; 上機(jī)題6：角色管理 在sys下創(chuàng)建角色：create role myrole; 給角色添加權(quán)限：grant create session to myrole; grant create table to myrole; 創(chuàng)建用戶： create user zhangsan; grant myrole to zhangsan;//賦予以上的兩個(gè)權(quán)限給zhangsan create role myrole; grant create session to myrole; grant create table to myrole; create user zhan

24、gsan identified by zhang; grant myrole to zhangsan; 實(shí)驗(yàn)三：數(shù)據(jù)字典視圖 實(shí)驗(yàn)?zāi)康? 在oracle下熟練掌握數(shù)據(jù)字典視圖的應(yīng)用。 實(shí)驗(yàn)要求 1、基本配置：Intel PentiumIII以上級別的CPU，大于512MB的內(nèi)存。 2、軟件要求：Window XP操作系統(tǒng)，ORACLE 9i 3、實(shí)驗(yàn)學(xué)時(shí)：2學(xué)時(shí)。 4、實(shí)驗(yàn)報(bào)告。 實(shí)驗(yàn)準(zhǔn)備 數(shù)據(jù)字典是oracle 數(shù)據(jù)庫中最重要的組成部分，它提供了數(shù)據(jù)庫的一些系統(tǒng)信息。數(shù)據(jù)字典記錄了數(shù)據(jù)庫的系統(tǒng)信息，它是只讀表和視圖的集合，數(shù)據(jù)字典的所有者為

25、sys 用戶。用戶只能在數(shù)據(jù)字典上執(zhí)行查詢操作（select 語句），而其維護(hù)和修改是由系統(tǒng)自動(dòng)完成的。 實(shí)驗(yàn)內(nèi)容 上機(jī)題1 user_tables user_tables； 用于顯示當(dāng)前用戶所擁有的所有表，它只返回用戶所對應(yīng)方案的所有表 比如：select table_name from user_tables; 上機(jī)題2 all_tables 用于顯示當(dāng)前用戶可以訪問的所有表，它不僅會返回當(dāng)前用戶方案的所有表，還 會返回當(dāng)前用戶可以訪問的其它方案的表：比如：select table_name from all_tables; 上機(jī)題3 dba_tables

26、 它會顯示所有方案擁有的數(shù)據(jù)庫表。但是查詢這種數(shù)據(jù)庫字典視圖，要求用戶必 須是dba 角色或是有select any table 系統(tǒng)權(quán)限。 例如：當(dāng)用system 用戶查詢數(shù)據(jù)字典視圖dba_tables 時(shí)，會返回system，sys， scott方案所對應(yīng)的數(shù)據(jù)庫表, select table_name from dba_tables; 上機(jī)題4 通過查詢dba_users 可以顯示所有數(shù)據(jù)庫用戶的詳細(xì)信息； select * from dba_users ; 通過查詢數(shù)據(jù)字典視圖dba_sys_privs，可以顯示用戶所具有的系統(tǒng)權(quán)限； select * from

27、 dba_sys_privs where grantee=LISI; 通過查詢數(shù)據(jù)字典視圖dba_tab_privs，可以顯示用戶具有的對象權(quán)限； connect system/sys; select * from dba_tab_privs where grantee=LISI; 通過查詢數(shù)據(jù)字典dba_col_privs 可以顯示用戶具有的列權(quán)限； select * from dba_col_privs where grantee=LISI; 通過查詢數(shù)據(jù)庫字典視圖dba_role_privs 可以顯示用戶所具有的角色。 select * from dba_role_pri

28、vs where grantee=ZHANGSAN; //要查看scott 具有的角色，可查詢dba_role_privs； select * from dba_role_privs where grantee=SCOTT; //查詢orale 中所有的系統(tǒng)權(quán)限，一般是dba select * from system_privilege_map order by name; //查詢oracle 中所有對象權(quán)限，一般是dba select distinct privilege from dba_tab_privs; //查詢oracle 中所有的角色，一般是dba select

29、 * from dba_roles; //查詢數(shù)據(jù)庫的表空間 select tablespace_name from dba_tablespaces; 上機(jī)題5 1、如何查詢一個(gè)角色包括的權(quán)限？ a.一個(gè)角色包含的系統(tǒng)權(quán)限 select * from dba_sys_privs where grantee=角色名 select * from dba_sys_privs where grantee=MYROLE; select * from role_sys_privs where role=角色名 b.一個(gè)角色包含的對象權(quán)限 select * from dba_tab_

30、privs where grantee=角色名; select * from role_tab_privs where role=角色名; select * from dba_tab_privs where grantee=MYROLE; 2、oracle 究竟有多少種系統(tǒng)角色？ select * from dba_roles; 3、如何查看某個(gè)用戶，具有什么樣的角色？ select * from dba_role_privs where grantee=用戶名; select * from dba_role_privs where grantee=ZHANGSAN; 4、

31、顯示當(dāng)前用戶可以訪問的所有數(shù)據(jù)字典視圖。 select * from dict where comments like %grant%; 5、顯示當(dāng)前數(shù)據(jù)庫的全稱n select * from global_name; 實(shí)驗(yàn)四 PL-SQL語言設(shè)計(jì) 實(shí)驗(yàn)?zāi)康? 在oracle下熟練掌握PL-SQL語言的使用，包括語言語法、例外處理、函數(shù)，包等。 實(shí)驗(yàn)要求 1、基本配置：Intel PentiumIII以上級別的CPU，大于512MB的內(nèi)存。 2、軟件要求：Window XP操作系統(tǒng)，ORACLE 9i 3、實(shí)驗(yàn)學(xué)時(shí)：4學(xué)時(shí)。 4、實(shí)驗(yàn)報(bào)告。

32、實(shí)驗(yàn)準(zhǔn)備 編寫規(guī)范n 1.注釋 單行注釋 -- select * from emp where empno=7788; --取得員工信息 多行注釋 /*...*/來劃分 2.標(biāo)志符號的命名規(guī)范 1).當(dāng)定義變量時(shí)，建議用v_作為前綴v_sal 2).當(dāng)定義常量時(shí)，建議用c_作為前綴c_rate 3).當(dāng)定義游標(biāo)時(shí)，建議用_cursor 作為后綴emp_cursor 4).當(dāng)定義例外時(shí)，建議用e_作為前綴e_error 3、pl/sql 塊：塊(block)是pl/sql 的基本程序單元，編寫pl/sql 程序?qū)嶋H上就是編寫pl/sql 塊，要完成相對簡單的應(yīng)

33、用功能，可能只需要編寫一個(gè)pl/sql 塊，但是如果想要實(shí)現(xiàn)復(fù)雜的功能，可能需要在一個(gè)pl/sql 塊中嵌套其它的pl/sql 塊。 pl/sql 塊由三個(gè)部分構(gòu)成：定義部分，執(zhí)行部分，例外處理部分： declare /*定義部分——定義常量、變量、游標(biāo)、例外、復(fù)雜數(shù)據(jù)類型*/ begin /*執(zhí)行部分——要執(zhí)行的pl/sql 語句和sql 語句*/ exception /*例外處理部分——處理運(yùn)行的各種錯(cuò)誤*/ end; pl/sql 塊的實(shí)例(1) 實(shí)例1-只包括執(zhí)行部分的pl/sql 塊n Sql 代碼 set serveroutput on --打開輸

34、出選項(xiàng) begin dbms_output.put_line(hello); end; 相關(guān)說明： dbms_output 是oracle 所提供的包(類似java 的開發(fā)包)，該包包含一些過程， put_line 就是dbms_output 包的一個(gè)過程。 pl/sql 塊的實(shí)例(2) 實(shí)例2-包含定義部分和執(zhí)行部分的pl/sql 塊n Sql 代碼 declare v_ename varchar2(5); begin select ename into v_ename from emp where empno=&aa; dbms_outp

35、ut.put_line(雇員名：||v_ename); exception when no_data_found then dbms_output.put_line(朋友，你的編號輸入有誤！); end; pl/sql 塊的實(shí)例(3)： 包含定義部分，執(zhí)行部分和例外處理部分n 為了避免pl/sql 程序的運(yùn)行錯(cuò)誤，提高pl/sql 的健壯性，應(yīng)該對可能的錯(cuò)誤 進(jìn)行處理，這個(gè)很有必要。 a.比如在實(shí)例2 中，如果輸入了不存在的雇員號，應(yīng)當(dāng)做例外處理。 b.有時(shí)出現(xiàn)異常，希望用另外的邏輯處理， 我們看看如何完成1 的要求。 oracle 事先預(yù)定義了一些例外，no_d

36、ata_found 就是找不到數(shù)據(jù)的例外。 declare --定義變量 v_ename varchar2(5); v_sal number(7,2); begin --執(zhí)行部分 select ename,sal into v_ename,v_sal from emp where empno=&a --在控制臺顯示用戶名 dbms_output.put_line(用戶名是：||v_ename|| 工資：||v_sal); --異常處理 exception when no_data_found then dbms_output.put_line(

37、朋友，你的編號輸入有誤！)； end; 函數(shù) 根據(jù)輸入雇員的姓名，返回該雇員的年薪 SET SERVEROUTPUT ON DECLARE annual_salazy emp.sal%type; name emp.ename%type begin select sal*12+nvl(comm, 0) into annual_salazy from emp where ename=&name; dbms_output.put_line(&name||的年薪是||annual_salazy); end; / 包：用于在邏輯上組合過程和函數(shù)

38、，它由包規(guī)范和包體兩部分組成。 我們可以使用create package 命令來創(chuàng)建包。 實(shí)例： --創(chuàng)建一個(gè)包sp_package --聲明該包有一個(gè)過程update_sal --聲明該包有一個(gè)函數(shù)annual_income Sql 代碼 create package sp_package is procedure update_sal(name varchar2, newsal number); function annual_income(name varchar2) return number; end; 包的規(guī)范只包含了過程和函數(shù)的說明，但是沒有過程和函數(shù)的實(shí)現(xiàn)

39、代碼。包體 用于實(shí)現(xiàn)包規(guī)范中的過程和函數(shù)。 create or replace package sp_package is procedure update_sal(name varchar2, newsal number); function annual_income(name varchar2) return number; end; 建立包體可以使用create package body 命令 --給包sp_package 實(shí)現(xiàn)包體 Sql 代碼 create or replace package body sp_package is procedure upd

40、ate_sal(name varchar2, newsal number) is begin update emp set sal = newsal where ename = name; end; function annual_income(name varchar2) return number is annual_salary number; begin select sal * 12 + nvl(comm, 0) into annual_salary from emp where ename = name; return annual_salary; e

41、nd; end;/ 如何調(diào)用包的過程或是函數(shù) 當(dāng)調(diào)用包的過程或是函數(shù)時(shí)，在過程和函數(shù)前需要帶有包名，如果要訪問其它方 案的包，還需要在包名前加方案名。 如：call sp_package.update_sal(SCOTT, 1500); / create or replace package body sp_package is procedure update_sal(name varchar2, newsal number) is begin update emp set sal = newsal where ename = name; end; function

42、 annual_income(name varchar2) return number is annual_salary number; begin select sal * 12 + nvl(comm, 0) into annual_salary from emp where ename = name; return annual_salary; end; end; / pl/sql語句塊 下面以輸入員工號，顯示雇員姓名、工資、個(gè)人所得稅(稅率為0.03)為例。說 明變量的使用，看看如何編寫。 Sql 代碼 DECLARE c_tax_rate num

43、ber(3,2):=0.03; v_ename emp.ename%type; v_sal emp.sal%type; v_tax_sal number(7,2); begin select ename,sal into v_ename,v_sal from emp where empno=&n; v_tax_sal := v_sal*c_tax_rate; dbms_output.put_line(姓名是：||v_ename||工資：||v_sal|| 交稅：||v_tax_sal); end; / pl/sql 的進(jìn)階--控制結(jié)構(gòu) 請編寫一個(gè)過程，可以輸入用戶名

44、，并循環(huán)添加10 個(gè)用戶到users 表中，用戶 編號從1 開始增加。 Sql 代碼 create or replace procedure sp_pro6(spName varchar2) is --定義 :=表示賦值 v_num number:=1; begin loop insert into users values(v_num,spName); --判斷是否要退出循環(huán) exit when v_num=10; --自增 v_num:=v_num+1; end loop; end; / 例外處理 oracle 將例外分為預(yù)定義例

45、外，非預(yù)定義例外和自定義例外三種。 預(yù)定義例外用于處理常見的oracle 錯(cuò)誤 非預(yù)定義例外用于處理預(yù)定義例外不能處理的例外 自定義例外用于處理與oracle 錯(cuò)誤無關(guān)的其它情況 預(yù)定義例外 a.預(yù)定義例外 case_not_found 在開發(fā)pl/sql 塊中編寫case 語句時(shí)，如果在when 子句中沒有包含必須的條件分支，就會觸發(fā)case_not_found 的例外： b. 預(yù)定義例外 cursor_already_open 當(dāng)重新打開已經(jīng)打開的游標(biāo)時(shí)，會隱含的觸發(fā)例外cursor_already_open c. 預(yù)定義例外 dup_val_on_index 在唯

46、一索引所對應(yīng)的列上插入重復(fù)的值時(shí)，會隱含的觸發(fā)例外 d. 預(yù)定義例外 invalid_cursorn 當(dāng)試圖在不合法的游標(biāo)上執(zhí)行操作時(shí)，會觸發(fā)該例外 e. 預(yù)定義例外 invalid_number 當(dāng)輸入的數(shù)據(jù)有誤時(shí)，會觸發(fā)該例外 f. 預(yù)定義例外 no_data_found 當(dāng)執(zhí)行select into 沒有返回行，就會觸發(fā)該例外 1. declare 2. v_sal emp.sal%type; 3. begin 4. select sal into v_sal from emp 5. when ename=&name; 6. exception 7. when

47、no_data_found then 8. dbms_output.put_line(不存在該員工)； 9. end; g. 預(yù)定義例外 too_many_rows 當(dāng)執(zhí)行select into 語句時(shí)，如果返回超過了一行，則會觸發(fā)該例外 h. 預(yù)定義例外 value_error 當(dāng)在執(zhí)行賦值操作時(shí)，如果變量的長度不足以容納實(shí)際數(shù)據(jù)，則會觸發(fā)該例外 i.login_denied 當(dāng)用戶非法登錄時(shí)，會觸發(fā)該例外 j.not_logged_on 如果用戶沒有登錄就執(zhí)行dml 操作，就會觸發(fā)該例外 k.storage_error 如果超過了內(nèi)存空間或是內(nèi)存被損壞，就觸發(fā)該例外

48、 l.timeout_on_resource 如果oracle 在等待資源時(shí)，出現(xiàn)了超時(shí)就觸發(fā)該例外 上機(jī)題1 設(shè)計(jì)一個(gè)函數(shù)：輸入部門編號，返回該部門的人數(shù)。 create or replace function sum_people(deptno number) return number is countnumber number; begin select COUNT(empno) into countnumber from emp where deptno=deptno; return countnumber; end; / Variable co

49、untpeople number; call sum_people(20) into :countpeople; print countpeople 上機(jī)題2. 編寫一個(gè)pl/sql 塊包含定義部分、執(zhí)行部分和異常處理部分，完成以下功能： 根據(jù)職工編號輸出該職工的姓名、薪水和所屬部門名稱；如果輸入不存在的職工編號，則輸出報(bào)錯(cuò)信息。 SET SERVEROUTPUT ON declare v_ename emp.ename%type; v_sal emp.sal%type; v_dname dept.dname%type; begin select ename,sa

50、l,dname into v_ename,v_sal ,v_dname from emp,dept where emp.deptno=dept.deptno and empno=&n; dbms_output.put_line(姓名：||v_ename||工資：||v_sal|| 所屬部門名稱：||v_dname); exception when NO_DATA_FOUND then dbms_output.put_line(捕獲到了NO_DATA FOUND異常); dbms_output.put_line(該職工編號不存在); END; / 上機(jī)題3.創(chuàng)建一個(gè)包sp_pac

51、kage，該包有一個(gè)過程和一個(gè)函數(shù)annual_income，其中update_sal就是同問題1的過程，而annual_income是根據(jù)職工號求他的年收入。 create or replace package sp_package is procedure Update_Sal(spempno number,newsal number); function annual_income(spempno number)return number; end; create or replace package body sp_package is procedure Upd

52、ate_Sal(spempno number,newsal number) is begin update emp set sal = newSal where empno = spempno; end; function annual_income(spempno number)return number is annual_salary number; begin select sal * 12 + nvl(comm, 0) into annual_salary from emp where empno = spempno; return annual_salar

53、y; end; end; 上機(jī)題4. 編寫一個(gè)pl/sql 塊包含定義部分、執(zhí)行部分和異常處理部分，完成以下功能： 根據(jù)部門編號輸出該部門所有員工的姓名、薪水；如果輸入不存在的部門編號，則輸出報(bào)錯(cuò)信息。 實(shí)驗(yàn)五 游標(biāo)設(shè)計(jì) 實(shí)驗(yàn)?zāi)康? 在oracle下熟練掌握PL-SQL語言中游標(biāo)的定義和使用。 實(shí)驗(yàn)要求 1、基本配置：Intel PentiumIII以上級別的CPU，大于512MB的內(nèi)存。 2、軟件要求：Window XP操作系統(tǒng)，ORACLE 9i 3、實(shí)驗(yàn)學(xué)時(shí)：4學(xué)時(shí)。 4、實(shí)驗(yàn)報(bào)告。 實(shí)驗(yàn)

54、準(zhǔn)備 游標(biāo)變量定義 當(dāng)定義游標(biāo)時(shí)需要指定相應(yīng)的select 語句，如需要利用游標(biāo)修改數(shù)據(jù),則要如下定義: declare cursor emp_cursor is select comm from emp for update of comm; 上機(jī)題1.請使用pl/sql 編寫一個(gè)程序塊，從鍵盤輸入部門號，顯示該部門所有員工姓名和工資。 上機(jī)題2.請使用pl/sql 編寫一個(gè)程序塊，從鍵盤輸入部門號，顯示該部門所有員工姓名和工資。如果某個(gè)員工的工資低于200 元，就添加100 元。 declare cursor emp_cursor is select

55、 comm from emp for update of comm; v_ename emp.ename%type; v_sal emp.sal%type; begin --執(zhí)行 open emp_cursor for select ename,sal from emp where deptno=&no ; --循環(huán)取出 loop fetch emp_cursor into v_ename,v_sal; --判斷是否test_cursor 為空 exit when emp_cursor %notfound; dbms_output.put_line(名字：|

56、|v_ename|| 工資：||v_sal); end loop; end;/ 上機(jī)題3. 編寫一個(gè)程序塊，更改所有雇員的補(bǔ)助：如果雇員的補(bǔ)助大于0就在原來的基礎(chǔ)上增加100；如果補(bǔ)助等于0就把補(bǔ)助設(shè)為200；如果是空值按照等于0處理。 set serveroutput on; declare cursor emp_comm is select comm from emp for update of comm; v_comm m%type; v_ename emp.ename%type; begin for emp_r in emp_comm loop if emp

57、_m>0 then v_comm:=emp_m+100; elsif emp_m is null then v_comm:=0; else v_comm:=200; end if; update emp set comm=v_comm where current of emp_comm; end loop; end; / 實(shí)驗(yàn)六 存儲過程 實(shí)驗(yàn)?zāi)康? 在oracle下熟練掌握PL-SQL語言中存儲過程的定義和使用。 實(shí)驗(yàn)要求 1、基本配置：Intel PentiumIII以

58、上級別的CPU，大于512MB的內(nèi)存。 2、軟件要求：Window XP操作系統(tǒng)，ORACLE 9i 3、實(shí)驗(yàn)學(xué)時(shí)：4學(xué)時(shí)。 4、實(shí)驗(yàn)報(bào)告。 實(shí)驗(yàn)準(zhǔn)備 存儲過程定義 create or replace procedure procedure_name is begin end 上機(jī)題1.請編寫一個(gè)過程，可以輸入雇員編號，顯示該雇員的姓名\薪水。 核心參考代碼: open emp_cursor for select ename,sal from emp where deptno=v_empno; --循環(huán)取出 loop fetch emp_cursor i

59、nto v_ename,v_sal; --判斷是否test_cursor 為空 exit when emp_cursor %notfound; dbms_output.put_line(名字：||v_ename|| 工資：||v_sal); end loop; 上機(jī)題2. 編寫一個(gè)存儲過程，完成以下功能: 根據(jù)輸入的部門編號,返回該部門的所有職工的平均工資,并寫出調(diào)用指令。 上機(jī)題3. 編寫一個(gè)存儲過程，更改所有雇員的補(bǔ)助：如果雇員的補(bǔ)助大于0就在原來的基礎(chǔ)上增加100；如果補(bǔ)助等于0就把補(bǔ)助設(shè)為200；如果是空值按照等于0處理。 實(shí)

60、驗(yàn)七 觸發(fā)器的設(shè)計(jì) 實(shí)驗(yàn)?zāi)康? 熟練掌握Oracle觸發(fā)器的設(shè)計(jì)。 實(shí)驗(yàn)要求 1、基本配置：Intel PentiumIII以上級別的CPU，大于512MB的內(nèi)存。 2、軟件要求：Window XP操作系統(tǒng)，ORACLE 9i 3、實(shí)驗(yàn)學(xué)時(shí)：4學(xué)時(shí)。 4、實(shí)驗(yàn)報(bào)告。 實(shí)驗(yàn)準(zhǔn)備 1.DML除觸發(fā)器的語法： CREATE[ORREPLACE]TRIGGERtrigger_name {BEFORE|AFTER} {INSERT|DELETE|UPDATE[OFcolumn[,column…]]} [OR{INSERT|DELETE|UPDATE[OFcolu

61、mn[,column…]]}...] ON[schema.]table_name|[schema.]view_name [FOREACHROW] [WHENcondition] PL/SQL_BLOCK|CALLprocedure_name; 例1: 建立一個(gè)觸發(fā)器, 當(dāng)職工表 emp 表被刪除一條記錄時(shí)，把被刪除記錄寫到職工表刪除日志表中去。 CREATETABLEemp_hisASSELECT*FROMEMPWHERE1=2; CREATEORREPLACETRIGGERtr_del_emp BEFOREDELETE--指定觸發(fā)時(shí)機(jī)為刪除操作前觸發(fā) ONscott.e

62、mp FOREACHROW--說明創(chuàng)建的是行級觸發(fā)器 BEGIN --將修改前數(shù)據(jù)插入到日志記錄表del_emp,以供監(jiān)督使用。 INSERTINTOemp_his(deptno,empno,ename,job,mgr,sal,comm,hiredate) VALUES(:old.deptno,:old.empno,:old.ename,:old.job,:old.mgr,:old.sal,:m,:old.hiredate); END; 例2：限制對Dept表修改（包括INSERT,DELETE,UPDATE）的時(shí)間范圍，即不允許在非工作時(shí)間修改Dept表。 CREAT

63、EORREPLACETRIGGERtr_dept_time BEFOREINSERTORDELETEORUPDATE ONDept BEGIN IF(TO_CHAR(sysdate,DAY)IN(星期六,星期日))OR(TO_CHAR(sysdate,HH24:MI)NOTBETWEEN08:30AND18:00)THEN RAISE_APPLICATION_ERROR(-20001,不是上班時(shí)間，不能修改Dept表); ENDIF; END; 2、ORACLE10G提供的系統(tǒng)事件觸發(fā)器可以在DDL或數(shù)據(jù)庫系統(tǒng)上被觸發(fā)。DDL指的是數(shù)據(jù)定義語言，如CREATE 、ALTER

64、及DROP 等。而數(shù)據(jù)庫系統(tǒng)事件包括數(shù)據(jù)庫服務(wù)器的啟動(dòng)或關(guān)閉，用戶的登錄與退出、數(shù)據(jù)庫服務(wù)錯(cuò)誤等。創(chuàng)建系統(tǒng)觸發(fā)器的語法如下： CREATEORREPLACETRIGGER[sachema.]trigger_name {BEFORE|AFTER} {ddl_event_list|database_event_list} ON{DATABASE|[schema.]SCHEMA} [WHENcondition] PL/SQL_block|CALLprocedure_name; 下面給出系統(tǒng)觸發(fā)器的種類和事件出現(xiàn)的時(shí)機(jī)（前或后）： 事件 允許的時(shí)機(jī) 說明 STARTUP AFT

65、ER 啟動(dòng)數(shù)據(jù)庫實(shí)例之后觸發(fā) SHUTDOWN BEFORE 關(guān)閉數(shù)據(jù)庫實(shí)例之前觸發(fā)（非正常關(guān)閉不觸發(fā)） SERVERERROR AFTER 數(shù)據(jù)庫服務(wù)器發(fā)生錯(cuò)誤之后觸發(fā) LOGON AFTER 成功登錄連接到數(shù)據(jù)庫后觸發(fā) LOGOFF BEFORE 開始斷開數(shù)據(jù)庫連接之前觸發(fā) CREATE BEFORE，AFTER 在執(zhí)行CREATE語句創(chuàng)建數(shù)據(jù)庫對象之前、之后觸發(fā) DROP BEFORE，AFTER 在執(zhí)行DROP語句刪除數(shù)據(jù)庫對象之前、之后觸發(fā) ALTER BEFORE，AFTER 在執(zhí)行ALTER語句更新數(shù)據(jù)庫對象之前、之后觸發(fā) DDL BEFORE，AFTER 在執(zhí)行大多數(shù)DDL語句之前、之后觸發(fā) GRANT BEFORE，AFTER 執(zhí)行GRANT語句授予權(quán)限之前、之后觸發(fā) REVOKE BEFORE，AFTER 執(zhí)行REVOKE語句收權(quán)限之前、之后觸犯發(fā) RENAME BEFORE，AFTER 執(zhí)行RENAME語句更改數(shù)據(jù)庫對象名稱之前、之后觸犯發(fā) AUDIT / NOAUDI