电子科技大学数据库与软件工程实验报告三

一、实验目的

​ 1、学习子查询和常用函数；
​ 2、了解数据库视图的操作；
​ 3、学习 PL/SQL 语言实现对数据库存储过程、触发器等操作。

二、实验内容

​ 在 SQL Developer 中使用学生用户连接 Oracle 数据库，使用实验二导入的 EMP 表（员工表）、DEPT 表（部门表）和 SALGRADE 表（工资等级表），实现数据库子查询、视图、存储过程、触发器等的操作过程。

三、实验软件

Oracle 数据库、SQL Developer

四、实验步骤及数据记录

1. 子查询

​ 1）查询出销售部（SALES）下面的员工姓名、工作、工资。

​ 分析：可以用联接查询实现。由于所需的结果信息都在 EMP 表中，可以先从 DEPT 表中查询出销售部对应的部门号，然后根据当前部门号再到 EMP表中查询出符合该部门的员工记录即可。

SELECT NAME,JOB,SAL FROM EMP
	WHERE DEPTNO=(SELECT DEPTNO FROM WHERE DNAME='SALES');

​ 查询结果，如图所示

​ 2）查询出 EMP 表中比任意一个销售员（SALESMAN）工资低（比销售员工资查询结果中的最大值小）的员工姓名、工作和工资。

​ 分析：销售员在 EMP 表中有很多条记录，每个人工资不相等，如要返回“比任意销售员的工资还低”的条件，只需返回比“最高工资还低”即可。如果用子查询做，子查询中就会返回多条记录。用普通的关系符（>、<等）运行就会出错。这时候需要用关键字 ANY。ANY 放在比较运算符后面，表示“任意”的意思。

SELECT ENAME,JOB,SAL FROM EMP
	WHERE SAL<ANY(SELECT SAL FROM EMP WHERE JOB='SALESMAN');

​ 查询结果如图所示

2. 伪列

​ 1）查询出员工表中前 5 名员工的姓名、工作和工资。

SELECT ROWNUM,ENAME,JOB,SAL FROM EMP WHERE ROWNUM<=5;

​ 查询结果如图所示

​ 2）查询出工资最高的前 5 名员工的姓名、工资和工资。

​ 分析：“工资最高的前 5 名”需要先降序排序，再取前 5 名，但是生成ROWNUM 操作比排序要早，排序时已经连同 ROWNUM 一起排序了，因此不能直接在案例 1 的语句中直接加上 ORDER BY 就行，而是需要对排序的结果重新做二次查询，产生新的 ROWNUM 才能作为查询的条件依据。

SELECT ROWNUM,T.* FROM
	(SELECT ENAME,JOB,SAL
	FROM EMP ORDER BY SAL DESC) T
	WHERE ROWNUM<=5;

​ 查询结果如图所示

3.数据库对象

​ 1）基于 EMP 表和 DEPT 表创建一个只读视图，该视图中只包含员工编号、姓名、工作、入职日期和部门名称，隐藏了员工的工资、奖金、部门编号等信息。

CREATE OR REPLACE VIEW EMPDETAIL
	AS
	SELECT EMPNO,ENAME,JOB,HIREDATE,DNAME
	FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO
	WITH READ ONLY;	

​ 查询创建的 EMPDETAIL 视图：

SELECT * FROM EMPDETAIL;

​ 查询结果如图所示：

​

​ 2）为 EMP 表的 ENAME 列创建唯一索引，为 EMP 表的工资列创建普通索引，把 JOB 列先变为小写再创建索引。

CREATE UNIQUE INDEX UQ_ENAEM_IDX ON EMP(ENAME);
CREATE INDEX IDX_SAL ON EMP(SAL);
CREATE INDEX IDX_JOB_LOWER ON EMP(LOWER(JOB));

​ 使用下面的 SELECT 命令查看表 EMP 的索引：

SELECT INDEX_NAME,INDEX_TYPE,TABEL_OWNER,TABLE_NAME,UNIQUENESS
	FROM USER_INDEXES WHERE TABLE_NAME='EMP';

​ 查询结果如图所示

​

4. 块、存储过程和触发器

​ 1）声明一个名为sname 的变量

DECLARE
	SNAME VARCHAR2(20):='jerry';
BEGIN

	SNAME:SNAME||'and tom';
	DBMS_OUTPUT.PUT_LINE(sname);
END;

​ 在SQL *Plus 中输出数据时，可能没有结果显示，可以使用命令：set serveroutput on设置输出到 SQL *Plus 控制台上。

​ 输出结果如图所示

​

​ 2）创建一个显示雇员总人数的存储过程。

CREATE OR REPLACE PROCEDURE EMP_COUNT
	AS
	V_TOTAL NUMBER(10);
BEGIN
	SELECT COUNT(*) INTO V_TOTAL FROM EMP;
	DBMS_OUTPUT.PUT_LINE('雇员总人数为：'||V_TOTAL);
END;

​ 存储过程需要点击 SQL Developer 的“运行”按钮才能执行，如果存在错误，就会显示：“警告：创建的过程带有编译错误”。如果存在错误，对脚本进行修改，直到没有错误产生。如果编译结果正确，将显示：“过程已编译”。

​ 调用存储过程，在工作表中输入以下语句并执行：

EXECUTE EMP_COUNT;

​ 输出结果如图所示

​

​ 3）制作一个数据库触发器。记录所有对 EMP 表进行的 INSERT、UPDATE和DELETE 操作。

CREATE TABLE EMP_LOG(
	who varchar2(30),
	when date);
	CREATE OR REPLACE TRIGGER EMP_OP
	
	BEFORE INSERT OR UPDATE OR DELETE
	ON EMP
BEGIN
	INSERT INTO EMP_LOG (who,when)
	VALUES(user,sysdate);
END EMP_OP;

​ 执行 EMP 表更新操作，查看日志表

​ 查询结果如图所示：

五、实验结论及思考题

思考题

1.查询出比所有销售员的工资都高的员工姓名、工作和工资。请写出 SELECT命令，并给出查询结果截图。

​ SELECT命令：

SELECT ENAME,JOB,SAL FROM EMP
WHERE SAL>ALL (SELECT SAL FROM EMP WHERE JOB='SALESMAN');

​ 查询结果如图所示

2、查询出 EMPDETAIL 视图中第 5 条到第 10 条之间的记录。请写出 SELECT命令，并给出查询结果截图。

​ SELECT命令：

SELECT *FROM (SELECT rownum no, EMPNO, ENAME, JOB, HIREDATE, DNAME FROM EMPDETAIL WHERE rownum<=10 ORDER BY rownum ASC) WHERE no>=5;

​ 查询结果如图所示

3、创建一个存储过程，显示工资最低的雇员名字。请写出创建存储过程的代码，并给出存储过程的执行结果截图。

代码：

CREATE OR REPLACE PROCEDURE SAL_LOW
AS
V_LOW STRING(20);
BEGIN
SELECT ENAME INTO V_LOW FROM EMP WHERE ROWNUM=1 ORDER 	BY SAL ASC;
DBMS_OUTPUT.PUT_LINE('最低工资：'||V_LOW);
END;

​ 执行结果如图所示

六、总结及心得体会

​ 1.在写思考题2时，用伪列的方法查询的5-10条的人的信息与实际不符合。

原因：ROWNUM是在查询后才生成的，因此要给查询一个用原有的ROWNUM升序排列的命令。

​ 2.在第一题的第二小问中，比任何一个服务员的工资低的意思是比其中一个低，而不是比所有的服务员低，所以代码用ANY，相反若是比所有的都低则用代码ALL。

链接：https://pan.baidu.com/s/1MT8VDCFujWZ76inUM5iHpw
提取码：lyrt