1洪囤、自動以輸入任意員工編號，輸出該員工編號撕氧，輸出該員工變號瘤缩、姓名、工資伦泥、部門剥啤、所在地

declare 
  -- Local variables here
  empno integer;
  ename nvarchar2(20);
  sal float;
  dname nvarchar2(20);
  loc nvarchar2(20);
  i integer;
begin
  -- Test statements here
  select empno,ename,sal,dname,loc into empno,ename,sal,dname,loc from emp join dept on emp.deptno= dept.deptno where empno = &i;
  dbms_output.put_line(empno||' '||ename||' '||sal||' '||dname||' '||loc);
end;

2锦溪、自定義輸入任意員工編號；如果該員工入職時間大于10年府怯，則獎金加1w刻诊，如果該員工入職時間大于5年，獎金加5000富腊，否則不加坏逢。最終輸出員工編號、姓名赘被、入職時間是整、原獎金、現(xiàn)獎金

declare 
  -- Local variables here
  empno integer;
  ename nvarchar2(20);
  comm_before integer;
  comm_after integer;
  hiredate date;
  i integer;
  t integer;
begin
  -- Test statements here
  select empno,ename,hiredate,comm into empno,ename,hiredate,comm_before from emp join dept on emp.deptno= dept.deptno where empno = &i;
  select months_between(sysdate,hiredate) into t from emp where empno = &i;
  if t>=120 then
     update emp set comm=comm+10000 where empno=i;
  elsif t>=60 and t<120 then
     update emp set comm=comm+5000 where empno=i;
  elsif t<60 then
     update emp set comm=comm where empno=i;
  end if;
  select comm into comm_after from emp where empno=i;  
  dbms_output.put_line(empno||' '||ename||' '||hiredate||' '||comm_before||' '||comm_after);
end;
/

3民假、對每位員工的薪水進行判斷浮入，如果該員工薪水高于其所在部門薪水，則將其薪水減50元羊异，否則不變

declare 
  -- Local variables here
  myempno emp.empno%type := '&no';
  empeg scott.emp%rowtype;
  avgSal number;
  saleg number;
  depteg scott.dept%rowtype;
begin
  -- Test statements here
  select * into empeg from scott.emp where emp.empno = myempno;
  select * into depteg from scott.dept where dept.deptno=empeg.deptno;
  select avg(sal) into avgSal from scott.emp group by emp.deptno having emp.deptno=empeg.deptno;
  select emp.sal into saleg from emp where emp.empno = myempno;
  if empeg.sal>avgSal then
    saleg := saleg-50;
  end if;
  dbms_output.put_line('員工編號：' || myempno || '姓名：' || empeg.ename || '之前工資：' || empeg.sal || '現(xiàn)在工資：' || saleg);
end;
/

4事秀、創(chuàng)建一個存儲過程，實現(xiàn)：通過輸入員工編號查看員工姓名野舶、工資易迹、獎金；
1.1 如果輸入的編號不存在平道，則進行異常處理睹欲；
1.2 如果工資高于4000，輸出工資高于4000一屋；
1.3 如果獎金沒有或為0窘疮，進行異常提示處理

create or replace procedure procemp(myempno in int) is
  -- Local variables here
  empeg scott.emp%rowtype;
  ifExist number;
begin
  -- Test statements here
  select * into empeg from scott.emp where emp.empno = myempno;
  select count(1) into ifExist from scott.emp where emp.empno = myempno;
  if ifExist=0 then
     dbms_output.put_line('錯誤！編號不存在');
  elsif empeg.sal>4000 then
     dbms_output.put_line('工資高于4000');
  elsif nvl(empeg.comm,0)=0 then
     dbms_output.put_line('獎金為0');
  else null;
  end if;
    dbms_output.put_line('員工編號：' || myempno || ' 姓名：' || empeg.ename || ' 工資：' || empeg.sal || ' 獎金：' || empeg.comm);
end procemp;