幾十個(gè)實(shí)用的PL/SQL(2)

2024-07-21 02:08:01

字體：大中小

供稿：網(wǎng)友

商業(yè)源碼熱門(mén)下載www.html.org.cn

第二階段

q.編寫(xiě)一個(gè)pl/sql程序塊，對(duì)名字以“a”或“s”開(kāi)始的所有雇員按他們的基本薪水的10%加薪。

a.

declare

       cursor c1 is

          select * from emp where substr(ename,1,1)='a' or substr(ename,1,1)='s' for update of sal;

begin

       for i in c1

       loop

              update emp set sal=nvl(sal,0)+nvl(sal,0)*0.1 where current of c1;

       end loop;

end;

/

q.編寫(xiě)一pl/sql，對(duì)所有的“銷(xiāo)售員”(salesman)增加傭金500.

a.

declare

       cursor c1 is

          select * from emp where job='salesman' for update of sal;

begin

       for i in c1

       loop

              update emp set sal=nvl(sal,0)+500 where current of c1;

       end loop;

end;

/

q.編寫(xiě)一pl/sql，以提升兩個(gè)資格最老的“職員”為“高級(jí)職員”。（工作時(shí)間越長(zhǎng)，優(yōu)先級(jí)越高）

a.

declare

       cursor c1 is

          select * from emp where job='clerk' order by hiredate for update of job;

          --升序排列，工齡長(zhǎng)的在前面

begin

       for i in c1

       loop

              exit when c1%rowcount>2;

              dbms_output.put_line(i.ename);

              update emp set job='highclerk' where current of c1;

       end loop;

end;

/

q.編寫(xiě)一pl/sql，對(duì)所有雇員按他們基本薪水的10%加薪，如果所增加的薪水大于5000，則取消加薪。

a.

declare

       cursor c1 is select * from emp for update of sal;

begin

       for i in c1

       loop



              if (i.sal+i.sal*0.1)<=5000 then

                     update emp set sal=sal+sal*0.1 where current of c1;

                     dbms_output.put_line(i.sal);

              end if;



       end loop;

end;

/

q.顯示emp中的第四條記錄。

a.

declare

       cursor c1 is select * from emp;

begin

       for i in c1

       loop

              if c1%rowcount=4 then

                     dbms_output.put_line(i. empno || ' ' ||i.ename || ' ' || i.job || ' ' || i.mgr || ' ' || i.hiredate || ' ' || i.sal || ' ' || i.comm || ' ' || i.deptno);

                     exit;

              end if;

       end loop;

end;

/

上一篇：第一次接受跨國(guó)大公司電話(huà)面試

下一篇：幾十個(gè)實(shí)用的PL/SQL(3)