使用pl/sql

第一阶段 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:=&num; num2:=&num; 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; / 问题点数：20、回复次数：102 Top 1 楼acev（睡眠不足(域名 .com ￥55，.cn ￥30 QQ:230567)）回复于 2003-06-18 17:31:28 得分 0 第三阶段 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; / Top 2 楼acev（睡眠不足(域名 .com ￥55，.cn ￥30 QQ:230567)）回复于 2003-06-18 17:31:58 得分 0 第四阶段 Q.编写一过程以接受用户输入的三个部门编号并显示其中两个部门编号的部门名称。 A. CREATE OR REPLACE PROCEDURE DeptName(no1 dept.deptno%TYPE,no2 dept.deptno%TYPE,no3 dept.deptno%TYPE) AS vflag NUMBER; vdeptno1 dept.deptno%TYPE; vdeptno2 dept.deptno%TYPE; vdname1 dept.dname%TYPE; vdname2 dept.dname%TYPE; BEGIN vflag:=TO_NUMBER(TO_CHAR(SYSDATE,'SS')); IF (vflag>=1 AND vflag<=10) OR (vflag>=50 AND vflag<60) THEN SELECT deptno,dname INTO vdeptno1,vdname1 FROM dept WHERE deptno=no1; SELECT deptno,dname INTO vdeptno2,vdname2 FROM dept WHERE deptno=no2; ELSIF (vflag>=11 AND vflag<=20) OR (vflag>=40 AND vflag<50) THEN SELECT deptno,dname INTO vdeptno1,vdname1 FROM dept WHERE deptno=no1; SELECT deptno,dname INTO vdeptno2,vdname2 FROM dept WHERE deptno=no3; ELSE SELECT deptno,dname INTO vdeptno1,vdname1 FROM dept WHERE deptno=no2; SELECT deptno,dname INTO vdeptno2,vdname2 FROM dept WHERE deptno=no3; END IF; DBMS_OUTPUT.PUT_LINE('部门编号:'||vdeptno1 ||' '||'部门名称:' ||vdname1); DBMS_OUTPUT.PUT_LINE('部门编号:'||vdeptno2 ||' '||'部门名称:' ||vdname2); END; / EXECUTE DeptName(10,20,30); Q.编写一过程以显示所指定雇员名的雇员部门名和位置。 A. CREATE OR REPLACE PROCEDURE DeptMesg(pename emp.ename%TYPE,pdname OUT dept.dname%TYPE,ploc OUT dept.loc%TYPE) AS BEGIN SELECT dname,loc INTO pdname,ploc FROM emp,dept WHERE emp.deptno=dept.deptno AND emp.ename=pename; END; / VARIABLE vdname VARCHAR2(14) VARIABLE vloc VARCHAR2(13) EXECUTE DeptMesg('SMITH',:vdname,:vloc); PRINT vdname vloc; Q.编写一个给特殊雇员加薪10%的过程，这之后，检查如果已经雇佣该雇员超过60个月，则给他额外加薪3000. A. CREATE OR REPLACE PROCEDURE Raise_Sal(no IN NUMBER) AS vhiredate DATE; vsal emp.sal%TYPE; BEGIN SELECT hiredate,sal INTO vhiredate,vsal FROM emp WHERE empno=no; IF MONTHS_BETWEEN(SYSDATE,vhiredate)>60 THEN vsal:=NVL(vsal,0)*1.1+3000; ELSE vsal:=NVL(vsal,0)*1.1; END IF; UPDATE emp SET sal=vsal WHERE empno=no; END; / VARIABLE no NUMBER BEGIN :no:=7369; END; / EXECUTE Raise_Sal(:no) SELECT empno,ename,sal,comm,hiredate FROM emp WHERE empno=:no; Q.编写一个函数以检查所指定雇员的薪水是否有效范围内。不同职位的薪水范围为： Designation Raise Clerk 1500-2500 Salesman 2501-3500 Analyst 3501-4500 Others 4501 and above. 如果薪水在此范围内，则显示消息“Salary is OK”，否则，更新薪水为该范围内的最水值。 A. CREATE OR REPLACE FUNCTION Sal_Level(no emp.empno%TYPE) RETURN CHAR AS vjob emp.job%TYPE; vsal emp.sal%TYPE; vmesg CHAR(50); BEGIN SELECT job,sal INTO vjob,vsal FROM emp WHERE empno=no; IF vjob='CLERK' THEN IF vsal>=1500 AND vsal<=2500 THEN vmesg:='Salary is OK.'; ELSE vsal:=1500; vmesg:='Have updated your salary to '||TO_CHAR(vsal); END IF; ELSIF vjob='SALESMAN' THEN IF vsal>=2501 AND vsal<=3500 THEN vmesg:='Salary is OK.'; ELSE vsal:=2501; vmesg:='Have updated your salary to '||TO_CHAR(vsal); END IF; ELSIF vjob='ANALYST' THEN IF vsal>=3501 AND vsal<=4500 THEN vmesg:='Salary is OK.'; ELSE vsal:=3501; vmesg:='Have updated your salary to '||TO_CHAR(vsal); END IF; ELSE IF vsal>=4501 THEN vmesg:='Salary is OK.'; ELSE vsal:=4501; vmesg:='Have updated your salary to '||TO_CHAR(vsal); END IF; END IF; UPDATE emp SET sal=vsal WHERE empno=no; RETURN vmesg; END; / DECLARE vmesg CHAR(50); vempno emp.empno%TYPE; BEGIN vempno:=&empno; vmesg:=Sal_Level(vempno); DBMS_OUTPUT.PUT_LINE(vmesg); END; / --SELECT empno,ename,sal,comm,hiredate FROM emp WHERE empno=:no; Q.编写一个函数以显示该雇员在此组织中的工作天数。 A. CREATE OR REPLACE FUNCTION Hire_Day(no emp.empno%TYPE) RETURN NUMBER AS vhiredate emp.hiredate%TYPE; vday NUMBER; BEGIN SELECT hiredate INTO vhiredate FROM emp WHERE empno=no; vday:=CEIL(SYSDATE-vhiredate); RETURN vday; END; / DECLARE vday NUMBER; vempno emp.empno%TYPE; BEGIN vempno:=&empno; vday:=Hire_Day(vempno); DBMS_OUTPUT.PUT_LINE(vday); END; / --SELECT empno,ename,sal,comm,hiredate FROM emp WHERE empno=:no; Top 3 楼acev（睡眠不足(域名 .com ￥55，.cn ￥30 QQ:230567)）回复于 2003-06-18 17:32:14 得分 0 第五阶段 Q.编写一个数据包，它有两个函数和两个过程以操作“emp”表。 该数据包要执行的任务为： 插入一个新雇员；删除一个现有雇员；显示指定雇员的整体薪水（薪水+佣金）；显示指定雇员所在部门名称。 A. CREATE OR REPLACE PACKAGE emppack AS PROCEDURE insrec(pempno emp.empno%TYPE,pename emp.ename%TYPE, pjob emp.job%TYPE,pmgr emp.mgr%TYPE, phiredate emp.hiredate%TYPE,psal emp.sal%TYPE, pcomm emp.comm%TYPE,pdeptno emp.deptno%TYPE); PROCEDURE delrec(pempno IN NUMBER); FUNCTION selsal(pempno NUMBER) RETURN NUMBER; FUNCTION seldname(pempno NUMBER) RETURN VARCHAR2; END; / CREATE OR REPLACE PACKAGE BODY emppack AS PROCEDURE insrec(pempno emp.empno%TYPE,pename emp.ename%TYPE, pjob emp.job%TYPE,pmgr emp.mgr%TYPE, phiredate emp.hiredate%TYPE,psal emp.sal%TYPE, pcomm emp.comm%TYPE,pdeptno emp.deptno%TYPE) IS BEGIN INSERT INTO emp VALUES(pempno,pename,pjob,pmgr,phiredate, psal,pcomm,pdeptno); DBMS_OUTPUT.PUT_LINE('1 record is created.'); END insrec; PROCEDURE delrec(pempno IN NUMBER) IS BEGIN DELETE FROM emp WHERE empno=pempno; DBMS_OUTPUT.PUT_LINE('1 record is deleted.'); END delrec; FUNCTION selsal(pempno NUMBER) RETURN NUMBER IS vTotalSal NUMBER; BEGIN SELECT NVL(sal,0)+NVL(comm,0) INTO vTotalSal FROM emp WHERE empno=pempno; RETURN vTotalSal; END selsal; FUNCTION seldname(pempno NUMBER) RETURN VARCHAR2 IS vdname dept.dname%TYPE; BEGIN SELECT dname INTO vdname FROM emp,dept WHERE empno=pempno AND emp.deptno=dept.deptno; RETURN vdname; END seldname; END; / --执行包中的过程和函数 EXECUTE emppack.insrec(1111,'Goldens','MANAGER',7698,'2003-01-18',2000,400,30); EXECUTE emppack.delrec(1111); DECLARE salary NUMBER; BEGIN salary:=emppack.selsal(7369); DBMS_OUTPUT.PUT_LINE('Total Salary is '||salary); END; / DECLARE department VARCHAR2(30); BEGIN department:=emppack.seldname(7369); DBMS_OUTPUT.PUT_LINE('Department name is '||department); END; / Q.编写一个数据库触发器以显示当任何时候雇员加薪时的加薪情况。 A. CREATE OR REPLACE TRIGGER emp_SalUp AFTER UPDATE OF sal ON emp FOR EACH ROW DECLARE vsal NUMBER; BEGIN vsal:=NVL(:NEW.sal,0)-NVL(:OLD.sal,0); IF vsal<=0 THEN RAISE_APPLICATION_ERROR(-20001,'Increased Salary is not zero and littler than zero'); END IF; END; / Q.编写一个数据库触发器，它允许用户只在上午9.00到下午5.00之间执行DML任务。 A. CREATE OR REPLACE TRIGGER operate_time_limited BEFORE INSERT OR UPDATE OR DELETE ON emp --FOR EACH ROW DECLARE vtime NUMBER; BEGIN vtime:=TO_NUMBER(TO_CHAR(SYSDATE,'HH24')); IF vtime NOT BETWEEN 9 AND 17 THEN RAISE_APPLICATION_ERROR(-20444,'Sorry!Not Except 9AM and 5PM.'); END IF; END; / Q.编写一个数据为触发器以检查某个组织中不能有两个总裁。 A. CREATE OR REPLACE TRIGGER check_president BEFORE INSERT OR UPDATE ON emp FOR EACH ROW WHEN (UPPER(NEW.job)='PRESIDENT') DECLARE vCount NUMBER; BEGIN SELECT COUNT(job) INTO vCount FROM emp WHERE UPPER(job)='PRESIDENT'; --把总统的个数统计出来，当为0时，变量值为0 IF vCount>0 THEN RAISE_APPLICATION_ERROR(-20444,'Sorry!Can''t have two President.'); END IF; END; / Q.编写一个数据库触发器，当任何时候某个部门从”dept”中删除时，该触发器将从”emp”表中删除该部门的所有雇员。 A. CREATE OR REPLACE TRIGGER del_emp_deptno BEFORE DELETE ON dept FOR EACH ROW BEGIN DELETE FROM emp WHERE deptno=:OLD.deptno; END; /