湖南科技学院综合性实验指导书

实验名称：PL/SQL编程

实验项目性质：综合性

所涉及课程：Oracle数据库

计划学时：4

一、         实验目的

（1）熟练掌握PL/SQL程序设计的基本知识。

（2）熟练掌握PL/SQL中控制结构的使用。具体包括选择结构语句（IF语句和CASE语句），循环结构（四种循环结构）。

（3）熟练使用PL/SQL中系统函数。

（4）掌握PL/SQL中异常处理语句的使用

（5）掌握PL/SQL中SELECT语句和DML语句的综合运用。

二、         实验内容及步骤

1.       创建用户，指定密码、表空间。用户名命名以本人姓名的缩写+‘_’+学号最后两位。

2.       为该用户授予sysdba、connect、resource权限。

3.       以本人的用户身份登录。

4.       练习选择结构语句的使用方法。

1、条件语句IF 语法：

IF <条件表达式> THEN

  <执行语句> …… <执行语句n>

[ELSIF <条件表达式> THEN

  <执行语句> …… <执行语句n>

 ……

 ELSE

  <执行语句>]

END IF;

2、分支语句CASE 语法：

CASE <变量>

  WHEN <表达式1> THEN 值1

  WHEN <表达式2> THEN 值2

  ……

  WHEN <表达式n> THEN 值n

  ELSE 值n + 1

END;

例1：声明一个整型变量Num，使用IF语句判断Num变量是正数、负数或0。

SET ServerOutput ON;

DECLARE

  Num INTEGER := -11;

BEGIN

  IF Num < 0 THEN

    dbms_output.put_line('负数');

  ELSIF Num >0 THEN

    dbms_output.put_line('正数');

  ELSE

    dbms_output.put_line('0');

  END IF;

END;

/

例2、使用CASE语句根据给定的整数输出对应的星期值：

SET ServerOutput ON;

DECLARE

  varDAY INTEGER := 3;

  Result VARCHAR2(20);

BEGIN

  Result := CASE varDAY

    WHEN 1 THEN '星期一'

    WHEN 2 THEN '星期二'

    WHEN 3 THEN '星期三'

    WHEN 4 THEN '星期四'

    WHEN 5 THEN '星期五'

    WHEN 6 THEN '星期六'

    WHEN 7 THEN '星期七'

    ELSE '数据越界'

  END;

  dbms_output.put_line(Result);

END;

5.       练习循环结构语句的使用方法。

1、循环语句LOOP…EXIT…END

LOOP

  <程序块1>

  IF <条件表达式> THEN

    EXIT

  END IF

  <程序块2> 

END LOOP;

2、循环语句LOOP…EXIT WHEN…END

LOOP

  <程序块1>

  EXIT WHEN <条件表达式>

  <程序块2>

END LOOP;

3、循环语句WHILE…LOOP…END LOOP

WHILE <条件表达式>

LOOP

  <程序块>

END LOOP;

4、循环语句FOR…IN…LOOP…END LOOP

FOR <循环变量> IN <初始值> .．<终止值>

LOOP

  <程序块>

END LOOP;

例1、计算1到3的累加和。

SET ServerOutput ON;

DECLARE

  v_Num INTEGER := 2;

  v_Sum INTEGER := 0;

BEGIN

  LOOP

    v_Sum := v_Sum + v_Num;

    dbms_output.put_line(v_Num);

    IF v_Num = 100 THEN

      EXIT;

    END IF;

    dbms_output.put_line(' + ');

    v_Num := v_Num + 2;

  END LOOP;

  dbms_output.put_line(' = ');

  dbms_output.put_line(v_Sum);

END;

例2、

SET ServerOutput ON;

DECLARE

  v_Num INTEGER := 1;

  v_Sum INTEGER := 0;

BEGIN

  LOOP

    v_Sum := v_Sum + v_Num;

    dbms_output.put_line(v_Num);

    EXIT WHEN v_Num = 3;

    dbms_output.put_line(' + ');

    v_Num := v_Num + 1;

  END LOOP;

  dbms_output.put_line(' = ');

  dbms_output.put_line(v_Sum);

END;

例3、

SET ServerOutput ON;

DECLARE

  v_Num INTEGER;

  v_Sum INTEGER := 0;

BEGIN

  FOR v_Num IN 1..3

  LOOP

    v_Sum := v_Sum + v_Num;

    dbms_output.put_line(v_Num);

    IF v_Num < 3 THEN

      dbms_output.put_line(' + ');

    END IF;

  END LOOP;

  dbms_output.put_line(' = ');

  dbms_output.put_line(v_Sum);

END;

6.       练习异常处理语句的使用方法。

WHEN语句来定义异常处理：

EXCEPTION

  WHEN <异常情况名> THEN

    <异常处理代码>

  WHEN <异常情况名> THEN

    <异常处理代码>

  ……

  WHEN OTHERS THEN

    <异常处理代码>

例1、SET SERVEROUTPUT ON;

DECLARE

    x NUMBER;

BEGIN

    x:= 'a123';--向NUMBER类型的变量X中赋值字符串，导致异常

EXCEPTION

   WHEN VALUE_ERROR THEN

     DBMS_OUTPUT.PUT_LINE('数据类型错误');

END;

例2、

SET SERVEROUTPUT ON;

DECLARE

   v_result NUMBER(10):=0;

BEGIN

    v_result:= 100/0;

DBMS_OUTPUT.PUT_LINE('结果是：'||v_result);

EXCEPTION

   WHEN ZERO_DIVIDE THEN

     DBMS_OUTPUT.PUT_LINE('除数是零！默认用1替代除数，结果是：'||100/1);

END;

7.       练习PL/SQL中SELECT语句和DML语句的综合运用。

创建表departments和表employees, 并为两张表输入若干数据。如下图所示。

表departments结构：

字段名称    字段类型         约束

Dep_id      number          主键

Dep_name    varchar2(20)

表employees结构：

字段名称    字段类型         约束

emp_id      number          主键

emp_name    varchar2(20)

sex         varchar2(20)

title       varchar2(20)

wage        number

idcard      varchar2(12)

dep_id      number          外键

完成思考题部分的PL/SQL程序编写。

三、         实验（设计）仪器设备和材料清单

（1）硬件环境：PC机

（2）软件环境：Oracle 10g

四、         实验要求

1、          验证实验内容部分的程序代码。

2、          综合运用各种程序结构完成思考题部分的PL/SQL程序编写。

五、         考核形式

以实验报告的程序代码结果作为成绩。

六、         实验报告要求

（1）实验目的。

（2）思考题的源程序清单。

（3）写出在实验过程中遇到的问题及解决方法。

（4）要求字迹端正、条理清晰、概念正确。

七、         思考题

1、          编写PL/SQL程序，使用LOOP…EXIT…END语句计算1—100之间所有偶数之和.

2、          编写PL/SQL程序，查询5号员工工资，如果工资小于3000，则加200员工资，并提示信息“5号员工工资已更新。”，如果工资大于3000，则提示信息“5号员工工资为XXX，已达到规定标准。”

3、          编写PL/SQL程序，查询1号员工的工资，使用CASE语句输出其工资等级。工资小于等于3000，等级为“低”；工资大于3000，小于5000，等级为”中”；工资大于等于5000，等级为高；

4、          编写PL/SQL程序，查询的departments表中是否有部门号为“6”的记录，如果没有，插入新记录部门号为6，部门名称为“后勤部”。如果有，显示查询结果。

 

第二篇：Oracle实验指导书(1-14)

《Oracle数据库管理系统》

实验指导书

学    号：     

姓    名：

班    级：  

专    业：   

20##年   月   日

实验一Oracle 10g的安装及常用工具的使用

一、实验目的

1.掌握Oracle 10g的安装过程及选项意义

2.掌握SQL*Plus语言环境

3.掌握SQL*Plus工具的使用

4.掌握iSQL*Plus工具的使用

5.掌握Oracle企业管理器的使用

二、实验内容

1.安装Oracle 10g。

2.检查“所有程序”菜单中的项目：选择“开始”→“程序”→“Oracle-OraDb10g_Home1”。

3.检查安装后的服务。

4．使用SQL*Plus建立与Oracle服务器的连接。

(1) 选择“开始”→“程序”→“Oracle-OraDb10-home1”→“Application    Development”→“SQL Plus”。

(2) 输入用户名和口令。主机字符串若为已配置的网络服务名，则表示连接网络服务名指定的服务器；如果为空，则表示连接本机的默认数据库。点击【确定】按钮，出现“连接成功”窗口。

(3) 此时可以使用SQL*Plus执行各种SQL语句，访问Oracle数据库的信息。

5．使用iSQL*Plus建立与Oracle服务器的连接。

6.熟悉Oracle企业管理器控制台，通过图形化的方式了解Oracle数据库的基本信息，使用企业管理器控制台执行各项任务，如管理Oracle实例、表空间、用户帐号、各种数据对象等。用户以管理员身份登录到Oracle数据库。

三、实验环境：

安装Windows XP（Windows Server 2003）以及Oracle 10g的主机。

四、实验要求

1.预习实验内容并写出上机报告。

2.实验中出现的问题及实验体会。

实验二 Oracle数据库的创建与管理

一、实验目的

利用OEM图形化工具和命令行方式完成管理表空间的实验。

1．创建表空间。

2．更改表空间状态。

3．查看表空间。

4．掌握DBCA创建数据库的步骤和方法。

5．使用OEM查看数据库信息。

6．通过练习深入理解Oracle数据库的体系结构。

二、实验内容

1．创建本地管理表空间APP，大小为50 MB，本地管理，自动分配。

2．增加一个20 MB的数据文件APP1，其中文件初始大小为10MB，可以重写，文件不能增大。

3．更改表空间状态。

(1) 使表空间脱机。

(2) 使表空间联机。

(3) 更改表空间为只读。

(4) 更改表空间为可读/写。

4．更改数据文件状态。

(1) 使数据文件脱机。

(2) 使数据文件联机。

（3）修改数据文件：文件可以自动增大，“增量”设置为2MB，“最大文件大小”设置为30MB。

5．查看表空间APP的信息。

6．利用DBCA创建数据库。

7．使用Oracle企业管理器控制台OEM查看数据库信息。

8．查看数据库包含的所有段类型（提示：select distinct segment_type from dba_segments;）。

9．(选做)利用命令行方式完成上述1、2、6、7、内容。

三、实验环境：

安装Windows XP（Windows Server 2003）以及Oracle 10g的主机。

四、实验要求1. 预习实验内容并写出上机报告。2. 实验中出现的问题及实验体会。

实验三 Oracle数据表的创建与管理

一、实验目的

1．了解表的基本概念

2．了解创建表的基本命令的用法

3．了解基本的数据类型

4．学会使用OEM和命令行方式创建表

5．学会设置简单的约束条件

二、实验内容

1．在创建的数据库中创建学生信息表“student”。

(1) 在OEM中创建student表，并设置各项约束。

(2) 使用命令CREATE TABLE方式创建student表。

2．课程信息表“course”。

(1) 在OEM中创建course表并设置各项约束。

(2) 使用命令CREATE TABLE方式创建course。

3. 成绩信息表“score”。

(1) 在OEM中创建score表并设置各项约束。

(2) 使用命令CREATE TABLE方式创建sscore。

  3．自己安装PL/SQL DEVELOPER并利用该软件为上述两个表各添加10条记录，记录内容自己确定。

三、实验环境：

安装Windows XP（Windows Server 2003）以及Oracle 10g的主机。

四、实验要求

1. 预习实验内容并写出上机报告。

2. 实验中出现的问题及实验体会。

实验四  管理数据库对象

一、实验目的

1．掌握在OEM中创建视图和维护视图的方法。

2．掌握在命令方式下创建视图和维护视图的方法。

3. 掌握序列的概念和使用方法。

4．掌握同义词的概念和使用方法。

5．掌握索引的概念和使用方法。

二、实验内容

1．创建考生成绩视图。为了方便查看学生的成绩，创建一个学生的成绩视图。该视图将显示学生的编号、姓名课程号、课程名称和成绩。基表为student、course、score。

4．创建索引。创建sname字段的一个索引，索引名称为studentname_index并查询它的基本信息，以此表明该索引确实被创建。通过查询user_indexes视图可以查到名为studentname_index的索引。

5．查询索引的信息。

6．删除索引。

7．创建序列。创建一个初始值为1000，增量为10，直到该序列达到1100，然后重新从1000开始的递增序列studentno。

8．修改序列。修改序列studentno的增量为20，并且设置最大值为10000。

9．使用序列。使用序列可以通过访问NEXTVAL和CURRVAL伪列来实现。NEXTVAL伪列返回序列的下一个值，CURRVAL伪列返回序列的当前值。

向student表添加新的一列sorder，并向student表中添加新的一行，内容自定，添加完成后，查看结果。

10．删除序列。删除序列studentno。

11．创建同义词。创建表student的同义词。创建学生成绩视图的公用同义词。

步骤提示：具有CREATE PUBLIC SYNONYM权限的用户才能创建公用同义词。

12．查看同义词。查询表student对象的公用同义词。

13．删除同义词。删除创建的同义词。

14．（选做）利用命令行方式实现上述1、2、3、4、6、7、10、11、12、13题的内容。

三、实验环境：

安装Windows XP（Windows Server 2003）以及Oracle 10g的主机。

四、实验要求

1. 预习实验内容并写出上机报告。

2. 实验中出现的问题及实验体会。

实验五、六  数据查询

一、实验目的

1．掌握SELECT语句的基本语法。

2．掌握数据汇总的方法。

3．掌握常用函数的基本语法。

4．掌握使用函数进行数据统计的方法。

二、实验内容

1、将scott的数据备份文件复制到d:\

2、在命令窗口执行imp scott/tiger file=d:\scott.dmp ignore=y,将scott的备份数据导入数据库中

3、列出参加考试的所有同学信息

4、列出考试的所有课程

5、在查找出自己各科的成绩

6、查出自己的平均成绩和总成绩

7、查出本班的成绩，要求查询结果中有姓名和课程名称。

8、列出本班各课程的平均成绩

9、列出本班男生和女生的各科平均成绩

10、列出不及格的同学名单

11、列出本班各科的最低成绩

12、统计本校男女生的人数

13、统计各部门的学生人数

14、统计各部门的男女生人数

15、统计各班的男女生人数

三、实验环境：

安装Windows XP（Windows Server 2003）以及Oracle 10g的主机。

四、实验要求

1. 预习实验内容并写出上机报告。

2. 实验中出现的问题及实验体会。

实验七  数据更新

一、实验目的

1．学会使用SQL语句对数据表进行数据的插入、修改和删除操作

2．掌握在进行数据更新时数据的完整性的控制方法

3．掌握利用SQL语句对数据表进行灵活控制的方法

二、实验内容

利用上次课所建的数据库及表，完成下列题目要求：

1．利用INSERT命令向course表中插入10条记录。

2．利用INSERT命令 向student表中插入30条记录。

4．修改自己成绩表中的数据，并查询数据的变化。

5．将自己每门课的成绩小于85分的增加15分。

6．删除自己的成绩

7．复习SQL语言，录活掌握select、insert、update、delete命令的使用。

三、实验环境：

安装Windows XP（Windows Server 2003）以及Oracle 10g的主机。

四、实验要求

1. 预习实验内容并写出上机报告。

2. 实验中出现的问题及实验体会。

实验八  PL/SQL语言基础

一、实验目的

1．掌握PL/SQL 块语句及语法规则。

2．掌握PL/SQL 数据类型。

3．掌握条件分支语句和CASE 语句。

4．掌握基本循环语句。

二、实验内容

1、将scott的数据备份文件复制到d:\。

2、在命令窗口执行imp scott/tiger file=d:\scott.dmp ignore=y,将scott的备份数据导入数据库中。

3．使用SQL*Plus 替代变量输入班级编号，删除该班级的所有考试信息，并处理可能出现的错误。如果成功删除，则显示“该班级成绩被删除”；如果该部门不存在，则显示消息“班级不存在”。

4．编写PL/SQL 块，使用SQL*Plus 替代变量输入学生姓名，删除该学生的信息 (使用SCOTT用户的student表和score表。)

步骤提示：

(1) 使用SQL*Plus 替代变量在student表中输入员工名称。

(2) 使用%ROWTYPE 为student表定义记录变量，并使用该变量为记录成员提供数据，为student表插入数据。

5．为成绩小于60分的学生成绩进行开方乘以10，并查询修改结果。

选做：

5. 分别使用三种循环方式计算10的阶乘。

6. 使用FOR 循环语句输出一个实心三角形。

三、实验环境：

Oracle 10g与安装有XP服务器以上版本的主机。

四、实验要求

1. 预习实验内容并写出上机报告。

2. 实验中出现的问题及实验体会。

实验九、十  游标、包、异常处理

一、实验目的

1．掌握程序控制结构、复合类型和异常处理操作。

2．掌握游标操作。

3．掌握包的创建及初始化操作。

二、实验内容

1．编写PL/SQL 块，使用SQL*Plus 替代变量输入员工名称，删除该员工所在部门的员工的信息，并使用SQL 游标属性确定删除了几行。(使用SCOTT用户的EMP表和DEPT表。)

步骤提示：

(1) 使用SQL*Plus 替代变量在emp表中输入员工名称。

(2) 使用%ROWTYPE 为emp表定义记录变量，并使用该变量为记录成员提供数据，为emp表插入数据。

2．使用记录类型、%ROWTYPE和游标查询emp表中员工编号为“1002”的员工姓名。

3．使用表和数组类型在屏幕上显示指定信息：

   Hello Jack,

   How are you?

4.先将员工编号为“1002”的员工姓名改为“John” ，再利用自定义异常处理来检测该员工姓名。

三、实验环境：

安装Windows XP（Windows Server 2003）以及Oracle 10g的主机。

四、实验要求

1. 预习实验内容并写出上机报告。

2. 实验中出现的问题及实验体会。

实验十一  管理PL/SQL存储过程

一、实验目的

1．掌握编写过程和调用过程的方法。

2．掌握编写函数和调用函数的方法。

3．掌握程序包的编写规范。

4．掌握执行程序包中过程和函数的方法。

二、实验内容

1、将scott的数据备份文件复制到d:\

2、在命令窗口执行imp scott/tiger file=d:\scott.dmp ignore=y,将scott的备份数据导入数据库中

3、编写一个过程，要求根据用户输入的学号(sno)查询student表，返回学生的姓名和性别(sname和ssex)。

4、编写一个函数，要求根据用户输入的学号查询出学生的成绩，要求要有学生的姓名和课程名称。

5、创建存储过程Pro_update，该存储过程带一个字符型传入参数ssno，修改学生的学号，然后依次实现执行、查看、修改和删除操作。

5．创建函数func_dele，根据输入的学号删除学生的所有信息，并依次实现执行、查看、修改和删除操作。

三、实验环境：

Oracle 10g与安装有XP服务器以上版本的主机。

四、实验要求

1. 预习实验内容并写出上机报告。

2. 实验中出现的问题及实验体会。

实验十二  管理PL/SQL触发器

一、实验目的

1.掌握触发器的原理。

2.掌握语句级触发器的编写方法及测试方法。

3.掌握行级触发器的编写方法及测试方法。

4.掌握替代触发器的编写方法及测试方法。

5.掌握DDL触发器的编写方法及测试方法。

6.掌握数据库级触发器的编写方法及测试方法。

二、实验内容

1、将scott的数据备份文件复制到d:\

2、在命令窗口执行imp scott/tiger file=d:\scott.dmp ignore=y,将scott的备份数据导入数据库中

3、创建级触发器，在student表中删除某一学生信息时，删除该学生的相关成绩信息。

(1) 建立触发器。

(2) 测试触发器。

选做：

4.创建DDL触发器，记录当前用户下创建对象的信息。

5.创建数据库级触发器，记录数据库发生错误的信息。

三、实验环境：

安装Windows XP（Windows Server 2003）以及Oracle 10g的主机。

四、实验要求

1. 预习实验内容并写出上机报告。

2. 实验中出现的问题及实验体会。

实验十三  用户的创建、更改和删除

一、实验目的和要求：

1．掌握创建和修改数据库用户的命令和使用方法。

2.掌握创建和修改数据库角色的命令和使用方法。

3.掌握授予和撤消角色权限的命令和使用方法。

4.熟练掌握采用企业管理控制台和命令行方式管理用户、角色、概要文件、权限的方法。

二、实验内容：

1．创建用户student，口令为student123，给用户student授权。

(1) 以Oracle系统用户SYSTEM登录。

(2) 创建用户student。

(3) 给用户student授予连接数据库的权限。

(4) 以student帐户连接数据库。

2．更改用户密码。

(1) 以Oracle系统用户SYSTEM登录。

(2) 更改用户student的密码为stu123456。

(3) 用户student以新密码连接数据库。

3．授权：给用户student授予SCOTT用户emp表的查看、更新权限。

(1) 以emp表拥有者用户SCOTT登录。

(2) 授予对象权限。

(3) 尝试以用户student查看、更新course表。

4．收回权限：收回用户student对SCOTT用户course表的查看、更新权限。

(1) 以course表拥有者用户SCOTT登录。

(2) 收回对象权限。

(3) 再次尝试以用户student查看、更新emp表。

5．删除用户student。

(1) 以Oracle系统用户SYSTEM登录。　

(2) 删除用户student。

 6. 手工创建数据库用户teacher，用户密码teacher123，默认表空间users，临时表空间temp，在users表空间可使用的限额为20 MB。

7．手工修改数据库用户的空间配额为10 MB，更改用户teacher的默认表空间users1，修改用户teacher的口令，修改用户的状态，锁定teacher帐户，然后解除锁定。

8．创建数据库角色manager，口令为manager123。

9．修改数据库角色manager，取消口令。

10．将创建表的权限授予数据库角色manager。

11．将创建表的权限从数据库角色manager撤消。

12．利用企业管理控制台和命令行两种方式创建一个概要文件 user_pro，要求：

(1) 空闲时间为 15 分钟；

(2) 登录失败次数为 3 次。

13．利用企业管理控制台和命令行两种方式创建一个新的用户，要求：

(1)你的姓名加上学号为用户名创建一个用户；

(2)使用 USER 表空间；

(3)使用所建概要文件；

(4)查询所创建的用户。

14．利用企业管理控制台和命令行两种方式给已创建的新用户授权。

(1)授予 connect 角色；

(2)授予一些系统权限；

(3)授予一些对象权限。

15．利用企业管理控制台和命令行两种方式创建一个新的角色，要求：

(1) 以姓名加上学号为角色名创建一个角色，并使用名字的汉语拼音作为口令；

(2) 修改创建的角色，去掉口令；　

(3) 给角色授予权限；

(4) 将角色授予用户。

注意：在操作过程中，利用第二种方式完成操作时，需将利用第一种方式创建的对象删除。

三、实验环境：

 Oracle 10g与安装有XP服务器以上版本的主机。

四、实验要求：1.预习实验内容并写出上机报告。2.实验中出现的问题及实验体会。

实验十四  数据库安全性

一、实验目的

1．理解事务和锁。

2. 掌握使用OEM方式创建和管理重做日志组和日志文件的方法。

3．掌握使用OEM方式创建和管理回滚段的方法。

4．理解数据库备份与恢复的分类。

5．掌握使用OEM方式和命令行方式实现数据库逻辑备份与恢复的方法。

二、实验内容

1．修改student表中自己学号，将最后两位变为“99”，姓名最后增加一个字“改”，并提交。查询修改结果。

3．查看系统锁信息。

4．创建重做日志组和日志文件“MyLog.log”。

5. 创建回滚段“MyRoleBack”。

6. 为Windows XP(或2000 Server)管理员授予批处理作业权限。

7．将管理员设置成“studentdata”数据库的首选身份证明，并指定其作用于“数据库实例”目标类型。

8．将“studentdata”数据库中的学生表备份到该数据库的数据文件路径下，备份文件名为“MyBak.dmp”。

9．将“studentdata”数据库中的course表导出到备份文件“C:\NewBak.dmp”。

10、删除course表中的部分数据。

11．使用备份文件“C:\NewBak.dmp”恢复“studentdata”数据库中的course表。

三、实验环境：

安装Windows XP（Windows Server 2003）以及Oracle 10g的主机。

四、实验要求

1. 预习实验内容并写出上机报告。

2. 实验中出现的问题及实验体会。