幾十個實用的PL/SQL
第一階段
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;
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;
