java-oracle中几十个实用的PL/SQL-PLSQL

第三阶段
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, 网管网www_bitscn_com
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

网管u家u.bitscn@com

(
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;