oracle大型数据库实验报告4.docx

oracle大型数据库实验报告4.docx

《oracle大型数据库实验报告4.docx》由会员分享，可在线阅读，更多相关《oracle大型数据库实验报告4.docx（12页珍藏版）》请在冰点文库上搜索。

oracle大型数据库实验报告4

五、思考题及其它

1、显示游标和隐式游标在用法上的区别？

2、raise语句应该放在PL/SQL块的哪个部分？

当在异常和oracle错误之间建立关联时，应该在哪个部分完成？

3、实验报告要求

本次实验书写两次，分别是

（一）和

（二）、（三）和（四）

实验六：

过程、函数、触发器

实验学时：

4

实验类型：

设计型

一．实验目的及要求

1．掌握过程的创建与维护；

2．掌握函数的创建与维护；

3．掌握触发器的创建于维护；

4．了解包的使用。

二、实验主要内容

1．过程的创建与维护；

2．函数的创建与维护；

3．触发器的创建于维护；

4．包的使用。

三、实验仪器设备

在局域网环境下，有一台服务器和若干台客户机。

服务器成功安装Oracle11g数据库服务器（企业版），客户机成功安装Oracle11g客户端软件，网络服务配置正确，数据库和客户端正常工作。

四、实验内容与步骤

（一）、过程的创建与维护

1、编写一个过程，可以输入一个雇员名，如果该雇员的的职位为PRESIDENT就给他的工资增加1000，如果该雇员的职位为MANAGER，就给他的工资加500，其他职位的雇员工资增加200。

并在SQL*PLUS中调用该过程以修改FORD的工资。

2、创建一个过程avg_sal，用于输出emp表中某个部门的平均工资，并在PL/SQL匿名块中调用该过程输出20号部门的平均工资。

3、从雇员基本信息表（EMP）中统计各部门（DEPTNO）人数后，将结果打印出来。

（选做）

CREATEORREPLACEPROCEDURESUM_COUNTIS

--创建一个游标，查询dept所有的部门编号

Dept_cursor%ROWTYPE;--定义一个游标类型的变量

--定义一个变量来存储部门人数

BEGIN

--打开游标

LOOP

INTODept_cursor;--读取游标

SELECTCOUNT（*）INTOnumFROMemp

WHEREdeptno=Dept_cursor.deptno;

/*--填充代码，打印输出部门号及其对应的部门人数*/

EXITWHEN%NOTFOUND;--退出循环

ENDLOOP;

--关闭游标

COMMIT;

END;

--执行存储过程sum_count

（二）、函数的创建与维护

1、创建一个函数get_sum_salary，获取某部门的人数（out参数）和工资总和（返回值）。

并统计10号部门的人数及工资总和。

（三）、触发器的创建于维护

1、创建一个Before触发器tri_emp_sal，当进行update操作时，员工工资只能涨不能降，不允许删除员工记录，并修改sal和删除记录，查看相关信息。

2、创建INSTEADOF触发器。

首先创建一个视图myview,由于该视图是复合查询所产生的视图，所以不能执行DML语句。

根据用户对视图所插入的数据判断需要将数据插入到哪个视图基表中，然后对该基表执行插入操作（选作）。

（四）、包的使用（选做）

1、创建的包为DEMO_PKG,该包中包含一个记录变量DEPTREC、两个函数和一个过程。

实现对dept表的增加、删除与查询。

--程序包头的创建

CREATEORREPLACEPACKAGEDEMO_PKGIS

DEPTRECDEPT%ROWTYPE;

--Adddeptfunction声明...

--deletedeptfuntion声明...

--querydeptprocedure声明...

ENDDEMO_PKG;

/*包主体的创建方法，它实现上面所声明的包定义*/

CREATEORREPLACEPACKAGEBODYDEMO_PKGIS

--add_dept定义

--delete_dept定义

--定义query_dept

ENDDEMO_PKG;

/*对包内共有元素的调用格式为：

包名.元素名称

调用DEMO_PKG包内函数对dept表进行插入、查询和删除操作，

并通过DEMO_PKG包中的记录变量DEPTREC显示所查询到的数据库信息：

*/

五、思考题及其它

1、传递参数方式有哪些？

2、过程与函数的使用原则？

3、before、after、instesdof触发器分别用在什么场合？

4、实验报告要求

请从

（一）、

（二）、（三）各选一道题写到实验报告中。

实验七表、视图、索引、同义词、序列

实验学时：

5

实验类型：

设计型

一、实验目的

1、掌握SQL语言中表、索引、视图的创建及其使用方法。

2、掌握SQL语言中查看索引、同义词、序列生成器创建方法。

二、实验环境

1、硬件设备：

计算机局域网，服务器1台，客户机100台

2、软件系统：

WindowsXP客户机操作系统；Oracle11g服务端数据库系统，客户端工具。

三、实验内容及步骤

（一）表的创建、维护及其使用方法

1．创建表空间

（1）创建表空间名称为Medicine

要求：

表空间文件路径为‘D:

\oracle\oradate\mybase\Medicine.dbf’，大小为50M；采用本地化管理（LOCAL），盘曲扩展采用自动方式；同时采用手工段空间管理方式，并记录日志信息。

2．创建表结构

利用SQL*PLUS或PL/SQLdeveloper将下列各表建立到员工医疗保险系统数据库中。

表结构见附录员工医疗保险系统表1~表4所示。

3．查看表结构

（1）利用SQL*Plus从数据字典DBA_TAB_COLUMNS查看员工医疗保险系统所有表的字段信息。

（2）利用SQL*Plus从数据字典DBA_CONSTRAINTS查看员工医疗保险系统所有表的约束信息。

4．修改表结构

（1）利用SQL*Plus将表“staff”重新命名为“staff_学号后两位（如staff_12）”。

（2）利用SQL*Plus为“staff_学号后四位”表添加“ageINT”、“salaryNUMBER（5,2）”两个字段，利用DESC命令查看“staff_学号后两位”表的字段信息。

（3）利用SQL*Plus向“staff_学号后两位”表添加“sname”字段惟一性约束，从数据字典DBA_CONSTRAINTS查看“staff_学号后两位”表的约束信息。

（5）利用SQL*Plus删除“staff_学号后两位”表上“sname”字段惟一性约束，从数据字典DBA_CONSTRAINTS查看“staff_学号后两位”表的约束信息。

（6）利用SQL*Plus将“staff_学号后四位”表“sname”字段长度修改为30，利用DESC命令查看“staff_学号后两位”表的字段信息。

4．插入表数据（选做）

（1）分别利用SQL*Plus向员工医疗保险系统的每个表中插入记录。

记录插入数据过程中遇到的问题及解决方法，注意体会各种约束对插入数据的影响和表数据的插入顺序。

（2）利用SQL*Plus创建员工表2（“staff2”），向表中插入5条不同的记录，注意与员工表中数据必须不同，主要体现在主键员工编号上，执行下面的操作：

●用多行数据插入的方法将员工表2中数据插入到员工表，观察执行的结果。

●将命令再执行一次，观察执行的结果，分析产生的错误原因。

（3）利用SQL*Plus且使用表间数据复制的方法创建员工表3（“staff3”），将全部字段的值复制到员工表3，查看员工表3的内容，体会表间数据复制的含义。

（4）利用SQL*Plus且使用表间数据复制的方法创建员工表4（“staff4”），将部分字段的值复制到员工表4，查看员工表4的内容，比较员工表3和员工表4中数据的不同，进一步体会表间数据复制的含义。

5．删除表数据（选做）

（1）利用SQL*Plus删除某医院编号的医院信息，看能否成功。

查看就诊表数据，从原理上解释原因，同时记录数据删除过程中遇到的问题及解决方法，注意体会外键约束数据删除方式对删除数据的影响。

（2）利用ROLLBACK命令恢复所有数据。

6．删除表结构（选做）

（1）利用SQL*Plus删除员工表3，看能否成功。

从原理上解释原因，同时记录外键约束表删除顺序的影响。

（2）利用SQL*Plus删除员工表4，并立即释放表空间，并且不希望将其放置到回收站里，看能否成功。

从原理上解释原因，同时记录外键约束表删除顺序的影响。

（二）视图的创建、维护及其使用方法（必做）

1．创建视图

（1）利用SQL*Plus建立可以添加工作为‘CLERK’的雇员的视图vu_emp_clerk，包含列编号、姓名、工作，不允许添加其他工种的雇员信息。

测试：

向视图插入如下一条记录，能否插入成功，并分析原因

SQL>insertintovu_emp_clerk（empno,ename,job）

values（1234,'李四','SALESMAN'）;

（2）建立视图vu_sal_view显示工资高于部门平均工资的雇员姓名、工资和部门号。

能否向对该视图进行DML操作，并分析原因。

2．查看视图

（1）查看视图定义

SQL>descvu_emp_clerk;

SQL>selecttextfromuser_views

whereview_name=upper（‘vu_emp_clerk’）;

3．删除视图

（1）利用SQL*Plus删除“vu_emp_clerk”视图。

（三）索引的创建、维护及其使用方法（必做）

1．创建索引

（1）利用SQL*Plus为员工表的员工姓名、员工性别、出生年月排序，以员工姓名升序、员工性别降序、出生年月降序排列，索引名为“emp_info_index”。

2．查看索引

（1）利用SQL*Plus从DBA_INDEXES数据字典中查看“staff_info_index”索引的信息，并查看该索引列的顺序及状态。

3．删除索引

（1）利用SQL*Plus将“emp_info_index”索引删除

（四）同义词、序列的创建、维护及其使用方法（选做）

1．创建同义词

（1）利用SQL*Plus创建医保卡表（CARD）的同义词，名为“ybk”。

2．查询同义词

（1）利用SQL*Pluss查看同义词“ybk”。

3．删除同义词

（1）利用SQL*Plus删除同义词“ybk”。

4．创建序列

（1）利用SQL*Plus创建序列，该序列最大值无限制，最小值为“1”，步长为“10”，序列名为“ygbx_seq2”。

5．查询序列

（1）利用SQL*Plus查看同义词“ygbx_seq2”。

6．修改序列

（1）利用SQL*Plus修改序列“ygbx_seq2”，将该序列最大值设为“1000”。

7．删除序列

（1）利用SQL*Plus删除序列“ygbx_seq2”

实验八oracle用户权限管理与分配

实验学时：

3

实验类型：

设计型

一、实验目的及要求

1、掌握Oracle的用户的创建与维护；

2、掌握oracle数据库权限的分配与管理；

3、了解oracle数据库角色的创建与管理；

4、掌握oracle用资源配置文件PROFILE管理密码。

二、实验主要内容

1、用户的建立、修改、查看、删除操作。

2、权限的建立、修改、查看、删除操作。

3、角色的建立、修改、查看、删除操作。

4、资源配置文件PROFIL的建立、修改、查看、删除操作。

三、实验仪器设备

在局域网环境下，有一台服务器和若干台客户机。

服务器成功安装Oracle11g数据库服务器（企业版），客户机成功安装Oracle11g客户端软件，网络服务配置正确，数据库和客户端正常工作。

四、实验步骤

（一）用户创建与管理

1、创建用户。

①创建“姓名（英文）”用户，密码为“学号”，默认表空间为“users”，临时表空间为“temp”。

②利用SQL*Plus，创建“姓名（英文）_sql”用户，密码为“学号+sql”，该用户处于锁状态。

2、查看用户。

①利用SQL*Plus，从DBA_USERS数据字典中查看“姓名（英文）_sql”用户的信息，并查看该用户验证的方式。

②利用SQL*Plus，从DBA_USERS数据字典中查看“姓名（英文）_sql”用户的默认表空间和临时表空间的信息。

3、修改用户。

①利用SQL*Plus，修改“姓名（英文）_sql”用户，将该用户解锁，并将密码改为“sql+学号”。

（二）权限管理。

①授予“姓名（英文）”用户“CREATEANYTABLE”、“CREATEANYINDEX”、“ALTERANYTABLE”、“DROPANYTABLE”、“DROPANYINDEX”以及“CREATESESSION”系统权限，并用WITHADMINOPTION传递权限。

②利用SQL*Plu，将“姓名（英文）”用户“CREATEANYTABLE”和“CREATESESSION”系统权限授予“姓名（英文）_sql”用户。

③授予“姓名（英文）”用户对“SCOTT”模式中“emp”表的查看、修改、删除等对象权限。

④利用SQL*Plus，回收“姓名（英文）”用户在“SCOTT”模式中对“emp”表的查看、修改、删除等对象权限。

⑤利用SQL*Plus或iSQL*Plus，收回“学号_sql”用户的“CREATEANYTABLE”系统权限。

（三）角色的创建与管理。

1、创建角色

①创建“role+学号”角色，赋予该角色能对表、索引、存储过程进行基本操作的权限。

②利用SQL*Plus，创建“学号_sql”角色，该角色具有“createindex”系统权限，并将该角色赋予“姓名_sql”用户。

2、查看角色。

①查看“role+学号”角色所具有的所有权限（查看视图dba_sys_privs或role_sys_privs）。

②利用SQL*Plus，查看“学号_sql”角色所具有的所有权限。

3、修改角色。

①修改“role+学号”角色，增加对角色的基本操作，并收回存储过程和序列的操作权限。

②利用SQL*Plus或iSQL*Plus，修改“学号_sql”角色，收回“createindex”系统，而授予“SELECTANYTABLE”系统权限。

③给角色“学号_sql”设置密码“学号_sql”，然后设置该角色生效。

4、删除角色。

①删除“role+学号”角色。

②利用SQL*Plus，删除“学号_sql”角色。

5、删除用户。

①删除“姓名”用户。

②利用SQL*Plus，删除“姓名_sql”用户。

（四）资源配置文件PROFILE的建立、修改、查看、删除操作

1、创建PROFILE文件。

①利用SQL*Plus创建概要文件“学号_pro”，要求在此概要文件中登录失败次数为5，锁定天数为1，密码有效期为60天，宽限时间为3天，密码可重用的时间为10天。

2、查看PROFILE文件。

①利用SQL*Plus，从DBA_PROFILES数据字典中查看“学号_pro”概要文件的资源名称和资源值等信息。

②利用SQL*Plus，从查看“学号_pro”概要文件中锁定天数的值。

3、修改PROFILE文件。

①利用SQL*Plus或iSQL*Plus，修改“学号_pro”概要文件，失败次数为3，锁定天数为2，密码有效期为90天。

4、删除PROFILE文件。

①利用SQL*Plus或iSQL*Plus，删除“学号_pro”概要文件，查看“user+学号”用户的概要文件。

附录：

员工医疗保险系统表

表1员工（staff）表结构

表2医院（hospital）表结构

表3医保卡（card）表结构

表4就诊表（see）结构