第一阶段
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程序块以计算某个雇员的年度薪水总额。
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 erec.job='ANALYST' THEN
UPDATE emp SET sal=sal+1500 WHERE empno=erec.empno;
ELSE
UPDATE emp SET sal=sal+2000 WHERE empno=erec.empno;
END IF;
-- UPDATE emp SET sal=sal+vraise WHERE empno=erec.empno;
-- DBMS_OUTPUT.PUT_LINE(vraise);
END;
/
Q.接受两个数相除并且显示结果。假如第二个数为0，则显示消息"DIVIDE BY ZERO"。
A.
DECLARE
num1 NUMBER;
num2 NUMBER;
BEGIN
num1:=#
num2:=#
DBMS_OUTPUT.PUT_LINE(num1 '/' num2 ' is ' num1/num2);
EXCEPTION
WHEN ZERO_DIVIDE THEN
DBMS_OUTPUT.PUT_LINE('Didn''t your teacher tell you not to DIVIDE BY ZERO?');
END;
/
第二阶段
Q.编写一个PL/SQL程序块，对名字以"A"或"S"开始的所有雇员按他们的基本薪水的10%加薪。
A.
DECLARE
CURSOR c1 IS
SELECT * FROM emp WHERE SUBSTR(ename,1,1)='A' OR SUBSTR(ename,1,1)='S' FOR UPDATE OF sal;
BEGIN
FOR i IN c1
LOOP
UPDATE emp SET sal=NVL(sal,0)+NVL(sal,0)*0.1 WHERE CURRENT OF c1;
END LOOP;
END;
/
Q.编写一PL/SQL，对所有的"销售员"(SALESMAN)增加佣金500.
A.
DECLARE
CURSOR c1 IS
SELECT * FROM emp WHERE job='SALESMAN' FOR UPDATE OF sal;
BEGIN
FOR i IN c1
LOOP
UPDATE emp SET sal=NVL(sal,0)+500 WHERE CURRENT OF c1;
END LOOP;
END;
/
Q.编写一PL/SQL，以提升两个资格最老的"职员"为"高级职员"。
（工作时间越长，优先级越高）
A.
DECLARE
CURSOR c1 IS
SELECT * FROM emp WHERE job='CLERK' ORDER BY hiredate FOR UPDATE OF job;
--升序排列，工龄长的在前面
BEGIN
FOR i IN c1
LOOP
EXIT WHEN c1%ROWCOUNT>2;
DBMS_OUTPUT.PUT_LINE(i.ename);
UPDATE emp SET job='HIGHCLERK' WHERE CURRENT OF c1;
END LOOP;
END;
/
Q.编写一PL/SQL，对所有雇员按他们基本薪水的10%加薪，假如所增加的薪水大于5000，则取消加薪。
A.
DECLARE
CURSOR c1 IS SELECT * FROM emp FOR UPDATE OF sal;
BEGIN
FOR i IN c1
LOOP
IF (i.sal+i.sal*0.1)<=5000 THEN
UPDATE emp SET sal=sal+sal*0.1 WHERE CURRENT OF c1;
DBMS_OUTPUT.PUT_LINE(i.sal);
END IF;
END LOOP;
END;
/
Q.显示EMP中的第四条记录。
A.
DECLARE
CURSOR c1 IS SELECT * FROM emp;
BEGIN
FOR i IN c1
LOOP
IF c1%ROWCOUNT=4 THEN
DBMS_OUTPUT.PUT_LINE(i. EMPNO ' ' i.ENAME ' ' i.JOB ' ' i.MGR ' ' i.HIREDATE ' ' i.SAL ' ' i.COMM ' ' i.DEPTNO);
EXIT;
END IF;
END LOOP;
END;
/
第三阶段
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;
BE
