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

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

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 0updated 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 0updated 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 0updated your salary to ´.||TO_CHAR(vsal).
END IF.
ELSE
IF vsal>=4501 THEN
vmesg:=´.Salary is OK.´..
ELSE
vsal:=4501.
vmesg:=´.Have 0updated 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.
/