几十个实用的PL/SQL(3)
第三阶段
Q.使用REF游标显示“EMP”表中的值。
A.
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，并向用户显示增加的薪水及其他详细信息。
A.
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表中插入新记录并且删除某些现有的记录。
A.
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;
/