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