实验五oracle综合性实验答案

end loop;
dbms_output.put_line(v_sum);
end;
7、 编写PL/SQL程序，查询5号员工工资，如果工资小于3000，则加200员工资，并提示信息“5号员工工资已更新。”，如果工资大于3000，则提示信息“5号员工工资为XXX，已达到规定标准。”并编写可能的异常情况处理代码。
INSERT INTO DHB.departments VALUES ('4','技术部');
INSERT INTO DHB.departments VALUES ('5','服务部');
commit;
5、INSERT INTO DHB.employees VALUES ('1','张三','男','部门经理',6000, '1101123aadx1','1');
when others then
dbms_output.put_line('发生其它错误！');
end;
8、 编写PL/SQL程序，查询1号员工的工资，使用CASE语句输出其工资等级。工资小于等于3000，等级为“低”；工资大于3000，小于5000，等级为”中”；工资大于等于5000，等级为高；
v_grade:=case
when v_wage<=3000 then '低'
when v_wage>=3000 and v_wage<5000 then '中'
when v_wage>=5000 then '高'
else '工资不明'
end;
dbms_output.put_line('该员工工资等级是'||v_grade);
1、
sqlplus sys/jsjorcl as sysdba
create user dhb identified by jsjorcl
default tablespace users
temporary tablespace temp;
grant sysdba,connect,resource to dhb;
Declare
V_num integer:=2;
v_sum integer:=0;
begin
loop
v_sum:=v_sum+v_num;
v_num:=v_num+2;
if v_num>100 then
exit;
end if;
INSERT INTO DHB.employees VALUES ('4','赵六','男','部门经理',6500, '1101123dddx4','2');
INSERT INTO DHB.employees VALUES ('5','高七','男','职员',2500, '1101123aadx5','2');
INSERT INTO DHB.employees VALUES ('8','孙十','男','职员',2800, '1101123dddx8','3');
commit;
6、 编写PL/SQL程序，使用LOOP…EXIT…END语句计算1—100之间所有偶数之和.
Set serveroutput on;
else
dbms_output.put_line('5号员工工资为'||v_wage||'，已达到规定标准。');
end if;
exception
when no_data_found then
dbms_output.put_line('数据库中没有该员工信息');
insert into dhb.departments values('6','后勤部');
dbms_output.put_line('插入了一条新记录！');
comloyees
set dhb.employees.wage=dhb.employees.wage+200
where dhb.employees.emp_id=5;
commit;
dbms_output.put_line('5号员工工资已更新。');
quit
sqlplus dhb/jsjorcl as sysdba
2、
create table dhb.departments
(dep_id number primary key,
dep_name varchar2(20));
3、
create table dhb.employees
(emp_id number primary key,
emp_name varchar2(20),
sex varchar2(2),
title varchar2(10),
wage number,
idcard varchar2(12),
dep_id number,
foreign key(dep_id) references dhb.departments(dep_id));
from dhb.departments
where dhb.departments.dep_id=6;
dbms_output.put_line('已存在部门号为6的记录，其部门名称是'||v_depname);
exception
when no_data_found then
Set serveroutput on;
Declare
v_wage dhb.employees.wage%type;
v_grade varchar2(10);
begin
select wage into v_wage
from dhb.employees
where dhb.employees.emp_id=1;
Set serveroutput on;
Declare
v_depname dhb.departments.dep_name%type;
v_grade varchar2(2);
begin
select dhb.departments.dep_name into v_depname
exception
when no_data_found then
dbms_output.put_line('数据库中没有该员工信息');
when others then
dbms_output.put_line('发生其它错误！');
end;
9、 编写PL/SQL程序，查询departments表中是否有部门号为“6”的记录，如果没有，插入新记录部门号为6，部门名称为“后勤部”。如果有，显示“已存在部门号为'6'的记录，其部门名称是XXX”。
4、INSERT INTO DHB.departments VALUES ('1','人事部');
INSERT INTO DHB.departments VALUES ('2','办公室');
INSERT INTO DHB.departments VALUES ('3','财务部');
INSERT INTO DHB.employees VALUES ('2','李四','男','职员',3000, '1101123dddx2','1');
INSERT INTO DHB.employees VALUES ('3','王五','女','职员',3500, '1101123aadx3','1');
Set serveroutput on;
Declare
v_wage dhb.employees.wage%type;
begin
select wage into v_wage
from dhb.employees
where dhb.employees.emp_id=5;
if v_wage<3000 then
INSERT INTO DHB.employees VALUES ('6','马八','男','职员',3100, '1101123dddx6','2');
INSERT INTO DHB.employees VALUES ('7','钱九','女','部门经理',5000, '1101123aadx7','3');