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

来源：互联网发布：shadow defender 知乎编辑：程序博客网时间：2024/05/04 21:39

第三阶段

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

DECLARE

TYPE emprectyp IS RECORD

(

EMPNO emp.empno%TYPE,

ENAME emp.ename%TYPE,

JOB emp.job%TYPE,

MGR emp.mgr%TYPE,

HIREDATE emp.hiredate%TYPE,

SAL emp.sal%TYPE,

COMM emp.comm%TYPE,

DEPTNO emp.deptno%TYPE

);

TYPE emp_cursor IS REF CURSOR RETURN emp%ROWTYPE;

vemp_cur EMP_CURSOR;

vemp_rec EMPRECTYP;

BEGIN

OPEN vemp_cur FOR SELECT * FROM emp;

LOOP

FETCH vemp_cur INTO vemp_rec;

EXIT WHEN vemp_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);

END LOOP;

CLOSE vemp_cur;

END;

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

DECLARE

TYPE emprec IS RECORD

(

EMPNO emp.empno%TYPE,

ENAME emp.ename%TYPE,

JOB emp.job%TYPE,

MGR emp.mgr%TYPE,

HIREDATE emp.hiredate%TYPE,

SAL emp.sal%TYPE,

COMM emp.comm%TYPE,

DEPTNO emp.deptno%TYPE

);

i BINARY_INTEGER:=1;

TYPE emp_tab IS TABLE OF EMPREC INDEX BY binary_integer;

vemp EMP_TAB;

CURSOR c1 IS SELECT * FROM emp;

BEGIN

FOR x IN c1

LOOP

vemp(i).empno:=x.empno;

vemp(i).ename:=x.ename;

vemp(i).job:=x.job;

vemp(i).mgr:=x.mgr;

vemp(i).hiredate:=x.hiredate;

vemp(i).sal:=x.sal+500;

vemp(i).comm:=x.comm;

vemp(i).deptno:=x.deptno;

i:=i+1;

END LOOP;

FOR j IN 1..i-1

LOOP

DBMS_OUTPUT.PUT(vemp(j).empno||' '||vemp(j).ename||' '||vemp(j).job);

DBMS_OUTPUT.PUT(vemp(j).mgr||' '||vemp(j).hiredate||' '||vemp(j).sal);

DBMS_OUTPUT.PUT_line(vemp(j).comm||' '||vemp(j).deptno);

END LOOP;

END;

Q.一旦将值送到PL/SQL表后，尝试在PL/SQL表中插入新记录并且删除某些现有的记录。

DECLARE

TYPE emprec IS RECORD

(

EMPNO emp.empno%TYPE,

ENAME emp.ename%TYPE,

JOB emp.job%TYPE,

MGR emp.mgr%TYPE,

HIREDATE emp.hiredate%TYPE,

SAL emp.sal%TYPE,

COMM emp.comm%TYPE,

DEPTNO emp.deptno%TYPE

);

i BINARY_INTEGER:=1;

TYPE emp_tab IS TABLE OF EMPREC INDEX BY binary_integer;

vemp EMP_TAB;

CURSOR c1 IS SELECT * FROM emp;

BEGIN

FOR x IN c1

LOOP

vemp(i).empno:=x.empno;

vemp(i).ename:=x.ename;

vemp(i).job:=x.job;

vemp(i).mgr:=x.mgr;

vemp(i).hiredate:=x.hiredate;

vemp(i).sal:=x.sal;

vemp(i).comm:=x.comm;

vemp(i).deptno:=x.deptno;

i:=i+1;

END LOOP;

-- FOR j IN 1..i-1

-- LOOP

-- DBMS_OUTPUT.PUT(vemp(j).empno||' '||vemp(j).ename||' '||vemp(j).job);

-- DBMS_OUTPUT.PUT(vemp(j).mgr||' '||vemp(j).hiredate||' '||vemp(j).sal);

-- DBMS_OUTPUT.PUT_line(vemp(j).comm||' '||vemp(j).deptno);

-- END LOOP;

--插入记录

DBMS_OUTPUT.PUT_LINE('插入记录:');

vemp(i).empno:=1000;

vemp(i).ename:='Goldens';

vemp(i).job:='Software';

vemp(i).mgr:=null;

vemp(i).hiredate:='2003-01-04';

vemp(i).sal:=8888;

vemp(i).comm:=10;

vemp(i).deptno:=10;

FOR j IN 1..i

LOOP

DBMS_OUTPUT.PUT(vemp(j).empno||' '||vemp(j).ename||' '||vemp(j).job);

DBMS_OUTPUT.PUT(vemp(j).mgr||' '||vemp(j).hiredate||' '||vemp(j).sal);

DBMS_OUTPUT.PUT_line(vemp(j).comm||' '||vemp(j).deptno);

END LOOP;

--删除第5、6条记录

DBMS_OUTPUT.PUT_LINE('删除第5、6条记录:');

FOR j IN 5..i-2

LOOP

vemp(j).empno:=vemp(j+2).empno;

vemp(j).ename:=vemp(j+2).ename;

vemp(j).job:=vemp(j+2).job;

vemp(j).mgr:=vemp(j+2).mgr;

vemp(j).hiredate:=vemp(j+1).hiredate;

vemp(j).sal:=vemp(j+2).sal;

vemp(j).comm:=vemp(j+2).comm;

vemp(j).deptno:=vemp(j+2).deptno;

END LOOP;

vemp(i-1).empno:=null;

vemp(i-1).ename:=null;

vemp(i-1).job:=null;

vemp(i-1).mgr:=null;

vemp(i-1).hiredate:=null;

vemp(i-1).sal:=null;

vemp(i-1).comm:=null;

vemp(i-1).deptno:=null;

vemp(i).empno:=null;

vemp(i).ename:=null;

vemp(i).job:=null;

vemp(i).mgr:=null;

vemp(i).hiredate:=null;

vemp(i).sal:=null;

vemp(i).comm:=null;

vemp(i).deptno:=null;

FOR j IN 1..i-2

LOOP

DBMS_OUTPUT.PUT(vemp(j).empno||' '||vemp(j).ename||' '||vemp(j).job);

DBMS_OUTPUT.PUT(vemp(j).mgr||' '||vemp(j).hiredate||' '||vemp(j).sal);

DBMS_OUTPUT.PUT_line(vemp(j).comm||' '||vemp(j).deptno);

END LOOP;

END;