PL/SQL初学者必读：几十个实用的PL/SQL-服务器专区

PL/SQL初学者必读：几十个实用的PL/SQL

作者：网络　佚名编辑： IT168 2005-10-27 00:00

　　【IT168 服务器学院】第一阶段

　　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;

　　/

　　

　　

　　第三阶段

　　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;

　　/

　　

　　

　　第四阶段

　　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;

　　

　　

　　第五阶段

　　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;

　　/

关注我们