巢湖学院
11）查询所有工种不是MANAGER和CLERK，且工资大于或等于2000的员工信息。 12）如何按照部门升序、雇员的年薪降序显示信息
13）查询每个部分员工平均工资超过1500的记录信息，并按降序排列。 14）显示每个部门的员工工资总和（使用ROLLUP关键字）。
15）显示每个部门的员工工资总和及每一个岗位工资总和（使用CUBE关键字）。 3、连接查询 1）内连接
查询所以员工的编号、员工姓名和员工所在的部门名称。 2）等值连接
查询职位为MANAGER每个员工的编号、员工姓名和员工所在的部门名称。 3）自然连接
在emp表中检索工资（sal字段）大于2000的记录，并实现emp和dept的自然连接 4）自连接
a、查询不低于CLARK工资的员工号、员工姓名和员工工资，查询后的结果要求按员工工资升序排列。
b、查询所有管理者的下属员工信息。 5）外连接
首先使用INSERT语句在emp表中插入新纪录，但并没有为deptno,dname列插入值，即他们的值为null
INSERT INTO emp(empno,ename,job) VALUES(9527,'EAST','SALESMAN'); 然后在dept表中插入如下记录
INSERT INTO dept(deptno,dname) VALUES(50,'HR',?Shanghai?) 完成下面的外连接：
a、将emp表和dept表进行左外连接，emp为左表，dept表为右表 b、将emp表和dept表进行右外连接，emp为左表，dept表为右表 c、需要了解所有员工和部门信息（完整外连接）。 4、子查询
1）单行单列子查询（使用比较运算符）
查询出既不是最高工资也不是最低工资的员工信息 2）单列多行子查询（IN子查询）
a、查询与SMITH员工从事相同工作的其他员工信息。 b、查询部门人数大于5的部门的员工的信息。 3）ANY或ALL子查询
a、查询工资大于部门编号为10的任意一个员工工资即可的其他部门的员工信息。 b、查询工资大于部门编号为30的所有员工工资的员工信息。 4）多列子查询
查询与‘SMITH’的部门和岗位完全相同的其他员工信息。 5）from子句中使用子查询
a、查询高于自己部门平均工资的员工信息。 b、查询最低工资大于2500的各种工作。 6）关联子查询
检索工资大于同职位的平均工资的员工信息 6、Oracle常用系统函数 1）字符函数的应用
21
巢湖学院
a、查询emp表中员工的姓名、工作和所在的部门，姓名的首字符大写，其他字符小写，工作以小写字符输出。
b、查询工名正好为6个字符的员工信息。 c、查询所有员工姓名的前三个字符。 2）日期和时间类函数
a、查询各月倒数第二天入职的员工信息。 3）转换类函数
a、显示所有员工的姓名、雇佣日期及工龄，日期以“YYYY-MM-DD”格式显示 b、显示1982-1-1后入职的员工信息。
（二）oracle常用的更新语句
1、插入语句
1）分别在emp和dept插入一条记录。
Emp: a、向emp表中插入一条记录，员工号为9357，员工名字为oracle，工资为2050元，部门号为20，入职日期为2002年5月10日
b、在emp表中，插入一条记录 (9527,'EAST','SALESMAN')
Dept: 在dept插入一条记录(40,?DESIGN?,?Beijing?) （能否成功插入？并分析结果） 2）求每个部门的平均工资，并将结果保存到数据库中。
第一步： 在数据库中建立一个有两个属性列的新表，其中一列存放部门号别名，另一列存放相应部门的员工的平均工资。
CREATE TABLE AvgSal(Dept_ID number(2),Sal_Avg number(7,2)) ; 其中Dept_ID代表部门类编号， Sal_Avg代表平均工资。 第二步：插入（如何实现？） 2、更新语句
1）把emp表中员工名称以J开头的所有员工的工资上调20%。 2）将和SCOTT一个部门的所有员工工资上调20%。 3）将oracle员工（刚插入的）调任到ALLEN 所在的部门，其工作和工资、奖金和都和ALLEN一样 。 3、删除语句
1）把emp表中empno=9357的员工信息删除。 2）删除和SCOTT一个部门的所有员工信息。
3) 用turncate删除AvgSal表（刚创建的平均工资表）
（三）oracle常用的事务语句
完成下面操作：
1、向emp表中插入一条记录，员工号为9357，员工名字为oracle，工资为2050元，部门号为20，入职日期为2002年5月10日 2、设置保存点
3、在emp表中，插入一条记录 (9527,'EAST','SALESMAN') 4、回滚事务到检查点，并查询emp，查看事务操作情况 5、回滚整个事务，并查询emp，查看事务操作情况。 5、提交事务
6、查询emp，查看事务操作情况
22
巢湖学院
五、思考题及其它
1、可以用列别名作为order by子句的一部分，但能否将列别名作为having子句的一部分呢？ 2、Where子句中不可以使用聚集函数，但可以使用聚集函数对应的别名作为子句的一部分吗？
3、where子句和having子句的区别？ 4、插入语句的时候应注意什么问题？
23
巢湖学院
实验五： PL/SQL编程
实验学时：2
实验类型：设计型
一．实验目的及要求
1．了解PL/SQL块的基本结构、功能及编程规范。 2．掌握PL/SQL块中各种SQL命令的使用方法。 3．掌握PL/SQL块中流程控制语句的使用方法 4．掌握PL/SQL块中游标的使用方法 5．掌握PL/SQL块中例外处理的使用方法
二、实验主要内容
1． PL/SQL块的基本结构及使用方法。 2． PL/SQL块中流程控制语句的使用方法 3． PL/SQL块中游标的使用方法 4． PL/SQL块中例外处理的使用方法
三、实验仪器设备
在局域网环境下，有一台服务器和若干台客户机。服务器成功安装Oracle 11g数据库服务器（企业版），客户机成功安装Oracle 11g客户端软件，网络服务配置正确，数据库和客户端正常工作。
四、实验内容与步骤
（一）、PL/SQL块中查询命令的使用
1、从控制台输入一个员工编号，声明一个记录类型（RECORD类型）emp_type，然后使用该类型的变量来存储emp表中的一条记录信息（只包括姓名、职务、工资及入职日期），并输出这条记录信息。 SQL> DECLARE
/* 填充代码--定义emp_type记录类型，并定义该类型的一个变量*/ BEGIN
/*填充代码*/ EXCEPTION
WHEN N0_DATA_FOUND THEN
Dbms_output.put_line(?请输入正确的雇员号！?); END； 2、声明一个%ROWTYPE类型的变量rowVar_emp，然后使用该类型的变量来存储emp表中的一条记录信息（SMITH的情况），并输出这条记录信息。 SQL> DECLARE
24
巢湖学院
/* 填充代码--定义%ROWTYPE类型的变量rowVar_emp*/ BEGIN
/*填充代码--查询并输出*/ EXCEPTION
WHEN N0_DATA_FOUND THEN
Dbms_output.put_line(?请输入正确的雇员号！?); END
（二）、PL/SQL块中流程控制语句的使用
1、条件控制语句的使用
1）编写一个PL/SQL块，将EMP表中名为SMITH的雇员的工资进行修改，若原工资大于$2000，则加$500，否则加$1000 。
2）编写一个PL/SQL块，从控制台输入一个部门号，如果是10部门，每个员工增加10%的工资，如果是20号部门，每个员工增加5%的工资，如果是30部门，每个员工增加3%的工资，其他部门的雇员工资增加3%的工资（if?then?elsif语句或者case语句）.
2、 循环控制语句的使用
求100到1000之间的水仙花数（三种循环）。
（三）、SQL/PL块中游标的使用
1、声明一个检索emp表中10号部门雇员信息的游标，然后打开游标，并指定检索的雇员姓名、工资信息，接着使用fetch…into语句和while循环读取游标中的所有雇员信息，最后输出读取的雇员信息.
（五）、SQL/PL块中例外处理的使用
1、从EMP表中删去SMITH的信息 ，并使用预定义异常来捕获可能出现的异常。
2、请编写一个PL/SQL块，从控制台输入一个雇员的编号，并给该雇员工资增加1000元，如果该雇员不存在，请提示。（自定义异常） SQL>DECLARE
/* 填充代码—声明变量和异常*/ BEGIN
/* 填充代码*/
If sql%notfound then
/* 填充代码—激发异常*/ EXCEPTION
/* 填充代码*/ END；
25