几十个实用的PL/SQL(3)

<script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

第三阶段

Q.使用REF游标显示“EMP”表中的值。

A.

DECLARE

      TYPEemprectypISRECORD

      (

       EMPNO emp.empno%TYPE,        

       ENAME emp.ename%TYPE,

       JOB   emp.job%TYPE,

       MGR   emp.mgr%TYPE,    

       HIREDATEemp.hiredate%TYPE,

       SAL     emp.sal%TYPE,

       COMM    emp.comm%TYPE,

       DEPTNO  emp.deptno%TYPE

      );

      TYPEemp_cursorISREFCURSORRETURNemp%ROWTYPE;

      vemp_curEMP_CURSOR;

      vemp_recEMPRECTYP;

BEGIN

      OPENvemp_curFORSELECT*FROMemp;

      LOOP

            FETCHvemp_curINTOvemp_rec;

             EXITWHENvemp_cur%NOTFOUND;

            DBMS_OUTPUT.PUT(vemp_rec.empno||' '||vemp_rec.ename||''||vemp_rec.job);

            DBMS_OUTPUT.PUT(vemp_rec.mgr||' '||vemp_rec.hiredate||''||vemp_rec.sal);

            DBMS_OUTPUT.PUT_line(vemp_rec.comm||' '||vemp_rec.deptno);

      ENDLOOP;

      CLOSEvemp_cur;

END;

/

Q.从“EMP”中获得值送到 PL/ SQL表，将 PL/ SQL表中的薪水值增加500，并向用户显示增加的薪水及其他详细信息。

A.

DECLARE

      TYPEemprecISRECORD

      (

       EMPNO emp.empno%TYPE,        

       ENAME emp.ename%TYPE,

       JOB   emp.job%TYPE,

       MGR   emp.mgr%TYPE,    

       HIREDATEemp.hiredate%TYPE,

       SAL     emp.sal%TYPE,

       COMM    emp.comm%TYPE,

       DEPTNO  emp.deptno%TYPE

      );

 

      iBINARY_INTEGER:=1;

 

      TYPEemp_tabISTABLEOFEMPRECINDEXBYbinary_integer; 1 <script type="text/javascript"> </script> <script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>