数据库

本类阅读TOP10

·SQL语句导入导出大全
·SQL Server日期计算
·SQL语句导入导出大全
·SQL to Excel 的应用
·Oracle中password file的作用及说明
·MS SQLServer OLEDB分布式事务无法启动的一般解决方案
·sqlserver2000数据库置疑的解决方法
·一个比较实用的大数据量分页存储过程
·如何在正运行 SQL Server 7.0 的服务器之间传输登录和密码
·SQL中两台服务器间使用连接服务器

分类导航
VC语言Delphi
VB语言ASP
PerlJava
Script数据库
其他语言游戏开发
文件格式网站制作
软件工程.NET开发
几十个实用的PL/SQL(4)

作者:未知 来源:月光软件站 加入时间:2005-2-28 月光软件站

第四阶段

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;




相关文章

相关软件