PL/SQL³õѧÕ߱ضÁ£º¼¸Ê®¸öʵÓõÄPL/SQL

µÚÒ»½×¶Î
Q.±àдһ¸öPL/SQL³ÌÐò¿éÒÔÏÔʾËù¸ø³ö¹ÍÔ±±àºÅµÄ¹ÍÔ±µÄÏêϸÐÅÏ¢¡£
A.
 DECLARE
 erec emp%ROWTYPE;
BEGIN
 SELECT * INTO erec from emp  WHERE empno=&¹ÍÔ±±àºÅ;
 DBMS_OUTPUT.PUT_LINE('EmpNo' || ' ' || 'Ename' || ' '|| 'Job' || ' ' || 'Manager' || ' ' || 'HireDate' || ' ' || 'Salary' || ' ' || 'Commision' || ' ' || 'DeptNo');
 DBMS_OUTPUT.PUT_LINE(erec.ename || ' ' || erec.job || '  ' || erec.mgr || '  ' ||erec.hiredate || '  ' || erec.sal || '  ' || erec.comm || '  ' || erec.deptno);
END;
/
Q.±àдһ¸öPL/SQL³ÌÐò¿éÒÔ¼ÆËãij¸ö¹ÍÔ±µÄÄê¶Èнˮ×ܶ
A.
DECLARE
 esal NUMBER;
 eename emp.ename%TYPE;
BEGIN
 SELECT (NVL(sal,0)+NVL(comm,0))*12,ename INTO esal,eename from emp WHERE empno=&¹ÍÔ±±àºÅ;
 DBMS_OUTPUT.PUT_LINE(eename || '''s Years Salary is ' || esal);
END;
/
Q.°´ÏÂÁмÓн±ÈÖ´ÐУº
  Deptno Raise(%age)
10 5%
20 10%
30 15%
40 20%
¼ÓнµÄ°Ù·Ö±ÈÊÇÒÔËûÃÇÏÖÓеÄнˮΪ¸ù¾ÝµÄ¡£Ð´Ò»PL/SQLÒÔ¶ÔÖ¸¶¨¹ÍÔ±¼Óн¡£
A.
DECLARE
 vcounter NUMBER:=10;
 vraise NUMBER;
BEGIN
 LOOP
  EXIT WHEN vcounter>40;
  UPDATE emp set sal=NVL(sal,0)+NVL(sal,0)*0.05  WHERE deptno=vcounter;
  vcounter:=vcounter+10;
 END LOOP;
END;
/
Q.±àдһPL/SQLÒÔÏò"emp"±íÌí¼Ó10¸öйÍÔ±±àºÅ¡£
£¨Ìáʾ£ºÈç¹ûµ±Ç°×î´óµÄ¹ÍÔ±±àºÅΪ7900£¬ÔòйÍÔ±±àºÅ½«Îª7901µ½7910£©
A.
DECLARE
 vcounter NUMBER;
BEGIN
 SELECT MAX(empno) INTO vcounter from emp;
 FOR i IN 1..10
 LOOP
  vcounter:=vcounter+1;
  INSERT INTO emp(empno) VALUES(vcounter);
 END LOOP;
END;
/
Q.ֻʹÓÃÒ»¸ö±äÁ¿À´½â¾öʵÑé¿Î×÷Òµ4¡£
A
DECLARE
 erec emp%ROWTYPE;
-- vraise NUMBER;
BEGIN
 SELECT * INTO erec
 from emp
 WHERE ename='&ename';
 IF erec.job='CLERK' THEN
  UPDATE emp SET sal=sal+500 WHERE empno=erec.empno;
 ELSIF erec.job='SALESMAN' THEN
  UPDATE emp SET sal=sal+1000 WHERE empno=erec.empno;
 ELSIF e