PL/SQL初学者必读：几十个实用的PL/SQL。

Question

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

Answer 1

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

Answer 2

第五阶段
Q.编写一个数据包，它有两个函数和两个过程以操作“emp”表。
  该数据包要执行的任务为：
   插入一个新雇员；删除一个现有雇员；显示指定雇员的整体薪水（薪水+佣金）；显示指定雇员所在部门名称。
A.
CREATE OR REPLACE PACKAGE emppack AS
PROCEDURE insrec(pempno emp.empno%TYPE,pename emp.ename%TYPE,
 pjob emp.job%TYPE,pmgr emp.mgr%TYPE,
 phiredate emp.hiredate%TYPE,psal emp.sal%TYPE,
 pcomm emp.comm%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 AS
PROCEDURE insrec(pempno emp.empno%TYPE,pename emp.ename%TYPE,
 pjob emp.job%TYPE,pmgr emp.mgr%TYPE,
 phiredate emp.hiredate%TYPE,psal emp.sal%TYPE,
 pcomm emp.comm%TYPE,pdeptno emp.deptno%TYPE)
IS
BEGIN
INSERT INTO emp VALUES(pempno,pename,pjob,pmgr,phiredate,
psal,pcomm,pdeptno);
DBMS_OUTPUT.PUT_LINE('1 record is created.');
END insrec;
PROCEDURE delrec(pempno IN NUMBER)
IS
BEGIN
DELETE FROM emp WHERE empno=pempno;
DBMS_OUTPUT.PUT_LINE('1 record is deleted.');
END delrec;
FUNCTION selsal(pempno NUMBER) RETURN NUMBER
IS
vTotalSal NUMBER;
BEGIN
SELECT NVL(sal,0)+NVL(comm,0) INTO vTotalSal
FROM emp
WHERE empno=pempno;
RETURN vTotalSal;
END selsal;
FUNCTION seldname(pempno NUMBER) RETURN VARCHAR2
IS
vdname dept.dname%TYPE;
BEGIN
SELECT dname INTO vdname 
FROM emp,dept
WHERE empno=pempno AND emp.deptno=dept.deptno;
RETURN vdname;
END seldname;
END;
/--执行包中的过程和函数
EXECUTE emppack.insrec(1111,'Goldens','MANAGER',7698,'2003-01-18',2000,400,30);
EXECUTE emppack.delrec(1111);DECLARE
salary NUMBER;
BEGIN
salary:=emppack.selsal(7369);
DBMS_OUTPUT.PUT_LINE('Total Salary is '||salary);
END;
/
DECLARE
department VARCHAR2(30);
BEGIN
department:=emppack.seldname(7369);
DBMS_OUTPUT.PUT_LINE('Department name is '||department);
END;
/Q.编写一个数据库触发器以显示当任何时候雇员加薪时的加薪情况。
A.
CREATE OR REPLACE TRIGGER emp_SalUp
AFTER UPDATE OF sal ON emp
FOR EACH ROW
DECLARE
vsal NUMBER;
BEGIN
vsal:=NVL(:NEW.sal,0)-NVL(:OLD.sal,0);
IF vsal<=0 THEN
RAISE_APPLICATION_ERROR(-20001,'Increased Salary is not zero and littler than zero');
END IF;
END;
/
Q.编写一个数据库触发器，它允许用户只在上午9.00到下午5.00之间执行DML任务。
A.
CREATE OR REPLACE TRIGGER operate_time_limited
BEFORE INSERT OR UPDATE OR DELETE ON emp
--FOR EACH ROW
DECLARE
vtime NUMBER;
BEGIN
vtime:=TO_NUMBER(TO_CHAR(SYSDATE,'HH24'));
IF vtime NOT BETWEEN 9 AND 17 THEN
RAISE_APPLICATION_ERROR(-20444,'Sorry!Not Except 9AM and 5PM.');
END IF;
END;
/
Q.编写一个数据为触发器以检查某个组织中不能有两个总裁。
A.
CREATE OR REPLACE TRIGGER check_president
BEFORE INSERT OR UPDATE ON emp
FOR EACH ROW
WHEN (UPPER(NEW.job)='PRESIDENT')
DECLARE
vCount NUMBER;
BEGIN
SELECT COUNT(job) INTO vCount
FROM emp
WHERE UPPER(job)='PRESIDENT';  --把总统的个数统计出来，当为0时，变量值为0

IF vCount>0 THEN
RAISE_APPLICATION_ERROR(-20444,'Sorry!Can''t have two President.');
END IF;
END;
/
Q.编写一个数据库触发器，当任何时候某个部门从”dept”中删除时，该触发器将从”emp”表中删除该部门的所有雇员。
A.
CREATE OR REPLACE TRIGGER del_emp_deptno
BEFORE DELETE ON dept
FOR EACH ROW
BEGIN
DELETE FROM emp WHERE deptno=:OLD.deptno;
END;
/

Answer 3

呵呵
老大,我也是学ACCP的
马上就要学PL/SQL了
谢谢

Answer 4

写在上面不太好看，以上资料存成doc文档，有谁要的请留下email，我发给大家。UP!

Answer 5

TO  power_zh(编程编程,一编就成!) 你在哪里学的？（我在深圳）

Answer 6

[email protected]
在此先谢过了！

Answer 7

[email protected] ,
谢谢，给我发一个

Answer 8

劳驾斑竹.
[email protected] 
谢谢！

Answer 9

Thanks
[email protected]
Please

Answer 10

TO xujie(gto) 你的发不了？/////////
> 日期: Fri, 20 Jun 2003 11:47:19 +0800 (CST)
> 主题: PL/SQL资料
> 大小: 101388 bytes 字节
> 动作: 失败没有能够发送到以下的收件人:550 Sorry,local database error (RCPT TO: [email protected] )不会再有任何动作来尝试发送你的邮件了。 请联系你的系统管理员或先通过其它非电子邮件的方式向你的朋友发送信息以免耽误。

Answer 11

给一份了，有好东西大家共享吗。
[email protected]
[email protected]

Answer 12

[email protected]
谢谢，给我发一个

Answer 13

[email protected]
謝謝樓主

Answer 14

谢谢了！
[email protected]

Answer 15

to  Nightlyie() :发送不了。
554 Mailbox unavailable. (RCPT TO: [email protected] )其他已发，注意查收。^_^,

Answer 16

[email protected]
thank you 
I love!

Answer 17

TO hnews(抓哇) :你也在这,你更要加油呀.

Answer 18

[email protected]
[email protected]
thank you!

Answer 19

[email protected]
给俺来一份吧

Answer 20

[email protected]
Good！ Thank you1！

Answer 21

[email protected]
thanks a lot