Oracle数据库—— PL/SQL基础编程

一、涉及内容

　　1. 掌握PL/SQL程序块的结构，理解并熟悉各种变量的应用。

二、具体操作

（一）使用system用户登录SQL*PLUS，使用SQL语句创建用户：u_你的姓名首字母（例如：u_zs），密码为： t_你的学号后三位（例如：t_165）。并授予新用户连接数据库的权限，然后以新用户连接数据库。

1. 创建用户u_sxl

 

2. 授予新用户u_sxl连接数据库的权限

 

3. 以新用户u_sxl连接数据库

 

（二）使用scott用户连接数据库，使用SQL语句创建表dep_你的学号后三位（例如：dep_165）

注意：创建表的同时要创建约束

列名    数据类型    列的说明

dno integer 系编号(主键约束)

dname   char(12)    系名(唯一约束)

1. 使用scott用户连接数据库(先修改一下密码)

 

2. 创建表dep_120

 

（三）使用SQL语句创建表stu_你的学号后三位（例如：stu_165）

注意：创建表的同时要创建约束

列名    数据类型    列的说明

sno integer 学号(主键约束)

sname   Varchar2(20)    姓名(非空约束)

sage    integer 年龄(check约束：在12~99岁之间)

sdep    integer 系编号(外键约束)

创建的表格如下所示：

 

（四） 使用scott方案下的emp表和dept表完成下列查询。（注意：每道题只能使用一条SQL语句实现）

1. 查询20号部门的所有员工的姓名、部门名称（要求：使用NATURAL JOIN）

 

2. 查询部门地点（LOC）、员工姓名。如果某部门没有员工，也要显示部门地点。（要求：使用左外连接）

 

3. 查询30号部门的员工奖金（COMM）的合计值。

  

4. 查询每个员工的姓名和他顶头上司（MGR）的姓名（要求：使用自身连接）

 

5. 查询月薪低于2100元，并且所在部门编号是30的员工编号和月薪。（要求：使用INTERSECT）

 

6. 查询平均工资最高的两个部门的部门编号。（要求：使用rownum伪列）

 

7. 查询与ALLEN的所在部门和雇用年份相同的所有员工的姓名。（要求：使用多列子查询）

 

8. 按工作岗位（job）分组，查询每种岗位的员工个数

 

9. 按工作岗位（job）分组，查询每种岗位的薪水的最低值。

 

10. 将“SALES”部门的员工薪水增加15% （注意：需要使用两张表）

 

11. 查询平均月薪高于2800元的部门名称、员工姓名、部门平均月薪。

 

12. 从emp表中删除 所在部门位于“BOSTON”的员工的信息。（注意：需要使用两张表）

    

 

（五）编写PL/SQL程序块，每道题目使用一个程序块实现。

1、创建并执行一个PL/SQL程序块，通过替代变量由键盘输入两个数值x和y，用第一个数x乘以第二个数y再除以10取整（小数四舍五入），将结果存入变量：v_你的姓名首字母（例如：v_zs），然后输出到屏幕上。

 

2、创建并执行一个PL/SQL程序块，查询scott方案的emp表中的最高薪水，将其存入变量：v_你的姓名首字母（例如：v_zs）中，然后在屏幕上显示。

 

3、创建并执行一个PL/SQL程序块，计算scott方案中emp表的某个雇员的年薪，雇员的编号通过替代变量由键盘输入，年薪等于月薪sal乘以12个月再加上奖金comm，使用NVL函数处理空值。将计算出的年薪存入变量：v_你的姓名首字母（例如：v_zs）中，然后将结果输出到屏幕上。

 

4、创建并执行一个PL/SQL程序块，向dept表中添加一行数据，用替代变量输入部门的编号和名称，部门位置为NULL。

 

5、创建并执行一个PL/SQL程序块，更新部门位置的值，用替代变量输入部门的编号和新的部门位置。

 

6、创建并执行一个PL/SQL程序块，删除第4题添加的部门，用替代变量输入部门的编号。