oracle9i实验指导书

《Oracle数据库》

实验指导书

实验一 Oracle9i的安装与配置

一、实验目的：

1. 掌握Oracle9i的安装与配置过程 2. 掌握Oracle9I服务的启动与关闭

二、实验内容：

1. Oracle9i的安装与配置过程 2. Oracle9I服务的启动与关闭

三、实验步骤、：

1．启动VMwareWorkstation，启动windows2000server

2. 放入Oracle9i安装光盘第一张，开始在虚拟操作系统下安装Oracle 3．配置客户端，添加服务命名，利用sqlplus进行试连接通过

实验二 Oracle基本知识与SQL*PLUS环境

一．目的和要求

熟悉Oracle的基本知识。

熟悉Oracle的命令操作环境SQL*PLUS。 熟悉并掌握一些SQL*PLUS命令。 二、实验内容：

1．SQLPLUS的基本命令

三、实验步骤：

1．连接数据库 >sqlplus /nolog

>conn sys/demo@demo as sysdba 2.练习下面的命令

APPEND text 或A text 把字符串增加到当前行的末尾 CHANGE /old/new/ 或 C/old/new/ 把当前行的旧字符串替换成新字符串 CLEAR BUFFER 或 CL BUFF 从SQL缓冲区中删除所有行 CONNECT userid/password 或 CONN userid/password 在当前的登录下，击活其它的Oracle用户 DEL 删除当前行 DESCRIBE tablename 或 DESC tablename 显示任何数据库表的数据结构 EXIT 退出SQL*Plus GET filename 把以filename为名字的文件内容调入SQL缓冲区中 INPUT 插入许多行 HELP 击活Oracle内部的帮助部件 HOST command 在SQL*Plus中击活一个操作系统命令 LIST 显示SQL缓冲区的所有行 RUN 显示并运行在缓冲区中的当前SQL命令 SAVE filename 把SQL缓冲区中的内容保存到以filename为名字的文件中，默认路径为orawin\\bin START filename或 @ filename 运行以前保存的命令文件

实验三 Oracle表的创建

一．目的和要求

1．了解并掌握Oracle中表结构的定义。

2．了解并掌握Oracle中的用Create命令定义表的方法，以及表的完整性定义。

3．了解并掌握Oracle中的用Alter命令 和Drop命令对表的修改和删除。

二．实验内容 1． 创建表EMP

CREATE TABLE EMP

(EMPNO NUMBER(4) NOT NULL PRIMARY KEY, ENAME VARCHAR2(10), JOB VARCHAR2(10), MGR NUMBER(4), HIREDATE DATE,

SAL NUMBER(7,2), COMM NUMBER(7,2),

DEPTNO NUMBER(2) NOT NULL);

用SQL*PLUS命令DESCRIBE来看生成的EMP表的列明细清单： 输入命令：DESCRIBE EMP

EMP生成的数据表结构显示如下： 2． 从其他表中抽取字段生成数据表

CREATE TABLE EMP_PART AS

SELECT EMPNO,ENAME,JOB,SAL,COMM FROM EMP; 输入命令：DESCRIBE EMP_PART

EMP_Part生成的数据表结构结果显示如下： 3． DROP命令删除数据表 DROP TABLE EMP_PART;

4． 给数据表EMP增加一个字段SPOUSES_NAME ALTER TABLE EMP

ADD （SPOUSES_NAME CHAR(10)）； 输入命令：DESCRIBE EMP

EMP生成的数据表结构显示如下：

5． 用ALTER的MODIFY命令修改已存在的字段的定义 ALTER TABLE EMP

MODIFY （ENAME VARCHAR2（12））；

输入命令：DESCRIBE EMP

EMP生成的数据表结构显示如下：

6． 用ALTER的DROP命令删除数据表中已存在的约束

ALTER TABLE EMP DROP PRIMARY KEY； 7．创建表CUSTOMER

create table customer(

last_name varchar2 (30) not null, state_cd varchar(2),

sales number); 8．创建表STATE

create table state(

state_cd varchar(2) not null, sate_name varchar2(30));

实验四 数据插入、修改和删除和查询

一．目的和要求

1．在数据表中用Insert增加记录。 2．用Update修改数据表中的数据。 3．用Delete删除表中的数据。 4．了解事务处理过程及其命令。

二．实验内容

1．用Insert在基本表customer中插入数据

SQL>insert into customer values (‘Nicholson’,’CA’,6989.99); SQL>insert into customer values (‘Martin’,’CA’,2345.45); SQL>insert into customer values (‘Laursen’,’CA’,34.34); SQL>insert into customer values (‘Bambi’,’CA’,1234.55); SQL>insert into customer values (‘McGraw’,’NJ’,123.45); 2．在表STATE中插入指定的字段

SQL>insert into state (state_name,state_cd) values (‘Massachusetttes’,’MA’); SQL>insert into state (state_name,state_cd)

2 values (‘California’, ’CA’);

SQL>insert into state (state_name,state_cd)

2 values (‘NewJersey’,’NJ’);

SQL>insert into state (state_name,state_cd)

2 values (‘NewYork’,’NY’);

3．修改数据

把state表中NewYork改为Florida，NY改为FD：

UPDATE state SET state_name = ‘Florida’, state_cd = ‘FD’ where state_name = ‘NewYork’ and state_cd = ‘NY’; 4．删除数据

从STATE表删除state_name为Florida和state_cd为FD的记录：

DELETE FROM STATE WHERE state_name = ‘Florida’ AND state_cd = ‘FD’;

5．查询

1．显示EMP表中所有的部门号、职工名称和管理者号码：

SELECT DEPTNO,ENAME,MGR

FROM EMP;

2． 算术运算符在SQL中的使用

SELECT ENAME, SAL+250*12 FROM EMP;

3． 连字符的使用

把职工号和职工名字连接起来，如下：

SELECT EMPNO||ENAME EMPLOYEE FROM EMP;

把职工号和职工名字中间用‘-’连接起来，并输出‘WORKS IN DEPARTMENT’，如下：

SELECT EMPNO||’-‘||ENAME EMPLOYEE, ‘WORKS IN DEPARTMENT’, DEPTNO

FROM EMP;

4． 禁止重复

如果列举出EMP表中所有部门号：

Select deptno from emp; 5． 排序

按单个字段排序，如按照ENAME排序，

SELECT ENAME, JOB, SAL*12, DEPTNO

FROM EMP

ORDER BY ENAME; 按多个字段排序：

如按部门号升序，按工资降序排序

SELECT DEPTNO, JOB, ENAME

FROM EMP

ORDER BY DEPTNO, SAL DESC;

6． 带条件的查询

1）查询工作是CLERK的所有职工的姓名，职工号和部门号

SELECT ENAME, EMPNO, JOB, DEPTNO

FROM EMP

WHERE JOB = ‘CLERK’;

2）从DEPT表中查询出部门号大于20的部门名称

SELECT DNAME, DEPTNO

FROM DEPT

WHERE DEPTNO > 20; 3）复合条件查询

查询工作是MANAGER并且工资大于1500，或者工作是SALESMAN的职工信息：

SELECT EMPNO，ENAME， JOB，SAL，DEPTNO

FROM EMP

WHERE SAL>1500 AND JOB = ‘MANAGER’ OR JOB = ‘SALESMAN’； 7． 操作符的应用

1）BETWEEN的应用

查询工资在1000到2000之间的职工名字和工资信息。

SELECT ENAME， SAL

FROM EMP

WHERE SAL BETWEEN 1000 AND 2000； 2）IN

查询有7902，7566，7788三个MGR号之一的所有职工：

SELECT EMPNO， ENAME， SAL， MGR

FROM EMP

WHERE MGR IN （7902，7566，7788）； 8． 单&号替代变量

1）数字变量输入：

SELECT EMPNO，ENAME，SAL

FROM EMP

WHERE DEPTNO = &DEPARTMENT_NUMBER;

Enter value for department_number:10 2）字符串变量输入：

SELECT EMPNO，ENAME，SAL*12

FROM EMP

WHERE JOB = ‘&JOB_TITLE’;

Enter value for job_title: MANAGER

实验五 视图、索引、序列和权限设置

一．目的和要求

1．掌握视图（VIEW）在Oracle的应用。 2．了解索引的应用。

3．了解Oracle中权限机制。 4．掌握GRANT和REVOKE命令。 二．实验内容 1．创建视图

生成一个部门号是10的视图：

CREATE VIEW D10EMP AS

SELECT EMPNO, ENAME, SAL

FROM EMP

WHERE DEPTNO = 10;

View created。

2．视图应用

从视图D10EMP中查询出全部信息：

SELECT * FROM D10EMP

ORDER BY ENAME;

3．删除视图

DROP VIEW D10EMP;

4．创建索引

CREATE INDEX I_ENAME ON EMP(ENAME);

CREATE UNIQUE INDEX I_EMPNO ON EMP(EMPNO);

5．索引应用

如果查询语句如下则没有用到索引I_ENAME：

SELECT ENAME,JOB,SAL FROM EMP;

如果查询语句如下则用到索引I_ENAME：

SELCT * FROM EMP WHERE ENAME = ‘JONES’;

6．删除索引

DROP INDEX I_ENAME;

7．创建一个用户

CREATE USER MYSELF IDENTIFIED BY MY;

8．修改用户口令

ALTER USER MYSELF IDENTIFIED BY ME；

9．对象权限授权

把DEPT 的SELECT对象权限授给MYSELF用户：

GRANT SELECT ON DEPT TO MYSELF;

把EMP的SELECT权限授给所有用户：

GRANT SELECT ON EMP TO PUBLIC;

10．收回对象权限

从MYSELF收回所有DEPT的对象权限：

REVOKE ALL ON DEPT FROM MYSELF；

收回所有用户对EMP的SELECT权限：

REVOKE SELECT ON EMP FROM PUBLIC;

11．删除用户

DROP USER MYSELF；

实验六 PL/SQL程序设计

一．目的和要求

1．了解PL/SQL在Oracle中的基本概念。 2．掌握PL/SQL的各组成部分。

3．PL/SQL的运用（存储过程，函数，异常）。 二．实验内容

1．在SQL*Plus中使用PL/SQL块处理

EMP表中职工号7788的职工，如果工资小于3000那么把工资更改为3000： SQL>DECLARE

X NUMBER(7,2); BEGIN

SELECT sal INTO x FROM emp WHERE empno = 7788; IF x < 3000 THEN UPDATE emp SET sal = 3000 WHERE empno = 7788; END IF; END;

如果想运行缓冲区的内容，那么可以用RUN命令或者/命令。请参阅第一章有关内容。

2．无参数的存储过程

CREATE OR REPLACE PROCEDURE proc_execution IS

BEGIN

UPDATE EMP SET ENAME = ‘yourname’

where EMPNO = 9010;

END proc_execution;

存储过程的在SQL*Plus中运行

SQL>EXECUTE proc_execution

3．带输入参数的存储过程：

解雇给定职工号的职工，并调用proc_execution：

SQL>CREATE OR REPLACE PROCEDURE fire_emp

(v_emp_no IN emp.empno%type)

IS

BEGIN

proc_execution;

DELETE FROM EMP WHERE empno = v_emp_no;

END fire_emp; /

Procedure created.

SQL>EXECUT fire_emp(7654)

PL/SQL procedure successfully completed.

存储过程删除了职工号7654的职工。

4．带输入输出的存储过程

查询EMP中给定职工号的姓名、工资和佣金。

SQL> CREATE OR REPLACE PROCEDURE query_emp

(v_emp_no IN emp.empno%type,

v_emp_name OUT emp.ename%type,

v_emp_sal OUT emp.sal%type,

v_emp_comm OUT emp.comm%type) IS

BEGIN

SELECT ename, sal, comm

INTO v_emp_name, v_emp_sal, v_emp_comm

FROM EMP WHERE empno = v_emp_no;

END query_emp; /

Procedure created.

SQL> VARIABLE emp_name varchar2(15);

SQL> VARIABLE emp_sal number;

SQL> VARIABLE emp_comm number;

SQL> EXECUTE query_emp(7654,:emp_name, :emp_sal, :emp_comm);

PL/SQL procedure successfully completed.

SQL> PRINT emp_name;

EMP_NAME

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

MARTIN

5．用Function查询出EMP中给定职工号的工资：

SQL> CREATE OR REPLACE FUNCTION get_sal

(v_emp_no IN emp.empno%type)

RETURN number

IS

V_emp_sal emp.sal%type := 0;

BEGIN

SELECT sal INTO v_emp_sal

FROM EMP WHERE empno = v_emp_no;

RETURN (v_emp_sal);

END get_sal;

/

Procedure created.

SQL>VARIABLE emp_sal number

SQL>EXECUTE :emp_sal := get_sal(7654)

PL/SQL procedure successfully completed .

SQL>PRINT emp_sal

EMP_SAL

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

1250

6．用异常处理完善程序

如例3中：解雇给定职工号的职工，并调用proc_execution：

SQL> CREATE OR REPLACE PROCEDURE fire_emp

(v_emp_no IN emp.empno%type) IS

BEGIN

proc_execution;

DELETE FROM EMP WHERE empno = v_emp_no;

END fire_emp;

Procedure created.

SQL>EXECUT fire_emp(7654)

如果职工号7654的职工不存在则出错。为了避免出错我们使用了EXCEPTION语句。

SQL> CREATE OR REPLACE PROCEDURE fire_emp

(v_emp_no IN emp.empno%type) IS

BEGIN

proc_execution;

DELETE FROM EMP WHERE empno = v_emp_no;

IF SQL%NOTFOUND THEN

RAISE_APPLICATION_ERROR

(-20202,'Employee does not exists.');

END IF;

END fire_emp;

Procedure created.

SQL>EXECUT fire_emp(7654)

就不会出错了。

实验七 触发器和游标

一．目的和要求

1．了解触发器的概念。

2．熟悉触发器的基本用法。 3．了解游标的概念。

4．熟悉游标的基本用法。 二．实验内容 1．声明显式游标

声明一个游标用来读取基表EMP中部门号是20且工作为分析员的职工：

DECLARE

Cursor c1 IS

SELECT ename, sal, hiredate FROM emp

WHERE deptno = 20 AND job = 'ANALYST';

v_ename VARCHAR2(10);

v_sal NUMBER(7,2);

v_hiredate date;

begin

OPEN c1;

FETCH c1 INTO v_ename, v_sal, v_hiredate;

CLOSE c1;

end;

2．游标的应用

使用游标属性判断游标是否打开：

IF c1%OPEN THEN

FETCH c1 INTO v_ename, v_sal, v_hiredate;

ELSE

OPEN c1;

END IF;

利用循环读取数据：

LOOP

FETCH c1 INTO v_ename, v_sal, v_hiredate ;

EXIT WHEN C1%ROWCOUNT > 10;

END LOOP;

利用游标修改数据，如果EMP中部门号是20，工作为分析员的职工工资小于2000，更改为2000：

DECLARE CURSOR c1 IS

SELECT empno, sal, hiredate, rowid

FROM emp WHERE deptno = 20 AND job = ‘ANALYST’

FOR UPDATE OF sal;

Emp_record c1%ROWTYPE;

BEGIN

OPEN c1;

FETCH c1 INTO emp_record;

IF emp_record.sal < 2000 THEN

UPDATE emp set sal = 2000 where empno = emp_record.empno;

END IF;

END;

利用游标，如果部门是SALES，地址不是DALLAS的，地址更改为DALLAS；如果部门不是SALES，地址不是NEW YORK的，地址更改为NEW YORK：

DECLARE CURSOR c1 IS

SELECT dname, loc FROM dept

FOR UPDATE OF loc;

Dept_rec c1%ROWTYPE;

Sales_count NUMBER := 0;

Non_sales NUMBER := 0;

BEGIN

OPEN c1;

LOOP

FETCH c1 INTO dept_rec;

EXIT WHEN c1%NOTFOUND;

IF dept_rec.dname = 'SALES' AND dept_rec.loc != 'DALLAS'

THEN

UPDATE dept SET LOC = 'DALLAS' WHERE CURRENT OF C1;

sales_count := sales_count+1;

ELSE IF DEPT_REC.DNAME != 'SALES' AND DEPT_REC.LOC != 'NEW YORK'

THEN

UPDATE dept SET LOC = 'NEW YORK' WHERE CURRENT OF C1;

non_sales := non_sales + 1;

END IF;

END LOOP;

CLOSE c1;

INSERT INTO counts(sales_set, non_sales_set)

VALUES (sales_count, non_sales);

COMMIT;

END;

3．创建触发器

在SCOTT的EMP表上建立语句前触发器EMP_PERMIT_CHANGES。

CREATE OR REPLACE TRIGGER SCOTT.EMP_Hello

BEFORE

DELETE OR INSERT OR UPDATE

ON SCOTT.EMP

BEGIN

RAISE_APPLICATION_ERROR(-20001,'How are you!');

END;

4．修改触发器

使EMP_Hello触发器不能触发：

ALTER TRIGGER SCOTT.EMP_Hello DISABLE;

5．删除触发器

DROP TRIGGER SCOTT.EMP_Hello;

实验八 Oracle开发

一. 目的和要求

1学习ASP与ORACLE的程序开发 二．实验内容

1．配置IIS

2．编写ASP程序

3．配置ASP与Oracle连接的相应环境 4.进行记录添加与查询的测试

以下是参考内容： s_add.asp

---------------------------------------------------- <%

Set my_conn = Server.CreateObject(\ConnString= \Source=demo1;User Id=user01;Password=lgc;\

my_Conn.Open ConnString

sno = trim(Request.form(\sname=trim(request.form(\sex=trim(Request.form(\

address=trim(Request.form(\

set rs = server.CreateObject(\ sql =\rs.cursorlocation=3

RS.open Sql,my_conn,1,3 RS.AddNew

RS(\

RS(\RS(\

RS(\

RS.update

rs.close

set rs=nothing

response.redirect \%>

S_read.asp

<%Set my_conn = Server.CreateObject(\ConnString= \Source=demo1;User Id=user01;Password=lgc;\

my_Conn.Open ConnString

set rs = server.CreateObject(\ sql =\rs.cursorlocation=3

RS.open Sql,my_conn,1,1

response.write \

response.write \学号姓名性别地址\

do while not rs.eof

sno=rs(\ sex=rs(\

sname=rs(\

address=rs(\

response.write \

response.write \ response.write \ response.write \

response.write \

rs.movenext

response.write \loop

response.write \

rs.close

set rs=nothing %>

Student.htm

\

本文来源：https://www.bwwdw.com/article/e03f.html