五、思考题及其它

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

2、raise语句应该放在PL/SQL块的哪个部分？当在异常和oracle错误之间建立关联时，应该在哪个部分完成？

3、实验报告要求

本次实验书写两次，分别是（一）和（二）、（三）和（四）

实验六： 过程、函数、触发器

实验学时：4

实验类型：设计型

一．实验目的及要求

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

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

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

4．了解包的使用。

二、实验主要内容

1．过程的创建与维护；

2．函数的创建与维护；

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

4．包的使用。

三、实验仪器设备

在局域网环境下，有一台服务器和若干台客户机。服务器成功安装Oracle 11g数据库服务器（企业版），客户机成功安装Oracle 11g客户端软件，网络服务配置正确，数据库和客户端正常工作。

四、实验内容与步骤

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

1、编写一个过程，可以输入一个雇员名，如果该雇员的的职位为PRESIDENT就给他的工资增加1000，如果该雇员的职位为MANAGER，就给他的工资加500，其他职位的雇员工资增加200。并在SQL*PLUS中调用该过程以修改FORD的工资。

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

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

  CREATE OR REPLACE PROCEDURE SUM_COUNT IS

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

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

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

BEGIN

                         --打开游标

  LOOP

                                 INTO Dept_cursor;--读取游标

    SELECT COUNT(*) INTO num  FROM emp

       WHERE deptno= Dept_cursor.deptno;

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

   

    EXIT WHEN          %NOTFOUND;--退出循环

  END LOOP;

                                --关闭游标

  COMMIT;

END;

--执行存储过程sum_count

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

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

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

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

2、创建INSTEAD OF 触发器。首先创建一个视图myview, 由于该视图是复合查询所产生的视图，所以不能执行DML语句。根据用户对视图所插入的数据判断需要将数据插入到哪个视图基表中，然后对该基表执行插入操作（选作）。

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

1、创建的包为DEMO_PKG,该包中包含一个记录变量DEPTREC、两个函数和一个过程。实现对dept表的增加、删除与查询。

--程序包头的创建

CREATE OR REPLACE PACKAGE DEMO_PKG  IS 

   DEPTREC DEPT%ROWTYPE;  

   --Add dept function声明...

   --delete dept funtion声明... 

   --query dept procedure声明... 

END DEMO_PKG; 

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

CREATE OR REPLACE PACKAGE BODY DEMO_PKG IS

   --add_dept定义

   --delete_dept定义

   --定义query_dept

END DEMO_PKG;

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

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

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

五、思考题及其它

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

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

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

4、实验报告要求

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

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

实验学时：5

实验类型：设计型

一、实验目的

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

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

二、实验环境

1、硬件设备：计算机局域网，服务器1台，客户机100台

2、软件系统： Windows XP客户机操作系统； Oracle11g服务端数据库系统，客户端工具。

三、实验内容及步骤

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

1．创建表空间

(1) 创建表空间名称为Medicine

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

2．创建表结构

利用SQL*PLUS或PL/SQL developer将下列各表建立到员工医疗保险系统数据库中。表结构见附录员工医疗保险系统表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_学号后四位”表添加“age INT”、“salary NUMBER(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> insert into vu_emp_clerk (empno,ename,job)

             values (1234,'李四','SALESMAN');

(2) 建立视图vu_sal_view显示工资高于部门平均工资的雇员姓名、工资和部门号。能否向对该视图进行DML操作，并分析原因。

2．查看视图

(1) 查看视图定义

    SQL>desc vu_emp_clerk;

    SQL>select text from user_views

            where view_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*Plus s查看同义词“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的建立、修改、查看、删除操作。

三、实验仪器设备

在局域网环境下，有一台服务器和若干台客户机。服务器成功安装Oracle 11g数据库服务器（企业版），客户机成功安装Oracle 11g客户端软件，网络服务配置正确，数据库和客户端正常工作。

四、实验步骤

（一）用户创建与管理

1、 创建用户。

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

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

2、查看用户。

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

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

3、修改用户。

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

（二） 权限管理。

①授予“姓名（英文）”用户“CREATE ANY TABLE”、“CREATE ANY INDEX”、“ALTER ANY TABLE”、“DROP ANY TABLE”、“DROP ANY INDEX”以及“CREATE SESSION”系统权限，并用WITH ADMIN OPTION传递权限。

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

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

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

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

(三)角色的创建与管理。

1、创建角色

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

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

2、查看角色。

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

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

3、 修改角色。

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

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

③给角色“学号_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）结构

 

第二篇：教师实验报告《大型数据库(Oracle)》

大型数据库(Oracle)

教师实验报告

指导老师：周友学

适用班级：信B1171/72

20##年8月25日  制定
实验一   Oracle安装与维护

一、实验目的

1.了解并掌握Oracle 10的安装方法

2.了解并掌握测试安装好的Oracle 10g的方法

二、实验内容及步骤

1.软件下载

Oracle公司针对个人学习之用提供免费的学习版本，可直接到Oracle公司的官方网站www.oracle.com上免费下载。

2.安装步骤

(1)   运行setup.exe,出现“Oracle Database 10g安装”画面。

(2)   “下一步”进入“Oracle Universal Installer：指定文件位置”， 设置源“路径”、“名称”和目的“路径”。

(3)   “下一步” 进入“Oracle Universal Installer：选择安装类型”，选择安装类型。

(4)   保持默认值，下一步，进入“Oracle Universal Installer：选择数据库配置”，选择数据库配置。

(5)   保持默认值，下一步，进入“Oracle Universal Installer：指定数据库配置选项”,指定“全局数据库名”和“SID”，对这两个参数的指定一般相同，例如：oract。也可以将“全局数据库名”设置为域名。例如：oract.abc.com如果选择“创建带样本方案的数据库，OUI会在数据库中创建HR、OE、SH等范例方案（sample schema）

(6)   下一步，进入“Oracle Universal Installer：选择数据库管理选项”。

(7)   保持默认值，下一步，进入“Oracle Universal Installer：指定数据库文件存储选项”。

(8)   保持默认值，下一步，进入“Oracle Universal Installer：指定备份和恢复选项”。

(9)   保持默认值，单击“下一步”，进入“Oracle Universal Installer：指定数据库方案的口令”，对不同的帐户设置不同的口令。

(10) 单击“下一步”，继续安装，进入“Oracle Universal Installer：概要”。

(11) 单击“安装”，开始安装过程，大约半小时。

(12) 数据库创建完成时，显示“Database Configuration Assistant”窗口。

(13) 单击“口令管理”，进入“口令管理”窗口。

(14) 解锁用户HR、OE和SCOTT，输入HR和OE的口令，SCOTT的默认口令为tiger。

(15) 单击“确定”返回“Database Configuration Assistant”窗口。

(16) 在图15所示窗口单击“确定”，进入“Oracle Universal Installer：安装 结束”窗口。

(17) 检查，单击“退出”，完成安装。

(18) 在自动打开的浏览器中以sys账户sysdba身份登录10g的企业管理器。第一次要接受license，单击I agree，以后就不用了。

3.测试安装好的Oracle 10g

在安装过程中，OUI会在 <ORACLE_HOME>\install下创建两个文件：

readme.txt：记录各种Oracle应用程序的URL与端口。

Portlist.ini：记录Oracle应用程序所使用的端口。

(1) 登录Enterprise Manager 10g Database Control

与以前的版本不同，Oracle企业管理器只有B/S模式。在浏览器中输入下列URL:http://<Oracle服务器名称>:1158/em

例如：http://localhost:1158/em

进入Enterprise Manager 10g登录窗口。以SYSDBA身份登录Oracle数据库。

(2) 使用iSQL*Pls登录Oracle数据库

iSQL*Plus是B/S模式的客户端工具。在Mydb浏览器中输入下列URL:

http://<Oracle服务器名称>:5560/isqlplus

例如：http://localhost:5560/isqlplus

进入iSQL*Plus登录窗口。用system帐户登录Oracle数据库。

(3) 使用SQL*Pls登录Oracle数据库

SQL*Plus是C/S模式的客户端工具程序。

单击“开始” > “所有程序” > “Oracle – Oracle10g_home” > “Application Development” > “SQL*Plus”

在登录窗口中输入system帐号与口令

(4) 使用命令行SQL*Pls登录Oracle数据库

传统的SQL*Plus是一个命令行客户端程序。在命令窗口中输入命令进行测试。

思考与练习:

在Windows操作系统下安装Oracle 10g数据库以后，计算机的运行速度明显降低，可以采取哪些措施应对。

实验二 创建数据库和表

一、实验目的

1. 掌握使用DBCA创建数据库

2. 掌握手工创建Oracle数据库的方法

3. 掌握创建数据表的方法

二、实验内容及步骤

1. 使用DBCA创建数据库

(1) 打开DBCA组件,创建数据库sale.

(2) 安装完后进入E:\oracle\product\10.2.0\db_1\install目录，打开readme文件，查看打开OEM的URL。

(3) 登陆OEM，管理sale数据库。

2. 手工创建Oracle数据库

数据库名：mydb

安装路径：d:\oracle\product\10.2.0\

(1)、手工创建相关目录

D:\oracle\product\10.2.0\admin\mydb

D:\oracle\product\10.2.0\admin\mydb\adump D:\oracle\product\10.2.0\admin\mydb\bdump

D:\oracle\product\10.2.0\admin\mydb\udump

D:\oracle\product\10.2.0\admin\mydb\cdump

D:\oracle\product\10.2.0\admin\mydb\ddump

D:\oracle\product\10.2.0\admin\mydb\pfile

D:\oracle\product\10.2.0\oradata\mydb

(2)、手工创建初始化参数文件D:\oracle\product\10.2.0\admin\mydb\pfile\ init.ora，内容可以copy别的实例init.ora文件后修改。

(3)、在命令提示符下,使用orapwd.exe命令，创建口令文件pwdmydb.ora,命令格式如下：

orapwd file=D:\oracle\product\10.2.0\db_1\database\pwdmydb.ora password=123 entries=5

(4)、通过oradim.exe命令，在服务里生成一个新的实例管理服务，启动方式为手工

set ORACLE_SID=mydb

oradim -new -sid MYDB -pfile D:\oracle\product\10.2.0\db_1\database\initmydb.ora

(5)、在命令提示符下打开sqlplus编辑器,创建服务器参数配置文件spfile.

sqlplus /nolog

SQL>connect / as SYSDBA

SQL>create spfile=’D:\oracle\product\10.2.0\db_1\database\spfilemydb.ora’ FROM pfile=’D:\oracle\product\10.2.0\admin\mydb\pfile\init.ora’;

SQL>startup nomount pfile="D:\oracle\product\10.2.0\admin\mydb\pfile\init.ora";

(6)、创建数据库，执行createdb.sql脚本命令，

SQL>@Createdb.sql

Createdb.sql脚本内容如下：

create database mydb

maxinstances 8

maxloghistory 1

maxlogfiles 16

maxlogmembers 3

maxdatafiles 100

logfile group 1 'd:\oracle\product\10.2.0\oradata\mydb\redo01.log' size 10m,

group 2 'd:\oracle\product\10.2.0\oradata\mydb\redo02.log' size 10m

datafile 'd:\oracle\product\10.2.0\oradata\mydb\system01.dbf' size 100m

autoextend on next 10m extent management local

sysaux datafile 'd:\oracle\product\10.2.0\oradata\mydb\sysaux01.dbf' size 30m

autoextend on next 10m

default temporary tablespace temp

tempfile 'd:\oracle\product\10.2.0\temp.dbf' size 10m autoextend on next 10m

undo tablespace undotbs1 datafile 'd:\oracle\product\10.2.0\oradata\mydb\undotbs1.dbf' size 20m

character set zhs16gbk

national character set al16utf16

user sys identified by mydb

user system identified by manager

/

(7)、创建数据库数据文件，执行脚本createfiles.sql

SQL>@createfiles.sql

Createfiles.sql脚本内容如下：

CREATE TABLESPACE "INDX" LOGGING DATAFILE 'd:\oracle\product\10.2.0\oradata\mydb\indx01.dbf' SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;

CREATE TABLESPACE "TOOLS" LOGGING DATAFILE 'd:\oracle\product\10.2.0\oradata\mydb\tools01.dbf' SIZE 10M REUSE AUTOEXTEND ON NEXT 320K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;

CREATE TABLESPACE "USERS" LOGGING DATAFILE 'd:\oracle\product\10.2.0\oradata\mydb\users01.dbf' SIZE 25M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;

CREATE UNDO TABLESPACE "UNDOTBS" DATAFILE 'd:\oracle\product\10.2.0\oradata\mydb\undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K;

/

8、创建数据字典，运行createdbcatalog.sql脚本命令

SQL>@createdbcatalog.sql

Createdbcatalog.sql脚本内容如下：

@d:\oracle\product\10.2.0\db_1\rdbms\admin\catalog.sql;

@d:\oracle\product\10.2.0\db_1\rdbms\admin\catexp7.sql;

@d:\oracle\product\10.2.0\db_1\rdbms\admin\catblock.sql;

@d:\oracle\product\10.2.0\db_1\rdbms\admin\catproc.sql;

@d:\oracle\product\10.2.0\db_1\rdbms\admin\catoctk.sql;

@d:\oracle\product\10.2.0\db_1\rdbms\admin\catobtk.sql;

@d:\oracle\product\10.2.0\db_1\rdbms\admin\caths.sql;

@d:\oracle\product\10.2.0\db_1\rdbms\admin\owminst.plb;

connect SYSTEM/manager

@d:\oracle\product\10.2.0\db_1\sqlplus\admin\pupbld.sql;

connect SYSTEM/manager

/

3. 在ORCL数据库中创建表Employees、表Departments和表Salary，表结构如表1，2，3所示。

（1）在OEM中分别创建表

在OEM目录中，选择表单击鼠标左键，在出现的界面上单击“创建”按钮，进入“表创建”界面，在各个选项卡上输入表Employees各字段信息、约束条件等，单击“确定”按钮，创建表Employees即可完成，依次完成其它表的创建。

（2）使用SQL语句分别创建表

表1 employees表结构

表2 departments表结构

表3 salary表结构

实验三 表数据的插入、修改和删除

一、实验目的

1. 掌握修改、删除表的的方法。

2．掌握主键约束、外键约束、唯一约束和检查约束的建立及维护方法。

3．掌握外部表、临时表的使用方法。

4．掌握使用SQL语句对数据库表进行插入、修改和删除数据操作。

二、实验内容及步骤

    分别使用OEM和PL/SQL语句，在实验2建立的表Employees、Departments和Salary中插入多行数据记录，然后修改和删除一些记录

1. 使用PL/SQL语句分别向表Employees、Departments和Salary中插入如下表所示数据记录。

表1 employees表数据

表2 departments表数据             表3 salary表数据

注意：在OEM中分别打开表Employees、Departments和Salary，观察数据变化。

2．使用PL/SQL命令修改各表中的某个记录的字段值。

（1）将表salary中编号为110001的职工收入改为2890；

（2）将表Departments表中生产部的名称修改为生产计划部；

（3）给每个职工增加收入100元。

修改完后使用select语句观察数据的变化。

3．删除所有性别为男的职工的记录。

4. 创建约束

(1)在employees表中创建主键约束,主码为employeeID,约束名为pk_employees_employeeID.

(2)创建外键约束,子表employees的departmentID字段参照主表

departments中的departmentID字段,约束名为fk_emp_dep_departtmentID.

(3)创建检查约束,employees表中的sex字段只能输入字符"男"或"女",约束名为ck_sex.

(4)在departments表的departmentName字段上创建唯一约束un_departmentName.

5. 修改及删除表

(1)将employees表中的zip字段删除,然后再增加一个字段,字段名为QQ,15位的变长字符型.

(2)将departments表删除.

6.将departments表中的数据以纯文本的形式保存到记事本中,并以逗号分隔,保存文件名为dp.txt,内容如下所示:

1,办公室

2,人力资源部

3,销售部

在oracle中创建dp.txt的外部表,然后用select语句进行简单查询.

7.创建事务临时表ttable保存employees表的临时数据,创建会话临时表保存departments表的临时数据.比较两个表的差别.

注:方法及命令格式请参照教材第10章,要求保存所有的SQL语句到文本文件形式的脚本中,以备检查。

实验四 数据库的查询

一、实验目的

1. 掌握SELECT语句的基本语法；

2. 掌握子查询的表示；

3. 掌握连接查询的表示；

4．掌握数据汇总的方法；

5．掌握层次查询的方法；

6．掌握分析查询的方法。

二、实验内容及步骤

1-4实验数据基于实验二给出的数据库表结构，及实验三给出的表数据。

1．SELECT语句的基本使用

（1）查询每个雇员的所有数据；

Select * from emp

（2）查询每个雇员的地址和电话；

Select add,tel from emp

（3）查询EmployeeID为010001的雇员的基本信息；

Select * from emp where employeeid=010001

（4）查询Employees表中女雇员的地址和电话，使用AS子句将结果中各列的标题分别指定为地址和电话；

Select add as 地址，tel as 电话 from emp  where sex=’女’

（5）计算每个雇员的实际收入；

Select in-out  from salary

（6）找出所有姓王的雇员的部门号；

Select dno from emp where ename like’王%’

（7）找出所有收入在20##-3000之间的雇员号码。

Select employeeid from emp where sal>=2000 and sal<=3000

2．子查询的使用

（1）查找在财务部工作的雇员的情况；

（2）查找财务部年龄不低于销售部雇员年龄的雇员的姓名。

3．连接查询的使用

（1）查询每个雇员的情况及其薪水情况；

Select * from salary

（2）查找财务部收入在2200元以上的雇员姓名及其薪水详情。

Select ename,sal from emp,dep

Where emp.deptno=dep.deptno and dname=’财务部’

4．数据汇总

（1）求各部门的雇员数；

Select  count（*） from emp group by deptno

（2）将各雇员的情况按收入由低到高排列；

 Select * from emp ordey by sal

（3）求财务部雇员的平均收入；

  Select avg(sal) from emp

Where deptno=(select deptno from dep where dname=’财务部’)

（4）求财务部雇员的平均实际收入；

Select in-out from emp

Where deptno=(select deptno from dep where dname=’财务部’)

（5）求财务部雇员的总人数。

Select count(*) from emp

 Where deptno=(select deptno from dep where dname=’财务部’)

6. 层次查询

基于自行车结构的数据进行层次查询实验,脚本如下:

create table bicycle

( part_id number(5) constraint pk_bicycle_part_id primary key,

parent_id number(5) constraint fk_bicycle_pid references bicycle(part_id),

part_name varchar2(30) not null,

mp_cost number(9, 2),

describe varchar2(30)

);

insert into bicycle values(1, null, '自行车', 379.28, '装配');

insert into bicycle values(2, 1, '导向系统', 101.11, '制造');

insert into bicycle values(3, 1, '驱动系统', 159.56, '制造');

insert into bicycle values(4, 1, '其他部分', 118.61, '制造和采购');

insert into bicycle values(5, 2, '车把', 37.28, '制造');

insert into bicycle values(6, 2, '前叉', 24.35, '制造');

insert into bicycle values(7, 2, '前轴', 19.67, '制造');

insert into bicycle values(8, 2, '前轮', 19.81, '制造');

insert into bicycle values(9, 7, '前轴棍', 8.16, '制造');

insert into bicycle values(10, 7, '前轴身', 4.82, '制造');

insert into bicycle values(11, 7, '前轴碗', 6.69, '制造');

insert into bicycle values(12, 10, '前轴管', 1.61, '制造');

insert into bicycle values(13, 10, '前花盘', 3.21, '制造');

insert into bicycle values(14, 3, '脚蹬', 18.99, '制造');

insert into bicycle values(15, 3, '中轴', 25.27, '制造');

insert into bicycle values(16, 3, '链条', 21.65, '制造');

insert into bicycle values(17, 3, '飞轮', 29.12, '制造');

insert into bicycle values(18, 3, '后轴', 31.72, '制造');

insert into bicycle values(19, 3, '后轮', 32.81, '制造');

insert into bicycle values(20, 17, '外套', 9.35, '制造');

insert into bicycle values(21, 17, '平档', 5.82, '制造');

insert into bicycle values(22, 17, '芯子', 5.11, '制造');

insert into bicycle values(23, 17, '千斤', 6.56, '制造');

insert into bicycle values(24, 17, '钢珠', 2.28, '采购');

insert into bicycle values(25, 4, '车架', 81.78, '制造');

insert into bicycle values(26, 4, '车闸', 15.26, '制造');

insert into bicycle values(27, 4, '链罩', 7.10, '采购');

insert into bicycle values(28, 4, '车铃', 4.33, '采购');

insert into bicycle values(29, 4, '车锁', 5.02, '采购');

insert into bicycle values(30, 4, '支架', 5.12, '制造');

（1）按自行车结构层次的先后顺序,查询自行车树状结构数据；

（2）查询自行车导向系统分支的树状结构数据；

（3）在自行车树状结构数据中显示成本小于100元的零部件信息；

（4）对自行车的成本进行加密，然后显示出来，加密密钥可任意给定。

7. 分析查询

基于某书店20##年的图书销售的数据进行分析查询实验,脚本如下:

create table sales_fact_2006

( sale_year number(4) not null,

sale_quarter number(1) not null,

sale_month number(2) not null,

sale_book_id varchar2(20) not null,

sale_region varchar2(10) not null,

sale_person varchar2(10) not null,

sale_amount number(10, 2) null,

constraint pk_sales_f2006 primary key(

     sale_year, sale_quarter, sale_month, sale_book_id, sale_region, sale_person)

);

insert into sales_fact_2006 values(2006, 1, 1, 'ISBN 7-X', '北京', '赵亦', 13526.12);

insert into sales_fact_2006 values(2006, 1, 2, 'ISBN 7-X', '北京', '钱尔',   8213.91);

insert into sales_fact_2006 values(2006, 1, 3, 'ISBN 7-X', '北京', '孙三', 33871.52);

insert into sales_fact_2006 values(2006, 2, 4, 'ISBN 7-X', '北京', '李斯', 22343.80);

insert into sales_fact_2006 values(2006, 2, 5, 'ISBN 7-X', '上海', '周武',  3455.93);

insert into sales_fact_2006 values(2006, 2, 6, 'ISBN 7-X', '上海', '孙三', 23427.72);

insert into sales_fact_2006 values(2006, 3, 7, 'ISBN 7-X', '香港', '杨琪', 897.15);

insert into sales_fact_2006 values(2006, 3, 8, 'ISBN 7-X', '香港', '钱尔',   12345);

insert into sales_fact_2006 values(2006, 3, 9, 'ISBN 7-X', '澳门', '冯久', 37817.12);

insert into sales_fact_2006 values(2006, 4, 10, 'ISBN 7-X', '澳门', '冯久', 6524.10);

insert into sales_fact_2006 values(2006, 4, 11, 'ISBN 7-X', '台北', '李斯',  93415.83);

insert into sales_fact_2006 values(2006, 4, 12, 'ISBN 7-X', '台北', '孙三', 23232.82);

（1）查询每个销售员的销售额，并使用sum()分析函数对每个窗口执行累加运算的分析，窗口范围为当前行及其后两行；

（2）制作一个总计表，表中包括每一个销售人员在每一个销售区域的销售额以及该销售额占该销售区域销售总额的比率；

（3）查询每个销售区域的销售额，并使用rank()函数计算每一行的相对位置。

实验五 PL/SQL编程

一、实验目的

1. 掌握PL/SQL程序块的结构；

2．掌握时间戳的使用；

3．掌握记录、PL/SQL表的使用；

4．掌握显示游标的使用；

5．掌握异常处理的方法。

二、实验内容及步骤

1．PL/SQL程序块

编写一个程序块，完成10以内偶数的累积。

2．时间戳

（1）显示一个时间戳，精确到纳秒。

（2）创建一个只包含一个数值型字段的表，往表中插入10000条记录，利用时间戳计算所用时间。

3．记录、PL/SQL表

（1）创建一个记录类型保存学生数据，类型名为STUDENT，包含XM，XB，BJ三个变量，然后定义一个该类型的变量STUDENT1，输入学生本个的信息，并输出。

（2）创建一个基类型为字符型的PL/SQL表类型，保存学生的姓名，然后定义一个该类型的变量STUDENT2，输入5名同学的姓名，并输出。

4．显示游标

    编写一个程序块，定义一个游标，用于处理EMP表中工资大于1500的员工信息，如果员工的工资在1500到2000之间，则加上200，如果大于2000，则加上100，修改结果保存到EMP表中，并显示输出。

    定义处理数据的变量要求用到%type。

注：关于显示游标，由于教材没有作详细说明，请查阅相关资料或教学课件。

5．异常处理

    定义一个自定义异常too_many，当EMP表中的记录条数超过10条时，抛出异常，并输出“员工数量过多！”的信息。

实验六 用户、角色

一、实验目的

1. 掌握使用用户实现数据库安全性；

2. 掌握使用角色实现数据库安全性。

二、实验内容及步骤

1．创建用户

（1）创建一个用户ph，密码为oracle；

（2）授予连接数据库权限、创建表权限，并允许其将权限转授予其它用户；

（3）设置其默认表空间为USERS，并分配20M的配额；

（4）连接到ph账户下，将创建表权限授予SCOTT；

（5）在ph账户下创建表xs,字段为xm,cj；

（6）将xs表的插入数据权限授予SCOTT；

（7）连接到SCOTT账户下，插入一条数据（lucy,80）。

2．创建角色

（1）连接到system账户下，授予ph创建角色的权限；

（2）创建一个以你的姓名的拼音首字母为名字的用户；

（3）连接到ph账户下，创建一个角色student，密码为student；

（4）授予student角色查询xs表的权限；

（5）将student角色赋予你自己的名字的用户。

实验七 存储过程的使用

一、实验目的

1. 掌握存储过程的创建方法；

2. 掌握存储过程的调用方法；

二、实验内容及步骤

1．创建添加职员记录的存储过程EmpAdd，通过参数接收职员信息，然后将数据插入到EMP表中。

2．调用EmpAdd，往EMP表中插入一条数据，具体数据由自己组织，然后用SQL语句查询EMP表，检查存储过程是不是正确执行。

3．区分in、out和inout三类参数

（1）编写一个计算EMP表中指定部门职员人数的存储过程count_num1，定义一个in类型的参数bm用于输入部门信息，定义一个out类型的参数person_num用于输出部门人数。

（2）编写一个与（1）相同的存储过程count_num2，但参数类型都定义为inout类型。

（3）分别调用这两个过程，比较两者在参数使用上的不同之处。