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

逃离我推掉我的手 2022-09-20 08:41 164阅读 0赞

第一阶段  
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;