java-oracle中几十个实用的PL_SQL(4)

文章作者 100test 发表时间 2007:03:26 17:55:05
来源 100Test.Com百考试题网

--插入记录
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.