Oracleѧϰ±Ê¼Ç£¨Ê®Èý£©

ÓÃÓαê±à³ÌʵÏÖ
1.¶ÔËùÓÐÔ±¹¤,Èç¹û¸ÃÔ±¹¤Ö°Î»ÊÇMANAGER£¬²¢ÇÒÔÚDALLAS¹¤×÷ÄÇô¾Í¸øËûн½ð¼Ó15£¥£»
Èç¹û¸ÃÔ±¹¤Ö°Î»ÊÇCLERK£¬²¢ÇÒÔÚNEW YORK¹¤×÷ÄÇô¾Í¸øËûн½ð¿Û³ý5£¥;ÆäËûÇé¿ö²»×÷´¦Àí
declare
  cursor c1 is select * from emp;
  c1rec c1%rowtype;
  v_loc varchar2(20);
begin
  for c1rec in c1 loop
    select loc into v_loc from dept where deptno = c1rec.deptno;
    if c1rec.job = 'MANAGER' and v_loc = 'DALLAS' then
       update emp set sal = sal * 1.15 where empno  = c1rec.empno;
    elsif c1rec.job='CLERK' and v_loc = 'NEW YORK' then
       update emp set sal = sal * 0.95 where empno  = c1rec.empno;
    else
  null;
 
   end if;
    
  end loop;  
end; 
2.¶ÔÖ±½ÓÉϼ¶ÊÇ'BLAKE'µÄËùÓÐÔ±¹¤£¬°´Õղμӹ¤×÷µÄʱ¼ä¼Óн£º
    81Äê6ÔÂÒÔÇ°µÄ¼Óн10£¥
    81Äê6ÔÂÒÔºóµÄ¼Óн5£¥
declare
  cursor c1 is select * from emp where mgr = (select
      empno from emp where ename='BLAKE'); --Ö±½ÓÉϼ¶ÊÇ'BLAKE'µÄËùÓÐÔ±¹¤
  c1rec c1%rowtype;
begin
  for c1rec in c1 loop
    if c1rec.hiredate < '01-6ÔÂ-81' then
       update emp set sal = sal * 1.1 where empno = c1rec.empno;
    else
       update emp set sal = sal * 1.05 where empno = c1rec.empno;    
    end if;
  end loop;
end;
3.¸ù¾ÝÔ±¹¤ÔÚ¸÷×Ô²¿ÃÅÖеŤ×ʸߵÍÅųöÔÚ²¿ÃÅÖеÄÃû´Î(ÔÊÐí²¢ÁÐ).
<1>  Ò»ÌõSQLÓï¾ä
   select deptno,ename,sal,(select count(*) + 1
from emp where deptno = a.deptno
and sal > a.sal) as ord
from emp a
order by deptno,sal desc;
<2> PL/SQL¿é
declare 
  cursor cc is
    select * from dept;
  ccrec cc%rowtype;
  curs