bsp; 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; /
上一页 [1] [2] [3] 下一页 [办公软件]在sybase中插入图片、PDF、文本文件 [办公软件]安装Sybase ASE [办公软件]linux指令大全(完整篇) [办公软件]Linux新手入门常用命令大全 [办公软件]在RedHat Linux 9里安装gaim0.80 [办公软件]浅谈Linux 下Java 1.5 汉字方块问题解决方法 [办公软件]Linux程序员必读:中文化与GB18030标准 [办公软件]linux指令大全 [办公软件]制作Linux启动盘的四种方法 [办公软件]Linux文件系统的反删除方法
|