oracle我以前的资料1.1

/**************题目1:根据编号查部门号*********************/
declare
  v_deptno naemp.EMPDEPTNO%type;
  v_empno naemp.EMPNO%type;
begin
  v_empno:=&请输入雇员编号;
  select empdeptno into v_deptno from naemp where empdeptno=v_empno;
  if sql%notfound  then
    dbms_output.PUT_LINE('没有这个雇员');
  else
    dbms_output.PUT_LINE('该雇员的编号为:'||v_deptno);
  end if;
exception
  when others then
    dbms_output.PUT_LINE('There is not such a empno');

end;

/*****************题目2:计算年收入************************/
declare
  v_empname naemp.EMPNAME%type;
  v_sal number;
begin
  v_empname:=&请输入雇员姓名;
  select empsal into v_sal from naemp where empname=v_empname;
  dbms_output.PUT_LINE('This employee has a hole year salary of  '||v_sal*12);

end;
/*******************题目3:计算加薪*****************/
declare
    v_no naemp.EMPNO%type;
    v_deptno naemp.EMPDEPTNO%type;
    v_sal naemp.EMPSAL%type;
    cursor emp_cur
    is
    select empno,empdeptno,empsal from naemp;
begin
    open emp_cur;
    dbms_output.PUT_LINE(emp_cur%rowcount);
    fetch emp_cur into v_no,v_deptno,v_sal;
    while emp_cur%found
    loop
        v_sal:=case v_no
                  when 10 then 1.05*v_sal
                  when 20 then 1.10*v_sal
                  when 30 then 1.15*v_sal
                  when 40 then 1.20*v_sal
                  else         v_sal
               end;      
        update naemp set empsal=v_sal where empno=v_no;
        fetch emp_cur into v_no,v_deptno,v_sal;
    end loop;
    close emp_cur;
    commit;
end;