实用的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;


第五阶段
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;
/
----8i下通过。资料来自ACCP

 

 
 

对该文的评论 人气：4012
      lyhold (2003-7-16 10:26:29)

学习
 
      yangzhenhai (2003-7-16 8:48:19)

这也要学？？？软件学院教这个？？？
 
      cwf555 (2003-7-14 14:48:58)

请ACCP的相关人员注意。 Q编写一个数据为触发器以检查某个组织中不能有两个总裁。的答案有问题？如果数据中有一位PRESIDENT，即WHERE UPPER(job)='PRESIDENT' 条件满足，此时修改这条记录的其他信息(如PAY)，会不会运行该触发器？
 
      huwenbo518 (2003-7-13 22:46:20)

这是从书上抄的吧。
 
      icomehere (2003-7-13 17:04:56)

极速开发平台，智能WEB报表平台,关注 http://winabs.nease.net/software.htm
 
      thefirstuser (2003-7-13 13:05:56)

“加薪的百分比是以他们现有的薪水为根据的。写一PL/SQL以对指定雇员加薪”的答案好像有点问题吧？！我也是青鸟本部第二学年的学员，有空的话大家可多多讨论啊！邮箱是[e-mail] 我是08班的，你了？


Oracle 存储过程返回结果集    microchu（收藏）
 
关键字     oracle、存储过程、结果集
 


1.返回数组 (作者:enhydraboy(乱舞的浮尘) )

在oracle后台创建一个程序包或者存储过程
connect scott/tiger;

CREATE OR REPLACE PACKAGE ado_callpkg AS
TYPE eid IS TABLE of NUMBER(4) INDEX BY BINARY_INTEGER;
TYPE ename IS TABLE of VARCHAR2(40) INDEX BY BINARY_INTEGER;
PROCEDURE getEmpNames (empid OUT eid,empname OUT ename);
end ado_callpkg;


CREATE OR REPLACE PACKAGE BODY ado_callpkg  AS
PROCEDURE getEmpNames (empid OUT eid,empname OUT ename) IS
CURSOR c1 IS select employee_id,first_name||','||Middle_Initial||','||last_name as name from employee;
cnt NUMBER DEFAULT 1;
c c1%ROWTYPE;
BEGIN
open c1;
LOOP
      FETCH c1 INTO c;
      empname(cnt):=c.name;
      empid(cnt):=c.employee_id;
      EXIT WHEN c1%NOTFOUND;  -- process the data
       cnt :=cnt+1;
  END LOOP;
close c1;
END;
end ado_callpkg;

2 前台vb程序调用

 Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim cmd As New ADODB.Command
    Dim str As String
   
    str = "{call ado_callpkg.getEmpNames({resultset 100,empid,empname})}"
    cn.Open "Provider=MSDAORA.1;Password=tiger;User ID=scott;Data Source=ORACLE;Persist Security Info=True"
    With cmd
        .CommandText = str
        .ActiveConnection = cn
        .CommandType = adCmdText
    End With
   
    rs.CursorLocation = adUseClient
    rs.Open cmd
    Do While Not rs.EOF
   
        Debug.Print rs.Fields(0).Value & vbTab & rs.Fields(1).Value
        rs.MoveNext
    Loop

------------

总结
1 oracle的后台存储过程，应该通过一个类似数组并且带有数字索引的变量返回，有多少个列，就有对应多少个变量
2 前台，调用的sql语句写法要注意，
{call <package_name>.<prodecure name>(<input1>,<input2>,....<inputn>,{resultset <number>,<output1>,<output2>,...<outputn>})}
注意的细节，
(1) <number>要自己指定一个数字，表示接受的行数大小，如果太小，而实际返回的记录大于这个数字，会出错
(2) 如果有输入参数，应该在command中创建输入参数,对应的地方用?替代，如
{call ado_callpkg.getEmpNames(?,{resultset 100,empid,empname})}
(3) output和你存储函数的定义一致，参数名要一样，次序也一样，否则也会出错。