第一階段
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;
/
第二階段
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;
BE
