Oracle实验指导书

实验一 Oracle查询工具的使用

一、目的和要求：

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

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

二、实验内容：

1.点击“开始”->“运行”，输入cmd命令进入DOS环境，然后执行SQL PLUS命令登录

数据库，并使用CONNECT命令切换当前的连接用户，具体操作如下：

（1） SQL PLUS system/密码

（2） Connect sys/密码 as sysdba

（3） Alter user scott account unlock identified by tiger;

（4） Connect scott/tiger@orcl

（5） Exit

2.在浏览器中输入以下网址：

http://localhost:5560/isqlplus

三、分析与思考

实验二 SQL语句基本查询语句

一、实验目的：

1.掌握select语句的基本语法

2.掌握常用函数的使用

3.了解格式化查询结果的常用命令

二、实验内容：

使用scott下的emp表和dept表，完成以下操作：

1.查询部门编号是20的员工信息。

2.查询工作为CLERK的员工的员工号、员工名和部门号。

3.查询奖金COMM高于工资sal的员工信息。

4.查询奖金高于工资20%的员工信息。

5.查询部门编号是10并且工作为MANAGER的员工和部门编号是20并且工作为CLERK

的员工的信息。

6.查询工作不是MANAGER和CLERK，并且工资大于或等于2000的员工信息。

7.查询有奖金的员工信息。

8.查询所有员工的人数和他们的平均工资。

9.查询没有奖金或奖金低于100的员工信息。

10.查询最近两年入职的员工信息。

11.查询工龄大于或等于10年的员工信息。

12.查询员工信息，要求以首字母大写的方式显示所有员工的姓名。

13.查询员工名正好为6个字母的员工信息。

14.查询员工名字中不包含字母S的员工。

15.查询员工姓名的第二个字母为M的员工信息。

16.查询所有员工姓名的前三个字符。

17.查询所有员工的姓名，如果包含字母s，则用S替换。

18.查询员工的的姓名和入职日期，并按入职日期从先到后进行排序。

19.显示所有员工的项目、工作、工资，按工作降序排序，若工作相同则按工资升序排序。

20.显示所有员工的姓名、入职的年份和月份，按入职日期所在的月份排序，若月份相同则

按入职的年份排序。

21.查询每个部门中的员工数量、平均工资和平均工作年限。

22.查询各个部门的人数及平均工资。

23.查询各种工作的最低工资，并输出最低工资低于3000的工作名称。

24.查询各个部门中不同工种的最高工资。

25.统计各个工种的员工人数与平均工资。

三、分析与思考

实验三 select语句高级查询

一、实验目的：

1.掌握select语句的多表连接查询

2.掌握select语句的子查询

二、实验内容：

根据oracle数据库scott下的emp表和dept表，完成下列操作：

1.查询所有工种为CLERK的员工的姓名及其部门名称。

2.查询所有部门及其员工信息，包括那些没有员工的部门。

3.查询所有员工及其部门信息，包括那些还不属于任何部门的员工。

4.查询在SALES部门工作的员工的姓名信息。

5.查询所有员工的姓名及其直接上级的姓名。

6.查询入职日期早于其上级领导的所有员工信息。

7.查询从事同一种工作但不属于同一部门的员工信息。

8.查询10号部门员工及其领导的信息。

9.使用UNION将工资大于2500的雇员信息与工作为ANALYST的雇员信息合并。

10.通过INTERSECT集合运算，查询工资大于2500，并且工作为ANALYST的雇员信

息。

11.使用MINUS集合查询工资大于2500，但工作不是ANALYST的雇员信息。

12.查询工资高于公司平均工资的所有员工信息。

13.查询与SMITH员工从事相同工作的所有员工信息。

14.查询工资比SMITH员工工资高的所有员工信息。

15.查询比所有在30号部门中工作的员工的工资都高的员工姓名和工资。

16.查询部门人数大于5的部门的员工信息。

17.查询所有员工工资都大于2000的部门的信息。

18.查询人数最多的部门信息。

19.查询至少有一个员工的部门信息。

20.查询工资高于本部门平均工资的员工信息。

21.查询工资高于部门平均工资的员工信息及其部门的平均工资。

22.查询每个员工的领导所在的部门的信息。

23.查询平均工资低于2000的部门及其员工信息。

三、分析与思考

实验四表的创建与管理

一、实验目的

1.掌握使用SQL语句创建、修改和删除表

2.掌握使用SQL语句插入、修改和删除数据

3.理解各种约束的作用，并能使用SQL命令为表建立各种约束

二、实验内容

1.创建表并添加相应的约束。要求：

（1）创建名为student的表，表中各列要求如下：

字段名称字段类型大小说明

10 主键

Sno CHAR

Sname V ARCHAR2

8

2 默认值为男，只能输Sex CHAR

入男或女

Birthday DATE

Sdept CHAR 20

（2）创建名为course的表，表中各列要求如下：

字段名称字段类型大小说明

10 主键

Cno CHAR

Cname CHAR 30 唯一值

3

Ccredit NUMBER

（2）创建名为score的表，表中各列要求如下：

字段名称字段类型大小说明

Sno CHAR

10 数据来自student

10 数据来自student

Cno CHAR

3 0~100 grade NUMBER

3.增加、修改和删除字段，要求：

（2） 给student表增加一个memo字段，类型为varchar2(200)。

（3） 将memo字段的类型修改为varchar2(300)。

（4） 删除memo字段。

4.向表中添加数据、更新数据、删除数据，并验证约束。要求：

（2） 使用insert into命令向三个表中分别插入若干行数据，验证主键约束、唯一性约束以及默认值约束。

（3） 使用update命令更新数据，验证外键约束。

（4） 使用delete命令删除数据。

5.删除表。要求：

利用drop table语句删除表。

三、分析与思考

实验五索引与视图

一、实验目的：

1.理解索引的概念和类型

2.掌握创建索引的命令

3.理解视图的概念和优点

4.掌握创建一般视图和可更新视图的命令

二、实验内容：

1.在数据库中创建student表，包括学号，姓名，性别，班级。在学号字段上创建唯一索

引。

2.（1）创建简单视图。

Connect scott/tiger;

Grant insert,update,delete on emp to system;

Connect system/oracle;

Create or replace view v_emp

As

Select empno,ename,job,hiredate,deptno from scott.emp;

(2)向视图中增加数据

Insert into v_emp values(1234,’JACK’,’CLERK’,’29-4月-1963’，10)；

（3）利用视图修改数据

Update v_emp set ename=’MARK’ WHERE empno=1234;

三、分析与思考

实验六序列和同义词

一、实验目的：

1.理解序列和同义词的概念和作用。

2.掌握序列的创建与应用。

3.掌握同义词的创建与应用。

二、实验内容：

3.使用新建序列的值，为新表填充字段值。

（1）

创建一张新表。

Create table my_table(id int primary key,

Name varchar2(20));

(2)创建序列id_seq

(3)将序列的值插入新表中。

4.以scott登录，创建emp表的同义词s_e.

(1)以system用户登录后授予scott用户创建同义词的权限。Connect system/oracle;

Grant create public synonym to scott;

(2)以scott登录，创建同义词。

Connect scott/tiger;

Create or replace public synonym s_e for emp;

(3)以system用户登录，利用同义词来操作原表。Connect system/oracle

Desc s_e;

Select * from s_e;

三、分析与思考

实验七PL/SQL编程

一、实验目的：

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

2.理解并熟练掌握各种变量的应用

二、实验内容：

1.使用标量变量。标量变量是最简单的变量，它只能存在单个值，它的数据类型也是系统

预定义的。以system连接数据库，在SQL*Plus中输入如下语句：

DECLARE

V_sal:=2000;

UPDATE scoot.emp SET sal=v_sal WHERE empno=7369;

以上代码定义了一个NUMBER数据类型的变量，并为其赋值2000，在代码被执行时，程序块中的UPDATE语句将员工编码为7369的员工工资更新为该变量值。

2.使用%TYPE类型的变量。使用%TYPE定义变量后，如果用户以后修改数据库中该列的数

据类型，则该变量的类型也会随之改变。在SQL*Plus中输入如下语句：

DECLARE

V_sal scott.emp.sal%TYPE;

BEGIN

V_sal:=20000;

UPDATE scott.emp SET sal= v_sal WHERE empno=7369;

END;

变量v_sal的数据类型会随着Scott.emp表中的sal字段的数据类型变化而变化。 3.使用记录变量。记录变量可以使用%ROWTYPE关键字定义，若记录的结构和现有的任何表

结构都不相同，那么需要自定义记录类型，人后才可以声明该记录类型的变量。在SQL*PLUS中输入如下程序，以查询某员工的信息。

DECLARE

V_row scott.emp%ROWTYPE;

BEGIN

Slect*into v_row frow scott.emp where empno=&no;

Dbms_output.put_line(‘编号’||v_row.empno);

Dbms_output.put_line(‘姓名’||v_row.ename);

Dbms_output.put_line(‘工资’||v_row.sal);

END;

三、分析与思考

实验八游标和触发器

一、实验目的：

1.掌握显示游标的用法。

2.能够熟练掌握DML触发器的使用。

二、实验内容：

1.利用游标输出EMP表的内容，包含EMPNO,ENAME,SAL三个字段。

2.先建立一个日志表emp_log，然后建立DML语句级触发器，对表emp上执行的DML操作进行监控，并将操作人员、操作类型和操作时间写入日志表中。

三、分析与思考

实验九 存储过程与函数

一、实验目的：

1.掌握存储过程与函数的概念

2.能够熟练创建和调用存储过程与函数

二、实验内容：

2.创建存储过程，根据职工编号删除scott.emp表中的相关记录。

（1） 以scott用户连接数据库，然后为system用户授予DELETE权限。

Connect scott/tiger;

GRANT DELETE ON emp TO system;

（2） 以system用户连接数据库,创建存储过程。

CONNECT system/abcdef;

CREATE OR REPLACE PROCEDURE delete_emp

(id scott.emp.empno%TYPE)

IS

BEGIN

DELETE FROM scott.emp WHERE empon=id;

EXCEPTION

WHEN OTHERA THEN

Dbme_output.put_line(‘errors’);

END

(3)system 用户调用delete_emp存储过程。

EXECUTE delete_emp(7369);

(4)scott用户调用delete_emp存储过程

GTANT EXECUTE ON delete_emp TO scott;

CONNECT scott/tiger;

EXECUTE system.delete_emp(7369);

3.创建存储过程，根据职工编号修改Scott.emp表中该职工的其他信息。

（1） 创建新用户，并授予权限。

CONNECT system/abcdef;

CREATE USER u1 IDENTIFIED BY abcdef;

GRANT CREATE SESSION,CREATE PROCEDURE TO u1;

GRANT SELECT,UPDATE ON scott.emp TO u1;

（2） 以新用户连接数据库，创建存储过程。

CONNECT u1/abcdef;

CREATE OR REPLACE PROCEDU update_emp

(no IN SCOTT.emp.empno%TYPE, --引用emp表中的某字段的数据类型，必须对该表具有SELECT权限

Name IN scott.emp.ename%TYPE DEFAULT NULL,

Jobl IN scott.emp.job%TYPE DEFAULT NULL,

mgrl IN scott.emp.mgr%TYPE DEFAULT NULL,

hiredatel IN scott.emp. hiredate%TYPE DEFAULT NULL,

salary IN scott.emp.sal%%TYPE DEFAULT NULL,

comm1 IN https://www.360docs.net/doc/1812442502.html,m%%TYPE DEFAULT NULL,

deptno1 IN scott.emp. deptno%%TYPE DEFAULT NULL,

)

IS

BEGIN

IF name IS NOT NULL THEN

UPDATE scott .emp SET ename=name WHERE empno=no;

END IF;

IF job1 IS NOT NULL THEN

UPDATE scott .emp SET mgr=mgr1 WHERE empno=no;

END IF;

IF mgr1 IS NOT NULL THEN

UPDATE scott .emp SET mgr=mgr1 WHERE empno=no;

END IF;

IF hiredate1 IS NOT NULL THEN

UPDATE scott .emp SET hiredate = hiredate 1 WHERE empno=no;

END IF;

IF salary IS NOT NULL THEN

UPDATE scott .emp SET sal= salary WHERE empno=no;

IF comml IS NOT NULL THEN

UPDATE scott .emp SET comm= comml WHERE empno=no;

END IF;

IF deptnol IS NOT NULL THEN

UPDATE scott .emp SET deptno= deptnol WHERE empno=no;

END IF;

COMMIF;

EXCEPTION

WHEN others THEN

ROLLBACK;

END;

(3)U1 调用update_emp存储过程。

EXEC update_emp(7369,salary=>2000);

4.创建存储过程，根据指定的职工编号查询该职工的详细信息。

（1） 创建存储过程。

CONNECT scott/tiger;

CRETE OR REPLACE PROCEDURE select_emp

(no IN scott.emp.empno%TYPE,emp_information OUT V ARCHAR2)

IS

r scott,emp%ROWTYPE;

BEGIN

SELECT*INTO r FROM scott.emp WHERE empno=no;

Emp_information:=emp_information||r.ename||’’||r.job||’’||r.sal||’’||r.mgr||’’||r.hiredate||’’||https://www.360docs.net/doc/1812442502.html, m||’’||r.deptno;

EXCEPTION

WHEN NO_DATA_FOUND THEN

Emp_information:=’NO person!’;

WHEN others THEN

Emp_information:=’NO Errors!’;

(2)调用存储过程。

SET SERVEROUTPUT ON

DECLARE

Info V ARCHAR2(50);

BEGIN

Select_emp(7369,info);

Dbms_output.put_line(info);

END;

5.创建函数，根据给定的部门编码计算该部门所有职工的平均工资。

（1） 创建函数。

CREATE OR REPLACE FUNCTION avg_sal

(no scott.emp.deptno%TYPE)

RETURN NUMBER

IS

Avgsal number(7,2);

BEGIN

SELECT A VG(sal)INTO avgsal FROM scott.emp WHERE deptno=no;

IF avgsal IS NOT NULL THEN ————因为上面的语句不触发异常，因此用IF语句判断是否查询成功

RETURN avgsal;

ELSE

Avgsal:=-1;

RETURN avgsal;

END IF;

END ang_sal;

(2)调用函数。

BEGIN

Dbms_output.put_line(avg_sal(&deptno));

END;

三、分析与思考