《几十个实用的plsql》由会员分享，可在线阅读，更多相关《几十个实用的plsql（22页珍藏版）》请在金锄头文库上搜索。
1、几十个实用的几十个实用的 PLSQLPLSQLPL/SQL 初学者必读：几十个实用的 PL/SQL第一阶段Q.编写一个 PL/SQL 程序块以显示所给出雇员编号的雇员的详细信息。A.DECLAREerec emp%ROWTYPE;BEGINSELECT * INTO erec from emp WHERE empno=DBMS_OUTPUT.PUT_LINE(EmpNo | | Ename | | Job | | Manager | | HireDate | | Salary | | Commision | | DeptNo);DBMS_OUTPUT.PUT_LINE(erec.ename |
2、 | erec.job | | erec.mgr | |erec.hiredate | | erec.sal | | m | | erec.deptno);END;/Q.编写一个 PL/SQL 程序块以计算某个雇员的年度薪水总额。A.DECLAREesal NUMBER;eename emp.ename%TYPE;BEGINSELECT (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
3、;/Q.按下列加薪比执行：Deptno Raise(%age)10 5%20 10%30 15%40 20%加薪的百分比是以他们现有的薪水为根据的。写一 PL/SQL 以对指定雇员加薪。A.DECLAREvcounter NUMBER:=10;vraise NUMBER;BEGINLOOPEXIT WHEN vcounter40;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 个新雇
4、员编号。（提示：如果当前最大的雇员编号为 7900，则新雇员编号将为 7901到 7910）A.DECLAREvcounter NUMBER;BEGINSELECT MAX(empno) INTO vcounter from emp;FOR i IN 1.10LOOPvcounter:=vcounter+1;INSERT INTO emp(empno) VALUES(vcounter);END LOOP;END;/Q.只使用一个变量来解决实验课作业 4。ADECLAREerec emp%ROWTYPE;- vraise NUMBER;BEGINSELECT * INTO erecfrom em
5、pWHERE ename=IF erec.job=CLERK THENUPDATE emp SET sal=sal+500 WHERE empno=erec.empno;ELSIF erec.job=SALESMAN THENUPDATE emp SET sal=sal+1000 WHERE empno=erec.empno;ELSIF erec.job=ANALYST THENUPDATE emp SET sal=sal+1500 WHERE empno=erec.empno;ELSEUPDATE emp SET sal=sal+2000 WHERE empno=erec.empno;END
6、 IF;- UPDATE emp SET sal=sal+vraise WHERE empno=erec.empno;- DBMS_OUTPUT.PUT_LINE(vraise);END;/Q.接受两个数相除并且显示结果。如果第二个数为 0，则显示消息“DIVIDE BY ZERO“。A.DECLAREnum1 NUMBER;num2 NUMBER;BEGINnum1:=num2:=DBMS_OUTPUT.PUT_LINE(num1 | / | num2 | is | num1/num2);EXCEPTIONWHEN ZERO_DIVIDE THENDBMS_OUTPUT.PUT_LINE(D
7、idnt your teacher tell you not to DIVIDE BY ZERO?);END;/第二阶段Q.编写一个 PL/SQL 程序块，对名字以“A“或“S“开始的所有雇员按他们的基本薪水的 10%加薪。A.DECLARECURSOR c1 IS SELECT * from emp WHERE SUBSTR(ename,1,1)=A OR SUBSTR(ename,1,1)=S FOR UPDATE OF sal;BEGINFOR i IN c1LOOPUPDATE emp SET sal=NVL(sal,0)+NVL(sal,0)*0.1 WHERE CURRENT OF
8、 c1;END LOOP;END;/Q.编写一 PL/SQL，对所有的“销售员“(SALESMAN)增加佣金 500.A.DECLARECURSOR c1 IS SELECT * from emp WHERE job=SALESMAN FOR UPDATE OF sal;BEGINFOR i IN c1LOOPUPDATE emp SET sal=NVL(sal,0)+500 WHERE CURRENT OF c1;END LOOP;END;/Q.编写一 PL/SQL，以提升两个资格最老的“职员“为“高级职员“。 （工作时间越长，优先级越高）A.DECLARECURSOR c1 IS SELE
9、CT * from emp WHERE job=CLERK ORDER BY hiredate FOR UPDATE OF job; -升序排列，工龄长的在前面BEGINFOR i IN c1LOOPEXIT WHEN c1%ROWCOUNT2;DBMS_OUTPUT.PUT_LINE(i.ename);UPDATE emp SET job=HIGHCLERK WHERE CURRENT OF c1;END LOOP;END;/Q.编写一 PL/SQL，对所有雇员按他们基本薪水的 10%加薪，如果所增加的薪水大于 5000，则取消加薪。A.DECLARECURSOR c1 IS SELECT
10、* from emp FOR UPDATE OF sal; BEGINFOR i IN c1LOOPIF (i.sal+i.sal*0.1)=1 AND vflag=50 AND vflag=11 AND vflag=40 AND vflag60 THENvsal:=NVL(vsal,0)*1.1+3000;ELSEvsal:=NVL(vsal,0)*1.1;END IF;UPDATE emp SET sal=vsal WHERE empno=no;END;/VARIABLE no NUMBERBEGIN:no:=7369;END;/EXECUTE Raise_Sal(:no)SELECT e
11、mpno,ename,sal,comm,hiredate from emp WHERE empno=:no;Q.编写一个函数以检查所指定雇员的薪水是否有效范围内。不同职位的薪水范围为：Designation RaiseClerk 1500-2500Salesman 2501-3500Analyst 3501-4500Others 4501 and above.如果薪水在此范围内，则显示消息“Salary is OK“，否则，更新薪水为该范围内的最水值。A.CREATE OR REPLACE FUNCTION Sal_Level(no emp.empno%TYPE) RETURN CHAR AS
12、vjob emp.job%TYPE;vsal emp.sal%TYPE;vmesg CHAR(50);BEGINSELECT job,sal INTO vjob,vsal from emp WHERE empno=no;IF vjob=CLERK THENIF vsal=1500 AND vsal=2501 AND vsal=3501 AND vsal=4501 THENvmesg:=Salary is OK.;ELSEvsal:=4501;vmesg:=Have updated your salary to |TO_CHAR(vsal);END IF;END IF;UPDATE emp SE
13、T sal=vsal WHERE empno=no;RETURN vmesg;END;/DECLAREvmesg CHAR(50);vempno emp.empno%TYPE;BEGINvempno:=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%TY
14、PE) RETURN NUMBER ASvhiredate emp.hiredate%TYPE;vday NUMBER;BEGINSELECT hiredate INTO vhiredate from emp WHERE empno=no;vday:=CEIL(SYSDATE-vhiredate);RETURN vday;END;/DECLAREvday NUMBER;vempno emp.empno%TYPE;BEGINvempno:=vday:=Hire_Day(vempno);DBMS_OUTPUT.PUT_LINE(vday);END;/-SELECT empno,ename,sal,
15、comm,hiredate from emp WHERE empno=:no;第五阶段Q.编写一个数据包，它有两个函数和两个过程以操作“emp“表。该数据包要执行的任务为：插入一个新雇员；删除一个现有雇员；显示指定雇员的整体薪水（薪水+佣金） ；显示指定雇员所在部门名称。A.CREATE OR REPLACE PACKAGE emppack ASPROCEDURE insrec(pempno emp.empno%TYPE,pename emp.ename%TYPE,pjob emp.job%TYPE,pmgr emp.mgr%TYPE,phiredate emp.hiredate%TYPE,p
16、sal emp.sal%TYPE,pcomm m%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 ASPROCEDURE insrec(pempno emp.empno%TYPE,pename emp.ename%TYPE,pjob emp.job%TYPE,pmgr emp.mg
