《3493949142《数据库原理及应用》实验指导书.doc》由会员分享，可在线阅读，更多相关《3493949142《数据库原理及应用》实验指导书.doc（24页珍藏版）》请在三一办公上搜索。
1、数据库原理及应用-ORACLE实验指导书信息科学与工程学院 2014年9月作者：何小卫目 录实验一ORACLE平台下基本SQL语言的应用实验二ORACLE基本操作与用户权限基本管理实验三 数据字典视图实验四 PL-SQL语言设计实验五 存储过程实验六 游标实验七 数据库触发器实验一：ORACLE平台下基本SQL语言的应用实验目的在oracle下熟练运用SQL语言来完成基本表的管理、索引的建立和删除、数据查询、数据更新、视图建立和删除等等操作。实验要求1、 件基本配置：Intel PentiumIII以上级别的CPU，大于512MB的内存。2、 软件要求：Window XP操作系统，ORACLE
2、 9i3、 实验学时：4学时。4、 实验报告。实验准备ORACLE 9i实验内容上机题1：利用Create Table 创建scott用户下的3个表，包括每个表的码和外码的定义。EmpC员工表字段内容如下:empno员工号（主码），ename员工姓名，job工作，mgr上级编号（外码），hiredate受雇日期，sal薪金，comm佣金，deptno部门编号（外码）；create table Empc(empno number,ename varchar(10),job varchar(20),mgr number,hiredate date,sal number,comm number,de
3、ptno number,primary key(empno),foreign key(mgr) references Empc(empno);alter table empc add constraint deptno foreign key(deptno) references deptc(deptno);DeptC部门表字段内容如下：deptno 部门号（主码），dname部门名称，loc办公地方create table Deptc(deptno number,ename varchar(10),loc varchar(10),primary key(deptno);BonusC奖金表字段
4、内容如下：ename员工姓名，job 工作名称，sal薪金，comm佣金create table bounsc(ename varchar(10),job varchar(10),sal number,comm number);上机题2：用Alter Table向表中BonusC增加comm的约束为0-3000元之间。alter table bounsc add constraint comm check(comm between 0 and 3000);上机题3：将一个员工记录插入到Emp中（每个属性值自定）；insert into emp values(102,zhangsan,manag
5、er,102,to_date(2010-05-06,yyyy-mm-dd),null,null,10);上机题4：删除所有张三职工记录；Delete from emp where ename=zhangsan;上机题5：创建如下的视图：创建“research”部门的职工情况，包含如下字段的视图view_research：包括empno、ename、sal、comm。create view research as select empno,ename,sal,comm from emp;上机题6：使用SELECT语句创建查询：1、列出至少有一个员工的全部部门及人数。select count(em
6、pno),deptno from emp group by deptno having count(ename)1; 2、列出薪金比“SMITH”多的全部员工。select ename,sal from emp where sal(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.en
7、ame from emp a,emp b where a.mgr=b.empno and a.hiredate1500;8、列出在部分门“SALES”（贩卖部）做事的员工的姓名，假定不知道贩卖部的部门编号。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”从事相同工作的全部员工。
8、select emp.* from emp where job in(select job from emp where ename=SCOTT); 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、列
9、出在每个部门做事的员工数量 、均匀收入、平均做事限期。 select count(ename),avg(sal),floor(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.deptnob.deptno;16、列出全
10、部部门的具体信息和部门人数。 select * from dept a left join(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、列出各个部门的MANAGER（司理）的最低薪金。 select min(sal) from emp where job=MANAGERgroup by deptno;19、列出全部员工的年收入 ,按年薪从低到高排序。 select ename
11、,sal*12 a from emp order by a asc;20、找出佣金高于薪金的60%的员工. select ename from emp where commsal*0.6;21、找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK),既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料. 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
12、、找出不收取佣金或收取的佣金低于100的员工. select * from emp where comm100 or comm=0;23、找出各月倒数第3天受雇的所有员工.select * from emp where hiredate=last day(hiredate)-2; 24、显示正好为5个字符的员工的姓名. select ename from emp where ename like ;25、显示不带有R的员工的姓名select ename from emp where ename not like%R%;. 26、显示所有员工姓名的前三个字符. select substr(ena
13、me,1,3)from emp;27、显示所有员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序. select ename,sal,job from emp order by job desc,sal;28、对于每个员工,显示其加入公司的天数. select ename,round(sysdate-hiredate) emp_date from emp;29、显示姓名字段的任何位置包含A的所有员工的姓名. select ename from emp where ename like%A%;30、查询和“李建国”是同一部门的职工姓名。（使用子查询）select ename fro
14、m emp where deptno=(select deptno from emp where ename=李建国);31、查询“Research”和”Manager”部门的职工姓名（UNION）select ename from emp where job=RESEARCH or job=MANAGER;实验二ORACLE基本操作与用户权限基本管理实验目的熟悉ORACLE环境下基本操作和基本用户权限管理实验要求1、 硬件基本配置：Intel PentiumIII以上级别的CPU，大于512MB的内存。2、 软件要求：Window XP操作系统，ORACLE 9i 3、 实验学时：2学时4、
15、 实验报告实验内容上机题1：连接命令connect username/password服务名as sysdba, as sysoper, 当用特权用户连接时候，必须带上as sysdba或as sysoper。Sys均可以；conn System/managerdisconnect：断开与当前数据库的连接；SQL/plus中运用OKpassword说明:该命令用于修改用户的密码，如果要想修改其它用户的密码，需要用sys/system 登录。show user 说明: 显示当前用户名exit：该命令会断开与数据库的连接，同时会退出sql*plusdisconnect;connect system
16、/sys as dydsba;show user;上机题2：创建用户和系统权限create user xiaozhang identified by xiao;grant create session to xiaozhang;grant create table to xiaozhang;grant unlimited tablespace to xiaozhang;select * from user_sys_privs;上机题3：限制用户和密码修改用户加锁：alter user 用户名 account lock用户解锁：alter user 用户名 account unlock用户口令即
17、刻失效：alter user 用户名 password expire删除用户：drop user 用户名 cascadecascade 用在当被删除的用户下还有未删除的对象（如一些表）时，强制级联删除。它表示删除用户所有对象。密码修改：用SYS (或SYSTEM)用户登录: CONN SYS/PASS_WORD AS SYSDBA;使用如下语句修改用户的密码: ALTER USER user_name IDENTIFIED BY newpass;alter user xiaozhang account lock;alter user xiaozhang account unlock;alter
18、 user xiaozhang password expire;alter user xiaozhang identified by zhang;上机题4：对象权力的管理: Grant,Revoke运用grant select on mytab to xiaozhang;grant update,select,delete on mytab to xiaozhang;grant all on mytab to xiaozhang;connect scott/tiger;grant select on emp to xiaozhang with grant option;connect xiao
19、zhang/zhang;select * from scott.emp;grant select on scott.emp to lisi;connect lisi/lisi;select * from scott.emp;上机题5：权限的传递sys用户把一些系统权限授权给xiaozhang用户.grant alter any table to xiaozhang with admin option查看当前用户的对象权限：select * from user_tab_privsgrant unlimited tablespace to xiaozhang with admin option;c
20、reate user lisi identified by lisi;grant create session to lisi;grant unlimited tablespace to lisi;select * from user_tab_privs;上机题6：角色管理在sys下创建角色：create role myrole;给角色添加权限：grant create session to myrole;grant create table to myrole;创建用户：create user zhangsan;grant myrole to zhangsan;/赋予以上的两个权限给zhan
21、gsancreate role myrole;grant create session to myrole;grant create table to myrole;create user zhangsan identified by zhang;grant myrole to zhangsan;实验三：数据字典视图实验目的在oracle下熟练掌握数据字典视图的应用。实验要求1、基本配置：Intel PentiumIII以上级别的CPU，大于512MB的内存。2、软件要求：Window XP操作系统，ORACLE 9i3、实验学时：2学时。4、实验报告。实验准备数据字典是oracle 数据库中
22、最重要的组成部分，它提供了数据库的一些系统信息。数据字典记录了数据库的系统信息，它是只读表和视图的集合，数据字典的所有者为sys 用户。用户只能在数据字典上执行查询操作（select 语句），而其维护和修改是由系统自动完成的。实验内容上机题1 user_tablesuser_tables；用于显示当前用户所拥有的所有表，它只返回用户所对应方案的所有表比如：select table_name from user_tables;上机题2 all_tables用于显示当前用户可以访问的所有表，它不仅会返回当前用户方案的所有表，还会返回当前用户可以访问的其它方案的表：比如：select table_n
23、ame from all_tables;上机题3 dba_tables它会显示所有方案拥有的数据库表。但是查询这种数据库字典视图，要求用户必须是dba 角色或是有select any table 系统权限。例如：当用system 用户查询数据字典视图dba_tables 时，会返回system，sys，scott方案所对应的数据库表, select table_name from dba_tables;上机题4通过查询dba_users 可以显示所有数据库用户的详细信息；select * from dba_users ;通过查询数据字典视图dba_sys_privs，可以显示用户所具有的系统权
24、限；select * from dba_sys_privs where grantee=LISI;通过查询数据字典视图dba_tab_privs，可以显示用户具有的对象权限；connect system/sys;select * from dba_tab_privs where grantee=LISI;通过查询数据字典dba_col_privs 可以显示用户具有的列权限；select * from dba_col_privs where grantee=LISI;通过查询数据库字典视图dba_role_privs 可以显示用户所具有的角色。select * from dba_role_pri
25、vs where grantee=ZHANGSAN;/要查看scott 具有的角色，可查询dba_role_privs；select * from dba_role_privs where grantee=SCOTT;/查询orale 中所有的系统权限，一般是dbaselect * from system_privilege_map order by name;/查询oracle 中所有对象权限，一般是dbaselect distinct privilege from dba_tab_privs;/查询oracle 中所有的角色，一般是dbaselect * from dba_roles;/查
26、询数据库的表空间select tablespace_name from dba_tablespaces;上机题51、如何查询一个角色包括的权限？a.一个角色包含的系统权限select * from dba_sys_privs where grantee=角色名select * from dba_sys_privs where grantee=MYROLE;select * from role_sys_privs where role=角色名b.一个角色包含的对象权限select * from dba_tab_privs where grantee=角色名;select * from role_
27、tab_privs where role=角色名;select * from dba_tab_privs where grantee=MYROLE;2、oracle 究竟有多少种系统角色？select * from dba_roles;3、如何查看某个用户，具有什么样的角色？select * from dba_role_privs where grantee=用户名;select * from dba_role_privs where grantee=ZHANGSAN;4、显示当前用户可以访问的所有数据字典视图。select * from dict where comments like %g
28、rant%;5、显示当前数据库的全称nselect * from global_name;实验四 PL-SQL语言设计实验目的在oracle下熟练掌握PL-SQL语言的使用，包括语言语法、例外处理、函数，包等。实验要求1、基本配置：Intel PentiumIII以上级别的CPU，大于512MB的内存。2、软件要求：Window XP操作系统，ORACLE 9i3、实验学时：4学时。4、实验报告。实验准备编写规范n1.注释单行注释 - select * from emp where empno=7788; -取得员工信息多行注释 /*.*/来划分2.标志符号的命名规范1).当定义变量时，建议用
29、v_作为前缀v_sal2).当定义常量时，建议用c_作为前缀c_rate3).当定义游标时，建议用_cursor 作为后缀emp_cursor4).当定义例外时，建议用e_作为前缀e_error3、pl/sql 块：块(block)是pl/sql 的基本程序单元，编写pl/sql 程序实际上就是编写pl/sql 块，要完成相对简单的应用功能，可能只需要编写一个pl/sql 块，但是如果想要实现复杂的功能，可能需要在一个pl/sql 块中嵌套其它的pl/sql 块。pl/sql 块由三个部分构成：定义部分，执行部分，例外处理部分：declare/*定义部分定义常量、变量、游标、例外、复杂数据类型
30、*/begin/*执行部分要执行的pl/sql 语句和sql 语句*/exception/*例外处理部分处理运行的各种错误*/end;pl/sql 块的实例(1)实例1-只包括执行部分的pl/sql 块nSql 代码 set serveroutput on -打开输出选项 begin dbms_output.put_line(hello); end;相关说明：dbms_output 是oracle 所提供的包(类似java 的开发包)，该包包含一些过程，put_line 就是dbms_output 包的一个过程。pl/sql 块的实例(2)实例2-包含定义部分和执行部分的pl/sql 块nSq
31、l 代码declare v_ename varchar2(5); begin select ename into v_ename from emp where empno=&aa; dbms_output.put_line(雇员名：|v_ename);exceptionwhen no_data_found thendbms_output.put_line(朋友，你的编号输入有误！); end;pl/sql 块的实例(3)：包含定义部分，执行部分和例外处理部分n为了避免pl/sql 程序的运行错误，提高pl/sql 的健壮性，应该对可能的错误进行处理，这个很有必要。a.比如在实例2 中，如果输入
32、了不存在的雇员号，应当做例外处理。b.有时出现异常，希望用另外的逻辑处理，我们看看如何完成1 的要求。oracle 事先预定义了一些例外，no_data_found 就是找不到数据的例外。 declare -定义变量 v_ename varchar2(5); v_sal number(7,2); begin -执行部分 select ename,sal into v_ename,v_sal from emp where empno=&a -在控制台显示用户名 dbms_output.put_line(用户名是：|v_ename| 工资：|v_sal); -异常处理exceptionwhen n
33、o_data_found thendbms_output.put_line(朋友，你的编号输入有误！)；end;函数根据输入雇员的姓名，返回该雇员的年薪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; /包：用于在逻辑上组合过程和函
34、数，它由包规范和包体两部分组成。我们可以使用create package 命令来创建包。实例：-创建一个包sp_package-声明该包有一个过程update_sal-声明该包有一个函数annual_incomeSql 代码create package sp_package isprocedure update_sal(name varchar2, newsal number);function annual_income(name varchar2) return number;end;包的规范只包含了过程和函数的说明，但是没有过程和函数的实现代码。包体用于实现包规范中的过程和函数。crea
35、te or replace package sp_package isprocedure update_sal(name varchar2, newsal number);function annual_income(name varchar2) return number;end;建立包体可以使用create package body 命令-给包sp_package 实现包体Sql 代码create or replace package body sp_package isprocedure update_sal(name varchar2, newsal number)isbeginupd
36、ate emp set sal = newsal where ename = name;end;function annual_income(name varchar2) return number isannual_salary number;begin select sal * 12 + nvl(comm, 0) into annual_salary from emp where ename = name; return annual_salary; end;end;/如何调用包的过程或是函数当调用包的过程或是函数时，在过程和函数前需要带有包名，如果要访问其它方案的包，还需要在包名前加方案
37、名。如：call sp_package.update_sal(SCOTT, 1500);/create or replace package body sp_package isprocedure update_sal(name varchar2, newsal number)isbeginupdate emp set sal = newsal where ename = name;end;function annual_income(name varchar2) return number isannual_salary number;begin select sal * 12 + nvl(
38、comm, 0) into annual_salary from emp where ename = name; return annual_salary; end;end;/pl/sql语句块下面以输入员工号，显示雇员姓名、工资、个人所得税(税率为0.03)为例。说明变量的使用，看看如何编写。Sql 代码DECLAREc_tax_rate number(3,2):=0.03;v_ename emp.ename%type;v_sal emp.sal%type;v_tax_sal number(7,2);beginselect ename,sal into v_ename,v_sal from
39、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 的进阶-控制结构请编写一个过程，可以输入用户名，并循环添加10 个用户到users 表中，用户编号从1 开始增加。Sql 代码 create or replace procedure sp_pro6(spName varchar2) is -定义 :=表示赋值 v_num number:=1; begin loop insert into users v
40、alues(v_num,spName); -判断是否要退出循环 exit when v_num=10; -自增 v_num:=v_num+1; end loop;end;/例外处理oracle 将例外分为预定义例外，非预定义例外和自定义例外三种。预定义例外用于处理常见的oracle 错误非预定义例外用于处理预定义例外不能处理的例外自定义例外用于处理与oracle 错误无关的其它情况预定义例外a.预定义例外 case_not_found在开发pl/sql 块中编写case 语句时，如果在when 子句中没有包含必须的条件分支，就会触发case_not_found 的例外：b. 预定义例外 cur
41、sor_already_open当重新打开已经打开的游标时，会隐含的触发例外cursor_already_openc. 预定义例外 dup_val_on_index在唯一索引所对应的列上插入重复的值时，会隐含的触发例外d. 预定义例外 invalid_cursorn当试图在不合法的游标上执行操作时，会触发该例外e. 预定义例外 invalid_number当输入的数据有误时，会触发该例外f. 预定义例外 no_data_found当执行select into 没有返回行，就会触发该例外1. declare2. v_sal emp.sal%type;3. begin4. select sal i
42、nto v_sal from emp5. when ename=&name;6. exception7. when no_data_found then8. dbms_output.put_line(不存在该员工)；9. end;g. 预定义例外 too_many_rows当执行select into 语句时，如果返回超过了一行，则会触发该例外h. 预定义例外 value_error当在执行赋值操作时，如果变量的长度不足以容纳实际数据，则会触发该例外i.login_denied当用户非法登录时，会触发该例外j.not_logged_on如果用户没有登录就执行dml 操作，就会触发该例外k.st
43、orage_error如果超过了内存空间或是内存被损坏，就触发该例外l.timeout_on_resource如果oracle 在等待资源时，出现了超时就触发该例外上机题1 设计一个函数：输入部门编号，返回该部门的人数。create or replace function sum_people(deptno number)return number iscountnumber number;begin select COUNT(empno) into countnumber from emp where deptno=deptno;return countnumber; end; /Varia
44、ble countpeople number;call sum_people(20) into :countpeople;print countpeople上机题2. 编写一个pl/sql 块包含定义部分、执行部分和异常处理部分，完成以下功能： 根据职工编号输出该职工的姓名、薪水和所属部门名称；如果输入不存在的职工编号，则输出报错信息。 SET SERVEROUTPUT ONdeclarev_ename emp.ename%type;v_sal emp.sal%type;v_dname dept.dname%type;beginselect ename,sal,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);exceptionwhen NO_DATA_FOUND thendbms_out
