set serverout on
emp表
1.输入一个员工号,输出该员工的姓名、薪金和大概的服务年限(按年月日显示)
declare
no emp.empno%type;
v_enm emp.ename%type;
v_sal emp.sal%type;
v_nx varchar2(30);
begin
no := &员工号;
select ename,sal,to_char(to_date('00010101','yyyymmdd')+
(sysdate-hiredate) - 366 -31,'yyyy"年"mm"月"dd"日"') into
v_enm,v_sal,v_nx
from emp
where empno = no;
dbms_output.put_line('员工姓名:'||v_enm||chr(10)||'工资:'||v_sal||chr(10)||'工作年限:'||v_nx);
exception
when others then
dbms_output.put_line(sqlerrm);
end;
2.接收一个员工号,输出该员工所在部门的名称
declare
no number;
dno emp.deptno%type;
dnm dept.dname%type;
begin
no := &员工号;
select deptno into dno from emp where empno = no;
select dname into dnm from dept where deptno = dno;
dbms_output.put_line('部门名称是:'||dnm);
exception
when others then
dbms_output.put_line(sqlerrm);
end;
3.接收一个员工号,如果该员工职位是MANAGER,并且在DALLAS工作那么就给他薪金加15%;
如果该员工职位是CLERK,并且在NEW YORK工作那么就给他薪金扣除5%;其他情况不作处理。
declare
no emp.empno%type;
dno dept.deptno%type;
v_job emp.job%type;
dnm dept.dname%type;
begin
no := &员工号;
select deptno,job into dno,v_job from emp
where empno = no;
select dname into dnm from dept
where deptno = dno;
if v_job = 'MANAGER' and dnm = 'DALLAS' then
update emp set sal = sal * 1.15 where empno = no;
elsif v_job = 'CLERK' and dnm = 'NEW YORK' then
update emp set sal = sal * 0.95 where empno = no;
else
null;
end if;
exception
when others then
dbms_output.put_line(sqlerrm);
end;
4.接收一个员工号,输出这个员工所在部门的平均工资
declare
no emp.empno%type;
v_sal number;
begin
no := &员工号;
select avg(sal) into v_sal
from emp where deptno = (select deptno from emp where empno = no);
dbms_output.put_line('平均工资:'||round(v_sal,2));
exception
when others then
dbms_output.put_line(sqlerrm);
end;
5.以交互的方式给部门表插入一条记录,如果出现主键冲突的异常,请显示“部门号已被占用”的字样。
declare
erow dept%rowtype;
begin
erow.deptno := '&部门号';
erow.dname := '&部门名';
erow.loc := '&地址';
insert into dept values (erow.deptno,erow.dname,erow.loc);
exception
when others then
dbms_output.put_line(sqlerrm);
end;