ORACLE试验参考指导书.doc

ORACLE数据库技术试验指导书

ORACLE数据库技术试验指导书

目 录

试验一 SQL*PLUS使用方法及SQL语言中简单查询语句

试验二 SQL语言中分组查询和连接查询语句

试验三 SQL语言中子查询及函数使用

试验四 SQL语言中数据操纵、事务控制和数据定义语句

试验五 SQL/PL块中SQL命令使用

试验六 SQL/PL块中步骤控制语句、游标、例外处理

试验七 数据库触发器、存放过程和存放函数

试验一 SQL*PLUS使用方法及SQL语言中简单查询语句

一、试验目标

1、熟悉SQL语言交互式使用工具SQL*PLUS使用方法

2、熟悉SQL*PLUS常见命令功效和使用方法

3、掌握SQL语言中简单查询语句功效和使用方法

二、试验环境

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

2、软件系统：Windows Server 网络操作系统，Windows /XP用户机操作系统；

Oracle9i服务端数据库系统，用户端工具。

三、试验内容

1、SQL*PLUS使用

(1) 进入（开启）SQL*PLUS

在Windows环境下,双击SQL*PLUS图标或从程序组找SQL*PLUS ，出现登录窗口

输入正确数据库用户名、密码和连接字符串后，若连接数据库成功，则会出现以下提醒符：SQL>

(2) 退出 SQL*PLUS

SQL>exit

则退回到Windows桌面。

(3) 创建表结构：Create table

创建部门记录表dept和雇员记录表emp结构

SQL> Create table dept

( deptno number(2) not null，

dname char(14)，

loc char(13))；

SQL> Create table emp

( empno number(4) not null，

ename char(9)，

job char(10),

mgr number(4),

Hiredate date,

sal number(7,2),

comm number(7,2),

deptno number(2) not null )；

(4) 显示表结构

显示部门记录表dept和雇员记录表emp结构

SQL>desc dept

SQL> desc emp

(5) 向表中插入统计

向部门记录表dept中插入一条统计

SQL> insert into dept

Values (60，‘Computer’，‘Beijing’)；

SQL> select * from dept;

向部门记录表dept中连续插入多条统计

SQL> insert into dept (deptno，dname，loc)

Values ( &deptno，&dname，&loc);

(6) 实施SQL缓冲区中命令

SQL>/ (直接实施)

SQL>run (先显示命令内容，再实施 )

(7) 实施磁盘上命令文件

a. 先调入缓冲区，再运行：

SQL>get f:\ora\dept.sql

SQL>/

b. 用@ 或start命令将指定命令文件调入缓冲区并实施。

SQL>@f:\ora \dept.sql

或 SQL>start f:\ora \dept.sql

2、简单查询语句

(1) 无条件简单查询：查表中全部统计

SQL> select Dname, Deptno

2 From dept；

对查询结果进行计算和统计

SQL> select count(*) ALL_EMP， sum(sal) ALL_Sal

2 From emp；

(2) 有条件简单查询：查表中部分统计

查在20号部门工作雇员姓名和工资

SQL> select ename，sal，deptno

2 From emp

3 where deptno=20；

查找工种是职员或分析员雇员姓名和工种

SQL> select ename，job

2 From emp

3 where job IN (‘Clerk’，’Analyst’)；

查找以“S”开头雇员姓名和所在部门

SQL> select ename，deptno

2 From emp

3 where ename like ‘S%’；

按工资升序排列20号部门雇员

SQL> select ename，sal，deptno

2 From emp

3 where deptno=20

4 order by sal asc ；

查全部部门中工资大于2800美元雇员

SQL> select ename，sal

from Account

where sal > 2800

union

select ename，sal

from Research

Where sal > 2800

Union

Select ename，sal

From sales

Where sal > 2800 ；

查在全部部门中全部存在工种

SQL> select Job

from Account

Intersect

select Job

from Research

Intersect

Select Job

From sales；

查在Account部门中有哪些职业，是Sales部门中所没有

SQL> select Job

from Account

minus

Select Job

From sales；

试验二 SQL语言中分组查询和连接查询语句

一、试验目标

1、掌握SQL语言中分组查询命令功效及其使用方法

2、掌握SQL语言中连接查询命令功效及其使用方法

二、试验环境

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

2、软件系统：Windows Server 网络操作系统，Windows /XP用户机操作系统；

Oracle9i服务端数据库系统，用户端工具。

三、试验内容

1、分组查询命令使用

(1) 不带条件分组查询：每组只返回一条统计。

计算每个部门工资总和

SQL> select deptno，sum(sal)

2 From emp

3 Group by deptno ；

查每部门每个工种雇员数。

SQL> select deptno， job，count(*)

2 From emp

3 Group by deptno，job；

(2) 带条件分组查询

部门工资总和超出$9000部门,并按工资总和升序排列。

SQL> select deptno，sum(sal)

2 From emp

3 Group by deptno

4 Having sum(sal) > 9000

5 Order by sum(sal) ；

2、连接查询命令使用

(1) 无条件连接

SQL> Select Dept. Deptno，Dname，Empno，Ename，Sal

2 From Dept，Emp；

(2) 等值连接 ：参与连接表在公共列上有相同值 。

查看工资高于￥3000雇员及所在部门情况 。

SQL>Select ename，sal，emp.deptno，loc，dname

2 From emp，dept

3 Where Emp.deptno=Dept.deptno and sal>3000 ；

(3) 自连接

查询每个雇员经理名字。

SQL> select worker.ename，worker.empno，manager.ename manager,manager.empno mgr

From emp worker，emp manager

Where worker. mgr=manager. empno；

(4) 外连接：可避免连接时丢失信息

SQL> Select ename，dept.deptno，loc

From emp，dept

Where emp.deptno = dept.deptno；

SQL> Select ename，dept.deptno，loc

From emp，dept

Where emp.deptno(+) = dept.deptno；

SQL> Select ename，dept.deptno，loc

From emp，dept

Where dept.deptno= emp.deptno (+)；

试验三 SQL语言中子查询及函数使用

一、试验目标

1、掌握SQL语言中子查询命令功效及其使用方法

2、掌握子查询命令中函数使用方法

二、试验环境

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

2、软件系统：Windows Server 网络操作系统，Windows /XP用户机操作系统；

Oracle9i服务端数据库系统，用户端工具。

三、试验内容

1、子查询命令使用

(1) 单行值子查询：子查询返回一条纪录

查询工资高于7698号雇员，而且工种和她相同雇员情况。

SQL> select ename，sal，job，empno

From emp

Where sal> ( Select sal

From emp

Where empno=7698 )

and job=( Select job

From emp

Where mpno=7698)；

查询工资高于或等于20号部门工资额最高雇员情况 。

SQL> Select ename，sal ，deptno

From emp

Where sal>=( Select max(sal)

From emp

Where deptno=20 );

(2) 多行值子查询：子查询返回一组纪录

查工资在￥到￥2900之间雇员所在部门雇员。

SQL> Select ename，sal ，job，deptno

From emp

Where deptno IN ( Select deptno

From emp

Where sal between and 2900);

查工资在￥到￥2900之间雇员所在部门雇员。

SQL> Select ename，sal ，job，deptno

From emp

Where deptno= ANY ( Select deptno

From emp

Where sal between and 2900)

Order by sal desc ；

(3) 多列值子查询

查询工资、奖金和20号部门雇员匹配(相同)雇员情况 。

SQL> Select ename，sal， comm， deptno

From emp

Where ( NVL(comm，0)，sal) IN ( Select NVL (comm，0)，sal

From emp

Where deptno=20) ；

2、子查询命令中函数使用

将全部雇员名字用大小写形式显示 。

SQL> Select empno，Upper(ename)，Initcap(ename)

From emp；

计算全部雇员日工资（设30天为25个工作日） 。

SQL> Select ename，round(sal/25，0)，round(sal/25，-1)

From emp；

显示目前日期，dual 是系统提供一张空表 。

SQL> Select sysdate

From dual ；

显示目前日期。

SQL> Select to_char( sysdate，’yyyy-mm-dd’)

From dual ；

分部门统计全部雇员中收入 。

SQL> Select deptno，sum(sal+comm)， sum(sal+NVL(comm，0))

From emp

Group by deptno；

依据输入值将雇员信息排序：若输入1，则按ename排序；若输入2，则按SAL排序；

缺省时，按ename 排序。

SQL> Select *

from emp

order by decode ( &ob，1 ，ename，2，sal，ename)；

试验四 SQL语言中数据操纵、事务控制和数据定义语句

一、试验目标

1、掌握SQL语言中数据操纵命令功效及其使用方法

2、掌握SQL语言中事务控制命令功效及其使用方法

3、掌握SQL语言中数据定义命令功效及其使用方法

二、试验环境

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

2、软件系统：Windows Server 网络操作系统，Windows /XP用户机操作系统；

Oracle9i服务端数据库系统，用户端工具。

三、试验内容

1、数据操纵命令使用

(1) 数据插入

向Dept 表插入一行数据(插入全部列时可省略列名) 。

SQL>insert into dept

Values (60，‘Computer’，‘Beijing’);

向dept表中一次插入多个纪录(全部字段可用*替换) 。

SQL> insert into dept (deptno，dname，loc)

Values ( &deptno，&dname，&loc);

将dept表中全部统计插入到空表department中。

SQL> insert into department

select *

from dept;

向Emp表中插入一条和Smith 内容相同统计，但姓名改为Richard，雇员号改为9999。

SQL> insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)

select 9999,‘Richard’,job,mgr,hiredate,sal,comm,deptno

from emp

where ename=‘Smith’;

(2) 数据修改

将 Martin 提升为经理，工资加￥1000 。

SQL> update emp

Set job = ‘Manager’，sal=sal+1000

Where ename= ‘Martin’;

将30号部门全部雇职员资和奖金，变为20号部门雇员最高工资和奖金。

SQL> update emp

Set (sal，comm)=( Select Max(sal)，max(NVL(comm，0))

From emp

Where deptno =20)

Where deptno=30；

(3) 数据删除

将 Martin从企业删除 。

SQL> delete from emp

Where ename= ‘Martin’；

2、事务控制命令使用

(1) 事务提交命令Commit

SQL> commit；

(2) 事务回退命令Rollback

SQL> Rollback ；

3、数据定义命令使用

(1) 表操作

手工创建表dept结构。

SQL> Create table dept

( deptno number(2) not null，

dname char(14)，

loc char(13))；

经过复制创建表deptpartment结构，其结构和dept相同。

SQL> Create table deptpartment

as select *

from dept

where 1=2；

将dept 表中Dname 列宽度加大到20个字符。

SQL> Alter table dept

modify dname char (20) ；

为表dept增加一列All_emp 用来存放部门人数 。

SQL> Alter table dept

add all_emp number(3)；

删除dept表。

SQL> drop table dept；

(2) 视图操作

为20号部门雇员创建一个视图，包含姓名,工资,部门号 。

SQL> Create view emp_view(name ，salary ，no)

As Select ename ，sal ，deptno

From emp

where deptno=20 ；

(3) 索引操作

为emp表建立empno列上唯一索引。

SQL> create unique index empno_index

on emp(empno);

(4) 同义词操作

为gx1用户dept表建立同义词New_dept 。

SQL> create synonym new_dept

for gx1.dept;

(5) 序列生成器操作

创建一个序列生成器deptno_seq，起始值为50,间隔为10 。

SQL> create sequence deptno_seq

Start with 50

increment by 10；

列出deptno_seq下次值(第一次使用时为50 )

SQL> select deptno_seq.nextval NEXT_VAL

From dual；

(6) 数据库链操作

创建一个数据库链Link_xsdb，用来连接远程销售数据库xsdb，其用户名为scott，口令为tiger。

SQL> create Database Link Link_xsdb

connect to scott identified by tiger

Using ‘xsdb’；

试验五 SQL/PL块中SQL命令使用

一、试验目标

1、了解SQL/PL块基础结构和功效

2、掌握SQL/PL块中多种SQL命令使用方法

二、试验环境

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

2、软件系统：Windows Server 网络操作系统，Windows /XP用户机操作系统；

Oracle9i服务端数据库系统，用户端工具。

三、试验内容

1、SQL/PL块中查询命令使用

将7788号雇员工资和奖金作为Smith工资和奖金。

SQL> DECLARE

v_empno emp.empno%TYPE:=7788；

v_ename emp.ename%TYPE:=‘Smith’；

v_sal emp.sal%TYPE；

v_comm m%TYPE；

BEGIN

SELECT sal，comm INTO v_sal，v_comm

FROM EMP

WHERE empno=v_empno；

update emp

set sal=v_sal，comm=v_comm

where ename= v_ename ；

commit;

END；

查询SMITH情况 。

SQL> DECLARE

emp_rec EMP%ROWTYPE；

v_ename EMP.ename%TYPE：='SMITH'；

BEGIN

SELECT * INTO emp_rec

FROM EMP

WHERE ENAME=v_ename；

------------------

END；

2、SQL/PL块中数据操纵命令使用

向EMP表插入一新雇员 。

SQL> DECLARE

v_deptno dept.deptno%TYPE not null:=50；

v_dname dept.dname%TYPE:=‘Computer’；

v_loc dept.loc%TYPE:=‘Beijing’；

BEGIN

insert into dept(deptno，dname，loc)

values(v_deptno，v_dname，v_loc)；

commit work;

END；

修改7788号雇员工资 。

SQL> DECLARE

v_empno emp.empno%TYPE:=7788；

v_addsal emp.sal%TYPE；

BEGIN

v_addsal:=1000；

UPDATE EMP

SET sal=sal+v_addsal

WHERE empno=v_empno；

COMMIT；

END；

从EMP表中删除7788号雇员 。

SQL> DECLARE

BEGIN

DELETE FROM EMP

WHERE empno=7788；

COMMIT；

END；

试验六 SQL/PL块中步骤控制语句、游标、例外处理

一、试验目标

1、掌握SQL/PL块中步骤控制语句使用方法

2、掌握SQL/PL块中游标使用方法

3、掌握SQL/PL块中例外处理使用方法

二、试验环境

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

2、软件系统：Windows Server 网络操作系统，Windows /XP用户机操作系统；

Oracle9i服务端数据库系统，用户端工具。

三、试验内容

1、SQL/PL块中步骤控制语句使用

(1) 条件控制语句使用

将EMP表中名为SMITH雇员工资进行修改，若原工资大于$，则加$500，不然加$1000 。

SQL>DECLARE

v_ename emp.ename%TYPE:='SMITH'；

v_addsal emp.sal%TYPE；

v_sal emp.sal%TYPE；

BEGIN

SELECT sal INTO v_sal

FROM EMP

WHERE ENAME=v_ename；

IF v_sal>20OO THEN

v_addsal:=500；

ELSE

v_addsal:=1000；

END IF；

UPDATE EMP

SET sall=sal+v_addsal

WHERE ENAME=v_ename；

commit；

END；

(2) 循环控制语句使用

给10号部门增加新雇员，只确定雇员号，其它信息忽略 。

SQL>DECLARE

v_empno emp.empno%TYPE:=8000；

BEGIN

LOOP

INSERT INTO emp(deptno,empno)

VALUES( 10，v_empno)；

v_empno:=v_empno+l00；

EXIT WHEN v_empno>=9000；

END LOOP；

END；

(3) GOTO控制语句使用

给10号部门增加新雇员，只确定雇员号，其它信息忽略 。

SQL>DECLRE

v_empno emp.empno%TYPE:=8000；

BEGIN

《lab1》

INSERT INTO emp(deptno,empno)

VALUES(l0，v_empno)；

v_empno:=v_empno+100；

IF v_empno<=9000 THEN

GOTO lab1；

END IF；

END；

2、SQL/PL块中游标使用

取出10号部门雇员姓名和工资

SQL>DECLARE

v_deptno emp.deptno%TYPE；

v_ename emp.ename%TYPE；

v_sal emp.sal%TYPE；

CURSOR C1 IS

SELECT ename，sal

FROM EMP

WHERE DEPTNO=v_deptno；

BEGIN

v_deptno:=10；

OPEN Cl；

FETCH C1 INTO v_ename，v_sal；

CLOSE C1 ；

END；

查询10号部门全部雇员姓名、工资，并插入到一临时表TMP中 。

SQL>DECLARE

v_deptno emp.deptno%TYPE:=10；

CURSOR C1 IS

SELECT ename，sal

FROM EMP

WHERE deptno=v_deptno；

emp_ec C1%ROWTYPE；

BEGIN

FOR emp_rec IN C1 LOOP