SQL与PL/SQL基础操作

SQL与PL/SQL基础操作

实验目的与要求：

了解PL/SQL在Oracle中的基本概念;掌握PL/SQL的各组成部分; PL/SQL的运用

注意此次实验需提交到实验平台（平台实验项目编号3中）。

实验内容

必做题：

一、SQL基础

1、以首字母大写其余字母小写的方式显示所有员工的姓名

提示：需用到substr(char,m[,n])获取子串的函数、length(char)获取字符串长度、upper(char)小写字母转换成大写字母函数、lower(char)大写字母转换成小写字母函数，并为转换后的姓名字段取别名为：员工姓名

Select upper(substr(ename,1,1))||lower(substr(ename,2,length(ename)-1)) 员工姓名 from scott.emp;

2、请为emp表插入一条数据：empno:7935  ename:test  job:clerk  mgr:7782   hiredate:

sysdate  sal:1305.23  comm:523.66  deptno:10

提示：insert  into 表名 values(各字段相应的值)

 

3、用round函数显示test员工的月薪(sal字段)，请保留1位小数

提示：Round(n,[m]),该函数用于执行四舍五入。如果省掉m，则四舍五入到整数;如果m是正数，则四舍五入到小数点的m位后。如果m是负数，则四舍五入到小数点的m位前。

 

4、用trunc函数显示test员工的月薪(sal字段)，请保留1位小数

提示：trunc(n,[m])是截取字段n小数点后第m位

5、用floor函数显示test员工的奖金(comm字段)

提示：Floor(n)是返回小于或等于n的最大整数。这个函数没有参数

6、用ceil函数显示test员工的奖金(comm字段)

提示：Ceil(n) 是返回大于或等于n的最小整数。这个函数没有参数

7、用虚拟存在的dual表显示12除以4的余数

提示：使用mod(m,n)函数

8、对于每个员工，显示其加入公司的天数，并为其显示天数取别名为：入职天数

提示：用当前系统时间减去入职时间即为加入公司的天数，并用trunc函数为结果入职天数取整

9、日期函数用于处理date类型的数据。默认情况下ORACLE日期格式是dd-mon-yy。

（1）sysdate:返回系统时间

（2）以指定格式返回系统时间：yyyy-mm-dd hh:mi:ss

提示：需要使用to_char函数

二、PL/SQL基础

1）合法字符

用PL/SQL程序时，允许使用下列字符：

所有大、小写字母

数字0到9

符号：（）+-*/〈 〉=！~；：.‘@ %，“# $ ^ & _ | { } ？ [ ]

2）变量

变量是PL/SQL中用来处理数据项所用的名字。读者根据下列规则选择变量名称：

变量必须以字母（A~Z）开头。

其后跟可选的一个或多个字母，数字（0~9）或特殊字符$、# 或_。

变量长度不超过30个字符。

变量名中不能有空格。

3）基本知识：一个完整的PL/SQL语句块由3个部分组成：

Declare

声明部分，定义变量、常量数据、游标、异常、局部子程序等

Begin

执行部分，实现块的功能

Exception

异常处理部分，处理程序执行过程中产生的异常

End；

10、通过变量的形式来显示工号为7844的员工的薪水(sal字段)

提示：如果定义一个类型与某个变量的数据类型或数据库表中某个列的数据类型一致（不知道该变量或列的数据类型）的变量，可以利用%type来实现。

若在sql*plus环境中看到dbms_output.put_line方法的输出结果，必须将环境变量serveroutput 设置为on，方法为：

SQL>set serveroutput on

11、通过记录类型的变量来显示工号为7900的员工信息:显示员工姓名与工资

提示：如果要定义一个与数据库中某个表结构一致的记录类型的变量，可以使用%rowtype来实现

在select into语句中使用记录类型变量

12、如何在values子句中使用记录类型变量来插入数据

为dept 表增加一条记录，三个字段的值如下：deptno:50  loc:BEIJING  dname:COMPUTER

提示：利用%rowtype获取记录类型定义变量，记录类型变量中分量的个数、顺序、类型应该与表中列的个数、顺序、类型完全匹配

Insert into 表名 values 记录类型变量

 

 13、如何在set子句中使用记录类型变量成员来修改数据

在emp表中工号为7935员工（数据库中不一定有，先检查，可用7934）的工资和奖金改成与工号为7369的员工一致，并查看修改后的信息

 

 

 14、如何在delete语句中使用记录类型变量

使用记录类型变量删除emp表中跟工号为7935所在的同一个部门的所有员工。

 

或者：

declare

v_emp scott.emp%rowtype;

begin

select * into v_emp from scott.emp where empno=7369;

insert into scott.emp values (7935,v_emp.ename,v_emp.job,v_emp.mgr, v_emp.hiredate,v_emp.sal, v_emp.comm, v_emp.deptno);

end;

 

select * from scott.emp where empno in (7369,7935);

15、输入一个员工号，修改该员工的工资，如果该员工为10号部门，则工资增加100；若为20号部门，则工资增加200，否则增加300

提示：（1）可以利用if条件语句

if 条件 then 执行语句；

elsif条件 then 执行语句；

…..

Else执行语句；(注意elseif用的形式是elsif)

End if;

（2）可以用case语句

 

 

输入x:7369

Select * from scott.emp where empno in(7369,7935);

 

选做题：

16、创建表temp_table，此表由2个字段构成：num_col number,info_col char(10)，表创建在users表空间

 

1. 利用循环插入50条记录

提示：

Declare

。。。。。。//申明变量

Begin

loop

执行语句；

Exit  [when 条件]；

End loop;

   End；